Window Functions

Compute values across related rows without collapsing the result set, using OVER with PARTITION BY and ORDER BY.

Syntax

function_name(args) OVER (
  PARTITION BY col1, col2
  ORDER BY col3 ASC|DESC
  frame_clause
)

Ranking Functions

ROW_NUMBER

SELECT grp, val, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val) AS rn
FROM w ORDER BY grp, val;
-- a, 10, 1 | a, 20, 2 | a, 30, 3 | b, 40, 1 | b, 50, 2 | b, 60, 3

RANK

SELECT grp, val, RANK() OVER (PARTITION BY grp ORDER BY val) AS rnk
FROM wr ORDER BY grp, val;
-- a, 10, 1 | a, 10, 1 | a, 20, 3  (gaps at ties)

DENSE_RANK

SELECT grp, val, DENSE_RANK() OVER (PARTITION BY grp ORDER BY val) AS drnk
FROM wr ORDER BY grp, val;
-- a, 10, 1 | a, 10, 1 | a, 20, 2  (no gaps)

NTILE

SELECT grp, val, NTILE(2) OVER (PARTITION BY grp ORDER BY val) AS tile
FROM w ORDER BY grp, val;
-- a, 10, 1 | a, 20, 1 | a, 30, 2 | b, 40, 1 | b, 50, 1 | b, 60, 2

Aggregate Window Functions

-- Running SUM
SELECT grp, val, SUM(val) OVER (PARTITION BY grp ORDER BY val) AS running_sum
FROM w ORDER BY grp, val;
-- a,10,10 | a,20,30 | a,30,60 | b,40,40 | b,50,90 | b,60,150

-- Partition AVG (no ORDER BY = full partition)
SELECT grp, val, AVG(val) OVER (PARTITION BY grp) AS avg_val
FROM w ORDER BY grp, val;
-- a: 20.0 for all | b: 50.0 for all

-- MIN / MAX over partition
SELECT grp, val, MIN(val) OVER (PARTITION BY grp) AS min_val
FROM w ORDER BY grp, val;

-- COUNT over partition
SELECT grp, val, COUNT(val) OVER (PARTITION BY grp) AS cnt
FROM w ORDER BY grp, val;
-- All 3 for both groups

Value Functions

LAG / LEAD

SELECT grp, val, LAG(val, 1) OVER (PARTITION BY grp ORDER BY val) AS prev_val
FROM w ORDER BY grp, val;
-- a,10,0 | a,20,10 | a,30,20 | b,40,0 | b,50,40 | b,60,50

SELECT grp, val, LEAD(val, 1) OVER (PARTITION BY grp ORDER BY val) AS next_val
FROM w ORDER BY grp, val;

FIRST_VALUE / LAST_VALUE

SELECT grp, val, FIRST_VALUE(val) OVER (PARTITION BY grp ORDER BY val) AS fv
FROM w ORDER BY grp, val;
-- a: 10 for all | b: 40 for all

SELECT grp, val, LAST_VALUE(val) OVER (
  PARTITION BY grp ORDER BY val
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lv FROM w ORDER BY grp, val;
-- a: 30 for all | b: 60 for all

Multiple Window Functions

SELECT grp, val,
  ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val) AS rn,
  RANK() OVER (PARTITION BY grp ORDER BY val) AS rnk
FROM wr ORDER BY grp, val;

Multi-Key Partition

SELECT g1, g2, v, ROW_NUMBER() OVER (PARTITION BY g1, g2 ORDER BY v) AS rn
FROM wm ORDER BY g1, g2, v;