はじめに
Oracle DatabaseなどのRDBMSでは、SELECT文で「ウィンドウ関数」と呼ばれる強力な分析機能を使うことができますが、Google Cloud BigQueryでも同様の機能があるため、いくつか抜粋して紹介します。
本記事を書いた後に軽く調べてみたところ、ウィンドウ関数はANSI SQL:2003で標準規定が設けられたそうで、代表的なRDBMSでは、たいていの場合ウィンドウ関数を使用できます。
筆者はかつてMySQLでウィンドウ関数が使えずに苦しんでいた時期があったため、「BigQueryでも使えるんだ！やったー！」という感激が先走って本記事を書いてしまいました。
事前準備
ウィンドウ関数を試すために、以下のDDLを実行して、テーブルとデータを用意します。
DDL
CREATE TABLE exam.exam_results (
school_year INT64 NOT NULL,
class_room STRING NOT NULL,
student_name STRING NOT NULL,
subject STRING NOT NULL,
score INT64 NOT NULL
);
INSERT
INSERT INTO exam.exam_results (school_year, class_room, student_name, subject, score)
VALUES
(2, 'A', '岩田 梨奈', '現代文', 72),
(2, 'A', '木村 孝之', '現代文', 89),
(2, 'A', '杉本 圭子', '現代文', 94),
(2, 'A', '藤田 和也', '現代文', 40),
(2, 'A', '山本 実穂', '現代文', 85),
(2, 'A', '岩田 梨奈', '数学', 58),
(2, 'A', '木村 孝之', '数学', 61),
(2, 'A', '杉本 圭子', '数学', 88),
(2, 'A', '藤田 和也', '数学', 64),
(2, 'A', '山本 実穂', '数学', 29),
(2, 'A', '岩田 梨奈', '倫理', 46),
(2, 'A', '木村 孝之', '倫理', 73),
(2, 'A', '杉本 圭子', '倫理', 90),
(2, 'A', '藤田 和也', '倫理', 53),
(2, 'A', '山本 実穂', '倫理', 63),
(2, 'B', '荒井 健介', '現代文', 67),
(2, 'B', '植田 洋介', '現代文', 29),
(2, 'B', '平林 美鈴', '現代文', 44),
(2, 'B', '松尾 有佳', '現代文', 38),
(2, 'B', '山口 勝彦', '現代文', 62),
(2, 'B', '荒井 健介', '数学', 100),
(2, 'B', '植田 洋介', '数学', 89),
(2, 'B', '平林 美鈴', '数学', 77),
(2, 'B', '松尾 有佳', '数学', 96),
(2, 'B', '山口 勝彦', '数学', 13),
(2, 'B', '荒井 健介', '倫理', 8),
(2, 'B', '植田 洋介', '倫理', 72),
(2, 'B', '平林 美鈴', '倫理', 95),
(2, 'B', '松尾 有佳', '倫理', 41),
(2, 'B', '山口 勝彦', '倫理', 89)
;
生徒氏名はすごい名前生成器でランダムに作成しました。
集計関数による集計
ウィンドウ関数の妥当性を検証できるよう、事前にINSERTしたデータを用いて、学年・科目別の平均点および、学年・学級・科目別の平均点を求めます。
学年・科目別平均点
SQL
SELECT
school_year, subject,
SUM(score) AS total_score, -- score合計値
COUNT(1) AS row_count, -- 受験者数
AVG(score) AS average_score -- 平均得点
FROM
exam.exam_results
GROUP BY
school_year, subject
ORDER BY
school_year, subject DESC
;
出力結果
| school_year | subject | total_score | row_count | average_score |
|---|---|---|---|---|
| 2 | 現代文 | 620 | 10 | 62.0 |
| 2 | 数学 | 675 | 10 | 67.5 |
| 2 | 倫理 | 630 | 10 | 63.0 |
学年・学級・科目別平均点
SQL
SELECT
school_year, class_room, subject,
SUM(score) AS total_score, -- score合計値
COUNT(1) AS row_count, -- 受験者数
AVG(score) AS average_score -- 平均得点
FROM
exam.exam_results
GROUP BY
school_year, class_room, subject
ORDER BY
school_year, class_room, subject DESC
;
出力結果
| school_year | class_room | subject | total_score | row_count | average_score |
|---|---|---|---|---|---|
| 2 | A | 現代文 | 380 | 5 | 76.0 |
| 2 | A | 数学 | 300 | 5 | 60.0 |
| 2 | A | 倫理 | 325 | 5 | 65.0 |
| 2 | B | 現代文 | 240 | 5 | 48.0 |
| 2 | B | 数学 | 375 | 5 | 75.0 |
| 2 | B | 倫理 | 305 | 5 | 61.0 |
ウィンドウ関数使用例
以下、ウィンドウ関数の実装例です。
合算値、平均値の導出
SQL
SELECT
school_year, class_room, subject, student_name, score,
SUM(score) OVER(PARTITION BY school_year, subject) AS total_score, -- 学年全体の科目別合計点
COUNT(1) OVER(PARTITION BY school_year, subject) AS row_count, -- 学年全体の受験者数
AVG(score) OVER(PARTITION BY school_year, subject) AS average_score, -- 学年・科目別平均点
SUM(score) OVER(PARTITION BY school_year, class_room, subject) AS c_total_score, -- 学年・学級科目別合計点
COUNT(1) OVER(PARTITION BY school_year, class_room, subject) AS c_row_count, -- 学級別受験者数
AVG(score) OVER(PARTITION BY school_year, class_room, subject) AS c_average_score, -- 学年・学級・科目別平均点
CASE
WHEN score <= AVG(score) OVER(PARTITION BY school_year, subject) / 2 THEN TRUE
ELSE FALSE
END AS failed -- 赤点（得点が学年平均の半分以下なら真）
FROM
exam.exam_results
ORDER BY
school_year, class_room, subject DESC, student_name
;
出力結果
| school_year | class_room | subject | student_name | score | total_score | row_count | average_score | c_total_score | c_row_count | c_average_score | failed |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | A | 現代文 | 山本 実穂 | 85 | 620 | 10 | 62.0 | 380 | 5 | 76.0 | false |
| 2 | A | 現代文 | 岩田 梨奈 | 72 | 620 | 10 | 62.0 | 380 | 5 | 76.0 | false |
| 2 | A | 現代文 | 木村 孝之 | 89 | 620 | 10 | 62.0 | 380 | 5 | 76.0 | false |
| 2 | A | 現代文 | 杉本 圭子 | 94 | 620 | 10 | 62.0 | 380 | 5 | 76.0 | false |
| 2 | A | 現代文 | 藤田 和也 | 40 | 620 | 10 | 62.0 | 380 | 5 | 76.0 | false |
| 2 | A | 数学 | 山本 実穂 | 29 | 675 | 10 | 67.5 | 300 | 5 | 60.0 | true |
| 2 | A | 数学 | 岩田 梨奈 | 58 | 675 | 10 | 67.5 | 300 | 5 | 60.0 | false |
| 2 | A | 数学 | 木村 孝之 | 61 | 675 | 10 | 67.5 | 300 | 5 | 60.0 | false |
| 2 | A | 数学 | 杉本 圭子 | 88 | 675 | 10 | 67.5 | 300 | 5 | 60.0 | false |
| 2 | A | 数学 | 藤田 和也 | 64 | 675 | 10 | 67.5 | 300 | 5 | 60.0 | false |
| 2 | A | 倫理 | 山本 実穂 | 63 | 630 | 10 | 63.0 | 325 | 5 | 65.0 | false |
| 2 | A | 倫理 | 岩田 梨奈 | 46 | 630 | 10 | 63.0 | 325 | 5 | 65.0 | false |
| 2 | A | 倫理 | 木村 孝之 | 73 | 630 | 10 | 63.0 | 325 | 5 | 65.0 | false |
| 2 | A | 倫理 | 杉本 圭子 | 90 | 630 | 10 | 63.0 | 325 | 5 | 65.0 | false |
| 2 | A | 倫理 | 藤田 和也 | 53 | 630 | 10 | 63.0 | 325 | 5 | 65.0 | false |
| 2 | B | 現代文 | 山口 勝彦 | 62 | 620 | 10 | 62.0 | 240 | 5 | 48.0 | false |
| 2 | B | 現代文 | 平林 美鈴 | 44 | 620 | 10 | 62.0 | 240 | 5 | 48.0 | false |
| 2 | B | 現代文 | 松尾 有佳 | 38 | 620 | 10 | 62.0 | 240 | 5 | 48.0 | false |
| 2 | B | 現代文 | 植田 洋介 | 29 | 620 | 10 | 62.0 | 240 | 5 | 48.0 | true |
| 2 | B | 現代文 | 荒井 健介 | 67 | 620 | 10 | 62.0 | 240 | 5 | 48.0 | false |
| 2 | B | 数学 | 山口 勝彦 | 13 | 675 | 10 | 67.5 | 375 | 5 | 75.0 | true |
| 2 | B | 数学 | 平林 美鈴 | 77 | 675 | 10 | 67.5 | 375 | 5 | 75.0 | false |
| 2 | B | 数学 | 松尾 有佳 | 96 | 675 | 10 | 67.5 | 375 | 5 | 75.0 | false |
| 2 | B | 数学 | 植田 洋介 | 89 | 675 | 10 | 67.5 | 375 | 5 | 75.0 | false |
| 2 | B | 数学 | 荒井 健介 | 100 | 675 | 10 | 67.5 | 375 | 5 | 75.0 | false |
| 2 | B | 倫理 | 山口 勝彦 | 89 | 630 | 10 | 63.0 | 305 | 5 | 61.0 | false |
| 2 | B | 倫理 | 平林 美鈴 | 95 | 630 | 10 | 63.0 | 305 | 5 | 61.0 | false |
| 2 | B | 倫理 | 松尾 有佳 | 41 | 630 | 10 | 63.0 | 305 | 5 | 61.0 | false |
| 2 | B | 倫理 | 植田 洋介 | 72 | 630 | 10 | 63.0 | 305 | 5 | 61.0 | false |
| 2 | B | 倫理 | 荒井 健介 | 8 | 630 | 10 | 63.0 | 305 | 5 | 61.0 | true |
クラス別の総得点や平均点は、事前に集計した値と一致しています。
本例では末尾の項目「赤点」を「得点が学年・科目別の平均点の半分以下」と定義しているように、CASE式の中でもウィンドウ関数を使用できます。
ランク関数使用例
先ほどのクエリでは全生徒のデータを取得しましたが、次は各学級・科目ごとに上位3位以内のデータを取得します。
SQL
SELECT
school_year, class_room, subject, student_name, score,
all_subject_rank, c_subject_rank
FROM
(
SELECT
school_year, class_room, subject, student_name, score,
RANK() OVER(PARTITION BY school_year, subject ORDER BY score DESC) AS all_subject_rank, -- 学年・科目別得点ランク
RANK() OVER(PARTITION BY school_year, class_room, subject ORDER BY score DESC) AS c_subject_rank -- 学年・学級・科目別得点ランク
FROM
exam.exam_results
)
WHERE
c_subject_rank <= 3
ORDER BY
subject DESC, all_subject_rank
;
出力結果
| school_year | class_room | subject | student_name | score | all_subject_rank | c_subject_rank |
|---|---|---|---|---|---|---|
| 2 | A | 現代文 | 杉本 圭子 | 94 | 1 | 1 |
| 2 | A | 現代文 | 木村 孝之 | 89 | 2 | 2 |
| 2 | A | 現代文 | 山本 実穂 | 85 | 3 | 3 |
| 2 | B | 現代文 | 荒井 健介 | 67 | 5 | 1 |
| 2 | B | 現代文 | 山口 勝彦 | 62 | 6 | 2 |
| 2 | B | 現代文 | 平林 美鈴 | 44 | 7 | 3 |
| 2 | B | 数学 | 荒井 健介 | 100 | 1 | 1 |
| 2 | B | 数学 | 松尾 有佳 | 96 | 2 | 2 |
| 2 | B | 数学 | 植田 洋介 | 89 | 3 | 3 |
| 2 | A | 数学 | 杉本 圭子 | 88 | 4 | 1 |
| 2 | A | 数学 | 藤田 和也 | 64 | 6 | 2 |
| 2 | A | 数学 | 木村 孝之 | 61 | 7 | 3 |
| 2 | B | 倫理 | 平林 美鈴 | 95 | 1 | 1 |
| 2 | A | 倫理 | 杉本 圭子 | 90 | 2 | 1 |
| 2 | B | 倫理 | 山口 勝彦 | 89 | 3 | 2 |
| 2 | A | 倫理 | 木村 孝之 | 73 | 4 | 2 |
| 2 | B | 倫理 | 植田 洋介 | 72 | 5 | 3 |
| 2 | A | 倫理 | 山本 実穂 | 63 | 6 | 3 |
Aクラスには現代文の成績優秀者が、Bクラスには数学の成績優秀者が、固まっている傾向を見て取れます。
WHERE句の中ではウィンドウ関数を使用できない点に注意しましょう。