MySQL の GROUP_CONCAT

こんにちは、tahara です。

よくあるグループ毎の最大値を持つレコードを取得する SQL の書き方です。 ただし、MySQL 限定。

次のようなテーブルとレコードがあるとします。

create table foo (
  id int(11),
  group_id int(11),
  value int(11),
  position int(11),
  primary key (id)
);
insert into foo(id, group_id, value, position) values
(1, 1, 11, 3),
(2, 1, 10, 1),
(3, 1, 11, 2),
(4, 2, 10, 1);

mysql> select * from foo;
+----+----------+-------+----------+
| id | group_id | value | position |
+----+----------+-------+----------+
|  1 |        1 |    11 |        3 |
|  2 |        1 |    10 |        1 |
|  3 |        1 |    11 |        2 |
|  4 |        2 |    10 |        1 |
+----+----------+-------+----------+
4 rows in set (0.00 sec)

やりたいのは、次の条件でレコードを取得することです。

• group_id 毎に最も value が大きいレコードを取得したい。
• ただし同じ group_id 毎に最も value が大きいレコード複数あが場合は position が最も小さいレコードを取得する。
• 上の例では id 3 と 4 のレコードを取得する SQL を書きたい。
• そしてパフォーマンスのために相関サブクエリは使いたくない。

MySQL ではこう書けます。

select foo.* from foo
inner join (
  select
    cast(
      substring_index(
        group_concat(distinct id order by value desc, position),
        ',',
        1)
      as signed) id
  from foo group by group_id
) t on foo.id = t.id;

group_concat がいい仕事をしてくれてます。 group_id 毎に id を value 降順、position 昇順にカンマ区切りの文字列にします。

mysql> select group_concat(distinct id order by value desc, position) as x
    -> from foo group by group_id;
+-------+
| x     |
+-------+
| 3,1,2 |
| 4     |
+-------+
2 rows in set (0.01 sec)

substring_index でカンマ区切り id の最初の1つを取得します。

mysql> select
    -> substring_index(
    ->   group_concat(distinct id order by value desc, position),
    ->   ',', 1) x
    -> from foo group by group_id;
+------+
| x    |
+------+
| 3    |
| 4    |
+------+
2 rows in set (0.00 sec)

substring_index で取得したものは文字列なので join でインデックスが効きません。 なので数値にキャストします。

mysql>   select
    ->     cast(
    ->       substring_index(
    ->         group_concat(distinct id order by value desc, position),
    ->         ',',
    ->         1)
    ->       as signed) id
    ->   from foo group by group_id;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

欲しいレコードの id がとれたので、あとは自分自身に join します。

mysql> select foo.* from foo
    -> inner join (
    ->   select
    ->     cast(
    ->       substring_index(
    ->         group_concat(distinct id order by value desc, position),
    ->         ',',
    ->         1)
    ->       as signed) id
    ->   from foo group by group_id
    -> ) t on foo.id = t.id;
+----+----------+-------+----------+
| id | group_id | value | position |
+----+----------+-------+----------+
|  3 |        1 |    11 |        2 |
|  4 |        2 |    10 |        1 |
+----+----------+-------+----------+
2 rows in set (0.00 sec)

MySQL 最高だー、と思いました。