ウィンドウ関数

ウィンドウ関数は集計しながらも各行を残して結果を表示します。SQLD/SQLPでも重要な概念です。グループ集計 + 行を維持

基本文法

function_name() OVER (
  PARTITION BY group_column
  ORDER BY sort_column
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

例題

RANK・DENSE_RANK・ROW_NUMBER

RANKは同順位の次の番号を飛ばし、DENSE_RANKは連番にします。ROW_NUMBERは各行に固有の番号を付けます。

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

部署内の給与順位

PARTITION BYで部署ごとに区切り、それぞれの部署内で順位を求めます。

SELECT name, department_id, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY department_id, dept_rank;

LAG・LEADで前後の行を参照

LAGは前の行、LEADは次の行の値を返します。期間ごとの比較などに使います。

SELECT name, salary,
  LAG(salary) OVER (ORDER BY salary) AS prev_salary,
  LEAD(salary) OVER (ORDER BY salary) AS next_salary,
  salary - LAG(salary) OVER (ORDER BY salary) AS salary_difference
FROM employees
ORDER BY salary;

SUM OVERで累積合計

ROWS BETWEENで集計範囲を指定します。この例は最初の行から現在行までの合計です。

SELECT name, salary,
  SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_salary
FROM employees
ORDER BY salary;

知っておくと便利なこと

PARTITION BYはグループを区切りますが、GROUP BYと異なり各行を残します。
RANKとDENSE_RANKは、同順位がある場合の次の順位が異なります。
LAG(column, N, default_value)はN行前を参照し、行がなければ既定値を返します。