SQL Window 関数 リファレンス
リファレンス 用 window 関数: OVER, PARTITION BY, ORDER BY, frame, および ranking/aggregate 関数.
Window Specification
The OVER clause defines the window: PARTITION BY groups rows, ORDER BY sorts within partitions, and the frame clause (ROWS/RANGE BETWEEN) bounds the window. Default frame is RANGE UNBOUNDED PRECEDING to CURRENT ROW (when ORDER BY present) or the whole partition (when absent). ROWS frames count physical rows; RANGE frames consider peer values. Frame bounds: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. The frame determines which rows each function sees.
- PARTITION BY
- Group rows
- ORDER BY
- Sort in partition
- frame
- ROWS/RANGE bounds
per-group windowsaffects framesliding windowWindow Functions
Ranking: ROW_NUMBER (unique rank), RANK (with gaps), DENSE_RANK (no gaps), NTILE (bucketing), PERCENT_RANK, CUME_DIST. Value: LAG/LEAD (offset access), FIRST_VALUE/LAST_VALUE, NTH_VALUE. Aggregate: SUM, AVG, COUNT, MIN, MAX over the frame. Distribution: PERCENTILE_CONT, PERCENTILE_DISC, MEDIAN. Window functions compute across related rows without collapsing them (unlike GROUP BY). They are essential for running totals, moving averages, rankings, and time-series analysis. Multiple window functions can use different OVER clauses.
- ROW_NUMBER
- Unique sequential
- LAG/LEAD
- Previous/next row
- running total
- SUM OVER
1, 2, 3...offset accesscumulative