SQL Window Functions Cheat Sheet and Reference

张凡

872

2页

0次

2020-06-04

5墨值下载

SQL Window Functions Cheat Sheet

month city sold

1 Rome 200

2 Paris 500

1 London 100

1 Paris 300

2 Rome 300

2 London 400

3 Rome 400

Abbreviation Meaning

UNBOUNDED PRECEDING BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

n PRECEDING BETWEEN n PRECEDING AND CURRENT ROW

CURRENT ROW BETWEEN CURRENT ROW AND CURRENT ROW

n FOLLOWING BETWEEN AND CURRENT ROW AND n FOLLOWING

UNBOUNDED FOLLOWING BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

sold city month

200 Rome 1

500 Paris 2

100 London 1

300 Paris 1

300 Rome 2

400 London 2

400 Rome 3

sold city month

300 Paris 1

500 Paris 2

200 Rome 1

300 Rome 2

400 Rome 3

100 London 1

400 London 2

WINDOW FUNCTIONS

compute their result based on a sliding

window frame, a set of rows that are

somehow related to the current row.

PARTITION BY

divides rows into multiple groups, called partitions, to

which the window function is applied.

WINDOW FRAME

is a set of rows that are somehow related to the current row. The window frame is evaluated separately within each partition.

ABBREVIATIONS DEFAULT WINDOW FRAME

ROWS | RANGE | GROUPS BETWEEN lower_bound AND upper_bound

ORDER BY

specifies the order of rows in each partition to which the

window function is applied.

LOGICAL ORDER OF OPERATIONS IN SQL

SYNTAX

Named Window Definition

AGGREGATE FUNCTIONS VS. WINDOW FUNCTIONS

unlike aggregate functions, window functions do not collapse rows.

PARTITION BY, ORDER B Y, and window frame definition are all optional.

Default Partition: with no PARTITION BY clause, the

entire result set is the partition.

As of 2020, GROUPS is only supported in PostgreSQL 11 and up.

PARTITION BY city PARTITION BY city ORDER BY month

Default ORDER BY: with no ORDER BY clause, the order of

rows within each partition is arbitrary.

If ORDER BY is specified, then the frame is

RANGE BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW.

Without ORDER B Y, the frame specification

is ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING.

You can use window functions in SELECT and ORDER BY. However, you can’t put window functions

anywhere in the FROM, WHERE, GROUP BY, or HAVING clauses.

SELECT city, month,

sum(sold) OVER (

PARTITION BY city

ORDER BY month

RANGE UNBOUNDED PRECEDING) total

FROM sales;

SELECT co u n t r y, c it y,

rank() OVER country_sold_avg

FROM sales

WHERE month BETWEEN 1 AND 6

GROUP BY country, city

HAVING sum(sold) > 10000

WINDOW country_sold_avg AS (

PARTITION BY country

ORDER BY avg(sold) DESC)

ORDER BY country, city;

1. FROM, JOIN

2. WHERE

3. GROUP BY

4. aggregate functions

5. HAVING

6. window functions

7. S E L E C T

8. DISTINCT

9. UNION/INTERSECT/EXCEPT

10. ORDER BY

11. OFFSET

12. LIMIT/FETCH/TOP

SELECT <column_1>, <column_2>,

<window_function>() OVER (

PARTITION BY <...>

ORDER BY <...>

<window_frame>) <window_column_alias>

FROM <ta ble _ n a m e>;

SELECT <column_1>, <column_2>,

<window_function>() OVER <window_name>

FROM <table_name>

WHERE <...>

GROUP BY <...>

HAVING <...>

WINDOW <window_name> AS (

PARTITION BY <...>

ORDER BY <...>

<window_frame>)

ORDER BY <...>;

current

row

∑ ∑

∑

Aggregate Functions Window Functions

month city sold sum

1 Paris 300 800

2 Paris 500 800

1 Rome 200 900

2 Rome 300 900

3 Rome 400 900

1 London 100 500

2 London 400 500

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

ROWS BETWEEN 1 PRECEDING

AND 1 FOLLOWING

