---
# System prepended metadata

title: Window functions
tags: [' yoctol', ' workshop', postgresql]

---

---
title: Window functions
tags: postgresql, yoctol, workshop
---

# Window functions

https://www.postgresql.org/docs/13/functions-window.html

## Syntax
 The syntax of a window function call is one of the following:
```sql
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
```
where window_definition has the syntax
```sql
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
```
The optional frame_clause can be one of
```sql
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
```
where frame_start and frame_end can be one of
```sql
UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING
```
and frame_exclusion can be one of
```sql
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
```

## setup
```sql
CREATE TABLE product_groups (
	group_id serial PRIMARY KEY,
	group_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
	product_id serial PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	price DECIMAL (11, 2),
	group_id INT NOT NULL,
	FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
);

INSERT INTO product_groups (group_name)
VALUES
	('Smartphone'),
	('Laptop'),
	('Tablet');

INSERT INTO products (product_name, group_id, price)
VALUES
	('Microsoft Lumia', 1, 200),
	('HTC One', 1, 400),
	('Nexus', 1, 500),
	('iPhone', 1, 900),
	('HP Elite', 2, 1200),
	('Lenovo Thinkpad', 2, 700),
	('Sony VAIO', 2, 700),
	('Dell Vostro', 2, 800),
	('iPad', 3, 700),
	('Kindle Fire', 3, 150),
	('Samsung Galaxy Tab', 3, 200);
```

## row_number ()

```sql
SELECT
	row_number () over (PARTITION by price),
	*
FROM
	products;
```


## rank ()

```sql
SELECT
	rank () over (PARTITION by group_id order by price),
	*
FROM
	products;
```

## dense_rank ()

```sql
SELECT
	dense_rank () over (PARTITION by group_id order by price),
	*
FROM
	products;
```

##  percent_rank ()

```sql
SELECT
	 percent_rank () over (PARTITION by group_id order by price),
	*
FROM
	products;
```

## cume_dist ()
https://zh.wikipedia.org/wiki/%E7%B4%AF%E7%A7%AF%E5%88%86%E5%B8%83%E5%87%BD%E6%95%B0

```sql
SELECT
	cume_dist () OVER (PARTITION BY group_id ORDER BY price),
	*
FROM
	products;
```

## ntile ()

```sql
SELECT
	 ntile (2) OVER (PARTITION BY group_id),
	*
FROM
	products;
```

```sql
SELECT
	 ntile (2) OVER (PARTITION BY price),
	*
FROM
	products;
```

## lag ()
```sql
SELECT
	  lag (price, 1) OVER (PARTITION BY group_id order by price),
	*
FROM
	products;
```

```sql
SELECT
	product_name,
	group_name,
	price,
	price - LAG (price, 1) OVER (
		PARTITION BY group_name
		ORDER BY
			price
	) AS price_diff
FROM
	products
INNER JOIN product_groups USING (group_id);
```

##  lead ()
```sql
SELECT
	  lead (price, 1) OVER (PARTITION BY group_id order by price),
	*
FROM
	products;
```

##  first_value ()  last_value ()
```sql
SELECT
	  first_value (price) OVER (PARTITION BY group_id order by price),
	*
FROM
	products;
```

##  nth_value ()
```sql
SELECT
	  nth_value (price, 3) OVER (PARTITION BY group_id order by price),
	*
FROM
	products;
```

refs:
https://www.postgresqltutorial.com/postgresql-window-function/

https://www.2ndquadrant.com/en/blog/window-functions-time-series-iot-analytics-postgres-bdr/

https://blog.jooq.org/2015/05/12/use-this-neat-window-function-trick-to-calculate-time-differences-in-a-time-series/

https://www.maxlist.xyz/2020/06/05/postgresql-interview-questions/#%E4%BA%8C_%E9%80%9A%E7%94%A8%E8%A1%A8%E9%81%94%E5%BC%8F_Common_Table_Expressions