お疲れ様です。すぺきよです。
RDBを使って開発していると、group byで集計したグループごと最初に出現したデータが欲しい時があります。
例えば、掲示板でユーザーごとに最後に発言したメッセージの一覧を抽出したいときなどですね。
この場合、一度ユーザーごとに最後に発言したメッセージのIDを集計するサブクエリを作って、そのID一覧とメッセージ本体のテーブルと再度結合、情報を収集する方法を今まで取ってきました。
これでもいいのですが、なんとなくこの方法はくどく感じていました。
そこで、何かいいものはないかとMariaDBのマニュアルを調べていると、使えそうなものがあったので、紹介します。
筆者の環境
MariaDB
Version 10.5.16
どう書けば良いか
まずは結論ですが、以下のlatest_messegeの列のように、group_concatとorder by句、limit句を組み合わせてることで実現できます。
select
user_name
,count(user_name) as post_count
,group_concat(msg order by id desc limit 1) as latest_message
from messages
group by user_name;注意点としては、MariaDB10.3.3以降のバージョンであることが求められます。
10.3.2以下では、group_concat内にLimit句を使うことができないそうです。
Until MariaDB 10.3.2, it was not possible to use the LIMIT clause with
https://mariadb.com/kb/en/group_concat/GROUP_CONCAT. This restriction was lifted in MariaDB 10.3.3.
実際に動作を試してみる
では、本当にうまく動くのか、動作検証をしてみましょう。
テストデータの準備
テーブルの準備
掲示板に入力されたメッセージが保存されていると想定するテーブルを以下のSQLを利用して準備します。
create table messages
(
id serial primary key
,user_name varchar(20) binary not null
,msg text not null
);自動採番されるIDと、ユーザー名、投稿されたメッセージを保存し、IDをPrimaryKeyとするシンプルなテーブルです。
データの準備
データは以下のように作成しました。
テスト用にわかりやすければなんでもOKです。
insert into messages(user_name, msg) values
('tanaka', 'こんにちは')
,('yamada', 'こんにちは、tanakaさん')
,('suzuki','tanakaさん、はじめまして')
,('tanaka', 'suzukiさん、はじめまして。')
,('yamada', 'こちらは今日もいい天気です。皆さんはどうですか？')
,('suzuki','こちらはあいにくの雨です。外出できません。')
,('tanaka', 'こちらは薄曇りですね。散歩日和です。');
ID列は自動採番されるので、特に指定していません。
実際に集計してみる
今回求めたいのは、ユーザーごとの最新のメッセージ一覧です。
そのため、今回の例では、以下のように集計されることが求められます。
suzuki:こちらはあいにくの雨です。外出できません。
tanaka:こちらは薄曇りですね。散歩日和です。
yamada:こちらは今日もいい天気です。皆さんはどうですか？では、実際に、次の集計用SQLを実行します。
select
user_name
,count(user_name) as post_count
,group_concat(msg order by id desc limit 1) as latest_message
from messages
group by user_name;そして、実際に実行してみた結果が以下の内容になります。
+-----------+------------+--------------------------------------------------------------------------+
| user_name | post_count | latest_message |
+-----------+------------+--------------------------------------------------------------------------+
| suzuki | 2 | こちらはあいにくの雨です。外出できません。 |
| tanaka | 3 | こちらは薄曇りですね。散歩日和です。 |
| yamada | 2 | こちらは今日もいい天気です。皆さんはどうですか？ |
+-----------+------------+--------------------------------------------------------------------------+正しく出力されていますね。
逆に一番古いメッセージを抽出する
動作検証のために、一番古いメッセージの出力も試してみましょう。
group_concat内のorder byの並び順を降順(desc)から昇順(asc)に変更するだけです。
具体的には以下のように記述します。
select
user_name
,count(user_name) as post_count
,group_concat(msg order by id asc limit 1) as latest_message
from messages
group by user_name;正しく動作していれば、次の結果が得られるはずです。
suzuki:tanakaさん、はじめまして
tanaka:こんにちは
yamada:こんにちは、tanakaさん実際に上記SQLを実行した結果が次のとおりです。
+-----------+------------+-----------------------------------+
| user_name | post_count | latest_message |
+-----------+------------+-----------------------------------+
| suzuki | 2 | tanakaさん、はじめまして |
| tanaka | 3 | こんにちは |
| yamada | 2 | こんにちは、tanakaさん |
+-----------+------------+-----------------------------------+うん、正しく抽出できているようですね。
さいごに
今回は、group byで集計対象としていないカラムに出力される値を明示的にコントロールする方法を紹介しました。
サブクエリを使う方法もいいのですが、SQLが長くなって複雑になりがちで、保守性が下がってしまいます。
それと比較すると、この書き方はコンパクトでわかりやすいですね。
