SQLにはウィンドウ関数という協力な機能があります。
ウィンドウ関数には順位を求めるRANK()
がありますが、今回はウィンドウ関数のOVER
やPARTITION BY
の使い方について、よくあるユースケースを題材にしてまとめます。
※今回の掲載するSQLはPostgreSQLで動作確認を行っています。
ウィンドウ関数とは
ウィンドウ関数は、レコードの順位など、他のレコードと比較した結果などの新しいカラムを作成する関数のことです。
集約関数と似ていますが、動作は全く違います。
集約関数はGROUP BY
した複数のレコードを1つのレコードに「まとめる(集約する)」働きをします。
一方、ウィンドウ関数は集約しません。レコードはそのまま結果セットに含まれます。
有名な物は順位を計算するRANK()
関数です。
RANK()
は指定した値を他のレコードと比較し、そのレコードが「何番目に大きいか(小さいか)」の順位の情報をカラムとして追加してくれます。
では、まずは実際につかって見ましょう。
ランキングを求めるRANK()
関数
まずは、RANK()
関数を使ってランキングを求めてみます。
今回は以下のように、学校のテストのようなデータベースを用意しました。
こちらはexam
テーブルです。
name
には、生徒の名前が、class
にはクラス(A組、B組など)、score
にはテストの点数が格納されています。
id | name | class | score |
---|---|---|---|
1 | Jade | a | 450 |
2 | Isaiah | a | 380 |
3 | Jeffry | a | 400 |
4 | Dallas | a | 350 |
5 | Lina | a | 360 |
6 | Presley | b | 330 |
7 | Rosetta | b | 290 |
8 | Terence | b | 440 |
9 | Adrian | b | 310 |
10 | Jasen | c | 410 |
11 | Kari | c | 370 |
12 | Yoshiko | c | 320 |
13 | Milton | c | 300 |
14 | Emmett | c | 470 |
実験したい方は、以下のSQLを実行してください。
※オンラインでSQLを実行するには、DB Fiddleというサービスもオススメです。
CREATE TABLE exam ( id SERIAL PRIMARY KEY, name TEXT, class TEXT, score INTEGER ); INSERT INTO exam(name, class, score) VALUES ('Jade' , 'a', 450), ('Isaiah' , 'a', 380), ('Jeffry' , 'a', 400), ('Dallas' , 'a', 350), ('Lina' , 'a', 360), ('Presley', 'b', 330), ('Rosetta', 'b', 290), ('Terence', 'b', 440), ('Adrian' , 'b', 310), ('Jasen' , 'c', 410), ('Kari' , 'c', 370), ('Yoshiko', 'c', 320), ('Milton' , 'c', 300), ('Emmett' , 'c', 470);
全体の順位を求める
さて、このテーブルに全体の順位を表すrank
を追加してみましょう。
ここでウィンドウ関数を使って見ます。
SELECT id, name, class, score, RANK() OVER (ORDER BY score DESC) FROM exam;
実行すると、以下のようになります。
id | name | class | score | rank |
---|---|---|---|---|
14 | Emmett | c | 470 | 1 |
1 | Jade | a | 450 | 2 |
8 | Terence | b | 440 | 3 |
10 | Jasen | c | 410 | 4 |
3 | Jeffry | a | 400 | 5 |
2 | Isaiah | a | 380 | 6 |
11 | Kari | c | 370 | 7 |
5 | Lina | a | 360 | 8 |
4 | Dallas | a | 350 | 9 |
6 | Presley | b | 330 | 10 |
12 | Yoshiko | c | 320 | 11 |
9 | Adrian | b | 310 | 12 |
13 | Milton | c | 300 | 13 |
7 | Rosetta | b | 290 | 14 |
ここでは、RANK() OVER (ORDER BY score DESC)
というウィンドウ関数を使いました。
それぞれの項目の説明は以下の通りです。
OVER
は「ウィンドウ関数を使う」という宣言のようなキーワード、ここで重要なのはORDER BY score DESC
ですね。
OVER句の中に書いたORDER BY
は、そのウィンドウ関数を適応する順番を指定します。
ここでは、score
の一番高い人が1位です。よって、ORDER BY
の指定によって「score
の降順に順位をふった」という事になります。
クラスごとの順位を求める
では、つづいてクラスごとの順位を求めてみましょう。
「クラスで何番目か」は重要ですね。
SELECT id, name, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) FROM exam;
実行すると、結果は以下のようになります。
id | name | class | score | rank |
---|---|---|---|---|
1 | Jade | a | 450 | 1 |
3 | Jeffry | a | 400 | 2 |
2 | Isaiah | a | 380 | 3 |
5 | Lina | a | 360 | 4 |
4 | Dallas | a | 350 | 5 |
8 | Terence | b | 440 | 1 |
6 | Presley | b | 330 | 2 |
9 | Adrian | b | 310 | 3 |
7 | Rosetta | b | 290 | 4 |
14 | Emmett | c | 470 | 1 |
10 | Jasen | c | 410 | 2 |
11 | Kari | c | 370 | 3 |
12 | Yoshiko | c | 320 | 4 |
13 | Milton | c | 300 | 5 |
しっかり「クラスで何位か」が分かるようになりました。
これはPARTITION BY class
という指定をしたからです。
これにより、class
が同じレコード間での順位が計算されたということです。
では、次はもう少し分析的な使い方をしてみましょう。
SUM()で移動合計を求める
SUM()
といえば、集約関数の1つ。
しかし、OVER
を使う事で、ウィンドウ関数としても使えます。
ここでは、以下のテーブルを使って、月ごとの累計会員数を求めてみましょう。
以下がテーブルkpi
です。
register
がその月の新規会員数です。
id | register | created_at |
---|---|---|
1 | 100 | 2020-02-01 00:00:00 |
2 | 200 | 2020-03-01 00:00:00 |
3 | 300 | 2020-04-01 00:00:00 |
4 | 400 | 2020-05-01 00:00:00 |
5 | 500 | 2020-06-01 00:00:00 |
6 | 600 | 2020-07-01 00:00:00 |
7 | 700 | 2020-08-01 00:00:00 |
8 | 800 | 2020-09-01 00:00:00 |
9 | 900 | 2020-10-01 00:00:00 |
実験したい方は、以下のSQLを実行してください。
CREATE TABLE kpi ( id SERIAL PRIMARY KEY, register INTEGER, created_at TIMESTAMP ); INSERT INTO kpi (register, created_at) VALUES (100, '2020-02-01'), (200, '2020-03-01'), (300, '2020-04-01'), (400, '2020-05-01'), (500, '2020-06-01'), (600, '2020-07-01'), (700, '2020-08-01'), (800, '2020-09-01'), (900, '2020-10-01');
では、月ごとの累計会員数を表示してみましょう。
SELECT created_at, register, SUM(register) OVER (ORDER BY created_at) AS moving_sum FROM kpi;
結果は以下のようになります。
移動合計moving_sum
が表示されます。
created_at | register | moving_sum |
---|---|---|
2020-02-01 00:00:00 | 100 | 100 |
2020-03-01 00:00:00 | 200 | 300 |
2020-04-01 00:00:00 | 300 | 600 |
2020-05-01 00:00:00 | 400 | 1000 |
2020-06-01 00:00:00 | 500 | 1500 |
2020-07-01 00:00:00 | 600 | 2100 |
2020-08-01 00:00:00 | 700 | 2800 |
2020-09-01 00:00:00 | 800 | 3600 |
2020-10-01 00:00:00 | 900 | 4500 |
SUM(register) OVER (ORDER BY created_at)
では、created_at
の昇順で段階的に和を求めます。
これが移動合計になります。
AVG()で移動平均を求める
この仕組みを使えば、もちろん移動平均
も求めることができます。
SELECT created_at, register, AVG(register) OVER (ORDER BY created_at) AS moving_avg, SUM(register) OVER (ORDER BY created_at) AS moving_sum FROM kpi;
この結果は以下のようになります。
created_at | register | moving_avg | moving_sum |
---|---|---|---|
2020-02-01 00:00:00 | 100 | 100 | 100 |
2020-03-01 00:00:00 | 200 | 150 | 300 |
2020-04-01 00:00:00 | 300 | 200 | 600 |
2020-05-01 00:00:00 | 400 | 250 | 1000 |
2020-06-01 00:00:00 | 500 | 300 | 1500 |
2020-07-01 00:00:00 | 600 | 350 | 2100 |
2020-08-01 00:00:00 | 700 | 400 | 2800 |
2020-09-01 00:00:00 | 800 | 450 | 3600 |
2020-10-01 00:00:00 | 900 | 500 | 4500 |
LAG()
で差分を求める
続いてLAG()
で差分を求めてみましょう。
今回は以下のように売り上げが入力されているテーブルsales
を使います。
id | sales | created_at |
---|---|---|
1 | 1000 | 2020-02-01 00:00:00 |
2 | 900 | 2020-03-01 00:00:00 |
3 | 850 | 2020-04-01 00:00:00 |
4 | 900 | 2020-05-01 00:00:00 |
5 | 950 | 2020-06-01 00:00:00 |
6 | 1000 | 2020-07-01 00:00:00 |
7 | 800 | 2020-08-01 00:00:00 |
8 | 980 | 2020-09-01 00:00:00 |
9 | 1000 | 2020-10-01 00:00:00 |
sales
カラムには月ごとの売り上げデータが入っており、これに「前月との差分」を表示してみましょう。
SQLは以下のようになります。
SELECT created_at, sales, sales - LAG(sales, 1, 0) OVER (ORDER BY created_at) AS diff FROM sales;
結果は次のようになります。
created_at | sales | diff |
---|---|---|
2020-02-01 00:00:00 | 1000 | 1000 |
2020-03-01 00:00:00 | 900 | -100 |
2020-04-01 00:00:00 | 850 | -50 |
2020-05-01 00:00:00 | 900 | 50 |
2020-06-01 00:00:00 | 950 | 50 |
2020-07-01 00:00:00 | 1000 | 50 |
2020-08-01 00:00:00 | 800 | -200 |
2020-09-01 00:00:00 | 980 | 180 |
2020-10-01 00:00:00 | 1000 | 20 |
注目すべきは以下の部分ですね。
sales - LAG(sales, 1, 0) OVER (ORDER BY created_at) AS diff
LAG()
は、ORDER BY
で指定した順番のうち「前に現れたレコードの値を取得」できます。(厳密に言うと、何レコード前の値を取得するかは引数で指定可能。)
今回は以下のようになっています。
今回は、1ヶ月前の売り上げの値を取得して、前月との差分を求めた、というわけです。
PARTITION BY
と組み合わせれば、より複雑な分析も可能になります。
さらに、LAG()
とは逆に「次に現れるレコード」を取得するLEAD()
もあります。
まとめ
ここでは様々な実例をためしながら、ウィンドウ関数の動作を図解でまとめました。
VIEW
と組み合わせれば、閲覧用のデータを作成するのに便利そうですね。
SQLを使って、自在に手早く欲しいデータが抽出できるようになりたいものですね。