1 row before the current row and

1 row aer the current row

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

RANGE BETWEEN 1 PRECEDING

AND 1 FOLLOWING

values in the range between 3 and 5

ORDER BY must contain a single expression

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

GROUPS BETWEEN 1 PRECEDING

AND 1 FOLLOWING

1 group before the current row and 1 group

aer the current row regardless of the value

PARTITION UNBOUNDED

PRECEDING

UNBOUNDED

FOLLOWING

N PRECEDING

M FOLLOWING

N ROWS

M ROWS

The bounds can be any of the five options:

∙ UNBOUNDED PRECEDING

∙ n PRECEDING

∙ CURRENT ROW

∙ n FOLLOWING

∙ UNBOUNDED FOLLOWING

The lower_bound must be BEFORE the upper_bound

current

row

current

row

current

row

CURRENT

ROW

Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

SQL Window Functions Cheat Sheet

Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

city price

row_number rank dense_rank

over(order by price)

Paris 7 1 1 1

Rome 7 2 1 1

London 8.5 3 3 2

Berlin 8.5 4 3 2

Moscow 9 5 5 3

Madrid 10 6 6 4

Oslo 10 7 6 4

LIST OF WINDOW

FUNCTIONS

AGGREGATE FUNCTIONS

RANKING FUNCTIONS

∙ row_number() − unique number for each row within partition, with dierent

numbers for tied values

∙ rank() − ranking within partition, with gaps and same ranking for tied values

∙ dense_rank() − ranking within partition, with no gaps and same ranking for tied values

ANALYTIC FUNCTIONS

∙ lead(expr, oset, default) − the value for the row oset rows aer the current; oset

and default are optional; default values: oset = 1, default = NULL

∙ lag(expr, oset, default) − the value for the row oset rows before the current; oset

and default are optional; default values: oset = 1, default = NULL

∙ nth_value(expr, n) − the value for the n-th row within the window frame; n must be an integer ∙ ntile(n) − divide rows within a partition as equally as possible into n groups, and assign

each row its group number.

∙ rst_value(expr) − the value for the first row within the window frame

∙ last_value(expr) − the value for the last row within the window frame

DISTRIBUTION FUNCTIONS

∙ percent_rank() − the percentile ranking number of a row—a value in [0, 1] interval:

(rank - 1) / (total number of rows - 1)

∙ cume_dist() − the cumulative distribution of a value within a group of values, i.e., the number of

rows with values less than or equal to the current row’s value divided by the total number of rows;

a value in (0, 1] interval

ORDER BY and Window Frame: rank() and dense_rank() require ORDER BY, but

row_number() does not require ORDER BY. Ranking functions do not accept window

frame definition (RO WS, R ANGE, GROUPS).

ORDER BY and Window Frame: rst_value(),

last_value(), and nth_value() do not

require an ORDER BY. They accept window frame

definition (ROWS, RANGE, GROUPS).

ORDER BY and Window Frame: ntile(),

l e a d(), and lag() require an ORDER BY.

They do not accept window frame definition

(ROWS, R ANGE, GROUPS).

ORDER BY and Window Frame:

Aggregate functions do not

require an ORDER BY. They accept

window frame definition (ROWS,

RANGE, GROUPS).

Note: You usually want to use RANGE BETWEEN

UNBOUNDED PRECEDING AND UNBOUNDED

FOLLOWING with last_value(). With the default

window frame for ORDER BY, RANGE UNBOUNDED

PRECEDING, last_value() returns the value for

the current row.

ORDER BY and Window Frame: Distribution functions require ORDER BY. They do not accept window frame

definition (ROWS, RANGE, GROUPS).

Aggregate Functions

∙ avg()

∙ count()

∙ max()

∙ min()

∙ sum()

Ranking Functions

∙ row_number()

∙ rank()

∙ dense_rank()

Distribution Functions

∙ percent_rank()

∙ cume_dist()

Analytic Functions

∙ lead()

∙ lag()

∙ ntile()

∙ rst_value()

∙ last_value()

∙ nth_value()

