これは MySQL アドベントカレンダー の11日目の記事です。
MySQL 村で育ってきたので、ウィンドウ関数のことはあまり知らなかったんだけど、最近会社で使うことがあったのでメモ。なお会社では PostgreSQL を使ってる。
MySQL は 8.0 からウィンドウ関数が使えるようになったけど、PostgreSQL は 8.4 から使えてた。 PostgreSQL 8.4 は 2009年リリースで、MySQL 8.0 は 2018年リリースなので 9年遅れでサポートされたんだな。
ウィンドウ関数は GROUP BY みたいなもんだけど、グループング対象のレコードをひとつにまとめるんじゃなくて、各レコードにウィンドウ関数の列を追加する…という雑な理解。
GROUP BY とウィンドウ関数を組み合わせたときの評価順がややこしかったんだけど、たぶんこんな感じ。
テーブル ↓ WHERE ↓ SELECT に書いた普通の式や関数 ↓ GROUP BY (集約関数) ↓ HAVING ↓ ウィンドウ関数 👈 ↓ DISTINCT ↓ ORDER BY ↓ LIMIT
こんなテーブルがあるとして（まったく正規化してないし、テーブル名やカラム名はテキトー）、1
mysql> SELECT * FROM city ORDER BY city_code; +-----------+--------------+-----------+--------------------------+------+----------+------------+---------+ | pref_code | pref_name | city_code | city_name | type | pop_male | pop_female | area | +-----------+--------------+-----------+--------------------------+------+----------+------------+---------+ | 1 | 北海道 | 1000 | 北海道 | a | 2465088 | 2759526 | 83424.4 | | 1 | 北海道 | 1100 | 札幌市 | 1 | 918682 | 1054713 | 1121.26 | | 1 | 北海道 | 1101 | 札幌市中央区 | 0 | 112853 | 135827 | 46.42 | | 1 | 北海道 | 1102 | 札幌市北区 | 0 | 136596 | 152727 | 63.57 | | 1 | 北海道 | 1103 | 札幌市東区 | 0 | 126023 | 139356 | 56.97 | | 1 | 北海道 | 1104 | 札幌市白石区 | 0 | 100062 | 111773 | 34.47 | 〜〜 | 47 | 沖縄県 | 47360 | 伊是名村 | 3 | 718 | 604 | 15.43 | | 47 | 沖縄県 | 47361 | 久米島町 | 3 | 3823 | 3369 | 63.65 | | 47 | 沖縄県 | 47362 | 八重瀬町 | 3 | 15244 | 15697 | 26.96 | | 47 | 沖縄県 | 47375 | 多良間村 | 3 | 575 | 483 | 22 | | 47 | 沖縄県 | 47381 | 竹富町 | 3 | 2033 | 1909 | 334.4 | | 47 | 沖縄県 | 47382 | 与那国町 | 3 | 923 | 753 | 28.9 | +-----------+--------------+-----------+--------------------------+------+----------+------------+---------+ 1964 rows in set (0.00 sec)
RANK() で人口の多い順に順位をつけてみる。
mysql> SELECT city_name, pop_male+pop_female 人口,
-> RANK() OVER(ORDER BY pop_male+pop_female desc) 順位 FROM city
-> ORDER BY 順位;
+--------------------------+----------+--------+
| city_name | 人口 | 順位 |
+--------------------------+----------+--------+
| 東京都 | 14047594 | 1 |
| 特別区部 | 9733276 | 2 |
| 神奈川県 | 9237337 | 3 |
| 大阪府 | 8837685 | 4 |
| 愛知県 | 7542415 | 5 |
| 埼玉県 | 7344765 | 6 |
| 千葉県 | 6284480 | 7 |
| 兵庫県 | 5465002 | 8 |
| 北海道 | 5224614 | 9 |
| 福岡県 | 5135214 | 10 |
| 横浜市 | 3777491 | 11 |
| 静岡県 | 3633202 | 12 |
〜〜
東京23区(特別区部)はともかく、横浜市もかなり人口多いんだな…。
ここで長野県の順位を見ようとして、WHERE で絞り込もうと思っても見れない。ウィンドウ関数は WHERE よりも後に評価されるので。
mysql> SELECT city_name, pop_male+pop_female 人口,
-> RANK() OVER(ORDER BY pop_male+pop_female desc) 順位 FROM city
-> WHERE city_name='長野県';
+-----------+---------+--------+
| city_name | 人口 | 順位 |
+-----------+---------+--------+
| 長野県 | 2048011 | 1 |
+-----------+---------+--------+
GROUP BY HAVING を使っても同じ。ウィンドウ関数は GROUP BY よりも後に評価されるので。
mysql> SELECT city_name, pop_male+pop_female 人口,
-> RANK() OVER(ORDER BY pop_male+pop_female desc) 順位 FROM city
-> GROUP BY city_name, 人口 HAVING city_name='長野県';
+-----------+---------+--------+
| city_name | 人口 | 順位 |
+-----------+---------+--------+
| 長野県 | 2048011 | 1 |
+-----------+---------+--------+
こういう場合はサブクエリを使うしかないのかな。
mysql> SELECT * FROM ( -> SELECT city_name, pop_male+pop_female 人口, -> RANK() OVER(ORDER BY pop_male+pop_female desc) 順位 FROM city -> ) t WHERE city_name='長野県'; +-----------+---------+--------+ | city_name | 人口 | 順位 | +-----------+---------+--------+ | 長野県 | 2048011 | 20 | +-----------+---------+--------+
RANK() のようなウィンドウ関数用の関数でなくても、普通の集約関数も OVER をつけるとウィンドウ関数として動く。
各都道府県の市町村の数は GROUP BY を使うとこんな感じで取得できるけど、
mysql> SELECT pref_name, COUNT(*) 市町村数 -> FROM city -> WHERE type IN ('1','2','3') -> GROUP BY pref_name, pref_code ORDER BY pref_code; +--------------+--------------+ | pref_name | 市町村数 | +--------------+--------------+ | 北海道 | 179 | | 青森県 | 40 | | 岩手県 | 33 | | 宮城県 | 35 | | 秋田県 | 25 | | 山形県 | 35 | 〜〜 | 長崎県 | 21 | | 熊本県 | 45 | | 大分県 | 18 | | 宮崎県 | 26 | | 鹿児島県 | 43 | | 沖縄県 | 41 | +--------------+--------------+
ウィンドウ関数で似たようなことをしてみる。
mysql> SELECT pref_name,
-> COUNT(*) OVER(PARTITION BY pref_name) 市町村数
-> FROM city
-> WHERE type IN ('1','2','3')
-> ORDER BY pref_code;
+--------------+--------------+
| pref_name | 市町村数 |
+--------------+--------------+
| 北海道 | 179 |
| 北海道 | 179 |
| 北海道 | 179 |
| 北海道 | 179 |
| 北海道 | 179 |
| 北海道 | 179 |
〜〜
| 沖縄県 | 41 |
| 沖縄県 | 41 |
| 沖縄県 | 41 |
| 沖縄県 | 41 |
| 沖縄県 | 41 |
| 沖縄県 | 41 |
+--------------+--------------+
これを DISTINCT すると GROUP BY と同じ結果になる。 (DISTINCT と ORDER BY を組み合わせたときは ORDER BY に指定したカラムを SELECT に入れないといけないので追加してる)
mysql> SELECT DISTINCT pref_code, pref_name, -> COUNT(*) OVER(PARTITION BY pref_name) 市町村 -> FROM city -> WHERE type IN ('1','2','3') -> ORDER BY pref_code; +-----------+--------------+-----------+ | pref_code | pref_name | 市町村 | +-----------+--------------+-----------+ | 1 | 北海道 | 179 | | 2 | 青森県 | 40 | | 3 | 岩手県 | 33 | | 4 | 宮城県 | 35 | | 5 | 秋田県 | 25 | | 6 | 山形県 | 35 | 〜〜
集約関数をウィンドウ関数として使えるかどうかはものによるみたいで、たとえば COUNT(DISTINCT *) OVER() は動かない。これは PostgreSQL でも同じ。
MySQL:
mysql> SELECT COUNT(DISTINCT pref_name) OVER() FROM city; ERROR 1235 (42000): This version of MySQL doesn't yet support '<window function>(DISTINCT ..)'
PostgreSQL:
test=# SELECT count(DISTINCT pref_name) over() FROM city; ERROR: DISTINCT is not implemented for window functions LINE 1: SELECT count(DISTINCT pref_name) over() FROM city; ^
ところで GROUP BY とウィンドウ関数を同時に使うとこんな感じ:
mysql> SELECT pref_name,
-> COUNT(*) g_count,
-> COUNT(*) OVER(PARTITION BY pref_name) w_count
-> FROM city
-> WHERE type IN ('1','2','3')
-> GROUP BY pref_name, pref_code
-> ORDER BY pref_code;
+--------------+---------+---------+
| pref_name | g_count | w_count |
+--------------+---------+---------+
| 北海道 | 179 | 1 |
| 青森県 | 40 | 1 |
| 岩手県 | 33 | 1 |
| 宮城県 | 35 | 1 |
| 秋田県 | 25 | 1 |
| 山形県 | 35 | 1 |
〜〜
GROUP BY した結果に対してウィンドウ関数が働くので、ウィンドウ関数の方の COUNT は 1 になってる。 このあたりが直感的にわかりにくい。慣れなのかな。
しかし、SELECT に同じように並べてるのに、通常の関数と集約関数とウィンドウ関数で評価タイミングが異なるのが SQL はイマイチなんだよなぁ…。
SELECT の評価順はこんな感じ。うむー。
SELECT DISTINCT -- ⑦ hoge -- ③ LENGTH(hoge), -- ③ COUNT(*), -- ④ COUNT(*) OVER() -- ⑥ FROM table -- ① WHERE condition -- ② GROUP BY hoge -- ④ HAVING condition -- ⑤ ORDER BY hoge -- ⑧ LIMIT x -- ⑨
- データは 統計局ホームページ/令和2年国勢調査/調査の結果 より取得したので正しいはず↩
