Window関数についての知識をまとめた(その1)

はじめに

データ分析における重要な関数そうなので、何章かに分けて、Window関数についてまとめてみます。

ドキュメントばかり見てもわかりにくいので、いくつかのサンプルを用意して説明します。

今回は、トリアヘズ動かして、集計関数との違いをまとめています。

MySQL における Window 関数

自分はMySQLをよく使うのでMySQLにおけるWindow関数について述べます。

MySQL でも 8.0 から Window 関数が使えるようになったそうです。

自分のローカルの環境では、バージョンが古く使えないので、試せない。

$ mysql --version
$ mysql  Ver 14.14 Distrib 5.6.47, for osx10.13 (x86_64) using  EditLine wrapper

そこで、ブラウザ上からMySQL 8.0 の環境を試せる 「DB Fiddle」というWebサービスを使ってみます。

Window関数とは

現在の行に対して、指定したグループ全体から見た集計値を計算する関数

GROUP BYなどの集計関数との違い

• 集計関数： 同じカラム値をもつ全行を一つに集計する（1行に集約される）
• Window関数： 行ごとに単一の値を返す（対象の行はそのまま残る）

BigQuery StandardSQLのドキュメントに Window関数 は 分析関数と呼ばれています。

そこでは、以下のように書かれています。

分析関数は、行のグループに対して値を計算し、各行に対して 1 つの結果を返します。これは、行のグループに対して 1 つの結果を返す集計関数とは異なります。

実行してみて違いを見てみよう

Schema (MySQL v8.0)

    CREATE TABLE tests (
        id INT NOT NULL PRIMARY KEY auto_increment,
        category_id INT NOT NULL,
        val INT NOT NULL
    );
    
    INSERT INTO tests VALUES
        ( 1, 100,     1),
        ( 2, 200,     2),
        ( 3, 100,    10),
        ( 4, 200,    20),
        ( 5, 100,   100),
        ( 6, 200,   200),
        ( 7, 100,  1000),
        ( 8, 200,  2000),
        ( 9, 100, 10000),
        (10, 200, 20000);

Query

    SELECT
      category_id,
      SUM(val) AS total
    FROM tests
    GROUP BY category_id;

Results

category_id	total
100	11111
200	22222

予想通りの出力かなと思います。

Window関数を用いると...

Query

    SELECT
    	id,
      category_id,
      val,
      SUM(val) OVER (PARTITION BY category_id) AS total
    FROM tests;

Results

id	category_id	val	total
1	100	1	11111
3	100	10	11111
5	100	100	11111
7	100	1000	11111
9	100	10000	11111
2	200	2	22222
4	200	20	22222
6	200	200	22222
8	200	2000	22222
10	200	20000	22222

---

View on DB Fiddle

行ごとに単一の値（計算結果）を返す（対象の行はそのまま残る）

とはこのことです。実際に実行してみるとイメージは詳細になりましたね。

次回はもう少し詳しく見ていきます。

参照

26-29p

アウトプット100本ノック実施中