∙ avg(expr) − average value for

rows within the window frame

∙ count(expr) − count of values

for rows within the window

frame

∙ m a x (expr) − maximum value

within the window frame

∙ min(expr) − minimum value

within the window frame

∙ sum(expr) − sum of values

within the window frame

month sold

1 500

2 300

3 400

4 100

5 500

NULL

500

300

400

100

lag(sold) OVER(ORDER BY month)

order by month

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

first_value

500

200

rst_value(sold) OVER

(PARTITION BY city ORDER BY month)

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

last_value

400

500

last_value(sold) OVER

(PARTITION BY city ORDER BY month

RANGE BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

month sold

1 500

2 300

3 400

4 100

5 500

300

400

100

500

NULL

lead(sold) OVER(ORDER BY month)

order by month

city sold cume_dist

Paris 100 0.2

Berlin 150 0.4

Rome 200 0.8

Moscow 200 0.8

London 300 1

80% of values are

less than or equal

to this one

cume_dist() OVER(ORDER BY sold)

city sold percent_rank

Paris 100 0

Berlin 150 0.25

Rome 200 0.5

Moscow 200 0.5

London 300 1

without this row 50% of

values are less than this

row’s value

percent_rank() OVER(ORDER BY sold)

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

Rome 5 300

London 1 100

nth_value

300

NULL

nth_value(sold, 2) OVER

(PARTITION BY city ORDER BY month)

month sold

1 500

2 300

3 400

4 100

5 500

500

300

400

lag(sold, 2, 0) OVER(ORDER BY month)

order by month

oset=2

month sold

1 500

2 300

3 400

4 100

5 500

400

100

500

lead(sold, 2, 0) OVER(ORDER BY month)

order by month

oset=2

city sold

Rome 100

Paris 100

London 200

Moscow 200

Berlin 200

Madrid 300

Oslo 300

Dublin 300

ntile(3)

SQL Window Functions Cheat Sheet

month city sold

1 Rome 200

2 Paris 500

1 London 100

1 Paris 300

2 Rome 300

2 London 400

3 Rome 400

Abbreviation Meaning

UNBOUNDED PRECEDING BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

n PRECEDING BETWEEN n PRECEDING AND CURRENT ROW

CURRENT ROW BETWEEN CURRENT ROW AND CURRENT ROW

n FOLLOWING BETWEEN AND CURRENT ROW AND n FOLLOWING

UNBOUNDED FOLLOWING BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

sold city month

200 Rome 1

500 Paris 2

100 London 1

300 Paris 1

300 Rome 2

400 London 2

400 Rome 3

sold city month

300 Paris 1

500 Paris 2

200 Rome 1

300 Rome 2

400 Rome 3

100 London 1

400 London 2

WINDOW FUNCTIONS

compute their result based on a sliding

window frame, a set of rows that are

somehow related to the current row.

PARTITION BY

divides rows into multiple groups, called partitions, to

which the window function is applied.

WINDOW FRAME

is a set of rows that are somehow related to the current row. The window frame is evaluated separately within each partition.

ABBREVIATIONS DEFAULT WINDOW FRAME

ROWS | RANGE | GROUPS BETWEEN lower_bound AND upper_bound

ORDER BY

specifies the order of rows in each partition to which the

window function is applied.

LOGICAL ORDER OF OPERATIONS IN SQL

SYNTAX

Named Window Definition

AGGREGATE FUNCTIONS VS. WINDOW FUNCTIONS

unlike aggregate functions, window functions do not collapse rows.

PARTITION BY, ORDER B Y, and window frame definition are all optional.

Default Partition: with no PARTITION BY clause, the

entire result set is the partition.

As of 2020, GROUPS is only supported in PostgreSQL 11 and up.

PARTITION BY city PARTITION BY city ORDER BY month

Default ORDER BY: with no ORDER BY clause, the order of

rows within each partition is arbitrary.

If ORDER BY is specified, then the frame is

RANGE BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW.

Without ORDER B Y, the frame specification

is ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING.

You can use window functions in SELECT and ORDER BY. However, you can’t put window functions

anywhere in the FROM, WHERE, GROUP BY, or HAVING clauses.

SELECT city, month,

sum(sold) OVER (

PARTITION BY city

ORDER BY month

RANGE UNBOUNDED PRECEDING) total

FROM sales;

SELECT co u n t r y, c it y,

rank() OVER country_sold_avg

FROM sales

WHERE month BETWEEN 1 AND 6

GROUP BY country, city

HAVING sum(sold) > 10000

WINDOW country_sold_avg AS (

PARTITION BY country

ORDER BY avg(sold) DESC)

ORDER BY country, city;

1. FROM, JOIN

2. WHERE

3. GROUP BY

4. aggregate functions

5. HAVING

6. window functions

7. S E L E C T

8. DISTINCT

9. UNION/INTERSECT/EXCEPT

10. ORDER BY

11. OFFSET

12. LIMIT/FETCH/TOP

SELECT <column_1>, <column_2>,

<window_function>() OVER (

PARTITION BY <...>

ORDER BY <...>

<window_frame>) <window_column_alias>

FROM <ta ble _ n a m e>;

SELECT <column_1>, <column_2>,

<window_function>() OVER <window_name>

FROM <table_name>

WHERE <...>

GROUP BY <...>

HAVING <...>

WINDOW <window_name> AS (

PARTITION BY <...>

ORDER BY <...>

<window_frame>)

ORDER BY <...>;

current

row

∑ ∑

∑

Aggregate Functions Window Functions

month city sold sum

1 Paris 300 800

2 Paris 500 800

1 Rome 200 900

2 Rome 300 900

3 Rome 400 900

1 London 100 500

2 London 400 500

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

ROWS BETWEEN 1 PRECEDING

AND 1 FOLLOWING

1 row before the current row and

1 row aer the current row

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

RANGE BETWEEN 1 PRECEDING

AND 1 FOLLOWING

values in the range between 3 and 5

ORDER BY must contain a single expression

city sold month

Paris 300 1

Rome 200 1

Paris 500 2

Rome 100 4

Paris 200 4

Paris 300 5

Rome 200 5

London 200 5

London 100 6

Rome 300 6

GROUPS BETWEEN 1 PRECEDING

AND 1 FOLLOWING

1 group before the current row and 1 group

aer the current row regardless of the value

PARTITION UNBOUNDED

PRECEDING

UNBOUNDED

FOLLOWING

N PRECEDING

M FOLLOWING

N ROWS

M ROWS

The bounds can be any of the five options:

∙ UNBOUNDED PRECEDING

∙ n PRECEDING

∙ CURRENT ROW

∙ n FOLLOWING

∙ UNBOUNDED FOLLOWING

The lower_bound must be BEFORE the upper_bound

current

row

current

row

current

row

CURRENT

ROW

Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

SQL Window Functions Cheat Sheet

Try out the interactive Window Functions course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

city price

row_number rank dense_rank

over(order by price)

Paris 7 1 1 1

Rome 7 2 1 1

London 8.5 3 3 2

Berlin 8.5 4 3 2

Moscow 9 5 5 3

Madrid 10 6 6 4

Oslo 10 7 6 4

LIST OF WINDOW

FUNCTIONS

AGGREGATE FUNCTIONS

RANKING FUNCTIONS

∙ row_number() − unique number for each row within partition, with dierent

numbers for tied values

∙ rank() − ranking within partition, with gaps and same ranking for tied values

∙ dense_rank() − ranking within partition, with no gaps and same ranking for tied values

ANALYTIC FUNCTIONS

∙ lead(expr, oset, default) − the value for the row oset rows aer the current; oset

and default are optional; default values: oset = 1, default = NULL

∙ lag(expr, oset, default) − the value for the row oset rows before the current; oset

and default are optional; default values: oset = 1, default = NULL

∙ nth_value(expr, n) − the value for the n-th row within the window frame; n must be an integer ∙ ntile(n) − divide rows within a partition as equally as possible into n groups, and assign

each row its group number.

∙ rst_value(expr) − the value for the first row within the window frame

∙ last_value(expr) − the value for the last row within the window frame

DISTRIBUTION FUNCTIONS

∙ percent_rank() − the percentile ranking number of a row—a value in [0, 1] interval:

(rank - 1) / (total number of rows - 1)

∙ cume_dist() − the cumulative distribution of a value within a group of values, i.e., the number of

rows with values less than or equal to the current row’s value divided by the total number of rows;

a value in (0, 1] interval

ORDER BY and Window Frame: rank() and dense_rank() require ORDER BY, but

row_number() does not require ORDER BY. Ranking functions do not accept window

frame definition (RO WS, R ANGE, GROUPS).

ORDER BY and Window Frame: rst_value(),

last_value(), and nth_value() do not

require an ORDER BY. They accept window frame

definition (ROWS, RANGE, GROUPS).

ORDER BY and Window Frame: ntile(),

l e a d(), and lag() require an ORDER BY.

They do not accept window frame definition

(ROWS, R ANGE, GROUPS).

ORDER BY and Window Frame:

Aggregate functions do not

require an ORDER BY. They accept

window frame definition (ROWS,

RANGE, GROUPS).

Note: You usually want to use RANGE BETWEEN

UNBOUNDED PRECEDING AND UNBOUNDED

FOLLOWING with last_value(). With the default

window frame for ORDER BY, RANGE UNBOUNDED

PRECEDING, last_value() returns the value for

the current row.

ORDER BY and Window Frame: Distribution functions require ORDER BY. They do not accept window frame

definition (ROWS, RANGE, GROUPS).

Aggregate Functions

∙ avg()

∙ count()

∙ max()

∙ min()

∙ sum()

Ranking Functions

∙ row_number()

∙ rank()

∙ dense_rank()

Distribution Functions

∙ percent_rank()

∙ cume_dist()

Analytic Functions

∙ lead()

∙ lag()

∙ ntile()

∙ rst_value()

∙ last_value()

∙ nth_value()

∙ avg(expr) − average value for

rows within the window frame

∙ count(expr) − count of values

for rows within the window

frame

∙ m a x (expr) − maximum value

within the window frame

∙ min(expr) − minimum value

within the window frame

∙ sum(expr) − sum of values

within the window frame

month sold

1 500

2 300

3 400

4 100

5 500

NULL

500

300

400

100

lag(sold) OVER(ORDER BY month)

order by month

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

first_value

500

200

rst_value(sold) OVER

(PARTITION BY city ORDER BY month)

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

last_value

400

500

last_value(sold) OVER

(PARTITION BY city ORDER BY month

RANGE BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

month sold

1 500

2 300

3 400

4 100

5 500

300

400

100

500

NULL

lead(sold) OVER(ORDER BY month)

order by month

city sold cume_dist

Paris 100 0.2

Berlin 150 0.4

Rome 200 0.8

Moscow 200 0.8

London 300 1

80% of values are

less than or equal

to this one

cume_dist() OVER(ORDER BY sold)

city sold percent_rank

Paris 100 0

Berlin 150 0.25

Rome 200 0.5

Moscow 200 0.5

London 300 1

without this row 50% of

values are less than this

row’s value

percent_rank() OVER(ORDER BY sold)

city month sold

Paris 1 500

Paris 2 300

Paris 3 400

Rome 2 200

Rome 3 300

Rome 4 500

Rome 5 300

London 1 100

nth_value

300

NULL

nth_value(sold, 2) OVER

(PARTITION BY city ORDER BY month)

month sold

1 500

2 300

3 400

4 100

5 500

500

300

400

lag(sold, 2, 0) OVER(ORDER BY month)

order by month

oset=2

month sold

1 500

2 300

3 400

4 100

5 500

400

100

500

lead(sold, 2, 0) OVER(ORDER BY month)

order by month

oset=2

city sold

Rome 100

Paris 100

London 200

Moscow 200

Berlin 200

Madrid 300

Oslo 300

Dublin 300

ntile(3)

of 2

5墨值下载

mysql

关注

评论