第89回　WINDOW関数を使ってみる

MySQL8.0から新たにWINDOW関数が追加されました。WINDOW関数は分析や集計に特化した関数群であり、データの分析、集計をする際に大変便利になっています。Oracle DatabaseやPostgreSQL等には以前からある機能なので、すでに利用したことがある方も多いかも知れません。今回はそんなWINDOW関数について使い方を学んで行きたいと思います。

なお、環境はCentOS7、MySQLはバージョン8.0.13を利用しています。また、サンプルデータとしてMySQL公式サイトにあるworld databaseデータを使用しています。

WINDOW関数を触ってみる

WINDOW関数を利用すれば通常、GROUP BYを用いた集約関数で集約されていた結果を集約することなく結果を表示することができます。

まずは例を見てみましょう。worldデータベースのcityテーブルから、CountryCodeを集約して各人口（Population）の平均と合計値を出力するとします。通常通りGROUP BYを利用して集約関数を使う場合は、下記のようにCountryCodeごとの出力になります。

mysql> SELECT CountryCode, sum(Population), avg(Population) FROM city GROUP BY CountryCode ORDER BY CountryCode;
+-------------+-----------------+-----------------+
| CountryCode | sum(Population) | avg(Population) |
+-------------+-----------------+-----------------+
| ABW         |           29034 |      29034.0000 |
| AFG         |         2332100 |     583025.0000 |
| AGO         |         2561600 |     512320.0000 |
(以下省略)

これをWINDOW関数を利用して出力してみます。

mysql> SELECT CountryCode, sum(Population)  OVER(PARTITION BY CountryCode)  as  sum_population, avg(Population)  OVER(PARTITION BY CountryCode) as avg_population FROM city ORDER BY CountryCode limit 20;
+-------------+----------------+----------------+
| CountryCode | sum_population | avg_population |
+-------------+----------------+----------------+
| ABW         |          29034 |     29034.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
（以下省略）

同じCountryCodeが複数回出力される結果になってしまいました。

今度はこの出力結果にDistrictカラムを追加して出力してみましょう。

mysql> SELECT CountryCode,District, sum(Population)  OVER(PARTITION BY CountryCode)  as  sum_population, avg(Population)  OVER(PARTITION BY CountryCode) as avg_population FROM city ORDER BY CountryCode limit 20;
+-------------+------------------+----------------+----------------+
| CountryCode | District         | sum_population | avg_population |
+-------------+------------------+----------------+----------------+
| ABW         | –                |          29034 |     29034.0000 |
| AFG         | Balkh            |        2332100 |    583025.0000 |
| AFG         | Kabol            |        2332100 |    583025.0000 |
| AFG         | Qandahar         |        2332100 |    583025.0000 |
| AFG         | Herat            |        2332100 |    583025.0000 |
| AGO         | Benguela         |        2561600 |    512320.0000 |
| AGO         | Luanda           |        2561600 |    512320.0000 |
| AGO         | Namibe           |        2561600 |    512320.0000 |
| AGO         | Huambo           |        2561600 |    512320.0000 |
| AGO         | Benguela         |        2561600 |    512320.0000 |
（以下省略）

今度は都市名が表示されるようになりました。このように、WINDOW関数は通常集約関数でまとめてしまっていたために表現できなかったカラムも表示することができます。

WINDOW関数の構文

ここからは実際にWINSOW関数の利用方法を確認します。

WINDOW関数を利用するにはOVER句を使用します。OVER句のカッコの中にはPARTITION BY、ORDER BY、空文字を指定することができます。PARTITION BYは分割するカラム名を入力し、ORDER BYには分割内の行の順序を指定します。もし、OVER句にPARITITION BYが指定されてなければ行全体を指します。

たとえば先ほどのクエリの場合、分割するカラム名はCountryCodeとなっているので、SELECT * FROM city order by CountryCode ascで表示させた結果を、以下のようにCountryCodeごとで区切られているイメージとなります。

cityテーブルを利用して、OVER句にCountryCodeごとと全体でのPopulationの合計を出してみます。

mysql> SELECT id, CountryCode, District, Population, sum(Population)  OVER(PARTITION BY CountryCode), sum(Population) OVER() FROM city ORDER BY CountryCode;
+-----+-------------+----------+------------+-------------------------------------------------+------------------------+
| id  | CountryCode | District | Population | sum(Population)  OVER(PARTITION BY CountryCode) | sum(Population) OVER() |
+-----+-------------+----------+------------+-------------------------------------------------+------------------------+
| 129 | ABW         | –        |      29034 |                                           29034 |             1429559884 |
|   2 | AFG         | Qandahar |     237500 |                                         2332100 |             1429559884 |
|   3 | AFG         | Herat    |     186800 |                                         2332100 |             1429559884 |
|   1 | AFG         | Kabol    |    1780000 |                                         2332100 |             1429559884 |
|   4 | AFG         | Balkh    |     127800 |                                         2332100 |             1429559884 |
|  56 | AGO         | Luanda   |    2022000 |                                         2561600 |             1429559884 |
|  59 | AGO         | Benguela |     128300 |                                         2561600 |             1429559884 |
|  57 | AGO         | Huambo   |     163100 |                                         2561600 |             1429559884 |
|  60 | AGO         | Namibe   |     118200 |                                         2561600 |             1429559884 |
|  58 | AGO         | Benguela |     130000 |                                         2561600 |             1429559884 |
（以下省略）

sum(Population) OVER(PARTITION BY CountryCode)には各国のPopulationの合計値が入ってきました。また、sum(Population) OVER()には全体のPopulationの合計値が出力されています。

