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

ウィンドウ関数の解説

ウィンドウ関数は、現在の行に関連する行のセットに対して計算を実行しますが、GROUP BYとは異なり、行を1つの出力行にまとめません。

構文

function_name() OVER (
  [PARTITION BY col1, col2]
  [ORDER BY col3 ASC|DESC]
  [frame_clause]
)

ランキング関数

SELECT name, department_id, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

関数	同順位	ギャップ
ROW_NUMBER	各行にユニークな番号	なし
RANK	同順位の行に同じ番号	あり（次のランクがスキップ）
DENSE_RANK	同順位の行に同じ番号	なし

グループごとのTop-N

SELECT * FROM (
  SELECT name, department_id, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id ORDER BY salary DESC
    ) AS rn
  FROM employees
) sub WHERE rn <= 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 mom_change
FROM monthly_sales;

累積合計と移動平均

SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total,
  AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3m
FROM monthly_sales;

利用可能性

• PostgreSQL: 8.4以降でフルサポート
• MySQL: 8.0以降でフルサポート（5.7では利用不可）
• SQLite: 3.25.0以降でフルサポート