こんにちは！HRです。

今回は、SQLのお勉強をしていたときにこれは便利だなと思ったSQL関数、ウィンドウ関数を紹介したいと思います。

ウィンドウ関数とは

ウィンドウ関数はテーブルの区間ごとに集計を行う関数です。SQL:2003 にて標準化され、現在Oracle,MySQL,PostgreSQLなど主要なRDBMSで使用できます。

また、ウィンドウ関数はGROUP BY関数と違い、行ごとに集計値を返します。

実例

実際にどういったものか下記の従業員給与テーブルを使って説明します。

合計を求める

まず、部署ごとの給与合計を求めましょう。下記のSQLを実行します。

SELECT 
   empno,
   empname,
   salary,
   SUM(salary) OVER(PARTITION BY empname) as sum
FROM empsalary;

sum行に給与合計が出力されました！

SQLの4行目がウィンドウ関数です。OVERはウィンドウ関数を使いますよという宣言です。PARTITION BYは指定した行を同じ値のグループを区間ごとに分割する指定を行います。

このように簡潔に区間ごとに集計を行うことができます。

ソート

PARTITION BY の他に区間ごとにソートできるORDER BYがあります。下記に実行例を出します。

SELECT 
   empno,
   empname,
   salary,
   SUM(salary) OVER(PARTITION BY empname ORDER BY salary) as sum
 FROM empsalary;

salaryをもとにソートされ、行ごとに合計された値が出力されます。

平均、最大、最小

また、平均を求めるAVG()やMAX()、MIN()などを使って部署ごとの給与の最大値、最小値を求めることができます。

SELECT 
   empname,
   salary,
   AVG(salary) OVER(PARTITION BY empname) as avg,
   MAX(salary) OVER(PARTITION BY empname) as max,
   MIN(salary) OVER(PARTITION BY empname) as min
FROM empsalary;

その他

その他の関数として行番号を出力するROW_NUMBER()関数、区分ごとに順位を出すRANK()関数など便利な関数があります。

ぜひ、ウィンドウ関数を活用して見てください！

参考

• PostgreSQL 10.4文書
• プログラマのためのSQL 第4版