WINDOW 関数を一通り試してみよう
- 公開日
- カテゴリ：MySQL
- タグ：MySQL
MySQL も 8 から WINDOW 関数が使えるようになり更に利便性が向上していますが、SQL の WINDOW 関数にはどんな関数があるのか？ということで、WINDOW 関数を一通り試してみます。
Contents
- WINDOW 関数
- 基本形
- 名前付き Window
- サンプルテーブル
- COUNT
- SUM
- AVG
- LAG / LEAD
- ROW_NUMBER
- RANK
- NTILE
- DENSE_RANK
- PERCENT_RANK
- CUME_DIST
- FIRST_VALUE / LAST_VALUE / NTH_VALUE
- JSON_ARRAYAGG
- JSON_OBJECTAGG
WINDOW 関数
WINDOW 関数は、結果行の集約を行うことなく集計・分析のための計算を行う事のできる関数。
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.1 Window 関数の説明
- 分析関数のコンセプト|BigQuery|Google Cloud
- PostgreSQL: Documentation: 14: 3.5. Window Functions
ウィンドウ関数は、一連のクエリー行に対して集計のような操作を実行します。ただし、集計操作ではクエリー行が単一の結果行にグループ化されますが、ウィンドウ関数ではクエリー行ごとに結果が生成されます。
引用元：MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.2 Window 関数の概念と構文
例えば集計関数を用いる際に GROUP BY すると結果行がまとめられますが、WINDOW 関数を用いる（もしくは集約関数を WINDOW 関数として処理する）場合は結果行はまとめられず、各行に結果が付与されるようになります。
# GROUP BY で集計
SELECT
user_id,
AVG(score) AS average
FROM scores
GROUP BY user_id;
+---------+---------+
| user_id | average |
+---------+---------+
| 1 | 76.7204 | <-- 行が user_id でまとめられる
| 2 | 73.7097 |
| 3 | 74.1505 |
| 4 | 74.5591 |
| 5 | 75.2688 |
+---------+---------+
# WINDOW 関数で集計
SELECT
user_id,
score,
AVG(score) OVER (PARTITION BY user_id) AS average
FROM scores;
+---------+-------+---------+
| user_id | score | average |
+---------+-------+---------+
| 1 | 85 | 76.7204 | <-- それぞれのレコードに average が付与される
| 1 | 83 | 76.7204 |
| 1 | 88 | 76.7204 |
| 1 | 90 | 76.7204 |
| 1 | 58 | 76.7204 |
+---------+-------+---------+
（AVG() は集約関数ですが上記のように集約関数を WINDOW 関数として処理する事も可能）
基本形
分析関数 OVER句([PARTITION BY 句] [ORDER BY 句] [frame 句])
FUNCTION_NAME(expr) OVER ([PARTITION BY expr, expr,..] [ORDER BY expr, expr,..] [ROWS|RANGE ...])
- PARTITION BY 句：どのカラムをグループとするか
- ORDER BY 句：ソート順の指定
- frame 句：対象行範囲の指定
これらを必要に応じて指定しつつ、振りたい値を行に付与していく。
名前付き Window
ウィンドウを定義する事で同じものをまとめられてクエリを簡単にできる。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.4 名前付きウィンドウ
SELECT
subject,
user_id,
average,
MIN(average) OVER w1 as min_avg,
MAX(average) OVER w1 as max_avg
FROM user_avg_scores_by_subject
WINDOW w1 AS (PARTITION BY subject) <- ウィンドウを定義
;
サンプルテーブル
サンプルで score テーブルを作成し、ここに対してクエリを投げていきます。
create table scores
(
id bigint unsigned auto_increment primary key,
user_id int unsigned not null,
subject varchar(255) not null,
score int unsigned not null,
implementation_date date not null,
created_at timestamp null,
updated_at timestamp null
)
collate = utf8mb4_unicode_ci;
5 名のユーザーが 1 日に 3 科目のテストを 1 ヶ月間受けた結果を格納したテーブルになっています。
mysql> SELECT id, user_id, subject, score, implementation_date FROM scores;
+-----+---------+---------+-------+---------------------+
| id | user_id | subject | score | implementation_date |
+-----+---------+---------+-------+---------------------+
| 1 | 1 | sub01 | 85 | 2021-12-01 |
| 2 | 1 | sub02 | 83 | 2021-12-01 |
| 3 | 1 | sub03 | 88 | 2021-12-01 |
| 4 | 1 | sub01 | 90 | 2021-12-02 |
| 5 | 1 | sub02 | 58 | 2021-12-02 |
| 6 | 1 | sub03 | 84 | 2021-12-02 |
| 7 | 1 | sub01 | 76 | 2021-12-03 |
| 8 | 1 | sub02 | 72 | 2021-12-03 |
（略）
次項からクエリを書いていきますが、COUNT や SUM などの集約関数も WINDOW 関数として処理できるのでそちらも含めて試していきます。
COUNT
レコード数をカウントします。
SELECT
implementation_date,
subject,
user_id,
score,
COUNT(user_id) OVER (PARTITION BY user_id) as total_tests
FROM scores
ORDER BY implementation_date, subject, user_id;
PARTITION BY 句に user_id を指定して、ユーザーごとの総レコード数（＝総テスト実施数）を付与します。
+---------------------+---------+---------+-------+-------------+
| implementation_date | subject | user_id | score | total_tests |
+---------------------+---------+---------+-------+-------------+
| 2021-12-01 | sub01 | 1 | 85 | 93 |
| 2021-12-01 | sub01 | 2 | 85 | 93 |
| 2021-12-01 | sub01 | 3 | 78 | 93 |
| 2021-12-01 | sub01 | 4 | 93 | 93 |
| 2021-12-01 | sub01 | 5 | 78 | 93 |
（略）
| 2021-12-31 | sub03 | 5 | 61 | 93 |
+---------------------+---------+---------+-------+-------------+
465 rows in set (0.00 sec)
1 日 3 テスト × 1 ヶ月（31 日）のため 1 人あたりの総テスト実施数は 93 回ですが、それらが各レコードに追加されていることがわかります。（このテーブルの総レコード数は 93 × 5 人分 = 465 レコード）
SUM
合計値を算出します。
SELECT
user_id,
subject,
score,
implementation_date,
SUM(score) OVER (PARTITION BY user_id, subject) as total_by_subject
FROM scores
ORDER BY user_id, implementation_date, subject;
user_id と subject でグルーピングして、各ユーザーの科目ごとの合計得点を付与します。
+---------+---------+-------+---------------------+------------------+
| user_id | subject | score | implementation_date | total_by_subject |
+---------+---------+-------+---------------------+------------------+
| 1 | sub01 | 85 | 2021-12-01 | 2417 |
| 1 | sub02 | 83 | 2021-12-01 | 2405 |
| 1 | sub03 | 88 | 2021-12-01 | 2313 |
（略）
| 5 | sub03 | 61 | 2021-12-31 | 2438 |
+---------+---------+-------+---------------------+------------------+
465 rows in set (0.00 sec)
AVG
平均値を算出します。
SELECT
user_id,
implementation_date,
subject,
score,
AVG(score) OVER (PARTITION BY user_id, subject) AS average
FROM scores
ORDER BY user_id, implementation_date, subject;
ユーザごと各科目の平均点を付与します。
+---------+---------------------+---------+-------+---------+
| user_id | implementation_date | subject | score | average |
+---------+---------------------+---------+-------+---------+
| 1 | 2021-12-01 | sub01 | 85 | 77.9677 |
| 1 | 2021-12-01 | sub02 | 83 | 77.5806 |
| 1 | 2021-12-01 | sub03 | 88 | 74.6129 |
（略）
| 5 | 2021-12-31 | sub03 | 61 | 78.6452 |
+---------+---------------------+---------+-------+---------+
465 rows in set (0.01 sec)
また、 frame 句で範囲を指定すればその区間での平均値も算出できるので、移動平均を求めたい時にも使えて便利です。
SELECT
user_id,
implementation_date,
subject,
score,
AVG(score) OVER (PARTITION BY user_id, subject) AS average,
AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average_3,
AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_average_5
FROM scores
ORDER BY user_id, implementation_date, subject;
3 区間と 5 区間での平均値を付与します。
+---------+---------------------+---------+-------+---------+------------------+------------------+
| user_id | implementation_date | subject | score | average | moving_average_3 | moving_average_5 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
| 1 | 2021-12-01 | sub01 | 85 | 77.9677 | 87.5000 | 83.6667 |
| 1 | 2021-12-01 | sub02 | 83 | 77.5806 | 70.5000 | 71.0000 |
| 1 | 2021-12-01 | sub03 | 88 | 74.6129 | 86.0000 | 80.3333 |
| 1 | 2021-12-02 | sub01 | 90 | 77.9677 | 83.6667 | 79.5000 |
| 1 | 2021-12-02 | sub02 | 58 | 77.5806 | 71.0000 | 71.2500 |
| 1 | 2021-12-02 | sub03 | 84 | 74.6129 | 80.3333 | 75.7500 |
| 1 | 2021-12-03 | sub01 | 76 | 77.9677 | 77.6667 | 81.8000 |
| 1 | 2021-12-03 | sub02 | 72 | 77.5806 | 67.3333 | 72.6000 |
| 1 | 2021-12-03 | sub03 | 69 | 74.6129 | 71.6667 | 72.8000 |
（略）
| 5 | 2021-12-31 | sub03 | 61 | 78.6452 | 63.5000 | 60.3333 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
465 rows in set (0.00 sec)
LAG / LEAD
指定行分、前の値（LAG）・後ろの値（LEAD）を付与します。
SELECT
user_id,
implementation_date,
subject,
score,
LAG(score, 1) over (PARTITION BY user_id, subject) as last_score,
LEAD(score, 1) over (PARTITION BY user_id, subject) as next_score
FROM scores
ORDER BY user_id, subject, implementation_date;
user_id と subject でグルーピングした上で、各行に前後行の score を付与します。
+---------+---------------------+---------+-------+------------+------------+
| user_id | implementation_date | subject | score | last_score | next_score |
+---------+---------------------+---------+-------+------------+------------+
| 1 | 2021-12-01 | sub01 | 85 | NULL | 90 |
| 1 | 2021-12-02 | sub01 | 90 | 85 | 76 |
| 1 | 2021-12-03 | sub01 | 76 | 90 | 67 |
| 1 | 2021-12-04 | sub01 | 67 | 76 | 91 |
| 1 | 2021-12-05 | sub01 | 91 | 67 | 98 |
| 1 | 2021-12-06 | sub01 | 98 | 91 | 58 |
| 1 | 2021-12-07 | sub01 | 58 | 98 | 83 |
（略）
| 1 | 2021-12-30 | sub01 | 93 | 56 | 76 |
| 1 | 2021-12-31 | sub01 | 76 | 93 | NULL |
| 1 | 2021-12-01 | sub02 | 83 | NULL | 58 |
| 1 | 2021-12-02 | sub02 | 58 | 83 | 72 |
| 1 | 2021-12-03 | sub02 | 72 | 58 | 72 |
（略）
| 5 | 2021-12-31 | sub03 | 61 | 66 | NULL |
+---------+---------------------+---------+-------+------------+------------+
465 rows in set (0.00 sec)
グルーピングされた行の先頭と後尾についてはそれぞれ last と next が存在しないので null になっています。
ROW_NUMBER
行グループ内で序数を振る。
SELECT
implementation_date,
user_id,
ROW_NUMBER() over (PARTITION BY user_id, subject) as times,
subject,
score
FROM scores;
ユーザーと科目でグルーピングし、科目毎の実施回（times）を振ってみます。
+---------------------+---------+-------+---------+-------+
| implementation_date | user_id | times | subject | score |
+---------------------+---------+-------+---------+-------+
| 2021-12-01 | 1 | 1 | sub01 | 85 |
| 2021-12-02 | 1 | 2 | sub01 | 90 |
| 2021-12-03 | 1 | 3 | sub01 | 76 |
| 2021-12-04 | 1 | 4 | sub01 | 67 |
| 2021-12-05 | 1 | 5 | sub01 | 91 |
| 2021-12-06 | 1 | 6 | sub01 | 98 |
（略）
| 2021-12-29 | 1 | 29 | sub01 | 56 |
| 2021-12-30 | 1 | 30 | sub01 | 93 |
| 2021-12-31 | 1 | 31 | sub01 | 76 |
| 2021-12-01 | 1 | 1 | sub02 | 83 |
| 2021-12-02 | 1 | 2 | sub02 | 58 |
| 2021-12-03 | 1 | 3 | sub02 | 72 |
（略）
| 2021-12-29 | 5 | 29 | sub03 | 54 |
| 2021-12-30 | 5 | 30 | sub03 | 66 |
| 2021-12-31 | 5 | 31 | sub03 | 61 |
+---------------------+---------+-------+---------+-------+
465 rows in set (0.01 sec)
RANK
順位付けを行う。
SELECT
implementation_date,
subject,
user_id,
score,
RANK() OVER (PARTITION BY subject, implementation_date ORDER BY score DESC) as ranking
FROM scores
ORDER BY implementation_date, subject, ranking;
subject と implementation_date でグルーピングを行い、score 降順でランク付けする事で、テスト日ごと各科目別の順位を付与します。
+---------------------+---------+---------+-------+---------+
| implementation_date | subject | user_id | score | ranking |
+---------------------+---------+---------+-------+---------+
| 2021-12-01 | sub01 | 4 | 93 | 1 |
| 2021-12-01 | sub01 | 1 | 85 | 2 |
| 2021-12-01 | sub01 | 2 | 85 | 2 |
| 2021-12-01 | sub01 | 3 | 78 | 4 |
| 2021-12-01 | sub01 | 5 | 78 | 4 |
| 2021-12-01 | sub02 | 1 | 83 | 1 |
| 2021-12-01 | sub02 | 2 | 81 | 2 |
| 2021-12-01 | sub02 | 5 | 76 | 3 |
| 2021-12-01 | sub02 | 3 | 70 | 4 |
| 2021-12-01 | sub02 | 4 | 50 | 5 |
| 2021-12-01 | sub03 | 4 | 97 | 1 |
（略）
| 2021-12-31 | sub03 | 5 | 61 | 5 |
+---------------------+---------+---------+-------+---------+
465 rows in set (0.01 sec)
NTILE
指定したグループ数で分類してランク付けを行う。分類は近しい値（＝できるだけ同じサイズ）でグルーピングされる。
SELECT
user_id,
total,
NTILE(3) OVER (ORDER BY total desc) as g_rank
FROM user_total_scores;
5 人の合計得点を 3 つのグループに分類してランク付けを行います。
+---------+-------+--------+
| user_id | total | g_rank |
+---------+-------+--------+
| 1 | 7135 | 1 |
| 5 | 7000 | 1 |
| 4 | 6934 | 2 |
| 3 | 6896 | 2 |
| 2 | 6855 | 3 |
+---------+-------+--------+
5 rows in set (0.00 sec)
3 つのグループに分類されてランク付けされているのが確認できます。
DENSE_RANK
グループのランクを振る。
同率順位があっても順位が繰り下がらないところが rank() との違い。
SELECT
subject,
user_id,
average,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY average desc) AS d_rnk,
RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk
FROM user_avg_scores_by_subject;
各科目別でのユーザーの平均点に対してランク付けを行います。
+---------+---------+---------+-------+-----+
| subject | user_id | average | d_rnk | rnk |
+---------+---------+---------+-------+-----+
| sub01 | 1 | 78 | 1 | 1 |
| sub01 | 4 | 76 | 2 | 2 |
| sub01 | 3 | 75 | 3 | 3 |
| sub01 | 5 | 74 | 4 | 4 |
| sub01 | 2 | 73 | 5 | 5 |
| sub02 | 1 | 78 | 1 | 1 |
| sub02 | 4 | 76 | 2 | 2 |
| sub02 | 2 | 73 | 3 | 3 |
| sub02 | 3 | 73 | 3 | 3 |
| sub02 | 5 | 73 | 3 | 3 |
| sub03 | 5 | 79 | 1 | 1 |
| sub03 | 1 | 75 | 2 | 2 |
| sub03 | 2 | 75 | 2 | 2 |
| sub03 | 3 | 74 | 3 | 4 |
| sub03 | 4 | 71 | 4 | 5 |
+---------+---------+---------+-------+-----+
sub03 の順位を見てみると、同率順位があった際にその後の順位が繰り下がっていないことがわかります。
PERCENT_RANK
パーセントランクを算出する。
- ランク最上位を 0 としてランクをパーセントで振る。
- ランクの範囲は 0 〜 1
- パーセントランクの計算式は（rank - 1）/（ウィンドウまたはパーティションの行数 - 1）
SELECT
subject,
user_id,
average,
RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk,
PERCENT_RANK() OVER (PARTITION BY subject ORDER BY average desc) as p_rnk
FROM user_avg_scores_by_subject;
各位ユーザー科目毎の平均点に対してそれぞれパーセントランクを振ってみます。
+---------+---------+---------+-----+-------+
| subject | user_id | average | rnk | p_rnk |
+---------+---------+---------+-----+-------+
| sub01 | 1 | 78 | 1 | 0 |
| sub01 | 4 | 76 | 2 | 0.25 |
| sub01 | 3 | 75 | 3 | 0.5 |
| sub01 | 5 | 74 | 4 | 0.75 |
| sub01 | 2 | 73 | 5 | 1 |
| sub02 | 1 | 78 | 1 | 0 |
| sub02 | 4 | 76 | 2 | 0.25 |
| sub02 | 2 | 73 | 3 | 0.5 |
| sub02 | 3 | 73 | 3 | 0.5 |
| sub02 | 5 | 73 | 3 | 0.5 |
| sub03 | 5 | 79 | 1 | 0 |
| sub03 | 1 | 75 | 2 | 0.25 |
| sub03 | 2 | 75 | 2 | 0.25 |
| sub03 | 3 | 74 | 4 | 0.75 |
| sub03 | 4 | 71 | 5 | 1 |
+---------+---------+---------+-----+-------+
CUME_DIST
行グループ内で累積分布を振る。
指定したグループの（orderによる）最終行を 1 として、そこに向けて 0 から積み上がっていくイメージ。相対的な位置がわかる。
SELECT
user_id,
subject,
average,
CUME_DIST() OVER (PARTITION BY subject ORDER BY average) as cume_dist_by_avg
FROM user_avg_scores_by_subject
ORDER BY user_id, subject;
ユーザーの科目別平均から、科目をグループとして累積分布を振ってみます。
+---------+---------+---------+------------------+
| user_id | subject | average | cume_dist_by_avg |
+---------+---------+---------+------------------+
| 1 | sub01 | 78 | 1 |
| 1 | sub02 | 78 | 1 |
| 1 | sub03 | 75 | 0.8 |
| 2 | sub01 | 73 | 0.2 |
| 2 | sub02 | 73 | 0.6 |
| 2 | sub03 | 75 | 0.8 |
| 3 | sub01 | 75 | 0.6 |
| 3 | sub02 | 73 | 0.6 |
| 3 | sub03 | 74 | 0.4 |
| 4 | sub01 | 76 | 0.8 |
| 4 | sub02 | 76 | 0.8 |
| 4 | sub03 | 71 | 0.2 |
| 5 | sub01 | 74 | 0.4 |
| 5 | sub02 | 73 | 0.6 |
| 5 | sub03 | 79 | 1 |
+---------+---------+---------+------------------+
結果を見ると、ユーザー 1 は科目 sub03 の成績に関しては 80% の位置にいる事がわかります。
FIRST_VALUE / LAST_VALUE / NTH_VALUE
グループにおいてそれぞれ最初・最後・指定行の値を振る。
SELECT
subject,
user_id,
average,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;
科目別ユーザー平均点から、点数順においてそれぞれ最初・最後・3 番目行の値を振ってみます。
+---------+---------+---------+-------+------+-------------+
| subject | user_id | average | first | last | third_value |
+---------+---------+---------+-------+------+-------------+
| sub01 | 1 | 78 | 73 | 78 | 75 |
| sub01 | 4 | 76 | 73 | 78 | 75 |
| sub01 | 3 | 75 | 73 | 78 | 75 |
| sub01 | 5 | 74 | 73 | 78 | 75 |
| sub01 | 2 | 73 | 73 | 78 | 75 |
| sub02 | 1 | 78 | 73 | 78 | 73 |
| sub02 | 4 | 76 | 73 | 78 | 73 |
| sub02 | 2 | 73 | 73 | 78 | 73 |
| sub02 | 3 | 73 | 73 | 78 | 73 |
| sub02 | 5 | 73 | 73 | 78 | 73 |
| sub03 | 5 | 79 | 71 | 79 | 75 |
| sub03 | 1 | 75 | 71 | 79 | 75 |
| sub03 | 2 | 75 | 71 | 79 | 75 |
| sub03 | 3 | 74 | 71 | 79 | 75 |
| sub03 | 4 | 71 | 71 | 79 | 75 |
+---------+---------+---------+-------+------+-------------+
LAST_VALUE() で全体の last を取る場合はデフォルトは自分行までしか読まないため上記クエリのように frame 句の指定が必要です。
その場合は FIRST_VALUE() を使って last も取得するとシンプルだし事故らなくて良いと思いました。
SELECT
subject,
user_id,
average,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average DESC) as last_by_first_value, -- <- average の降順の最初行をとる
NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;
結果は LAST_VALUE() と同じになります。
+---------+---------+---------+-------+------+---------------------+-------------+
| subject | user_id | average | first | last | last_by_first_value | third_value |
+---------+---------+---------+-------+------+---------------------+-------------+
| sub01 | 1 | 78 | 73 | 78 | 78 | 75 |
| sub01 | 4 | 76 | 73 | 78 | 78 | 75 |
| sub01 | 3 | 75 | 73 | 78 | 78 | 75 |
| sub01 | 5 | 74 | 73 | 78 | 78 | 75 |
| sub01 | 2 | 73 | 73 | 78 | 78 | 75 |
| sub02 | 1 | 78 | 73 | 78 | 78 | 73 |
| sub02 | 4 | 76 | 73 | 78 | 78 | 73 |
| sub02 | 2 | 73 | 73 | 78 | 78 | 73 |
| sub02 | 3 | 73 | 73 | 78 | 78 | 73 |
| sub02 | 5 | 73 | 73 | 78 | 78 | 73 |
| sub03 | 5 | 79 | 71 | 79 | 79 | 75 |
| sub03 | 1 | 75 | 71 | 79 | 79 | 75 |
| sub03 | 2 | 75 | 71 | 79 | 79 | 75 |
| sub03 | 3 | 74 | 71 | 79 | 79 | 75 |
| sub03 | 4 | 71 | 71 | 79 | 79 | 75 |
+---------+---------+---------+-------+------+---------------------+-------------+
JSON_ARRAYAGG
グループの値を JSON にまとめる。
SELECT
user_id,
subject,
implementation_date,
score,
JSON_ARRAYAGG(score) OVER (PARTITION BY user_id, subject ORDER BY user_id, subject) as all_scores_for_subject
FROM scores
ORDER BY user_id, subject;
ユーザーの科目別の得点をまとめてみます。
+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
| user_id | subject | implementation_date | score | all_scores_for_subject |
+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | sub01 | 2021-12-01 | 85 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
| 1 | sub01 | 2021-12-02 | 90 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
| 1 | sub01 | 2021-12-03 | 76 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
（略）
そのユーザーの科目別の得点がまとめられていることが確認できました。
JSON_OBJECTAGG
グループの値を JSON にまとめる。
こちらは key:value の形式で出力できる。
SELECT
implementation_date,
subject,
user_id,
score,
JSON_OBJECTAGG(user_id, score) OVER (PARTITION BY implementation_date, subject ORDER BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as scores_for_everyone
FROM scores
ORDER BY implementation_date, subject, user_id;
結果行に、その回・科目の全員の得点を付与してみます。
+---------------------+---------+---------+-------+------------------------------------------------+
| implementation_date | subject | user_id | score | scores_for_everyone |
+---------------------+---------+---------+-------+------------------------------------------------+
| 2021-12-01 | sub01 | 1 | 85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 2 | 85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 3 | 78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 4 | 93 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 5 | 78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub02 | 1 | 83 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76} |
| 2021-12-01 | sub02 | 2 | 81 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76} |
（略）
| 2021-12-31 | sub03 | 5 | 61 | {"1": 79, "2": 96, "3": 67, "4": 100, "5": 61} |
+---------------------+---------+---------+-------+------------------------------------------------+
user_id : score でまとめられているのが確認できました。
まとめ
WINDOW 関数は「分析関数」と呼ばれているだけあって分析を行う上で知っておいて損はない便利な関数が多く、一度試しておくと選択肢の幅が広がるのでオススメです。
WINDOW 関数自体も、知ってると知らないとでは記述するクエリ量が地味に違ってくる（＝計算量も変わってくる）ので、集計関数と併せて上手く使いこなしていきたいところです。
