Quartz 5

Window Functions

Properties1
tagsdatabase/sql, database/analytics

4 min read

Window Functions

Window functions perform calculations across a set of rows related to the current row — the “window” — without collapsing the result set like GROUP BY. They are the SQL implementation of analytical processing, combining the detail of individual rows with aggregate computations. Available in PostgreSQL, MySQL 8+, SQLite 3.25+, and most modern DBMS.

Syntax

function_name(args) OVER (
    [PARTITION BY partition_columns]
    [ORDER BY sort_columns]
    [frame_clause]
)
  • PARTITION BY — Divides rows into partitions (like GROUP BY but preserves all rows)
  • ORDER BY — Defines the order within each partition
  • Frame clause — Defines which rows relative to the current row are included (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

GROUP BY vs PARTITION BY

AspectGROUP BYPARTITION BY (Window)
Rows returnedOne per groupAll original rows
AggregationCollapses rowsAnnotates rows
Access to detailLostPreserved
Use with HAVINGYesNo (use WHERE on outer query)

Ranking Functions

SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
  • ROW_NUMBER() — Unique sequential integer, no ties
  • RANK() — Tied values get the same rank, next rank skips (1, 2, 2, 4)
  • DENSE_RANK() — Tied values get the same rank, no gaps (1, 2, 2, 3)
  • NTILE(n) — Distributes rows into n roughly equal buckets

Offset Functions

Access values from other rows in the partition without a self-join:

SELECT date, revenue,
    LAG(revenue, 1)  OVER (ORDER BY date) AS prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;
  • LAG(expr, offset, default) — Value from a preceding row
  • LEAD(expr, offset, default) — Value from a following row
  • FIRST_VALUE(expr) — First value in the window frame
  • LAST_VALUE(expr) — Last value in the window frame (careful: default frame may exclude future rows)

Aggregate Window Functions

Standard aggregates used as window functions:

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    COUNT(*)    OVER (PARTITION BY department) AS dept_count
FROM employees;

Running totals with frame clause:

SELECT date, amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

SQL Execution Order

Understanding where window functions execute in the SQL pipeline:

FROM → WHERE → GROUP BY → HAVING → SELECT (window functions here) → ORDER BY → LIMIT

Window functions execute after GROUP BY/HAVING but before ORDER BY. You cannot reference a window function in WHERE or HAVING — wrap in a Common Table Expression (CTE) or subquery:

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn = 1;  -- top earner per department

Combining GROUP BY + PARTITION BY

GROUP BY first aggregates data, then PARTITION BY performs window calculations on the aggregated results. Use a Common Table Expression (CTE) or subquery to pipeline them:

SELECT 
    student_name, class, avg_score,
    RANK() OVER (PARTITION BY class ORDER BY avg_score DESC) AS class_rank,
    AVG(avg_score) OVER (PARTITION BY class) AS class_overall_avg
FROM (
    SELECT student_name, class, AVG(score) AS avg_score
    FROM test_scores
    GROUP BY student_name, class
) AS student_averages;

The inner query collapses rows with GROUP BY; the outer query applies window functions to the aggregated result. This pattern is often more performant than window functions on raw data because GROUP BY reduces the dataset size first.

Important: partition columns must exist in the grouped result set. If GROUP BY collapses away a column, that column cannot be used in PARTITION BY.

Named Windows

Reuse a window definition across multiple functions:

SELECT name,
    ROW_NUMBER() OVER w AS rn,
    RANK()       OVER w AS rank
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Sources

Graph View

Backlinks