Compute values across a set of rows related to the current row.
<func>() OVER (
[PARTITION BY <columns>]
[ORDER BY <columns>]
[ROWS | RANGE BETWEEN <start> AND <end>]
)## Overview Window functions perform calculations across a set of rows (called a "window") that are related to the current row without collapsing the result set into groups. Unlike aggregate functions used with GROUP BY, window functions retain all individual rows in the output while computing values such as running totals, rankings, moving averages, and row comparisons. Each window function call includes an OVER clause that defines the partitioning, ordering, and frame boundaries for the computation. ## Execution Order Window functions are evaluated after WHERE, GROUP BY, HAVING, and the SELECT expression list, but before DISTINCT and ORDER BY. This means window functions can reference aggregate results (when GROUP BY is present) and column aliases, but their output is subject to subsequent DISTINCT elimination and result ordering. ## Behavior - **PARTITION BY**: Divides the result set into independent partitions. The window function is computed separately for each partition. If PARTITION BY is omitted, the entire result set is treated as a single partition. - **ORDER BY within OVER**: Defines the logical order of rows within each partition. This ordering determines the meaning of frame boundaries and the behavior of ranking functions. The ORDER BY in the OVER clause is independent of the query-level ORDER BY. - **Frame specification**: The ROWS or RANGE clause defines which rows in the partition are included in the window for each row: - `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` includes all rows from the partition start through the current row (default when ORDER BY is present). - `ROWS BETWEEN N PRECEDING AND N FOLLOWING` defines a sliding window of fixed size. - `RANGE BETWEEN` uses logical value ranges rather than physical row offsets. - `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` includes the entire partition. - **Ranking functions**: ROW_NUMBER assigns a unique sequential integer. RANK assigns the same number to tied rows and leaves gaps (1, 1, 3). DENSE_RANK assigns the same number to tied rows without gaps (1, 1, 2). NTILE distributes rows into N roughly equal-sized buckets. - **Value functions**: LAG(expr, offset, default) accesses a row at a given offset before the current row. LEAD accesses a row after the current row. FIRST_VALUE and LAST_VALUE return the first or last value in the window frame. NTH_VALUE returns the value at position N. - **Aggregate functions as window functions**: Any standard aggregate (SUM, COUNT, AVG, MIN, MAX) can be used as a window function by adding an OVER clause. The aggregate is computed over the window frame rather than the entire group. - **NULL handling**: Ranking functions treat NULLs as equal for ordering purposes (all NULLs receive the same rank). LAG and LEAD return their default argument (or NULL if none) when the offset falls outside the partition. Aggregate window functions skip NULLs consistent with their non-window behavior. - **Multiple window functions**: A single query can use multiple window functions with different OVER specifications. The optimizer shares sort operations when multiple windows require the same partitioning and ordering. - **Named windows**: DeltaForge supports the WINDOW clause for defining reusable window specifications: `WINDOW w AS (PARTITION BY dept ORDER BY salary)` followed by `OVER w` in multiple function calls. ## Compatibility Window functions conform to the SQL:2003 standard. All standard window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE) are supported, along with aggregate functions used in window context. ROWS and RANGE frame specifications follow the SQL:2003 and SQL:2011 standards.
-- ROW_NUMBER: sequential ranking
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;-- Running total with a cumulative frame
SELECT date, amount,
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM transactions;-- Partition-level average
SELECT department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;-- RANK and DENSE_RANK for tie-aware ranking
SELECT product_id, revenue,
RANK() OVER (ORDER BY revenue DESC) AS rank_with_gaps,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS rank_no_gaps
FROM product_sales;-- LAG and LEAD: access adjacent rows
SELECT month, revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;-- NTILE: distribute rows into N buckets
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;