BigQueryでウィンドウ関数を使って、分析に役立つSQLを書いてみる

# はじめに

Oracle DatabaseなどのRDBMSでは、SELECT文で「ウィンドウ関数」と呼ばれる強力な分析機能を使うことができますが、Google Cloud BigQueryでも同様の機能があるため、いくつか抜粋して紹介します。

:::note 
本記事を書いた後に軽く調べてみたところ、ウィンドウ関数はANSI SQL:2003で標準規定が設けられたそうで、代表的なRDBMSでは、たいていの場合ウィンドウ関数を使用できます。
筆者はかつてMySQLでウィンドウ関数が使えずに苦しんでいた時期があったため、「BigQueryでも使えるんだ！やったー！」という感激が先走って本記事を書いてしまいました。
:::

## 事前準備

ウィンドウ関数を試すために、以下のDDLを実行して、テーブルとデータを用意します。
```sql: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
);
```
```sql: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)
;
```
:::note
生徒氏名は[すごい名前生成器](https://namegen.jp/)でランダムに作成しました。
:::

# 集計関数による集計

ウィンドウ関数の妥当性を検証できるよう、事前にINSERTしたデータを用いて、学年・科目別の平均点および、学年・学級・科目別の平均点を求めます。

## 学年・科目別平均点
```sql: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: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: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: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クラスには数学の成績優秀者が、固まっている傾向を見て取れます。

:::note
WHERE句の中ではウィンドウ関数を使用できない点に注意しましょう。
:::