7/17にMySQL 8.0.2 DMRが出ました。
8.0.2の追加機能に**ウィンドウ関数（window functions）**があります。
PostgreSQLでははるか昔（2009年）8.4の時点ですでにサポートされているので特に目新しいものではありませんが、MySQL使いには馴染みがない機能です。
※MariaDBでは少し前に10.2でサポートされたみたいです。
ウィンドウ関数は複雑な集計や分析向けの機能であり理解が難しいので、ここでは一番取っつきやすい（と勝手に思った）RANK関数を試してみます。
1. MySQL 8.0.2がサポートしているウィンドウ関数
こちらに情報があります。
新しくサポートされた関数の説明はこちらです。
※集計・分析には、新しくサポートされた関数以外に、COUNT()、SUM()など従来からある集約関数（GROUP BYによるもの）も使えます。
これによると、順位付けに使える関数は
- RANK()
- DENSE_RANK()
- ROW_NUMBER()
があり、それぞれ、同率1位が2つある場合に、
- RANK() ⇒ 1, 1, 3, 4, …
- DENSE_RANK() ⇒ 1, 1, 2, 3, …
- ROW_NUMBER() ⇒ 1, 2, 3, 4, …
となります（今回はRANK()を使います）。
なお、制約もあります。
DISTINCTで集約できない（⇒GROUP BYを使ってね）、ウィンドウ関数のネストはできない、などです。
また、こちらにも情報があります。
2. テスト環境を用意する
MySQL 8.0.2をインストールし、データベースとテーブルを用意します。
実はここで、以前 MySQL 8.0.1 DMRでテーブルスペース暗号化＋REDOログ／UNDOログ暗号化 を書いたときの環境をそのままyumでアップデートしたところ、以下のようなログを吐いて起動しなくなってしまいました。
mysqld.log
2017-07-29T14:05:35.440331Z 0 [Note] Basedir set to /usr/
2017-07-29T14:05:35.440531Z 0 [Warning] The syntax '--symbolic-links/-s' is deprecated and will be removed in a future release
2017-07-29T14:05:35.441955Z 0 [Note] /usr/sbin/mysqld (mysqld 8.0.2-dmr) starting as process 13566 ...
2017-07-29T14:05:35.448117Z 0 [Note] InnoDB: Using Linux native AIO
2017-07-29T14:05:35.448325Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-07-29T14:05:35.450368Z 1 [ERROR] Failed to find valid data directory.
2017-07-29T14:05:35.450495Z 0 [ERROR] Data Dictionary initialization failed.
2017-07-29T14:05:35.450507Z 0 [ERROR] Aborting
2017-07-29T14:05:35.450533Z 0 [Note] Binlog end
2017-07-29T14:05:35.451611Z 0 [Note] Shutting down plugin 'MyISAM'
2017-07-29T14:05:35.451629Z 0 [Note] Shutting down plugin 'InnoDB'
2017-07-29T14:05:35.451640Z 0 [Note] Shutting down plugin 'CSV'
2017-07-29T14:05:35.451645Z 0 [Note] Shutting down plugin 'keyring_file'
2017-07-29T14:05:35.451918Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
どうやら、8.0.2でテーブルスペースやUNDOログ用のテーブルスペースなどの扱いに変更が加えられたことが原因のようです。
一旦、暗号化のための設定をmy.cnfから外し、新しいデータディレクトリを用意して起動しました。
テストのために用意したテーブルの定義とレコードの内容は以下の通りです。
※わが地元です。
- 愛知県の市町村人口と市町村制施行日テーブル
win_test.aichi
mysql> CREATE TABLE win_test.aichi (id INT PRIMARY KEY AUTO_INCREMENT,
-> ctv_name VARCHAR(50) NOT NULL,
-> population INT NOT NULL,
-> eff_date DATE NOT NULL,
-> ctv_type INT NOT NULL) ENGINE innodb;
Query OK, 0 rows affected (0.01 sec)
※ここでレコードをINSERT
mysql> SELECT * FROM win_test.aichi;
+----+-----------------+------------+------------+----------+
| id | ctv_name | population | eff_date | ctv_type |
+----+-----------------+------------+------------+----------+
| 1 | 愛西市 | 62275 | 2005-04-01 | 4 |
| 2 | 阿久比町 | 28249 | 1953-01-01 | 5 |
| 3 | あま市 | 87149 | 2010-03-22 | 4 |
| 4 | 安城市 | 185920 | 1952-05-05 | 4 |
| 5 | 一宮市 | 380574 | 1921-09-01 | 3 |
| 6 | 稲沢市 | 136478 | 1958-11-01 | 4 |
| 7 | 犬山市 | 74071 | 1954-04-01 | 4 |
| 8 | 岩倉市 | 47936 | 1971-12-01 | 4 |
| 9 | 大口町 | 23778 | 1962-04-01 | 5 |
| 10 | 大治町 | 31709 | 1975-04-01 | 5 |
| 11 | 大府市 | 90995 | 1970-09-01 | 4 |
| 12 | 岡崎市 | 384224 | 1916-07-01 | 2 |
| 13 | 尾張旭市 | 81317 | 1970-12-01 | 4 |
| 14 | 春日井市 | 307121 | 1943-06-01 | 3 |
| 15 | 蟹江町 | 37140 | 1906-07-01 | 5 |
| 16 | 蒲郡市 | 80269 | 1954-04-01 | 4 |
| 17 | 刈谷市 | 151432 | 1950-04-01 | 4 |
| 18 | 北名古屋市 | 85160 | 2006-03-20 | 4 |
| 19 | 清須市 | 68616 | 2005-07-07 | 4 |
| 20 | 幸田町 | 40617 | 1954-08-01 | 5 |
| 21 | 江南市 | 98261 | 1954-06-01 | 4 |
| 22 | 小牧市 | 149130 | 1955-01-01 | 4 |
| 23 | 設楽町 | 4819 | 2005-10-01 | 5 |
| 24 | 新城市 | 46108 | 2005-10-01 | 4 |
| 25 | 瀬戸市 | 128396 | 1929-10-01 | 4 |
| 26 | 高浜市 | 47316 | 1970-12-01 | 4 |
| 27 | 武豊町 | 42604 | 1954-10-05 | 5 |
| 28 | 田原市 | 61472 | 2003-08-20 | 4 |
| 29 | 知多市 | 84573 | 1970-09-01 | 4 |
| 30 | 知立市 | 70927 | 1970-12-01 | 4 |
| 31 | 津島市 | 62713 | 1947-03-01 | 4 |
| 32 | 東栄町 | 3241 | 1955-04-01 | 5 |
| 33 | 東海市 | 113022 | 1969-04-01 | 4 |
| 34 | 東郷町 | 43344 | 1970-04-01 | 5 |
| 35 | 常滑市 | 57287 | 1954-04-01 | 4 |
| 36 | 飛島村 | 4423 | 1906-07-01 | 6 |
| 37 | 豊明市 | 69377 | 1972-08-01 | 4 |
| 38 | 豊川市 | 183134 | 1943-06-01 | 4 |
| 39 | 豊田市 | 425064 | 1951-03-01 | 2 |
| 40 | 豊根村 | 1108 | 1889-10-01 | 6 |
| 41 | 豊橋市 | 373736 | 1906-08-01 | 2 |
| 42 | 豊山町 | 15425 | 1972-04-01 | 5 |
| 43 | 長久手市 | 59178 | 2012-01-04 | 4 |
| 44 | 名古屋市 | 2309753 | 1889-10-01 | 1 |
| 45 | 西尾市 | 169130 | 1953-12-15 | 4 |
| 46 | 日進市 | 89528 | 1994-10-01 | 4 |
| 47 | 半田市 | 117181 | 1937-10-01 | 4 |
| 48 | 東浦町 | 49328 | 1948-06-01 | 5 |
| 49 | 扶桑町 | 33916 | 1952-08-01 | 5 |
| 50 | 碧南市 | 71881 | 1948-04-05 | 4 |
| 51 | 南知多町 | 18134 | 1961-06-01 | 5 |
| 52 | 美浜町 | 23204 | 1955-04-01 | 5 |
| 53 | みよし市 | 62496 | 2010-01-04 | 4 |
| 54 | 弥富市 | 43185 | 2006-04-01 | 4 |
+----+-----------------+------------+------------+----------+
54 rows in set (0.00 sec)
- 市町村の種類テーブル
win_test.ctv_type
mysql> CREATE TABLE win_test.ctv_type (type_id INT PRIMARY KEY AUTO_INCREMENT,
-> type_name VARCHAR(50) NOT NULL,
-> ctv_type_simple INT NOT NULL) ENGINE innodb;
Query OK, 0 rows affected (0.02 sec)
※ここでレコードをINSERT
mysql> SELECT * FROM win_test.ctv_type;
+---------+--------------------+-----------------+
| type_id | type_name | ctv_type_simple |
+---------+--------------------+-----------------+
| 1 | 政令指定都市 | 1 |
| 2 | 中核市 | 1 |
| 3 | 施行時特例市 | 1 |
| 4 | 市 | 1 |
| 5 | 町 | 2 |
| 6 | 村 | 3 |
+---------+--------------------+-----------------+
6 rows in set (0.00 sec)
3. RANK関数で順位付けしてみる
まず、全体の人口ランキングを出してみます。
RANKを付ける対象や、集計の区分を指定するためにOVER()を付けますが、今回は「人口（population）の降順」（＝人口が多い順）のみ指定します（カッコ内にORDER BYを指定）。
人口ランキング（全体）
mysql> SELECT RANK() OVER (ORDER BY population DESC) AS pop_rank,
-> ctv_name, population FROM win_test.aichi;
+----------+-----------------+------------+
| pop_rank | ctv_name | population |
+----------+-----------------+------------+
| 1 | 名古屋市 | 2309753 |
| 2 | 豊田市 | 425064 |
| 3 | 岡崎市 | 384224 |
| 4 | 一宮市 | 380574 |
| 5 | 豊橋市 | 373736 |
| 6 | 春日井市 | 307121 |
| 7 | 安城市 | 185920 |
（中略）
| 35 | 東浦町 | 49328 |
| 36 | 岩倉市 | 47936 |
| 37 | 高浜市 | 47316 |
| 38 | 新城市 | 46108 |
| 39 | 東郷町 | 43344 |
| 40 | 弥富市 | 43185 |
| 41 | 武豊町 | 42604 |
| 42 | 幸田町 | 40617 |
| 43 | 蟹江町 | 37140 |
| 44 | 扶桑町 | 33916 |
| 45 | 大治町 | 31709 |
| 46 | 阿久比町 | 28249 |
| 47 | 大口町 | 23778 |
| 48 | 美浜町 | 23204 |
| 49 | 南知多町 | 18134 |
| 50 | 豊山町 | 15425 |
| 51 | 設楽町 | 4819 |
| 52 | 飛島村 | 4423 |
| 53 | 東栄町 | 3241 |
| 54 | 豊根村 | 1108 |
+----------+-----------------+------------+
54 rows in set (0.00 sec)
これを、市町村の種類別の人口ランキングに変更します。
OVER()でORDER BYに加えてPARTITION BYで「市町村の種類（ctv_type）」を指定します。
人口ランキング（種類別）
mysql> SELECT RANK() OVER (PARTITION BY ctv_type ORDER BY population DESC)
-> AS pop_rank,
-> ctv_name, population, ctv_type FROM win_test.aichi;
+----------+-----------------+------------+----------+
| pop_rank | ctv_name | population | ctv_type |
+----------+-----------------+------------+----------+
| 1 | 名古屋市 | 2309753 | 1 |
| 1 | 豊田市 | 425064 | 2 |
| 2 | 岡崎市 | 384224 | 2 |
| 3 | 豊橋市 | 373736 | 2 |
| 1 | 一宮市 | 380574 | 3 |
| 2 | 春日井市 | 307121 | 3 |
| 1 | 安城市 | 185920 | 4 |
| 2 | 豊川市 | 183134 | 4 |
| 3 | 西尾市 | 169130 | 4 |
（中略）
| 32 | 弥富市 | 43185 | 4 |
| 1 | 東浦町 | 49328 | 5 |
| 2 | 東郷町 | 43344 | 5 |
| 3 | 武豊町 | 42604 | 5 |
（中略）
| 14 | 東栄町 | 3241 | 5 |
| 1 | 飛島村 | 4423 | 6 |
| 2 | 豊根村 | 1108 | 6 |
+----------+-----------------+------------+----------+
54 rows in set (0.00 sec)
種類（ctv_type）で区切る形で順位（pop_rank）が付いていることが分かります。
テーブルを結合した状態も試してみます。
人口ランキング（テーブル結合1）
mysql> SELECT RANK() OVER (PARTITION BY a.ctv_type ORDER BY a.population DESC)
-> AS pop_rank,
-> a.ctv_name, a.population, t.type_name AS ctv_type_name
-> FROM win_test.aichi a, win_test.ctv_type t
-> WHERE a.ctv_type = t.type_id;
+----------+-----------------+------------+--------------------+
| pop_rank | ctv_name | population | ctv_type_name |
+----------+-----------------+------------+--------------------+
| 1 | 名古屋市 | 2309753 | 政令指定都市 |
| 1 | 豊田市 | 425064 | 中核市 |
| 2 | 岡崎市 | 384224 | 中核市 |
| 3 | 豊橋市 | 373736 | 中核市 |
| 1 | 一宮市 | 380574 | 施行時特例市 |
| 2 | 春日井市 | 307121 | 施行時特例市 |
| 1 | 安城市 | 185920 | 市 |
| 2 | 豊川市 | 183134 | 市 |
（中略）
| 1 | 東浦町 | 49328 | 町 |
| 2 | 東郷町 | 43344 | 町 |
（中略）
| 1 | 飛島村 | 4423 | 村 |
| 2 | 豊根村 | 1108 | 村 |
+----------+-----------------+------------+--------------------+
54 rows in set (0.00 sec)
OVER()で、結合した複数のテーブルの列を条件にしてみます。
政令指定都市、中核市などを区別せずに「市」にまとめます。
人口ランキング（テーブル結合2）
mysql> SELECT RANK() OVER (PARTITION BY t.ctv_type_simple ORDER BY a.population DESC)
-> AS pop_rank,
-> a.ctv_name, a.population,
-> t.ctv_type_simple
-> FROM win_test.aichi a, win_test.ctv_type t
-> WHERE a.ctv_type = t.type_id;
+----------+-----------------+------------+-----------------+
| pop_rank | ctv_name | population | ctv_type_simple |
+----------+-----------------+------------+-----------------+
| 1 | 名古屋市 | 2309753 | 1 |
| 2 | 豊田市 | 425064 | 1 |
| 3 | 岡崎市 | 384224 | 1 |
| 4 | 一宮市 | 380574 | 1 |
| 5 | 豊橋市 | 373736 | 1 |
| 6 | 春日井市 | 307121 | 1 |
| 7 | 安城市 | 185920 | 1 |
（中略）
| 1 | 東浦町 | 49328 | 2 |
| 2 | 東郷町 | 43344 | 2 |
（中略）
| 1 | 飛島村 | 4423 | 3 |
| 2 | 豊根村 | 1108 | 3 |
+----------+-----------------+------------+-----------------+
54 rows in set (0.00 sec)
4. RANK関数とソート
RANK()の対象を、市町村制施行日の古い順に変更してみます。
施行日順1
mysql> SELECT RANK() OVER (ORDER BY eff_date) AS eff_rank,
-> ctv_name, population, eff_date FROM win_test.aichi;
+----------+-----------------+------------+------------+
| eff_rank | ctv_name | population | eff_date |
+----------+-----------------+------------+------------+
| 1 | 豊根村 | 1108 | 1889-10-01 |
| 1 | 名古屋市 | 2309753 | 1889-10-01 |
| 3 | 蟹江町 | 37140 | 1906-07-01 |
| 3 | 飛島村 | 4423 | 1906-07-01 |
| 5 | 豊橋市 | 373736 | 1906-08-01 |
| 6 | 岡崎市 | 384224 | 1916-07-01 |
| 7 | 一宮市 | 380574 | 1921-09-01 |
| 8 | 瀬戸市 | 128396 | 1929-10-01 |
| 9 | 半田市 | 117181 | 1937-10-01 |
| 10 | 春日井市 | 307121 | 1943-06-01 |
| 10 | 豊川市 | 183134 | 1943-06-01 |
（中略）
| 52 | みよし市 | 62496 | 2010-01-04 |
| 53 | あま市 | 87149 | 2010-03-22 |
| 54 | 長久手市 | 59178 | 2012-01-04 |
+----------+-----------------+------------+------------+
54 rows in set (0.00 sec)
同順位で、人口が少ない「豊根村」が「名古屋市」より上に表示されるのが気になるのであれば、いつも通りの場所（OVER()の外）でORDER BYすればOKです。
施行日順2
mysql> SELECT RANK() OVER (ORDER BY eff_date) AS eff_rank,
-> ctv_name, population, eff_date FROM win_test.aichi
-> ORDER BY eff_rank ASC, population DESC;
+----------+-----------------+------------+------------+
| eff_rank | ctv_name | population | eff_date |
+----------+-----------------+------------+------------+
| 1 | 名古屋市 | 2309753 | 1889-10-01 |
| 1 | 豊根村 | 1108 | 1889-10-01 |
| 3 | 蟹江町 | 37140 | 1906-07-01 |
| 3 | 飛島村 | 4423 | 1906-07-01 |
（中略）
| 54 | 長久手市 | 59178 | 2012-01-04 |
+----------+-----------------+------------+------------+
54 rows in set (0.00 sec)
全然、複雑な集計でも分析でもありませんが、OVER()の使い方はなんとなくイメージできたかと思います。
MySQL 8.0.2 DMRでウィンドウ関数その２ 少しだけ集計・分析っぽいことをしてみる に続きます。
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。