MySQLでSQL一発でグループごとに連番を振る

やりたいこと

MySQL 5.7 で、グループ毎にレコードに連番を振りたいです（一度の SQL 実行で）。
その際、各グループ内は指定した順でソートしておきたい。

ROW_NUMBER() について

行番号を振るときに使える ROW_NUMBER() が MySQL 5.7 にはありません。
そのため別の方法を考える必要があります。
調べていたら、SQL 中で変数を使って連番を振る方法が見つかりました。

ROW_NUMBER() を使わないでやる

以下のテーブルで試してみます。

mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name               | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair             | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean         | 2020-01-08 |
|  9 |        4 | KitKat             | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

このテーブルで group_id 毎に連番を振ります。
同一 group_id 内では created_at の降順でソートします。

mysql5.7.sql

SELECT
  @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
  history.id,
  @prev_group_id := group_id AS group_id,
  history.name,
  history.created_at
FROM
  history,
  (select @rownum := 0) hoge,
  (select @prev_group_id := '') fuga
ORDER BY
  group_id,
  created_at DESC

連番の算出は SQL 中で分岐して処理しています。
一つ前のレコードの group_id と値が同じなら 1 ずつ加算し、そうでないなら 1 から振り直す、という流れです。
実行結果は下記。

+--------+----+----------+--------------------+------------+
| rownum | id | group_id | name               | created_at |
+--------+----+----------+--------------------+------------+
|      1 |  2 |        1 | Donut              | 2020-01-02 |
|      2 |  1 |        1 | Cupcake            | 2020-01-01 |
|      1 |  5 |        2 | Gingerbread        | 2020-01-05 |
|      2 |  4 |        2 | Froyo              | 2020-01-04 |
|      3 |  3 |        2 | Eclair             | 2020-01-03 |
|      1 |  6 |        3 | Honeycomb          | 2020-01-06 |
|      1 |  9 |        4 | KitKat             | 2020-01-09 |
|      2 |  8 |        4 | Jelly Bean         | 2020-01-08 |
|      3 |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
+--------+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

いい感じに連番が振れました。

ROW_NUMBER() を使ってやる

MySQL 8 では ROW_NUMBER() が使えるため、下記のように書けます。

mysql8.sql

SELECT
  ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY group_id, created_at DESC) AS rownum,
  id,
  group_id,
  name,
  created_at
FROM
  history
ORDER BY
  group_id,
  created_at DESC

所感

SQL 内で変数を使うので、少しとっつきにくい感じがあると思いました。
ROW_NUMBER() が使えればよりシンプルに書けますが、諸般の事情で MySQL 8 系を利用できない場合もあるので、記憶の片隅に置いておくと良いと思います。

参考サイト