下記の結果より、Population全体の合計値と同じになっていることが確認できました。

> SELECT sum(Population) FROM city;
+-----------------+
| sum(Population) |
+-----------------+
|      1429559884 |
+-----------------+
1 row in set (0.00 sec)

WINDOW関数で利用できる集約関数と非集約関数

WINDOW関数で利用できる集約関数はCOUNT()やMAX()など、たいていのものはWINDOW関数として扱うことができますが、COUNT(DISTINCT)やGROUP_CONCAT()などは利用することができません。利用できる集約関数は公式ドキュメントの12.21.2 Window Function Concepts and Syntaxにあるので参考にしてください。

また、MySQLには非集約関数として以下の11個の関数が用意されています。

関数名	概要
CUME_DIST()	累積分布を計算します。
DENSE_RANK()	パーティション内のランクを返します。RANKと違って連続する数字でランクを割り当てます。
FIRST_VALUE()	パーティション内の最初の行を返します。
LAG()	パーティション内でN行前の値を返します。
LAST_VALUE()	パーティション内の最後の行を返します
LEAD()	パーティション内でN行先の値を返します。
NTH_VALUE()	ウィンドウフレーム内のN行目を返します。
NTILE()	全体をN分割したときにどこに属するかを返します。
PERCENT_RANK()	パーティション内の％ランクを返します。
RANK()	パーティション内のランクを返します。
ROW_NUMBER()	パーティション内で順番をつけます。

例として、RANK関数とROW_NUMBER関数を使ってみましょう。

RANK関数を利用して、各CountryCodeごとのPopulationのランキング（district_ranking⁠）⁠、その出力結果の全体をシーケンシャルな数字row_numとして出力してみます。

mysql>SELECT
    ->     id,
    ->     CountryCode,
    ->     District,
    ->     Population,
    ->     sum(Population)  OVER(PARTITION BY CountryCode) as
sum_population,
    ->     rank() OVER(PARTITION BY CountryCode ORDER BY Population desc )
as district_ranking,
    ->     row_number() OVER() as row_num
    ->   FROM city ORDER BY CountryCode, district_ranking;
+------+-------------+------------------------+------------+----------------+------------------+---------+
| id   | CountryCode | District               | Population | sum_population | district_ranking | row_num |
+------+-------------+------------------------+------------+----------------+------------------+---------+
|  129 | ABW         | –                      |      29034 |          29034 |                1 |       1 |
|    1 | AFG         | Kabol                  |    1780000 |        2332100 |                1 |       2 |
|    2 | AFG         | Qandahar               |     237500 |        2332100 |                2 |       3 |
|    3 | AFG         | Herat                  |     186800 |        2332100 |                3 |       4 |
|    4 | AFG         | Balkh                  |     127800 |        2332100 |                4 |       5 |
|   56 | AGO         | Luanda                 |    2022000 |        2561600 |                1 |       6 |
|   57 | AGO         | Huambo                 |     163100 |        2561600 |                2 |       7 |
|   58 | AGO         | Benguela               |     130000 |        2561600 |                3 |       8 |
|   59 | AGO         | Benguela               |     128300 |        2561600 |                4 |       9 |
|   60 | AGO         | Namibe                 |     118200 |        2561600 |                5 |      10 |
~
~
| 4070 | ZWE         | Harare                 |     274912 |        2730420 |                3 |    4076 |
| 4071 | ZWE         | Harare                 |     164362 |        2730420 |                4 |    4077 |
| 4072 | ZWE         | Manicaland             |     131367 |        2730420 |                5 |    4078 |
| 4073 | ZWE         | Midlands               |     128037 |        2730420 |                6 |    4079 |
+------+-------------+------------------------+------------+----------------+------------------+---------+
4079 rows in set (0.02 sec)

RANK関数を利用してDistrictごとでPopulationのランキングを付けることができました。row_numにも連続して数字が1から割り振られています。

今までは相関サブクエリや一時集計テーブルを駆使して出力していたものが簡単に出力することができました。

それぞれの詳細な使い方については、公式ドキュメントの12.21.1 Window Function Descriptionsを参考にしてください。

フレームについて

フレームは分割された各パーティションでどこまでを対象とするかを定めることができます。たとえば、ROWS UNBOUNDED PRECEDINGとすればパーティションの開始行から現在までのすべての行をフレームとして定義でき、ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGとすれば、現在の行の前後1行をフレームにすることができます。詳細はここでは説明しませんが、もし気になる方は12.21.3 Window Function Frame Specificationをご確認ください。

名前付きWINDOW

WINDOWは名前をつけることができ、同じWINDOWを利用する場合は名前をつけたWINDOWを利用することで、簡潔に書くことができます。最初に利用したSQLを使って名前をつけて書き換えると、下記のように書くことができます。

> SELECT
    ->     CountryCode,
    ->     sum(Population)  OVER w as  sum_population,
    ->     avg(Population)  OVER w as avg_population
    -> FROM city WINDOW  w AS (PARTITION BY CountryCode)
    -> ORDER BY CountryCode limit 20;
+-------------+----------------+----------------+
| CountryCode | sum_population | avg_population |
+-------------+----------------+----------------+
| ABW         |          29034 |     29034.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AFG         |        2332100 |    583025.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
| AGO         |        2561600 |    512320.0000 |
（以下省略）

まとめ

今回は、MySQL8.0に新たに追加されたWINDOW関数について簡単に説明しました。データの件数が多い場合は素直に行かないことがあるかもしれませんが、いままで実施していた一時データの作成やサブクエリを用いた分析が、いくつか不要になることもあるかも知れないので、ぜひこの機会に一度触れてみてください。