MySQL8.0でwindow関数

これを見ている

MySQLパフォーマンスチューニングTIPS - Speaker Deck

MySQL 8.0からwindow関数が使えるようになっていたらしい。

window関数は分析用と思われがちだが、各ユーザの最も高いスコアを取得とか、各ユーザの最後のデータを取得といったシステムの運用管理向けな処理にも使える。8.0より前でも自己結合とgroup byとmax/minを駆使すれば取れたのだが、window関数を使う事でとても簡単に書くことができる。

以下のようなテーブルがあったとして

id	user	score	ctime
1	a	90	2019-01-01
2	b	80	2019-01-17
3	b	30	2019-02-02
4	c	40	2019-01-11
5	a	70	2019-02-01
6	c	40	2019-03-01

各ユーザの最後のデータを取得

SELECT id, user, score, ctime 
FROM (
  SELECT
    *, 
    ROW_NUMBER() over (PARTITION BY user ORDER BY ctime DESC) as date_order
  FROM user_scores
) t
WHERE date_order = 1;

+----+------+-------+---------------------+
| id | user | score | ctime               |
+----+------+-------+---------------------+
|  5 | a    |    70 | 2019-02-01 00:00:00 |
|  3 | b    |    80 | 2019-02-02 00:00:00 |
|  6 | c    |    40 | 2019-03-01 00:00:00 |
+----+------+-------+---------------------+

各ユーザの最高得点を取得

SELECT id, user, score, ctime 
FROM (
  SELECT
    *, 
    ROW_NUMBER() over (PARTITION BY user ORDER BY score DESC) as score_order
  FROM user_scores
) t
WHERE score_order = 1;

+----+------+-------+---------------------+
| id | user | score | ctime               |
+----+------+-------+---------------------+
|  1 | a    |    90 | 2019-01-01 00:00:00 |
|  3 | b    |    80 | 2019-02-02 00:00:00 |
|  4 | c    |    40 | 2019-01-11 00:00:00 |
+----+------+-------+---------------------+

簡単ですね