分析のためにSQLを使う際、window関数はとても便利です。一方でとっつきにくい考え方や、情報が少なかったりしてどうしても敬遠してしまいがちです。例を交えて簡単にまとめてみたいと思います。
window関数とは
PostgreSQLの公式ドキュメントには以下のように説明があります。
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
ポイントは
- 現在の行に関するテーブル全体を舐める計算をする
- 集約関数と考え方は似ている。集約関数は文字通り1行に集約するが、window関数を使った場合は対象の行はそのまま残る
- 対象の行全てに対して処理が行われる
と言ったところでしょうか。一方で、BigQuery StandardSQLのドキュメントには以下のように書かれています。
データベースでは、分析関数は行のグループ全体に対して集計値を計算する関数です。行のグループに対して単一の集計値を返す集計関数とは異なり、分析関数は入力行のグループに対して分析関数を計算することで、行ごとに単一の値を返します。
分析関数は複雑な分析オペレーションを簡潔に表す強力なメカニズムであり、分析関数を使用しなければコストの高いセルフ JOIN や SQL クエリ外での計算を行う必要が生じる評価を効率的に行えます。
分析関数は、SQL 標準や一部の商用データベースで「（分析）ウィンドウ関数」とも呼ばれています。これは、分析関数が window や window frame と呼ばれる行のグループに対して評価されるためです。その他の一部のデータベースでは、オンライン分析処理（OLAP）関数と呼ばれることもあります。
文字だけではわかりにくいので実際の例で確認していきます。
部署ごとの平均賃金が欲しい
depname | empid | salary
-----------+-------+--------
develop | 11 | 5200
develop | 7 | 4200
develop | 9 | 4500
develop | 8 | 6000
develop | 10 | 5200
personnel | 5 | 3500
personnel | 2 | 3900
sales | 3 | 4800
sales | 1 | 5000
sales | 4 | 4800
部署・従業員ID・賃金を持つテーブルを使って考えます。このテーブルの名前はemp_infoテーブルとします。
例えば、行ごとに部署ごとの平均賃金を追加したいと考えます。アウトプットイメージは以下の通りです。
depname | empid | salary | avg_salary
-----------+-------+--------+-------------
develop | 11 | 5200 | 5020
develop | 7 | 4200 | 5020
develop | 9 | 4500 | 5020
develop | 8 | 6000 | 5020
develop | 10 | 5200 | 5020
personnel | 5 | 3500 | 3700
personnel | 2 | 3900 | 3700
sales | 3 | 4800 | 4866
sales | 1 | 5000 | 4866
sales | 4 | 4800 | 4866
普通に集約関数（GROUP BY）を使ってみます。
SELECT
depname,
AVG(depname) AS avg_salary
FROM emp_info
GROUP BY depname
得られる結果は、
depname | avg_salary
-----------+--------
develop | 5200
personnel | 3700
sales | 4866
です。（簡略化のため小数点以下はカットしています）（このテーブルの名前はdep_avg_salaryとします）
完全に集約されてしまいました。各行に平均賃金を追加したいのですが部署ごとの平均賃金が出てしまいました。
SELECT
emp_info.depname,
emp_info.empid,
emp_info.salary,
dep_avg_salary.avg_salary
FROM emp_info
INNER JOIN dep_avg_salary ON emp_info.depname = dep_avg_salary.depname
とやれば欲しい答えが出ますが、計算量が多いですね。もっと簡単にやります。
ここでwindow関数が登場します。
SELECT
depname,
empid,
salary,
AVG(salary) OVER (PARTITION BY depname)
FROM emp_info;
これが求めたい結果です。
depname | empid | salary | avg_salary
-----------+-------+--------+-------------
develop | 11 | 5200 | 5020
develop | 7 | 4200 | 5020
develop | 9 | 4500 | 5020
develop | 8 | 6000 | 5020
develop | 10 | 5200 | 5020
personnel | 5 | 3500 | 3700
personnel | 2 | 3900 | 3700
sales | 3 | 4800 | 4866
sales | 1 | 5000 | 4866
sales | 4 | 4800 | 4866
OVERとPARTITION BYという2つの関数が出てきました。続いてこれらを解説します。
OVERはwindow関数を使いますよーというサインです。OVERの後に、どのようにwindowを作るのかということを定義します。
PARTITIONでwindow、つまりどの範囲でグループを作るか指定します。
AVG(salary) OVER (PARTITION BY depname)は、depnameでグループを作った上で、自分が属するdepnameのsalaryのAVGをちょうだいと言っています。
部署ごとの給料ランキングが欲しい
以下のような結果が欲しいとします。
depname | empid | salary | rank_salary
-----------+-------+--------+-------------
develop | 11 | 5200 | 2
develop | 7 | 4200 | 4
develop | 9 | 4500 | 3
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
personnel | 5 | 3500 | 2
personnel | 2 | 3900 | 1
sales | 3 | 4800 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
これもwindow関数を使っていきましょう。
SELECT
depname,
empid,
salary,
RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank_salary
FROM emp_info;
これも前から読んでいきます。RANK()が欲しいと言っています。次にどの範囲のランキングが欲しいのか指定します。
OVERでwindow関数を使うことを指定します。depnameごとにwindowを作ってその中でsalaryの大きい順で並べます。
こうすると、部署ごとで自分の賃金がいくらかゲットできます。
指定した順番通りに番号を割り振りたい
次は、先ほどのデータに入社日を加えたテーブルで考えてみます。
depname | empid | salary | enter_date
-----------+-------+--------+-------------
develop | 11 | 5200 | 2018-01-15
develop | 7 | 4200 | 2018-04-15
develop | 9 | 4500 | 2018-02-01
develop | 8 | 6000 | 2018-01-01
develop | 10 | 5200 | 2018-04-01
personnel | 5 | 3500 | 2018-03-01
personnel | 2 | 3900 | 2018-02-15
sales | 3 | 4800 | 2018-01-31
sales | 1 | 5000 | 2018-02-15
sales | 4 | 4800 | 2018-04-01
部署ごとに入社日順に番号を割り振りたいとなったとします。
ROW_NUMBER()関数を使います。
SELECT
depname,
empid,
salary,
ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enter_date) AS enterid
FROM emp_info;
depnameごとにwindowを作ってenter_date順に並べます。それにROW_NUMBERを付与するのです。
すると以下のようにアウトプットされます。
depname | empid | salary | enter_date| enterid
-----------+-------+--------+------------+--------
develop | 11 | 5200 | 2018-01-15| 2
develop | 7 | 4200 | 2018-04-15| 5
develop | 9 | 4500 | 2018-02-01| 3
develop | 8 | 6000 | 2018-01-01| 1
develop | 10 | 5200 | 2018-04-01| 4
personnel | 5 | 3500 | 2018-03-01| 2
personnel | 2 | 3900 | 2018-02-15| 1
sales | 3 | 4800 | 2018-01-31| 1
sales | 1 | 5000 | 2018-02-15| 2
sales | 4 | 4800 | 2018-04-01| 3
1つ前・1つ後に入社した従業員の入社日を知りたい
LAG/LEAD関数を使います。LAGは前、LEADは後です。
2つ引数をとります。第一引数は指定カラム、第二引数は何個前・後を指定するかです。
SELECT
depname,
empid,
salary,
enter_date,
LAG(enter_date) OVER (ORDER BY enter_date) AS prev_enter_date,
LEAD(enter_date) OVER (ORDER BY enter_date) AS post_enter_date,
FROM emp_info;
depname | empid | salary | enter_date| prev_enter_date| post_enter_date
-----------+-------+--------+------------+----------------+------------------
develop | 11 | 5200 | 2018-01-15| 2018-01-01| 2018-01-31
develop | 7 | 4200 | 2018-04-15| 2018-04-01| NULL
develop | 9 | 4500 | 2018-02-01| 2018-01-31| 2018-02-15
develop | 8 | 6000 | 2018-01-01| NULL| 2018-01-15
develop | 10 | 5200 | 2018-04-01| 2018-03-01| 2018-04-15
personnel | 5 | 3500 | 2018-03-01| 2018-02-15| 2018-04-01
personnel | 2 | 3900 | 2018-02-15| 2018-02-01| 2018-03-01
sales | 3 | 4800 | 2018-01-31| 2018-01-15| 2018-02-01
sales | 1 | 5000 | 2018-02-15| 2018-02-01| 2018-03-01
sales | 4 | 4800 | 2018-04-01| 2018-03-01| 2018-04-15
この場合、例えば「同じ部署で1つ前に入社した従業員の入社日」が欲しいのならば、LAG(enter_date) OVER (PARTITION BY depname ORDER BY enter_date)としますが、今回はwindowの指定がない、つまり全体に対してなのでPARTITION BYは不要です。1つ前、1つ後を求めるのは一見とても面倒に見えますが、LAG/LEADとwindow関数を用いれば簡単にできます。
また、LAG/LEAD関数は引数を3つとります。LAG(column [,offset] [,default])です。columnは必須で、1つ前のどのカラムを返すのかを指定します。offsetはデフォルトでは1です。現在の行より何個前の値を返すか指定できます。defaultは1つ前が存在しないときに何を返すかです。デフォルトはNULLです。
参考情報
BigQuery StandardSQL 分析関数
分析関数が図と共にとても丁寧に解説されています。
PostgreSQL window functions
こちらも丁寧に解説しています。
SQL window functions
説明はちょっと雑ですが、どんな関数があるかを見渡すのに役立ちます。
ブログ読んでください
ブログも書いているのでぜひ！