SQLウィンドウ関数: ROW_NUMBER、RANK、LAG、LEAD

SQLウィンドウ関数: ROW_NUMBER、RANK、LAG、LEAD

ウィンドウ関数は、GROUP BYのように行を1つの出力行に集約することなく、現在の行に関連する行のセットに対して計算を実行します。ウィンドウを定義するために OVER 句を使用します。

基本構文

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

ランキング関数

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

• ROW_NUMBER: 一意の連番、同順位なし（1, 2, 3, 4）
• RANK: 同順位あり、同順位後に欠番（1, 2, 2, 4）
• DENSE_RANK: 同順位あり、欠番なし（1, 2, 2, 3）

LAGとLEAD

自己結合なしで前後の行の値にアクセスできます:

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

累計と移動平均

SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(amount) OVER (ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

名前付きウィンドウ

SELECT name, department, salary,
  SUM(salary) OVER w AS dept_total,
  AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department);

パフォーマンスに関する注意

• ウィンドウ関数はPARTITION BYとORDER BYのカラムによるソートが必要
• これらのカラムに一致するインデックスを追加することでソートステップを省略できる
• ウィンドウ関数は一般に、同じ結果を得るための相関サブクエリよりも効率的

ウィンドウ関数は分析、レポーティング、行レベルの詳細と集約計算を共存させる必要があるあらゆるシナリオで不可欠です。