MySQL8.0でウィンドウ関数が使えるようになったので試してみる
今更感はありますが、MySQL8.0から待望のWindow関数が実装されました。
SQLを利用してレポートの作成等を行う際には非常に便利なのですが、再度復習しておこうと思います。
なお、Shinjuku.LTのウェブサイトでは一切使われていません。
よく使われるSQLとは文法が結構違うため、まず最初に例を見てみようと思います。
なお、データはMySQLの公式サイトからダウンロードできる、 world database というデータを利用します。
select -- 言語毎にパーティショニングして、人工順にソートしてランクを出力 rank() over(partition by cl.language order by population desc) ranking, c.code, c.name, cl.language, -- 人工 * 割合。パーティション内で1行ごとに足しわせる sum(round(population * percentage / 100)) over(partition by cl.language order by population desc) summary from country c inner join countrylanguage cl on c.code = cl.countrycode limit 20; +---------+------+------------------+-----------+----------+ | ranking | code | name | language | summary | +---------+------+------------------+-----------+----------+ | 1 | GEO | Georgia | Abhyasi | 84456 | | 1 | UGA | Uganda | Acholi | 958232 | | 1 | BEN | Benin | Adja | 676767 | | 1 | ERI | Eritrea | Afar | 165550 | | 2 | DJI | Djibouti | Afar | 387574 | | 1 | ZAF | South Africa | Afrikaans | 5773911 | | 2 | NAM | Namibia | Afrikaans | 5937881 | | 1 | PER | Peru | Aimará | 590226 | | 2 | CHL | Chile | Aimará | 666281 | | 3 | BOL | Bolivia | Aimará | 932809 | | 1 | JPN | Japan | Ainu | 0 | | 1 | BEN | Benin | Aizo | 530439 | | 1 | GHA | Ghana | Akan | 10591088 | | 2 | CIV | Côte d’Ivoire | Akan | 15026888 | | 1 | ITA | Italy | Albaniana | 115360 | | 2 | YUG | Yugoslavia | Albaniana | 1870960 | | 3 | ALB | Albania | Albaniana | 5200735 | | 4 | MKD | Macedonia | Albaniana | 5664231 | | 1 | AGO | Angola | Ambo | 309072 | | 1 | ETH | Ethiopia | Amhara | 18769500 | +---------+------+------------------+-----------+----------+
コードの2行目の部分がWindow関数の部分です。
特徴的な記法なのが、
over(partition by cl.language order by population desc)
かと思います。
over句について
Window関数は、over句の中で partition by ~~ 、 order by ~~ のどちらかもしくは両方を組み合わせて使用します。
上記の例は、languageでパーティションを区切り、パーティション内でpopulationの順にソートする、といった内容になっています。
出力結果を見ると、partition by は group by と似ているような印象をもたれるかもしれませんが、
違いとして、group by は集約して集約した結果を1つのレコードにまとめます。
それに対して、 partiotion by は各行をパーティショニングはしますが、集約はしません。
そのため、入力と出力の行数は同じです。
order by については普通のSQLの order by と同じです。
MySQLにおいては order by は必須ではありませんが、Oracle等では order byがない場合構文エラーとなります。
Window集約関数
上記の例にもありますが、通常の集約関数(avg(), sum() 等)でも over句と組み合わせて使うことができます。
基本的に通常の集約関数と変わりませんが、集約処理は行われません。
よく使われるWindow関数
row_number()
最初に row_number() です。
名前の通り、単純に行番号を振る関数です。
単純に行番号として利用するもよし、ランキングのような使い方もできます！
MySQLでは、従来は行番号を振るのが面倒でしたが、row_number()により改善されました！
また、こちらにもあるように、
従来の方法では結果が保証されるわけではありません。
そのため、MySQL8.0以降ではこちらを積極的に利用したほうが良さそうです。
select row_number() over (order by GNP desc) row_num, name, GNP from country limit 20; +---------+--------------------+------------+ | row_num | name | GNP | +---------+--------------------+------------+ | 1 | United States | 8510700.00 | | 2 | Japan | 3787042.00 | | 3 | Germany | 2133367.00 | | 4 | France | 1424285.00 | | 5 | United Kingdom | 1378330.00 | | 6 | Italy | 1161755.00 | | 7 | China | 982268.00 | | 8 | Brazil | 776739.00 | | 9 | Canada | 598862.00 | | 10 | Spain | 553233.00 | | 11 | India | 447114.00 | | 12 | Mexico | 414972.00 | | 13 | Netherlands | 371362.00 | | 14 | Australia | 351182.00 | | 15 | Argentina | 340238.00 | | 16 | South Korea | 320749.00 | | 17 | Russian Federation | 276608.00 | | 18 | Switzerland | 264478.00 | | 19 | Taiwan | 256254.00 | | 20 | Belgium | 249704.00 | +---------+--------------------+------------+
rank(), dense_rank()
次に rank() です。
前述の row_number() と似ていますが、違いは同一の行には同じ行セットの場合は同一の順位を割り振ります。
また、rank() と似ている関数で dense_rank() という関数があります。
この2つの違いは、例えば2位の行セットが2つあった場合、前者は 1, 2, 2, 4 となりますが、
校舎は 1, 2, 2, 3 といったように出力されます。
関数の名前のとおり、ランキングとして使えると思います。
使用する際には row_number() も含め適切なものを比較検討すると良さそうです。
-- rank() select rank() over(order by countryCode) ranking, countryCode, language from countrylanguage limit 20; +---------+-------------+------------------+ | ranking | countryCode | language | +---------+-------------+------------------+ | 1 | ABW | Dutch | | 1 | ABW | English | | 1 | ABW | Papiamento | | 1 | ABW | Spanish | | 5 | AFG | Balochi | | 5 | AFG | Dari | | 5 | AFG | Pashto | | 5 | AFG | Turkmenian | | 5 | AFG | Uzbek | | 10 | AGO | Ambo | | 10 | AGO | Chokwe | | 10 | AGO | Kongo | | 10 | AGO | Luchazi | | 10 | AGO | Luimbe-nganguela | | 10 | AGO | Luvale | | 10 | AGO | Mbundu | | 10 | AGO | Nyaneka-nkhumbi | | 10 | AGO | Ovimbundu | | 19 | AIA | English | | 20 | ALB | Albaniana | +---------+-------------+------------------+ -- dense_rank() select dense_rank() over(order by countryCode) ranking, countryCode, language from countrylanguage limit 20; +---------+-------------+------------------+ | ranking | countryCode | language | +---------+-------------+------------------+ | 1 | ABW | Dutch | | 1 | ABW | English | | 1 | ABW | Papiamento | | 1 | ABW | Spanish | | 2 | AFG | Balochi | | 2 | AFG | Dari | | 2 | AFG | Pashto | | 2 | AFG | Turkmenian | | 2 | AFG | Uzbek | | 3 | AGO | Ambo | | 3 | AGO | Chokwe | | 3 | AGO | Kongo | | 3 | AGO | Luchazi | | 3 | AGO | Luimbe-nganguela | | 3 | AGO | Luvale | | 3 | AGO | Mbundu | | 3 | AGO | Nyaneka-nkhumbi | | 3 | AGO | Ovimbundu | | 4 | AIA | English | | 5 | ALB | Albaniana | +---------+-------------+------------------+
lag(), lead()
次に lag() と lead() を紹介します。
lag() は列名を引数に取り、1つ前の行の列の値を返します。
lead() は反対に、1つ後のの行の列の値を返します。
前後の値を比較したい場合に良さそうです。
select name, GNP, lag(GNP) over(order by GNP desc) as `lag`, lead(GNP) over(order by GNP desc) as `lead`, -- 前の行のGNPとの差分 lag(GNP) over(order by GNP desc) - GNP lag_diff, -- 次の行のGNPとの差分 GNP - lead(GNP) over(order by GNP desc) lead_diff from country limit 20; +--------------------+------------+------------+------------+------------+------------+ | name | GNP | lag | lead | lag_diff | lead_diff | +--------------------+------------+------------+------------+------------+------------+ | United States | 8510700.00 | NULL | 3787042.00 | NULL | 4723658.00 | | Japan | 3787042.00 | 8510700.00 | 2133367.00 | 4723658.00 | 1653675.00 | | Germany | 2133367.00 | 3787042.00 | 1424285.00 | 1653675.00 | 709082.00 | | France | 1424285.00 | 2133367.00 | 1378330.00 | 709082.00 | 45955.00 | | United Kingdom | 1378330.00 | 1424285.00 | 1161755.00 | 45955.00 | 216575.00 | | Italy | 1161755.00 | 1378330.00 | 982268.00 | 216575.00 | 179487.00 | | China | 982268.00 | 1161755.00 | 776739.00 | 179487.00 | 205529.00 | | Brazil | 776739.00 | 982268.00 | 598862.00 | 205529.00 | 177877.00 | | Canada | 598862.00 | 776739.00 | 553233.00 | 177877.00 | 45629.00 | | Spain | 553233.00 | 598862.00 | 447114.00 | 45629.00 | 106119.00 | | India | 447114.00 | 553233.00 | 414972.00 | 106119.00 | 32142.00 | | Mexico | 414972.00 | 447114.00 | 371362.00 | 32142.00 | 43610.00 | | Netherlands | 371362.00 | 414972.00 | 351182.00 | 43610.00 | 20180.00 | | Australia | 351182.00 | 371362.00 | 340238.00 | 20180.00 | 10944.00 | | Argentina | 340238.00 | 351182.00 | 320749.00 | 10944.00 | 19489.00 | | South Korea | 320749.00 | 340238.00 | 276608.00 | 19489.00 | 44141.00 | | Russian Federation | 276608.00 | 320749.00 | 264478.00 | 44141.00 | 12130.00 | | Switzerland | 264478.00 | 276608.00 | 256254.00 | 12130.00 | 8224.00 | | Taiwan | 256254.00 | 264478.00 | 249704.00 | 8224.00 | 6550.00 | | Belgium | 249704.00 | 256254.00 | 226492.00 | 6550.00 | 23212.00 | +--------------------+------------+------------+------------+------------+------------+
個人的には row_number() がかなり嬉しいですが、今回紹介したもの以外にも便利そうな関数があるので、
また試してみたいと思います。
