SQL Window functions
TLDR: Show other row data within each row
SQL window functions perform calculations across a set of table rows related to the current row, without collapsing the results into groups like aggregate functions.
They allow you to rank, sum, average, or access data from other rows while retaining the original row structure. Window functions are defined using the OVER() clause, which specifies the window or "frame" of rows to consider.
Common window functions include:
- ROW_NUMBER(): Assigns a unique sequential number to each row.
- RANK(): Assigns ranks with gaps for ties.
- DENSE_RANK(): Similar to RANK(), but without gaps.
- LEAD() and LAG(): Access values from subsequent or previous rows.
- SUM(), AVG(), or any other aggregate function: Calculate cumulative totals or averages over the specified window.
- These functions are powerful for analytics, trend analysis, and reporting, enabling complex calculations over partitions of data without altering the overall query results.
Example use cases:
Comparing This Month's Sales to Last Month's
SELECT
month,
sales,
LAG(sales) OVER (ORDER BY month) AS previous_month_sales,
sales - LAG(sales) OVER (ORDER BY month) AS sales_difference
FROM
monthly_sales;
Identifying Gaps
SELECT
order_id,
LAG(order_id) OVER (ORDER BY order_id) + 1 AS expected_order_id
FROM
orders
WHERE
order_id <> LAG(order_id) OVER (ORDER BY order_id) + 1;
Tracking cumulative values over time, such as monthly sales or balances
SELECT
month,
sales,
SUM(sales) OVER (ORDER BY month) AS running_total
FROM
monthly_sales;