---
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
Sign in with Wallet
Connect another wallet