(cache) SQL:2003 のウィンドウ関数を MariaDB 10.2 で試す

今回は SQL:2003 の規格で追加されたウィンドウ関数を使ってみる。この機能を使うとカラムをグループ化して集約関数を使うのが楽になる。

使った環境は次の通り。

$ sw_vers                    
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

MariaDB では 10.2.0 からウィンドウ関数が使えるようになっている。

Window Functions - MariaDB Knowledge Base

インストールは Homebrew を使えばさくっといける。

$ brew install mariadb
$ brew services start mariadb

ちなみに MySQL 5.7 ではウィンドウ関数がまだ実装されていない。

サンプル用のデータを用意する

まずは MariaDB のシェルに入る。

$ mysql -u root

サンプル用のデータを入れるためのデータベースを作成する。

> DROP DATABASE IF EXISTS sample;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)
> USE sample
Database changed

サンプル用のテーブルを作る。今回はボードゲームのレーティングを示すテーブルっぽいものにした。テーブル名はプレイヤーの方が良かったかな。

> DROP TABLE IF EXISTS users;
Query OK, 0 rows affected, 1 warning (0.00 sec)
> CREATE TABLE users (
    ->   name VARCHAR(255),
    ->   category VARCHAR(255),
    ->   rate INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

なんか適当にレコードを追加しておく。ゲームのカテゴリごとにプレイヤーとレーティングが格納されている。

> INSERT INTO
    ->   users
    -> VALUES
    ->   ('Alice', 'Shogi', 2000),
    ->   ('Bob', 'Igo', 1800),
    ->   ('Carol', 'Shogi', 1800),
    ->   ('Daniel', 'Igo', 1600);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

なんかこんな感じのデータができた。

> SELECT
    ->   *
    -> FROM users;
+--------+----------+------+
| name   | category | rate |
+--------+----------+------+
| Alice  | Shogi    | 2000 |
| Bob    | Igo      | 1800 |
| Carol  | Shogi    | 1800 |
| Daniel | Igo      | 1600 |
+--------+----------+------+
4 rows in set (0.00 sec)

GROUP BY と共に集約関数を使う

まずはオーソドックスな GROUP BY と一緒に集約関数を使うやり方を見てみる。

カテゴリごとのレーティングを AVG() 関数を使って計算してみよう。

> SELECT
    ->   category,
    ->   AVG(rate) AS avg
    -> FROM users
    -> GROUP BY category;
+----------+-----------+
| category | avg       |
+----------+-----------+
| Igo      | 1700.0000 |
| Shogi    | 1900.0000 |
+----------+-----------+
2 rows in set (0.00 sec)

まあ、見慣れた感じだ。

ちなみに GROUP BY を使うと基本的には SELECT で表示できるカラムが限定される。具体的には GROUP BY で指定したものか、あるいは集約関数を適用したものしか使うことができない。次の例では GROUP BY で指定しているわけでも集約関数を適用したわけでもない name カラムを SELECT に指定している。これはエラーにはなっていないものの、平均しているのに特定のレコードの内容 (name) が表示されていて、あまり意味をなしていない。

> SELECT
    ->   name,
    ->   category,
    ->   AVG(rate) AS avg
    -> FROM users
    -> GROUP BY category;
+-------+----------+-----------+
| name  | category | avg       |
+-------+----------+-----------+
| Bob   | Igo      | 1700.0000 |
| Alice | Shogi    | 1900.0000 |
+-------+----------+-----------+
2 rows in set (0.00 sec)

ウィンドウ関数を使う

それでは続いてウィンドウ関数を使うパターンを紹介する。ウィンドウ関数では GROUP BY の代わりに集約関数の後に OVER() をつける。

次の例では先ほどと同じようにカテゴリごとのレーティングの平均を計算している。異なるのは GROUP BY の代わりにウィンドウ関数を使っているところ。 OVER() には PARTITION BY でグループ化するカラムを指定する。

> SELECT
    ->   name,
    ->   category,
    ->   AVG(rate) OVER(PARTITION BY category) AS avg
    -> FROM users;
+--------+----------+-----------+
| name   | category | avg       |
+--------+----------+-----------+
| Alice  | Shogi    | 1900.0000 |
| Bob    | Igo      | 1700.0000 |
| Carol  | Shogi    | 1900.0000 |
| Daniel | Igo      | 1700.0000 |
+--------+----------+-----------+
4 rows in set (0.01 sec)

ウィンドウ関数を使う場合は全てのレコードに対して集約関数の結果が表示されている。また、もちろん AVG() だけでなく SUM() や COUNT() といった集約関数でも同じように使える。

また、ウィンドウ関数では新たに使える集約関数が増えている。例えば RANK() を使うと特定のカラムの内容に応じて順位をつけたりできる。次の例では rate の内容に応じてソートした上で、それに順位をつけている。

> SELECT
    ->   name,
    ->   rate,
    ->   RANK() OVER(ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+------+------+
| name   | rate | rank |
+--------+------+------+
| Alice  | 2000 |    1 |
| Bob    | 1800 |    2 |
| Carol  | 1800 |    2 |
| Daniel | 1600 |    4 |
+--------+------+------+
4 rows in set (0.00 sec)

ちなみに、同じ値のときでも異なる番号を振りたいときは ROW_NUMBER() を使う。

> SELECT
    ->   name,
    ->   rate,
    ->   ROW_NUMBER() OVER(ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+------+------+
| name   | rate | rank |
+--------+------+------+
| Alice  | 2000 |    1 |
| Bob    | 1800 |    2 |
| Carol  | 1800 |    3 |
| Daniel | 1600 |    4 |
+--------+------+------+
4 rows in set (0.00 sec)

先ほどの例だとカテゴリの違いを無視して順位付けをしたので、ちょっと不自然だったかもしれない。グループ化した上で順位をつけたいときは、次のように PARTITION BY と ORDER BY を組み合わせて使う。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   RANK() OVER(PARTITION BY category ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+----------+------+------+
| name   | category | rate | rank |
+--------+----------+------+------+
| Alice  | Shogi    | 2000 |    1 |
| Bob    | Igo      | 1800 |    1 |
| Carol  | Shogi    | 1800 |    2 |
| Daniel | Igo      | 1600 |    2 |
+--------+----------+------+------+
4 rows in set (0.00 sec)

ばっちり。

まとめ

SQL:2003 の規格で追加されたウィンドウ関数を使うとグループ化して集約関数を適用するのが楽にできる。

CUBE SUGAR CONTAINER

技術系のこと書きます。

SQL:2003 のウィンドウ関数を MariaDB 10.2 で試す

サンプル用のデータを用意する

GROUP BY と共に集約関数を使う

ウィンドウ関数を使う

まとめ