はじめに
「MySQLにはWindow関数が未実装」と言うと、他のRDBMS界隈（ex. Oracle, PostgreSQL ...）などからよく驚かれていましたが、ついに MySQL8.0 で正式に実装されることになりました。
MySQL8.0自体はまだリリース候補版（not GA）のステータスですが、折角なので検証してみました。
Window関数で何ができるの？
ただ、MySQLにしか触れてこなかった人からすると、Window関数に関する知識が少なく、具体的にどういうものかイメージも難しいと思います（私もその一人です）。
既にWindow関数を実装しているPostgreSQLのマニュアルには以下のような説明がありました。
PostgreSQL 9.6.3文書 3.5. ウィンドウ関数
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。
この説明からも分かる通り、Windows関数は取得（SELECT）したデータに対して集計処理（ランク付け等）を行うものです。昨今の流行りであるビッグデータ、OLAP関連では必須の機能と言えます。
実装されているWindow関数
12.19.1 Window Function Descriptions
MySQL8.0で実装されるWindow関数の一覧は上記マニュアルに記載されており、
合計 11 個でした。ちなみに PostgreSQL の関数と同じものです。
Window関数の検証
ここからはWindow関数を実際に使ってみます。
チュートリアルとして前述の PostgreSQL のマニュアルに記載されたものでもいいのですが、ネットを放浪しているうちに良いサイトを見つけました。今回はこちらの内容を引用してみたいと思います。
SQL School - SQL Window Functions by Mode Analytics, Inc
※ 掲載元の許可は取っております
1. MySQL8.0のインストール、セットアップ
※CentOS7.3環境を使用
まずはMySQL8.0をインストールします。今回は手軽にyumを使います。
# yum install http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# yum-config-manager --disable mysql57-community
# yum-config-manager --enable mysql80-community
# yum install mysql-community-server
... omited ...
Installed:
mysql-community-server.x86_64 0:8.0.2-0.1.dmr.el7
Dependency Installed:
mysql-community-client.x86_64 0:8.0.2-0.1.dmr.el7 mysql-community-common.x86_64 0:8.0.2-0.1.dmr.el7
mysql-community-libs.x86_64 0:8.0.2-0.1.dmr.el7
# systemctl start mysqld
# cat /var/log/mysqld.log | grep temporary
2017-08-24T05:45:49.342390Z 5 [Note] A temporary password is generated for root@localhost: 2OSWHLlc5u:y
# mysql -u root -p2OSWHLlc5u:y
mysql> SET PASSWORD = "MySQL8.0";
mysql> show global variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> exit
2. テストデータの準備
今回はテストデータとして、アメリカのCapital Bikeshare社が提供するテストデータを使用しています。
これには、同社が提供するレンタサイクルサービスの利用者から取得した以下の情報が含まれています。
- Duration – 利用時間（単位：ミリ秒）
- Start Date – 貸出日時
- End Date – 返却日時
- Start Station – 出発地点の名前および番号
- End Station – 到着地点の名前および番号
- Bike Number – 自転車のID
- Member Type – 利用者の種別（"registered" or "casual")
"Download Capital Bikeshare trip history data"のリンク先から、2016年度のデータである以下の4ファイルをダウンロードしました。
・ 2016-Q1-cabi-trip-history-data.zip
・ 2016-Q2-cabi-trips-history-data.zip
・ 2016-Q3-cabi-trips-history-data.zip
・ 2016-Q4-cabi-trips-history-data.zip
# ls -lh /vagrant/ | grep trip
-rwxrwxrwx. 1 vagrant vagrant 11M Aug 24 14:42 2016-Q1-cabi-trip-history-data.zip
-rwxrwxrwx. 1 vagrant vagrant 19M Aug 24 14:41 2016-Q2-cabi-trips-history-data.zip
-rwxrwxrwx. 1 vagrant vagrant 21M Aug 24 14:42 2016-Q3-cabi-trips-history-data.zip
-rwxrwxrwx. 1 vagrant vagrant 16M Aug 24 14:41 2016-Q4-cabi-trips-history-data.zip
# mv /vagrant/2016-Q* ./
# yum install -y unzip
# unzip 2016-Q1-cabi-trip-history-data.zip
# unzip 2016-Q2-cabi-trips-history-data.zip
# unzip 2016-Q3-cabi-trips-history-data.zip
# unzip 2016-Q4-cabi-trips-history-data.zip
# ls -lh | grep .csv
-rw-r--r--. 1 root root 63M Apr 6 2016 2016-Q1-Trips-History-Data.csv
-rw-r--r--. 1 root root 106M Jul 15 2016 2016-Q2-Trips-History-Data.csv
-rw-r--r--. 1 root root 82M Oct 21 2016 2016-Q3-Trips-History-Data-1.csv
-rw-r--r--. 1 root root 39M Oct 21 2016 2016-Q3-Trips-History-Data-2.csv
-rw-r--r--. 1 root root 88M Mar 22 16:37 2016-Q4-Trips-History-Data.csv
# mv *.csv /var/lib/mysql-files/
# chown -R mysql:mysql /var/lib/mysql-files/
# mysql -u root -pMySQL8.0
mysql> CREATE DATABASE window_test;
mysql> USE window_test;
mysql> CREATE TABLE `trip_histories` (
`Duration` int unsigned,
`Start_time` datetime,
`End_time` datetime,
`Start_station_number` smallint unsigned,
`Start_station_name` varchar(200),
`End_station_number` smallint unsigned,
`End_station_name` varchar(200),
`Bike_number` varchar(30),
`Member_type` varchar(20));
mysql> LOAD DATA INFILE '/var/lib/mysql-files/2016-Q1-Trips-History-Data.csv' INTO TABLE trip_histories FIELDS TERMINATED BY ',' IGNORE 1 LINES (`Duration`, @Start_time, @End_time, `Start_station_number`, `Start_station_name`, `End_station_number`, `End_station_name`, `Bike_number`, `Member_type`) SET `Start_time` = STR_TO_DATE(@Start_time, '%c/%d/%Y %H:%i'), `End_time` = STR_TO_DATE(@End_time, '%c/%d/%Y %H:%i');
※ CSVファイルには"3/31/2016 23:59"という形式で日時データが格納されているため、LOAD DATA文の中でMySQLのDATETIME型に合うよう変換する必要があります
同じように残りのファイルもロードします。
mysql> LOAD DATA INFILE '/var/lib/mysql-files/2016-Q2-Trips-History-Data.csv' INTO TABLE trip_histories FIELDS TERMINATED BY ',' IGNORE 1 LINES (`Duration`, @Start_time, @End_time, `Start_station_number`, `Start_station_name`, `End_station_number`, `End_station_name`, `Bike_number`, `Member_type`) SET `Start_time` = STR_TO_DATE(@Start_time, '%c/%d/%Y %H:%i'), `End_time` = STR_TO_DATE(@End_time, '%c/%d/%Y %H:%i');
mysql> LOAD DATA INFILE '/var/lib/mysql-files/2016-Q3-Trips-History-Data-1.csv' INTO TABLE trip_histories FIELDS TERMINATED BY ',' IGNORE 1 LINES (`Duration`, @Start_time, @End_time, `Start_station_number`, `Start_station_name`, `End_station_number`, `End_station_name`, `Bike_number`, `Member_type`) SET `Start_time` = STR_TO_DATE(@Start_time, '%c/%d/%Y %H:%i'), `End_time` = STR_TO_DATE(@End_time, '%c/%d/%Y %H:%i');
mysql> LOAD DATA INFILE '/var/lib/mysql-files/2016-Q3-Trips-History-Data-2.csv' INTO TABLE trip_histories FIELDS TERMINATED BY ',' IGNORE 1 LINES (`Duration`, @Start_time, @End_time, `Start_station_number`, `Start_station_name`, `End_station_number`, `End_station_name`, `Bike_number`, `Member_type`) SET `Start_time` = STR_TO_DATE(@Start_time, '%c/%d/%Y %H:%i'), `End_time` = STR_TO_DATE(@End_time, '%c/%d/%Y %H:%i');
mysql> LOAD DATA INFILE '/var/lib/mysql-files/2016-Q4-Trips-History-Data.csv' INTO TABLE trip_histories FIELDS TERMINATED BY ',' IGNORE 1 LINES (`Duration`, @Start_time, @End_time, `Start_station_number`, `Start_station_name`, `End_station_number`, `End_station_name`, `Bike_number`, `Member_type`) SET `Start_time` = STR_TO_DATE(@Start_time, '%c/%d/%Y %H:%i'), `End_time` = STR_TO_DATE(@End_time, '%c/%d/%Y %H:%i');
mysql> SELECT COUNT(*) FROM trip_histories;
+----------+
| COUNT(*) |
+----------+
| 3333786 |
+----------+
1 row in set (1.86 sec)
3. Window関数を使ってみる
サイトの内容に従いながら、実際にWindow関数を動かしてみます。
[1] 基本となるWindow関数
Window関数は、"OVER (PARTITION BY ...)" or "OVER (ORDER BY ...)" をつけて使用します。
OVERの前にくるのは、Window関数に限らず、従来の集約関数（SUM, COUNTなど）でも可能です。
下の例は、貸出日時（Start_time）が '2016-01-30' よりも前のデータから、Window関数で出発地点（Start_station_name）ごとに区切っています。
その中で、貸出日時（Start_time）が早い順で並び替えを行い、利用期間の合計（累計）を取得しています。
つまり、以下の結果セットの場合、「出発地点が"10th & E St NW"のレコードを貸出日時が早い順に並び替え、
その上で利用時間の類型を右端のカラムで表示する」という処理を実現できます。
・(A) の"running_total" + (B) の"Duration" + (C) の"Duration" = (B)および(C) のrunnig_total
・(C) の"running_total" + (D) の"Duration" = (D) の"running_total"
※（B）と（C）の貸出日時が一緒 ＝ 2人組と考えられる
mysql> SELECT
Start_station_name,
Duration,
SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
LIMIT 10;
+--------------------+----------+---------------+
| Start_station_name | Duration | running_total |
+--------------------+----------+---------------+
| 10th & E St NW | 918924 | 918924 | ... (A)
| 10th & E St NW | 3389936 | 7696559 | ... (B)
| 10th & E St NW | 3387699 | 7696559 | ... (C)
| 10th & E St NW | 76807986 | 84504545 | ... (D)
| 10th & E St NW | 1551018 | 90725361 |
| 10th & E St NW | 1567448 | 90725361 |
| 10th & E St NW | 1551535 | 90725361 |
| 10th & E St NW | 1550815 | 90725361 |
| 10th & E St NW | 1528721 | 93794032 |
| 10th & E St NW | 1539950 | 93794032 |
+--------------------+----------+---------------+
10 rows in set (2.81 sec)
[2] よく使われる関数 ： SUM, COUNT, AVG
SUM(), COUNT(), AVG()といった関数が、Window関数と組み合わせてよく使用されます。
以下のクエリの例では、出発地点ごとに区切ったのち、各地点ごとの「合計利用時間」、「合計利用回数」、「利用時間の平均」を取得しています。
mysql> SELECT
Start_station_name,
Duration,
SUM(Duration) OVER (PARTITION BY Start_station_name) AS running_total,
COUNT(Duration) OVER (PARTITION BY Start_station_name) AS running_count,
AVG(Duration) OVER (PARTITION BY Start_station_name) AS running_avg
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
LIMIT 10;
+--------------------+----------+---------------+---------------+--------------+
| Start_station_name | Duration | running_total | running_count | running_avg |
+--------------------+----------+---------------+---------------+--------------+
| 10th & E St NW | 1168391 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 1381793 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 955454 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 473646 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 534100 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 4135906 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 381701 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 853161 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 874863 | 529898207 | 420 | 1261662.3976 |
| 10th & E St NW | 169259 | 529898207 | 420 | 1261662.3976 |
+--------------------+----------+---------------+---------------+--------------+
10 rows in set (3.36 sec)
[3] ROW_NUMBER()
ROW_NUMBER関数は、その名の通り取得した行（レコード）の番号を表示します。
Widnow関数内のORDER BY句で指定されたカラムの順番に沿って、「1」から番号が割り振られます。なお、ROW_NUMBER関数は括弧の中に変数を指定する必要はありません。
従来のMySQLでは行番号を出力する場合に手間がかかっていましたが、ROW_NUMBER()を使えば簡単に取得することができます。
mysql> SELECT
ROW_NUMBER() OVER (ORDER BY Start_time DESC) AS row_num,
Start_station_name,
Start_time,
Duration
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
LIMIT 10;
+---------+-------------------------------------------+---------------------+----------+
| row_num | Start_station_name | Start_time | Duration |
+---------+-------------------------------------------+---------------------+----------+
| 1 | California St & Florida Ave NW | 2016-01-29 23:59:00 | 1114344 |
| 2 | 15th & East Capitol St NE | 2016-01-29 23:59:00 | 373555 |
| 3 | Massachusetts Ave & Dupont Circle NW | 2016-01-29 23:57:00 | 479492 |
| 4 | 17th & Rhode Island Ave NW | 2016-01-29 23:57:00 | 418439 |
| 5 | 7th & F St NW / National Portrait Gallery | 2016-01-29 23:55:00 | 879521 |
| 6 | 7th & F St NW / National Portrait Gallery | 2016-01-29 23:54:00 | 982446 |
| 7 | 18th & R St NW | 2016-01-29 23:53:00 | 473575 |
| 8 | Massachusetts Ave & Dupont Circle NW | 2016-01-29 23:53:00 | 770368 |
| 9 | 11th & H St NE | 2016-01-29 23:50:00 | 383772 |
| 10 | Columbus Circle / Union Station | 2016-01-29 23:49:00 | 883801 |
+---------+-------------------------------------------+---------------------+----------+
10 rows in set (3.69 sec)
[4] RANK() and DENSE_RANK()
RANK関数は、上記のROW_NUMBER関数と少し違います。例えば、貸出日時（Start_time）でソートを行ってみると、同じ時間のレコードが複数存在するかもしれません。
そうしたケースで、同じ時間のレコードは一つの行番号にまとめたい場合は、ROW_NUMBER()よりRANK()の方が適切です。
下の例は、[1] のクエリの結果の中で、同じ貸出日時のレコードについては同じ行番号を割り振ったケースとなります。
※ いわゆる「ランキング」のイメージです
mysql> SELECT
RANK() OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS rank_num,
Start_station_name,
Duration,
SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
LIMIT 10;
+----------+--------------------+----------+---------------+
| rank_num | Start_station_name | Duration | running_total |
+----------+--------------------+----------+---------------+
| 1 | 10th & E St NW | 918924 | 918924 |
| 2 | 10th & E St NW | 3389936 | 7696559 |
| 2 | 10th & E St NW | 3387699 | 7696559 |
| 4 | 10th & E St NW | 76807986 | 84504545 |
| 5 | 10th & E St NW | 1551018 | 90725361 |
| 5 | 10th & E St NW | 1567448 | 90725361 |
| 5 | 10th & E St NW | 1551535 | 90725361 |
| 5 | 10th & E St NW | 1550815 | 90725361 |
| 9 | 10th & E St NW | 1528721 | 93794032 |
| 9 | 10th & E St NW | 1539950 | 93794032 |
+----------+--------------------+----------+---------------+
10 rows in set (3.40 sec)
また、DENSE_RANK()は、RANK()とほぼ同じ挙動ですが、同一レコードの後のレコードを連番扱いにする点が異なっています。
こちらは、番号の振り方の規定に沿って随時切り替えてください。
mysql> SELECT
DENSE_RANK() OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS rank_num,
Start_station_name,
Duration,
SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
LIMIT 10;
+----------+--------------------+----------+---------------+
| rank_num | Start_station_name | Duration | running_total |
+----------+--------------------+----------+---------------+
| 1 | 10th & E St NW | 918924 | 918924 |
| 2 | 10th & E St NW | 3389936 | 7696559 |
| 2 | 10th & E St NW | 3387699 | 7696559 |
| 3 | 10th & E St NW | 76807986 | 84504545 |
| 4 | 10th & E St NW | 1551018 | 90725361 |
| 4 | 10th & E St NW | 1567448 | 90725361 |
| 4 | 10th & E St NW | 1551535 | 90725361 |
| 4 | 10th & E St NW | 1550815 | 90725361 |
| 5 | 10th & E St NW | 1528721 | 93794032 |
| 5 | 10th & E St NW | 1539950 | 93794032 |
+----------+--------------------+----------+---------------+
10 rows in set (3.77 sec)
[5] NTILE
Window関数を使って、該当行が全体の中でどこに位置しているかを表示することもできます（100分率、4分率…）。
全体をどれくらいの単位で分割するかは、NTILE関数の括弧内で指定します。
以下のクエリは、出発地点で区切り、利用時間の昇順で並べていますが、同じ出発地点のレコード全体の中での位置を、
quartile（4分率）/quintile（5分率）/percentile（100分率）の3通りで分析したものとなります。
その中から、Start_station_name = "10th & E St NW"となっているもののみに着目すると、全体が「420件」あります。
そのため、100件目のレコードは各分割率ごとに以下のような位置になります。
・quartile（4分率） : 1番目のグループ（1グループあたり105件）
・quintile（5分率） : 2番目のグループ（1グループあたり84件）
・percentile（100分率） ： 20番目のグループ（1グループあたり4.2件）
NTILE関数は、テストの点数順に生徒を並び替え、上位○○％の生徒を合格とする場合の判定などに便利です。
mysql> SELECT
COUNT(*)
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
AND Start_station_name = "10th & E St NW";
+----------+
| COUNT(*) |
+----------+
| 420 |
+----------+
1 row in set (2.45 sec)
mysql> SELECT
ROW_NUMBER() OVER (PARTITION BY Start_station_name ORDER BY Duration) AS row_num,
Start_station_name,
Duration,
NTILE(4) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS quartile,
NTILE(5) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS quintile,
NTILE(100) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS percentile
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
AND Start_station_name = "10th & E St NW"
LIMIT 100;
+---------+--------------------+----------+----------+----------+------------+
| row_num | Start_station_name | Duration | quartile | quintile | percentile |
+---------+--------------------+----------+----------+----------+------------+
| 1 | 10th & E St NW | 64653 | 1 | 1 | 1 |
| 2 | 10th & E St NW | 83260 | 1 | 1 | 1 |
| 3 | 10th & E St NW | 110038 | 1 | 1 | 1 |
| 4 | 10th & E St NW | 124887 | 1 | 1 | 1 |
| 5 | 10th & E St NW | 133040 | 1 | 1 | 1 |
| 6 | 10th & E St NW | 134227 | 1 | 1 | 2 |
...
| 95 | 10th & E St NW | 441657 | 1 | 2 | 19 |
| 96 | 10th & E St NW | 443152 | 1 | 2 | 20 |
| 97 | 10th & E St NW | 443700 | 1 | 2 | 20 |
| 98 | 10th & E St NW | 445121 | 1 | 2 | 20 |
| 99 | 10th & E St NW | 445837 | 1 | 2 | 20 |
| 100 | 10th & E St NW | 447317 | 1 | 2 | 20 |★★★
+---------+--------------------+----------+----------+----------+------------+
100 rows in set (3.40 sec)
[6] LAG and LEAD
特定の行をその前後の行と比較することは、特にデータの順番に意味があるケースなどで非常に有用です。
LAG関数、LEAD関数を使用すれば、前後の行の値などを引き出すことができます。
下の例では、各レコードの前後の利用期間（Duration）の値を、previous_val/following_val の2カラムに格納しています。
括弧内の数字で、前後の何行目の値か指定もできます。
mysql> SELECT
Start_station_name,
Duration,
LAG(Duration, 1) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS previous_val,
LEAD(Duration, 1) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS following_val
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
ORDER BY
Start_station_name, Duration
LIMIT 10;
+--------------------+----------+--------------+---------------+
| Start_station_name | Duration | previous_val | following_val |
+--------------------+----------+--------------+---------------+
| 10th & E St NW | 64653 | NULL | 83260 |
| 10th & E St NW | 83260 | 64653 | 110038 |
| 10th & E St NW | 110038 | 83260 | 124887 |
| 10th & E St NW | 124887 | 110038 | 133040 |
| 10th & E St NW | 133040 | 124887 | 134227 |
| 10th & E St NW | 134227 | 133040 | 148289 |
| 10th & E St NW | 148289 | 134227 | 150007 |
| 10th & E St NW | 150007 | 148289 | 151539 |
| 10th & E St NW | 151539 | 150007 | 153661 |
| 10th & E St NW | 153661 | 151539 | 158515 |
+--------------------+----------+--------------+---------------+
10 rows in set (3.60 sec)
LAG関数、LEAD関数を活用すれば、各行の値の差分を簡単に取得できます。
mysql> SELECT
Start_station_name,
Duration,
Duration - LAG(Duration, 1) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS diff
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
ORDER BY
Start_station_name, Duration
LIMIT 10;
+--------------------+----------+-------+
| Start_station_name | Duration | diff |
+--------------------+----------+-------+
| 10th & E St NW | 64653 | NULL |
| 10th & E St NW | 83260 | 18607 |
| 10th & E St NW | 110038 | 26778 |
| 10th & E St NW | 124887 | 14849 |
| 10th & E St NW | 133040 | 8153 |
| 10th & E St NW | 134227 | 1187 |
| 10th & E St NW | 148289 | 14062 |
| 10th & E St NW | 150007 | 1718 |
| 10th & E St NW | 151539 | 1532 |
| 10th & E St NW | 153661 | 2122 |
+--------------------+----------+-------+
10 rows in set (3.70 sec)
※ LAG関数の場合は「一番最初の行」、LEAD関数の場合は「一番最後の行」がNULLになります
[番外編] Window関数のエイリアス
これまでの例の通り、Window関数を使用するとクエリが長くなってしまいがちです。
Window関数で指定する範囲（OVER以下）が同じである場合は、エイリアスを活用するとクエリがすっきりします。
[5] NTILE で取り上げた例だと、以下のようになります。
■エイリアス使用前
mysql> SELECT
ROW_NUMBER() OVER (PARTITION BY Start_station_name ORDER BY Duration) AS row_num,
Start_station_name,
Duration,
NTILE(4) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS quartile,
NTILE(5) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS quintile,
NTILE(100) OVER (PARTITION BY Start_station_name ORDER BY Duration) AS percentile
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
AND Start_station_name = "10th & E St NW"
LIMIT 100;
■エイリアス使用後
mysql> SELECT
ROW_NUMBER() OVER (PARTITION BY Start_station_name ORDER BY Duration) AS row_num,
Start_station_name,
Duration,
NTILE(4) OVER ntile_window AS quartile,
NTILE(5) OVER ntile_window AS quintile,
NTILE(100) OVER ntile_window AS percentile
FROM
trip_histories
WHERE
Start_time < '2016-01-30'
AND Start_station_name = "10th & E St NW"
WINDOW ntile_window AS (PARTITION BY Start_station_name ORDER BY Duration)
LIMIT 100;
おわりに
Window関数は昨今のWebサービスの要件ではほぼ必須と言える機能なので、MySQL8.0がGAリリースされる前に積極的に触ってみることをオススメします！
また、今回の記事について認識に誤りなどがあればフィードバックいただけると幸いです。