OVERとPARTITION BYのおかげで、行をまとめてしまうことなく、順位、累計、グループ内での比較を計算しましょう。
このレッスンを Kodokon で開くGROUP BYは行をグループにまとめてしまいますが、ウィンドウ関数はすべての行を保ったまま集約や順位を計算します。グループごとの上位N件、累計、自分のチームの平均との比較のためのツールです。SQLiteではバージョン3.25（2018年）から利用できます。データセットを読み込みましょう。
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
salary INTEGER NOT NULL
);
INSERT INTO employees (name, dept, salary)
VALUES
('alice', 'tech', 5200),
('bruno', 'tech', 4800),
('chloe', 'tech', 4800),
('david', 'tech', 4100),
('emma', 'sales', 3900),
('fanny', 'sales', 3600),
('gilles', 'sales', 3600),
('hugo', 'sales', 3200);三つの順位付け関数、同順位に直面したときの三つの振る舞い。ROW_NUMBERは重複なしで番号を振り（同順位は任意に決着）、RANKは同順位に同じ順位を与えてから飛ばし（1, 2, 2, 4）、DENSE_RANKは飛ばしません（1, 2, 2, 3）。WINDOW句を使えばウィンドウの定義を繰り返さずに済みます（SQLiteとPostgreSQLはサポート、SQL Serverはサポートしません）。
SELECT name, salary,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);PARTITION BYは部分集合ごとに計算をやり直します。部署ごとに一つのウィンドウで、何もまとめません。CTEと組み合わせると、まさに面接で問われるパターンになります。各部署で最も給料の高いN人です。CTEが必須なのは、ウィンドウ関数がWHEREのあとに評価されるからです。rnで直接フィルタリングすることはできません。
WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (
PARTITION BY dept
ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;定番の集約はOVERでウィンドウになります。SUM(salary) OVER (ORDER BY ...)は行ごとに累計を生み出します。厳密に行ごとの累計にするには、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWでフレームを指定しましょう。
SELECT name, dept, salary,
SUM(salary) OVER (
PARTITION BY dept
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM employees;