@Saito3110h (勉強中 saito)posted at 2022-04-11

カラムの値ごとに最大値を求めたい[MySQL]

Q&A

Closed

MySQL SQL MySQL8.0

解決したいこと

テーブルからログインしているユーザ(IDをA001と仮定します)から最後に送信または受信したメッセージとそのユーザIDを取得したいです。

該当のテーブルと設定

発生している問題・エラー

該当するソースコード

ログインしているユーザのIDはA001と仮定します。やりたいこととしては、①ログインしているユーザと送受信履歴のあるユーザからIDパターンを作成し、IDパターンごとのmsg_dateの最大値(MAX)を求めます。②HAVINGで① = msg_dateでそれぞれのユーザごとの最終メッセージを取得しようと考えています。

SELECT a.send_user, a.receive_user, a.msg_date,
    CASE WHEN a.send_user != 'A001' THEN CONCAT('A001', a.send_user) WHEN a.receive_user != 'A001' THEN CONCAT('A001', a.receive_user) END AS DM_ID_PTN
    
FROM DM a
WHERE a.send_user = 'A001' OR a.receive_user = 'A001' 

ORDER BY a.msg_date DESC
;

これを実行すると、下のようになります。これにDM_ID_PTNごとにmsg_dateの最大値を作りたいです。

自分で試したこと

GROUP BY DM_ID_PTNでやろうとしましたが、主キーID AUTO_INCREMENTのせいでどうしても昇順の状態で集約されてしまい、それぞれの最初のメッセージしか取得できなくなってしまいます。

1Answer

@y231 posted at 2022-04-11

「ログインユーザが送信または受信した相手ごとに、最終メッセージを取得したい」ということですかね。
IDパターンはそのままのカラムでGROUP BYすればいいと思いますが、どちらでも大丈夫です。

方法1:「送受信パターンごとの最終日時」テーブルをサブクエリとします。

SELECT d.*, CONCAT(d.send_user, d.receive_user) AS DM_ID_PTN
FROM DM d
INNER JOIN (
	SELECT send_user, receive_user, MAX(msg_date) AS max_date
	FROM DM 
    WHERE send_user = 'A001' OR receive_user = 'A001'
	GROUP BY send_user, receive_user
) m
ON m.send_user = d.send_user
AND m.receive_user = d.receive_user
AND m.max_date = d.msg_date

方法2:「これより大きい日時が見つからないデータ」を取得します。

SELECT d.*, CONCAT(d.send_user, d.receive_user) AS DM_ID_PTN
FROM DM d
WHERE (d.send_user = 'A001' OR d.receive_user = 'A001')
AND NOT EXISTS (
	SELECT 1
	FROM DM m
	WHERE m.send_user = d.send_user
	AND m.receive_user = d.receive_user
	AND d.msg_date < m.msg_date
)

データ規模にもよるかもしれませんが、処理速度的にはNOT EXISTSの方が早いかもしれないです。
また、他にも書き方はあると思います。

0Like

Comments

@Saito3110h
Questioner
ご回答ありがとうございます。
これらを試してみましたが、やはり、最終メッセージを取得することはできませんでした。
GROUP BY での集約も自動的に昇順で集約されてしまうため、最終日で集約することができませんでした。
@y231
あなたの言う「最終メッセージ」とは何でしょうか。

私が提示したSQLが取得するものは「A001が送信した各ユーザへの最終メッセージ、および（または）、A001が受信した各ユーザからの最終メッセージ」になります。

「A001と各ユーザのメッセージ、どちらかが最後に発言したメッセージ」
という意味でしょうか。
もう少し簡潔に説明していただけると助かります。
@y231
「A001とA002（各ユーザ）でのやり取りでの最終メッセージ」なら、
最初のSQLを参考にMAXをとってサブクエリにすればいいんじゃないでしょうか。

SELECT d.* FROM DM d
INNER JOIN (
SELECT
(CASE WHEN send_user = 'A001' THEN receive_user ELSE send_user END) AS target_user,
MAX(msg_date) AS max_date
FROM DM
WHERE send_user = 'A001' OR receive_user = 'A001'
GROUP BY (CASE WHEN send_user = 'A001' THEN receive_user ELSE send_user END)
) m
ON m.max_date = d.msg_date
AND (
d.send_user = 'A001' AND m.target_user = d.receive_user
OR d.receive_user = 'A001' AND m.target_user = d.send_user
)
@Saito3110h
Questioner
コメントありがとうございます。
想定していたのは、ログインしているユーザが最後に送信又は受信したメッセージを１つのデータ列として、非ログインユーザごとに取得することを考えていました。また、ご回答に対しても少し誤解をしていました。言葉にするのが難しくなってしまい、申し訳ないです。
拝見する限り、それは難しいとわかったため、ログインユーザーが最後に送信したメッセージと最後に受信したメッセージを2回に分けて取得し、サーバーサイドで判別しようと思います。
@Saito3110h
Questioner
ご回答ありがとうございました。やりたいことはできたので、質問をクローズさせていただきます。

Are you sure you want to delete the question?