SQL window functions are a powerful tool for data analysis and manipulation. They allow you to perform calculations on a subset of rows within a larger result set, without affecting the overall query results. This makes it easier to perform complex calculations and analysis on your data, without having to write multiple queries or use temporary tables.
Window functions are similar to aggregate functions, but they operate on a “”window”” of rows rather than the entire result set. This window can be defined using the OVER clause, which specifies the partitioning and ordering of the rows within the window.
Some common window functions include:
- ROW_NUMBER(): assigns a unique number to each row within the window
- RANK(): assigns a rank to each row based on its value within the window
- DENSE_RANK(): assigns a rank to each row, but with no gaps between ranks
- LAG() and LEAD(): retrieve the value of a column from a previous or next row within the window
- SUM(), AVG(), MIN(), MAX(): perform aggregate calculations on the values within the window
This cheat sheet provides an overview of SQL window functions, including their syntax and usage.
Aggregate Functions
| Function | Description |
|---|---|
AVG() | Calculates the average value of a column within a window. |
COUNT() | Counts the number of rows within a window. |
MAX() | Returns the maximum value of a column within a window. |
MIN() | Returns the minimum value of a column within a window. |
SUM() | Calculates the sum of a column within a window. |
Ranking Functions
| Function | Description |
|---|---|
DENSE_RANK() | Assigns a rank to each row within a window, with no gaps in the ranking sequence. |
RANK() | Assigns a rank to each row within a window, with gaps in the ranking sequence. |
ROW_NUMBER() | Assigns a unique number to each row within a window. |
Analytic Functions
| Function | Description |
|---|---|
FIRST_VALUE() | Returns the first value of a column within a window. |
LAST_VALUE() | Returns the last value of a column within a window. |
LEAD() | Returns the value of a column in the next row within a window. |
LAG() | Returns the value of a column in the previous row within a window. |
NTILE() | Divides the rows within a window into a specified number of groups. |
PERCENT_RANK() | Calculates the relative rank of each row within a window. |
RATIO_TO_REPORT() | Calculates the ratio of a value to the sum of values within a window. |
Window Specification
| Function | Description |
|---|---|
OVER() | Specifies the window over which a function is applied. |
PARTITION BY | Divides the rows within a window into partitions based on one or more columns. |
ORDER BY | Sorts the rows within a window based on one or more columns. |
ROWS BETWEEN | Specifies the range of rows within a window to include in a calculation. |
Example Usage
SELECT
AVG(sales) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg,
RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
FROM sales_data;This query calculates the moving average of sales for each region, based on the previous two months and the current month. It also assigns a rank to each region based on their sales, with ties receiving the same rank.