こんにちは、knsk765 です。データの分析するときってウィンドウ関数は欠かせないですよね。
はじめてウィンドウ関数を知ったときは衝撃でした。これまで強引な集計処理をゴリゴリ書いてどれだけの時間を無駄にしつづけたのかと。
ざっくりいうと GROUP BY を使った集約関数のように結果をまとめてしまわずに、元の行を保持したまま集計列を追加できるのがウィンドウ関数です。
Excel の表に関数を入れた集計列を追加するのがイメージとしては近いかなと思います。
ウィンドウ関数のちゃんとした説明はこちら（PostgreSQL 文書）。
目次
準備
実行環境
PostgreSQL 14.5
テーブル
まずはサンプルデータを用意します。
CREATE TABLE public.scores ( id int4 NOT NULL, acquisition_date date NOT NULL, user_name varchar NOT NULL, score int4 NOT NULL, CONSTRAINT scores_pk PRIMARY KEY (id) ); INSERT INTO scores VALUES (1, '2024-03-14', '松崎しげろ', 87), (2, '2024-03-14', '竹内刀', 60), (3, '2024-03-14', 'ア三バ', 0), (4, '2024-03-14', '空条Q太郎', 14), (5, '2024-03-15', '松崎しげろ', 96), (6, '2024-03-15', '竹内刀', 25), (7, '2024-03-15', 'ア三バ', 43), (8, '2024-03-15', '空条Q太郎', 8), (9, '2024-03-16', '松崎しげろ', 32), (10, '2024-03-16', '竹内刀', 63), (11, '2024-03-16', 'ア三バ', 19), (12, '2024-03-16', '空条Q太郎', 68);
こんなデータです。なんかの点数を取得した日々の履歴的なイメージです。 なんの点数かは謎だし登場人物も謎。
| id | acquisition_date | user_name | score |
|---|---|---|---|
| 1 | 2024-03-14 | 松崎しげろ | 87 |
| 2 | 2024-03-14 | 竹内刀 | 60 |
| 3 | 2024-03-14 | ア三バ | 0 |
| 4 | 2024-03-14 | 空条Q太郎 | 14 |
| 5 | 2024-03-15 | 松崎しげろ | 96 |
| 6 | 2024-03-15 | 竹内刀 | 25 |
| 7 | 2024-03-15 | ア三バ | 43 |
| 8 | 2024-03-15 | 空条Q太郎 | 8 |
| 9 | 2024-03-16 | 松崎しげろ | 32 |
| 10 | 2024-03-16 | 竹内刀 | 63 |
| 11 | 2024-03-16 | ア三バ | 19 |
| 12 | 2024-03-16 | 空条Q太郎 | 68 |
その日一番高い得点を調べろ
GROUP BY と MAX 関数
まずはウィンドウ関数ではなく普通の集約関数で。
SELECT acquisition_date, MAX(score) max_score FROM scores GROUP BY acquisition_date ORDER BY acquisition_date;
| acquisition_date | max_score |
|---|---|
| 2024-03-14 | 87 |
| 2024-03-15 | 96 |
| 2024-03-16 | 68 |
これは簡単ですね。
じゃあ、その最高得点をとったのは誰なのか知りたくないですか？知りたいですよね？
こんな結果がほしいわけです。
| acquisition_date | score | user_name |
|---|---|---|
| 2024-03-14 | 87 | 松崎しげろ |
| 2024-03-15 | 96 | 松崎しげろ |
| 2024-03-16 | 68 | 空条Q太郎 |
では答えから。
WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY acquisition_date ORDER BY score DESC) rownum, acquisition_date, score, user_name FROM scores ) SELECT acquisition_date, score, user_name FROM cte WHERE rownum = 1 ORDER BY acquisition_date;
ここで注目すべきは ROW_NUMBER。 これがウィンドウ関数です。
なにをしているのか、分解して見てみましょう。
ROW_NUMBER 関数
SELECT ROW_NUMBER() OVER (PARTITION BY acquisition_date ORDER BY score DESC) rownum, acquisition_date, score, user_name FROM scores;
実行結果。ROW_NUMBER 関数で 1 から 4 の番号が振られていますね。
| rownum | acquisition_date | score | user_name |
|---|---|---|---|
| 1 | 2024-03-14 | 87 | 松崎しげろ |
| 2 | 2024-03-14 | 60 | 竹内刀 |
| 3 | 2024-03-14 | 14 | 空条Q太郎 |
| 4 | 2024-03-14 | 0 | ア三バ |
| 1 | 2024-03-15 | 96 | 松崎しげろ |
| 2 | 2024-03-15 | 43 | ア三バ |
| 3 | 2024-03-15 | 25 | 竹内刀 |
| 4 | 2024-03-15 | 8 | 空条Q太郎 |
| 1 | 2024-03-16 | 68 | 空条Q太郎 |
| 2 | 2024-03-16 | 63 | 竹内刀 |
| 3 | 2024-03-16 | 32 | 松崎しげろ |
| 4 | 2024-03-16 | 19 | ア三バ |
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：
ROW_NUMBER は SELECT で取得した結果に連番を振る関数です。
振り方のルールは OVER の中の PARTITION BY と ORDER BY で決まります。
PARTITION BY acquisition_date：
GROUP BY のようなもので、同じ acquisition_date の値でレコードセットをグルーピングします。
ORDER BY score DESC：
グルーピングされた acquisition_date の中で score 降順に並べます。
つまり
- 日付ごとにグルーピングした中で
- 点数の高い順で 1 から順番に番号を振る
こうすると日付ごとに rownum = 1 のレコードが最も高い点数となるので、あとは絞り込むだけ。
最高点と最低点出したやつ誰だ
こんなふうに日ごとに最高得点者と最低得点者を出すこともできます。
| acquisition_date | score | user_name |
|---|---|---|
| 2024-03-14 | 87 | 松崎しげろ |
| 2024-03-14 | 0 | ア三バ |
| 2024-03-15 | 96 | 松崎しげろ |
| 2024-03-15 | 8 | 空条Q太郎 |
| 2024-03-16 | 68 | 空条Q太郎 |
| 2024-03-16 | 19 | ア三バ |
WITH cte AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY acquisition_date ORDER BY score asc) rownum_asc, ROW_NUMBER() OVER ( PARTITION BY acquisition_date ORDER BY score desc) rownum_desc, acquisition_date, user_name, score FROM scores ) SELECT acquisition_date, score, user_name FROM cte WHERE rownum_asc = 1 or rownum_desc = 1 ORDER BY acquisition_date, rownum_desc, rownum_asc;
集計もしたい
SUM で累計推移
ウィンドウ関数として、SUM なども利用できます。
人ごとの日別累計推移を出してみましょう。
WITH cte AS ( SELECT SUM(score) OVER ( PARTITION BY user_name ORDER BY acquisition_date) score_sum, acquisition_date, user_name, score FROM scores ) SELECT user_name, acquisition_date, score, score_sum FROM cte ORDER BY user_name, acquisition_date;
| user_name | acquisition_date | score | score_sum |
|---|---|---|---|
| ア三バ | 2024-03-14 | 0 | 0 |
| ア三バ | 2024-03-15 | 43 | 43 |
| ア三バ | 2024-03-16 | 19 | 62 |
| 松崎しげろ | 2024-03-14 | 87 | 87 |
| 松崎しげろ | 2024-03-15 | 96 | 183 |
| 松崎しげろ | 2024-03-16 | 32 | 215 |
| 空条Q太郎 | 2024-03-14 | 14 | 14 |
| 空条Q太郎 | 2024-03-15 | 8 | 22 |
| 空条Q太郎 | 2024-03-16 | 68 | 90 |
| 竹内刀 | 2024-03-14 | 60 | 60 |
| 竹内刀 | 2024-03-15 | 25 | 85 |
| 竹内刀 | 2024-03-16 | 63 | 148 |
その日の平均値より高いのかい？低いのかい？
どっちなんだい？
WITH cte AS ( SELECT ROUND( AVG(score) OVER (PARTITION BY acquisition_date), 1) score_avg, acquisition_date, user_name, score FROM scores ) SELECT acquisition_date, score_avg, user_name, score, CASE WHEN score < score_avg THEN '😱' WHEN score > score_avg THEN '😍' ELSE '😐' END status FROM cte ORDER BY acquisition_date, user_name;
CASE も使って平均との比較をわかりやすく表示してみました。
| acquisition_date | score_avg | user_name | score | status |
|---|---|---|---|---|
| 2024-03-14 | 40.3 | ア三バ | 0 | 😱 |
| 2024-03-14 | 40.3 | 松崎しげろ | 87 | 😍 |
| 2024-03-14 | 40.3 | 空条Q太郎 | 14 | 😱 |
| 2024-03-14 | 40.3 | 竹内刀 | 60 | 😍 |
| 2024-03-15 | 43.0 | ア三バ | 43 | 😐 |
| 2024-03-15 | 43.0 | 松崎しげろ | 96 | 😍 |
| 2024-03-15 | 43.0 | 空条Q太郎 | 8 | 😱 |
| 2024-03-15 | 43.0 | 竹内刀 | 25 | 😱 |
| 2024-03-16 | 45.5 | ア三バ | 19 | 😱 |
| 2024-03-16 | 45.5 | 松崎しげろ | 32 | 😱 |
| 2024-03-16 | 45.5 | 空条Q太郎 | 68 | 😍 |
| 2024-03-16 | 45.5 | 竹内刀 | 63 | 😍 |
昨日と比べてどう？
SQL で取得したレコード同士、前後の行で比較して差分を出したりしたいことってないですか？
Excel なら簡単なのに... と諦めるのは早い。できます。
では前日の自分の得点との差分を出してみましょう。
WITH cte AS ( SELECT LAG(score, 1) OVER ( PARTITION BY user_name ORDER BY acquisition_date) score_previous, acquisition_date, user_name, score FROM scores ) SELECT user_name, acquisition_date, score, score_previous, score - score_previous AS score_diff FROM cte ORDER BY user_name, acquisition_date;
| user_name | acquisition_date | score | score_previous | score_diff |
|---|---|---|---|---|
| ア三バ | 2024-03-14 | 0 | (NULL) | (NULL) |
| ア三バ | 2024-03-15 | 43 | 0 | 43 |
| ア三バ | 2024-03-16 | 19 | 43 | -24 |
| 松崎しげろ | 2024-03-14 | 87 | (NULL) | (NULL) |
| 松崎しげろ | 2024-03-15 | 96 | 87 | 9 |
| 松崎しげろ | 2024-03-16 | 32 | 96 | -64 |
| 空条Q太郎 | 2024-03-14 | 14 | (NULL) | (NULL) |
| 空条Q太郎 | 2024-03-15 | 8 | 14 | -6 |
| 空条Q太郎 | 2024-03-16 | 68 | 8 | 60 |
| 竹内刀 | 2024-03-14 | 60 | (NULL) | (NULL) |
| 竹内刀 | 2024-03-15 | 25 | 60 | -35 |
| 竹内刀 | 2024-03-16 | 63 | 25 | 38 |
score_previous 列を見ると、同じユーザーの 1行前の score が表示されています。
LAG 関数を使うと指定した行数分前のデータを持って来れます。
LAG(対象列, さかのぼる行数) OVER (PARTITION BY ... ORDER BY ...)：
今回は LAG の第二引数に 1 を指定したので 1行前のデータを持ってきたわけです。
逆にあとの行を持ってきたいときは LEAD 関数を使います。
などと得意げに語っていますが、今さっきまで LAG も LEAD も知りませんでした...
おまけ
前回の記事の「おまけ」で書いた下の表、今回ご紹介したウィンドウ関数を使えば実現できます。
| id | tree_nmae |
|---|---|
| 1 | +- 管理本部 |
| 101 | | +- 総務人事部 |
| 10101 | | | +- 総務課 |
| 10102 | | | `- 人事課 |
| 102 | | +- 財務部 |
| 10201 | | | `- 経理課 |
| 103 | | `- 情報システム室 |
| 2 | +- 営業本部 |
| 201 | | +- 営業企画部 |
| 20101 | | | `- 営業企画課 |
| 202 | | `- 営業部 |
| 20201 | | +- 大阪営業所 |
| 20202 | | +- 東京営業所 |
| 20203 | | `- 名古屋営業所 |
| 3 | `- 開発本部 |
| 301 | +- 商品開発部 |
| 30101 | | +- 1課 |
| 30102 | | `- 2課 |
| 3010201 | | +- 第一小隊 |
| 3010202 | | `- 第二小隊 |
| 302 | `- 空手部 |
「気になる答えはまた次の機会に！」と書きましたが、また引っ張ります。
いや、早く答え合わせさせろよ！という方は下の採用ページからぜひご応募を！
