はじめに
現在「達人に学ぶSQL徹底指南書」を読みながらSQLを学習中のTairaです
SQLを学んでいると、ある段階でこんな疑問を持ちます。
- 相関サブクエリ、読みにくくない？
- GROUP BY すると元の行が消えるの、正直つらい
- 「前の行」「平均との差」「順位」みたいな処理をしたい
このあたりを一気に解決するのが Window関数 です。
Window関数という言葉は知っているが詳しく知らない方は見ていただけるとWindow関数の概要を把握することができます。
Window関数とは一言で言うと
「行を潰さずに、集計・順位・比較ができる仕組み」 です。
GROUP BY は「行をまとめる」ための構文ですが、 Window関数は 各行を残したまま、周囲の行を参照 できます。
では、行を集約しないと何が良いかについてGROUP BYと比較しながら説明します。
GROUP BY との決定的な違い
GROUP BY の場合
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
- 結果は「部署ごと1行」
- 元の
employeesの行構造は失われる
Window関数の場合
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
- 各社員の行はそのまま
- 「同じ部署の平均給与」が横に付く
👉 分析用途ではこちらの方が圧倒的に自然
Window関数の基本構文
関数名() OVER (
PARTITION BY ...
ORDER BY ...
)
-
PARTITION BY：グループ分け（GROUP BYに近い） -
ORDER BY：順序（これがあると「前後行」を扱える）
PARTITION BY + 行を集約する機能 = GROUP BYと考えてもらえればと思います。
よく使われるWindow関数
① 集計系（でもGROUP BYしない）
SUM() OVER (...)
AVG() OVER (...)
COUNT() OVER (...)
→ 「平均との差」「構成比」などに使える
② 順位系
ROW_NUMBER()
RANK()
DENSE_RANK()
→ 「部署内順位」「売上ランキング」など
③ 行間比較
LAG()
LEAD()
SELECT
date,
sales,
sales - LAG(sales) OVER (ORDER BY date) AS diff_from_yesterday
FROM daily_sales;
👉 相関サブクエリ不要で前日比較
相関サブクエリとの関係
昔はこう書いていました。
SELECT *
FROM sales s1
WHERE sales >
(SELECT AVG(sales)
FROM sales s2
WHERE s2.department = s1.department);
Window関数なら：
SELECT *
FROM (
SELECT *,
AVG(sales) OVER (PARTITION BY department) AS avg_sales
FROM sales
) t
WHERE sales > avg_sales;
相関サブクエリと比べて
- 可読性が高い
- 同じテーブルを何度もスキャンしない
- オプティマイザが最適化しやすい
👉 「相関サブクエリの上位互換」と言われる理由
なぜ「今のSQLの基本」なのか
- SQLは宣言型言語
- ループや逐次処理は本来の役割ではない
- Window関数は 集合を一度眺めて処理する発想
手続き的に考えたくなったら
「Window関数で書けないか？」をまず疑う
この思考転換が、SQLが一段レベルアップする境目です。
まとめ
- Window関数は 行を保持したまま分析できる
- GROUP BY・相関サブクエリの弱点を補完する
- モダンなSQLでは避けて通れない基本機能