Analytic (Window) Functions

15.1 Syntax

function(args) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS|RANGE|GROUPS BETWEEN ... AND ...] [EXCLUDE ...])

15.2 Ranking

SELECT department_id, last_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) drnk,
       NTILE(4)     OVER (ORDER BY salary) quartile,
       PERCENT_RANK() OVER (ORDER BY salary) pct_rank,
       CUME_DIST()  OVER (ORDER BY salary) cume
FROM employees;

-- Top-N per group
SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) r FROM employees e)
WHERE r <= 3;

15.3 Aggregates over Windows

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) running_total,
       AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) moving_avg_7,
       SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) last_30_days,
       RATIO_TO_REPORT(amount) OVER () pct_of_total,
       COUNT(*) OVER (PARTITION BY TRUNC(order_date, 'MM')) orders_in_month
FROM daily_sales;

15.4 Offset and Value Functions

SELECT order_date, amount,
       LAG(amount, 1, 0) OVER (ORDER BY order_date) prev_amt,
       LEAD(amount)      OVER (ORDER BY order_date) next_amt,
       FIRST_VALUE(amount) OVER (ORDER BY order_date) first_amt,
       LAST_VALUE(amount IGNORE NULLS) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_amt,
       NTH_VALUE(amount, 2) FROM FIRST OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) second_amt
FROM daily_sales;

15.5 Window Frames

ROWS (physical), RANGE (logical value range), GROUPS (peer groups, 21c).

EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS (21c).

Default frame with ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

21c WINDOW clause: ... OVER w ... WINDOW w AS (PARTITION BY ...).

15.6 Classic Problems

Running totals, year-over-year comparison, gaps and islands, sessionization, deduplication, median, Pareto analysis.

-- Gaps and islands: consecutive login days
SELECT MIN(login_date) start_d, MAX(login_date) end_d, COUNT(*) days
FROM (SELECT login_date, login_date - ROW_NUMBER() OVER (ORDER BY login_date) grp FROM logins WHERE user_id = 1)
GROUP BY grp ORDER BY start_d;