ROW_NUMBER()が使えないMySQLでGROUP_CONCATを使って上位1件を抽出する

# やりたいこと

ROW_NUMBER()はとにかく便利な関数で、例えば以下のようなことがしたいときにはすぐ使ってしまいます。

- ユーザ毎に最後に購入した商品を知りたい

例えば次のように書けばすぐに抽出できます。

```sql
 SELECT
   user_id,
   kounyu_date,
   shohin_cd
 FROM (
   SELECT
     user_id,
     kounyu_date,
     sr_no,
     shohin_cd,
     ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY kounyu_date DESC, sr_no DESC) AS rank
  FROM
    tbl_kounyu_rireki
 ) tmp_tbl
 WHERE
   rank = 1
```

ところが、**古いMySQLではROW_NUMBER()が使えません／(^o^)＼**
同じテーブルをJOINする等の方法で同じことはできなくはないのですが、データ量の多いテーブルだと処理に時間がかかってしまうこともあり、ROW_NUMBER()のように早く抽出する方法を模索していました。

# GROUP_CONCATを用いた方法

できる時とできない時があるとは思いますが、GROUP_CONCAT関数を用いることで、ユーザ変数やJOINを用いずに同様のことができました。
以下の表から、ユーザ毎に最後に購入した商品コードを抽出します。

```text
mysql> SELECT * FROM tbl_kounyu_rireki;
+---------+-------------+-------+-----------+
| user_id | kounyu_date | sr_no | shohin_cd |
+---------+-------------+-------+-----------+
|       3 | 2019-12-21  |     1 |      1004 |
|       3 | 2019-12-21  |     2 |      1030 |
|       3 | 2020-04-10  |     1 |      1005 |
|       3 | 2020-04-10  |     2 |      2010 |
|       5 | 2020-03-02  |     1 |      1004 |
|       5 | 2020-05-02  |     1 |      1004 |
|      14 | 2020-04-29  |     1 |      1030 |
+---------+-------------+-------+-----------+
7 rows in set (0.00 sec)
```

以下のクエリを発行します。

```sql
SELECT
  user_id,
  kounyu_date,
  CAST(CASE WHEN sr_no_str LIKE '%,%' THEN LEFT(sr_no_str, INSTR(sr_no_str, ',') - 1) ELSE sr_no_str END AS SIGNED) AS sr_no,
  CAST(CASE WHEN shohin_cd_str LIKE '%,%' THEN LEFT(shohin_cd_str, INSTR(shohin_cd_str, ',') - 1) ELSE shohin_cd_str END AS SIGNED) AS shohin_cd
FROM (
  SELECT
    user_id,
    MAX(kounyu_date) AS kounyu_date,
    GROUP_CONCAT(sr_no ORDER BY kounyu_date DESC, sr_no DESC) AS sr_no_str,
    GROUP_CONCAT(shohin_cd ORDER BY kounyu_date DESC, sr_no DESC) AS shohin_cd_str
  FROM
    tbl_kounyu_rireki
  GROUP BY
    user_id
) tmp_tbl;
```

結果は以下の通りです。

```text
+---------+-------------+-------+-----------+
| user_id | kounyu_date | sr_no | shohin_cd |
+---------+-------------+-------+-----------+
|       3 | 2020-04-10  |     2 |      2010 |
|       5 | 2020-05-02  |     1 |      1004 |
|      14 | 2020-04-29  |     1 |      1030 |
+---------+-------------+-------+-----------+
3 rows in set (0.00 sec)
```

# 説明

サブクエリの中だけを実行すると、以下のようになっています。

```text
+---------+-------------+-----------+---------------------+
| user_id | kounyu_date | sr_no_str | shohin_cd_str       |
+---------+-------------+-----------+---------------------+
|       3 | 2020-04-10  | 2,1,2,1   | 2010,1005,1030,1004 |
|       5 | 2020-05-02  | 1,1       | 1004,1004           |
|      14 | 2020-04-29  | 1         | 1030                |
+---------+-------------+-----------+---------------------+
3 rows in set (0.00 sec)
```

GROUP_CONCAT関数はグループ内のデータを指定した順序で結合してくれる変わった関数で、MySQL特有のような気がします。古いバージョンから存在しています。（遅くともMySQL5.1から使用可能？）
これを用いることで、shohin_cd_strの先頭に、「ユーザが最後に購入した商品コード」がくるようになっており、デフォルトではカンマ区切りで、購入した順に商品コードが結合されます。
この文字列の先頭をLEFT関数で切り取ってやれば、欲しいデータを得ることができました。

GROUP_CONCAT関数で得られた文字列からは以下のようにほしいデータを切り出しています。

```sql
  CAST(
    CASE
      WHEN shohin_cd_str LIKE '%,%' -- 2つ以上結合されている場合は,までを切り取りたい
        THEN LEFT(shohin_cd_str, INSTR(shohin_cd_str, ',') - 1) -- 最初の,までを切り取る
      ELSE
        shohin_cd_str -- 1つしかない場合は,を含んでいないのでそのままでよい
    END
  AS SIGNED) AS shohin_cd -- 元のデータ型が整数なので文字列から変換
```

このクエリは自分自身をJOINしておらず、また一時表も使用していないため、パフォーマンスは優れていると思います。ただし結合される文字列がものすごく長くなったりする場合等は使用できないかもしれないため、「使えたらラッキー」くらいに思っておいた方がよいと思います。（自戒）
しかも「上位3件」のような場合は対応できないため、本当に上位1件だけほしい場合にしか使えません。使用用途はかなり狭いですが、何かの参考になればと思います。

# 参考

- [MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数 - GROUP_CONCAT()](https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat)
- [MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数 - INSTR()](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_instr)