駆け出しデータサイエンティストのNishです
今回はウィンドウ関数についての解説記事を書きました
簡易的な時系列分析にも使用できるので、是非、理解しておきたい内容ですね
ウィンドウ関数とは
かなりざっくり説明すると、「各レコードについて、指定した範囲での集計値を抽出する」機能です
例えば、ある会社の年商が格納されたテーブルがあるとします
このテーブルから、以下のような集計が行えます
- 前年度からどれだけ売り上げが上昇(下降)したかを各年度について集計
- 直近３年間の年商の平均を各年度について集計
構文
ウィンドウ関数の構文は以下の形式です
<集計したい内容> OVER( <集計範囲の指定> )
集計したい内容は集約関数か、RANK()などのウィンドウ専用の関数を指定すればokです
範囲内のランキングを集計したい場合は、RANK()とかけばいいし、
範囲内のhegoカラムの最大値を集計したい場合はMAX(hoge)とすればよいです
次に、集計範囲ですが、コチラは２段階で指定します
- データ全体のグループ分け(指定しなければ、テーブル全体が対象)
- 各レコードについて、同じグループのどの範囲を集計対象とするか
↑の２段階で範囲を指定します
１番目で作成された分割をウィンドウと呼び、２番目の指定範囲をフレームと呼びます
フレームまで指定することで、行数単位で集計範囲を決めることができます
(前後１行の平均を求めたりすることが可能です)
フレームはデフォルトで、グループの先頭から現在行までなので注意が必要です
グループ全体をしていするには以下のように書きましょう
SUM(hoge) OVER(PARTITION BY fuga
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
言葉だけだと理解しにくいと思うので、
実際にウィンドウ関数を使用した集計処理の例を以下に提示していきますね
ウィンドウ関数による集計
事前準備
以下のデータからデータ集計を行います
Webサイトのpvデータっぽいものを用意しました
各Webページにはpage_idとカテゴリが付与されているものとします
+---------+---------------+-----+
| page_id | category | pv |
+---------+---------------+-----+
| 10001 | News | 100 |
| 10002 | Entertainment | 130 |
| 10003 | Sports | 400 |
| 10004 | Humor | 200 |
| 10005 | News | 120 |
| 10006 | News | 90 |
| 10007 | Sports | 310 |
| 10008 | News | 200 |
| 10009 | Entertainment | 50 |
| 10010 | Entertainment | 380 |
| 10011 | Sports | 500 |
| 10012 | Humor | 140 |
+---------+---------------+-----+
ランキング
全体でのランキング
ウィンドウ関数を用いて、ランキングの集計を行います
集計範囲はテーブル全体なので、ウィンドウ、フレーム共に指定なしです
以下のクエリで、各ページのpvランキングを割り出せます
SELECT
page_id,
category,
pv,
RANK() OVER(ORDER BY pv DESC) AS ranking
FROM
page_rank
+---------+---------------+-----+---------+
| page_id | category | pv | ranking |
+---------+---------------+-----+---------+
| 10011 | Sports | 500 | 1 |
| 10003 | Sports | 400 | 2 |
| 10010 | Entertainment | 380 | 3 |
| 10007 | Sports | 310 | 4 |
| 10004 | Humor | 200 | 5 |
| 10008 | News | 200 | 5 |
| 10012 | Humor | 140 | 7 |
| 10002 | Entertainment | 130 | 8 |
| 10005 | News | 120 | 9 |
| 10001 | News | 100 | 10 |
| 10006 | News | 90 | 11 |
| 10009 | Entertainment | 50 | 12 |
+---------+---------------+-----+---------+
カテゴリ内でのランキング
次に、同じカテゴリ内でのpvランキングを集計します
今回は、各レコードの集計範囲を指定する必要があります
同じカテゴリ内でのランキングを出すので、レコード全体をcategoryでPARTITION BYします
PARTITION BYすることでウィンドウを指定している訳ですね
カテゴリ内での集計なので、フレームの指定は不要です
SELECT
page_id,
category,
pv,
RANK() OVER(PARTITION BY category ORDER BY pv DESC) AS ranking
FROM
page_rank
+---------+---------------+-----+---------+
| page_id | category | pv | ranking |
+---------+---------------+-----+---------+
| 10010 | Entertainment | 380 | 1 |
| 10002 | Entertainment | 130 | 2 |
| 10009 | Entertainment | 50 | 3 |
| 10004 | Humor | 200 | 1 |
| 10012 | Humor | 140 | 2 |
| 10008 | News | 200 | 1 |
| 10005 | News | 120 | 2 |
| 10001 | News | 100 | 3 |
| 10006 | News | 90 | 4 |
| 10011 | Sports | 500 | 1 |
| 10003 | Sports | 400 | 2 |
| 10007 | Sports | 310 | 3 |
+---------+---------------+-----+---------+
上位サイトとのpvの差
ランキングが一つ上のサイトとのpvの比較をしてみます
今回は、あとpvがいくつあれば順位が上がったかを集計します
ランキング自体はテーブル全体なので、ウィンドウの指定は不要
ランキングが一つ上の行を参照したいので、フレームとして１行上のレコードを指定しています
(範囲がレコード一つ分なので、MINでもMAXでもSUMでも、集計結果は変わりません)
ちなみに、フレームにレコードが存在しない場合は集計結果がNULLになります
１位のレコードについては、(heigh_pv - pv)がNULLとなってキモチワルイので、
COALESCEを使って0を表示しています
SELECT
page_id,
category,
pv,
COALESCE(heigh_pv - pv, 0) AS diff_pv
FROM (
SELECT
page_id,
category,
pv,
MIN(pv) OVER(ORDER BY pv DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS heigh_pv
FROM
page_rank
) AS sq
+---------+---------------+-----+---------+
| page_id | category | pv | diff_pv |
+---------+---------------+-----+---------+
| 10011 | Sports | 500 | 0 |
| 10003 | Sports | 400 | 100 |
| 10010 | Entertainment | 380 | 20 |
| 10007 | Sports | 310 | 70 |
| 10004 | Humor | 200 | 110 |
| 10008 | News | 200 | 0 |
| 10012 | Humor | 140 | 60 |
| 10002 | Entertainment | 130 | 10 |
| 10005 | News | 120 | 10 |
| 10001 | News | 100 | 20 |
| 10006 | News | 90 | 10 |
| 10009 | Entertainment | 50 | 40 |
+---------+---------------+-----+---------+