【図解】SQLウィンドウ関数の使い方

SQLにはウィンドウ関数という協力な機能があります。
ウィンドウ関数には順位を求めるRANK()がありますが、今回はウィンドウ関数のOVERやPARTITION BYの使い方について、よくあるユースケースを題材にしてまとめます。
※今回の掲載するSQLはPostgreSQLで動作確認を行っています。

PostgreSQL

ウィンドウ関数とは

ウィンドウ関数は、レコードの順位など、他のレコードと比較した結果などの新しいカラムを作成する関数のことです。
集約関数と似ていますが、動作は全く違います。
集約関数はGROUP BYした複数のレコードを1つのレコードに「まとめる（集約する）」働きをします。

集約関数は複数のレコードを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というサービスもオススメです。

https://www.db-fiddle.com/

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)というウィンドウ関数を使いました。
それぞれの項目の説明は以下の通りです。

RANK() OVER (...)の図解

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が同じレコード間での順位が計算されたということです。

PARTITION BYの図解

では、次はもう少し分析的な使い方をしてみましょう。

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で指定した順番のうち「前に現れたレコードの値を取得」できます。（厳密に言うと、何レコード前の値を取得するかは引数で指定可能。）

今回は以下のようになっています。

LAG()関数の引数

今回は、1ヶ月前の売り上げの値を取得して、前月との差分を求めた、というわけです。

LAG()関数の動作

PARTITION BYと組み合わせれば、より複雑な分析も可能になります。
さらに、LAG()とは逆に「次に現れるレコード」を取得するLEAD()もあります。

まとめ

ここでは様々な実例をためしながら、ウィンドウ関数の動作を図解でまとめました。
VIEWと組み合わせれば、閲覧用のデータを作成するのに便利そうですね。
SQLを使って、自在に手早く欲しいデータが抽出できるようになりたいものですね。

参考リンク

www.postgresql.jp

参考文献