今回は 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/ROW_NUMBER/DENSE_RANK
また、ウィンドウ関数では新たに使える集約関数が増えている。
例えば 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)
同じ順位があったとき、その分の順位を飛ばさないようにするには DENSE_RANK() を使う。
> SELECT -> name, -> rate, -> DENSE_RANK() OVER(ORDER BY rate DESC) AS rank -> FROM users; +--------+------+------+ | name | rate | rank | +--------+------+------+ | Alice | 2000 | 1 | | Bob | 1800 | 2 | | Carol | 1800 | 2 | | Daniel | 1600 | 3 | +--------+------+------+ 4 rows in set (0.01 sec)
PARTITION BY と ORDER BY を組み合わせて使う
先ほどの例だとカテゴリの違いを無視して順位付けをしたので、ちょっと不自然だったかもしれない。
グループ化した上で順位をつけたいときは、次のように 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)
LAG/LEAD
それ以外にも LAG() や LEAD() を使うと現在のレコードの前後のレコードが取得できる。
> SELECT -> name, -> category, -> rate, -> -- 一つ前のレコードを得る -> LAG(name) OVER(ORDER BY rate DESC) AS prev1, -> -- 二つ前のレコードを得る -> LAG(name, 2) OVER(ORDER BY rate DESC) AS prev2, -> -- 一つ先のレコードを得る -> LEAD(name) OVER(ORDER BY rate DESC) AS next1, -> -- 二つ先のレコードを得る -> LEAD(name, 2) OVER(ORDER BY rate DESC) AS next2 -> FROM users; +--------+----------+------+-------+-------+--------+--------+ | name | category | rate | prev1 | prev2 | next1 | next2 | +--------+----------+------+-------+-------+--------+--------+ | Alice | Shogi | 2000 | NULL | NULL | Bob | Carol | | Bob | Igo | 1800 | Alice | NULL | Carol | Daniel | | Carol | Shogi | 1800 | Bob | Alice | Daniel | NULL | | Daniel | Igo | 1600 | Carol | Bob | NULL | NULL | +--------+----------+------+-------+-------+--------+--------+ 4 rows in set (0.00 sec)
ROWS … で集計する範囲を指定する
また、集約関数の対象となる範囲を ROWS ... で指定できる。
例えば、次のようにすると現在のレコードの前後一つずつを SUM() で合計できる。
PRECEDING の前に指定するのが前いくつ分を対象にするか。
そして FOLLOWING の前に指定するのが後ろいくつ分を対象にするかになる。
> SELECT -> name, -> category, -> rate, -> -- 前後一つずつのレートを合計する -> SUM(rate) OVER( -> ORDER BY rate DESC -> ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -> ) AS sum_neighbor -> FROM users; +--------+----------+------+--------------+ | name | category | rate | sum_neighbor | +--------+----------+------+--------------+ | Alice | Shogi | 2000 | 3800 | | Bob | Igo | 1800 | 5600 | | Carol | Shogi | 1800 | 5200 | | Daniel | Igo | 1600 | 3400 | +--------+----------+------+--------------+ 4 rows in set (0.00 sec)
変数となっているところを UNBOUNDED にすると無制限になる。
> SELECT -> name, -> category, -> rate, -> SUM(rate) OVER( -> ORDER BY rate DESC -> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -> ) AS sum_neighbor -> FROM users; +--------+----------+------+--------------+ | name | category | rate | sum_neighbor | +--------+----------+------+--------------+ | Alice | Shogi | 2000 | 7200 | | Bob | Igo | 1800 | 7200 | | Carol | Shogi | 1800 | 7200 | | Daniel | Igo | 1600 | 7200 | +--------+----------+------+--------------+ 4 rows in set (0.00 sec)
上手く使えば、こんな感じで累積の値を簡単に集計できる。
> SELECT -> name, -> category, -> rate, -> SUM(rate) OVER( -> ORDER BY rate DESC -> ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING -> ) AS cumulative_sum -> FROM users; +--------+----------+------+----------------+ | name | category | rate | cumulative_sum | +--------+----------+------+----------------+ | Alice | Shogi | 2000 | 2000 | | Bob | Igo | 1800 | 3800 | | Carol | Shogi | 1800 | 5600 | | Daniel | Igo | 1600 | 7200 | +--------+----------+------+----------------+ 4 rows in set (0.00 sec)
この ROWS ... の指定をしないと挙動が変わってしまう集約関数もあるようなので気をつけたい。
> SELECT -> name, -> category, -> rate, -> SUM(rate) OVER( -> ORDER BY rate DESC -> ) AS sum_neighbor -> FROM users; +--------+----------+------+--------------+ | name | category | rate | sum_neighbor | +--------+----------+------+--------------+ | Alice | Shogi | 2000 | 2000 | | Bob | Igo | 1800 | 5600 | | Carol | Shogi | 1800 | 5600 | | Daniel | Igo | 1600 | 7200 | +--------+----------+------+--------------+ 4 rows in set (0.00 sec)
FIRST_VALUE/LAST_VALUE
例えば最初に登場する要素と最後に登場する要素を取得する FIRST_VALUE() と LAST_VALUE() も ROWS ... の指定がいるようだ。
> SELECT -> name, -> category, -> rate, -> -- レートでソートして一番最初にくる内容を表示する -> FIRST_VALUE(name) OVER( -> ORDER BY rate DESC -> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -> ) AS first, -> -- レートでソートして一番最後にくる内容を表示する -> LAST_VALUE(name) OVER( -> ORDER BY rate DESC -> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -> ) AS last -> FROM users; +--------+----------+------+-------+--------+ | name | category | rate | first | last | +--------+----------+------+-------+--------+ | Alice | Shogi | 2000 | Alice | Daniel | | Bob | Igo | 1800 | Alice | Daniel | | Carol | Shogi | 1800 | Alice | Daniel | | Daniel | Igo | 1600 | Alice | Daniel | +--------+----------+------+-------+--------+ 4 rows in set (0.00 sec)
ROWS ... をコメントアウトすると、次のように意図した挙動にならない。
> SELECT -> name, -> category, -> rate, -> -- ROWS ... を入れないと上手く動作しない場合がある -> FIRST_VALUE(name) OVER( -> ORDER BY rate DESC -> -- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -> ) AS first, -> -- ROWS ... を入れないと上手く動作しない場合がある -> LAST_VALUE(name) OVER( -> ORDER BY rate DESC -> -- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -> ) AS last -> FROM users; +--------+----------+------+-------+--------+ | name | category | rate | first | last | +--------+----------+------+-------+--------+ | Alice | Shogi | 2000 | Alice | Alice | | Bob | Igo | 1800 | Alice | Carol | | Carol | Shogi | 1800 | Alice | Carol | | Daniel | Igo | 1600 | Alice | Daniel | +--------+----------+------+-------+--------+ 4 rows in set (0.00 sec)
まとめ
SQL:2003 の規格で追加されたウィンドウ関数を使うとグループ化して集約関数を適用するのが楽にできる。
ただし、集約関数の適用範囲を決める ROWS ... の挙動には注意が必要となる。
- 作者: 加嵜長門,田宮直人,丸山弘詩
- 出版社/メーカー: マイナビ出版
- 発売日: 2017/03/27
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
