SQLで重複を除いたパフォーマンスの良いSQL文について

前提・実現したいこと

以下のメッセージテーブルから、自分がやりとりした相手一覧と、時間、ユーザ情報を取得したいです。
to_user_id,from_user_idから、自分に送られてきたメッセージ、また自分が送ったメッセージをgroupし一覧で取得し、messageテーブルの作成日、メッセー文章、ユーザ情報を取得したいです。

messageテーブル
|----+------------+--------------+-----------+-----------------|
| id | to_user_id | from_user_id | message   | created_at      |
|----+------------+--------------+-----------+-----------------|
| 1  | 1          | 2            | こんちわ! | 2021/1/1 00:01  |
| 2  | 2          | 3            | こんちわ! | 2021/1/3 00:02  |
| 3  | 3          | 4            | こんちわ! | 2021/1/2 00:03  |
| 4  | 4          | 5            | こんちわ! | 2021/1/4 00:03  |
| 5  | 5          | 1            | こんちわ! | 2021/1/5 00:03  |
| 6  | 1          | 2            | こんちわ! | 2021/1/10 00:33 |
| 7  | 3          | 2            | こんちわ! | 2021/1/11 20:00 |
| 8  | 3          | 2            | こんちわ! | 2021/1/10 10:03 |
| 9  | 2          | 3            | こんちわ! | 2021/1/2 01:03  |
| 10 | 5          | 1            | こんちわ! | 2021/1/4 02:03  |
|----+------------+--------------+-----------+-----------------|

user table 
|----+------------|
| id | name       |
|----+------------|
| 1  | みっちゃん |
| 2  | なっちゃん |
| 3  | タカハシ   |
| 4  | りょうた   |
| 5  | うさみ     |
|----+------------|

試したこと

sql文で以下を実行し、userテーブルを軸に、messageテーブルをつなげ取得することができましたが、messageテーブルの作成日、messageを追加すると同じユーザが重複して取得されてしまいます。ユーザはあくまでも1つで最新のmessageが紐付けられた状態で取得したいです。

select * from user join (
    SELECT DISTINCT CASE WHEN to_user_id = 2 THEN from_user_id ELSE to_user_id END as userid,message.created_at
    FROM message
    WHERE from_user_id = 2 OR to_user_id = 2
    ORDER BY created_at desc
) msg  on msg.userid = user.system_user_id

どうぞよろしくお願いいたします。

行動規範の内容に同意します

回答1件

ユーザ2がやりとりしたメッセージのうち、相手ごとの最新メッセージが欲しいということでしょうか? たとえばユーザ2とユーザ3が2021/01/01と2021/02/01の2回やり取りしていた場合、2021/02/01のメッセージデータだけが欲しいという理解です。

であれば、例えば以下のようなSQLで実現することができます。

SQL
1WITH 
2s AS (
3  SELECT
4    id,
5    CASE WHEN from_user_id = 2 THEN to_user_id ELSE from_user_id END AS user_id,
6    message,
7    created_at
8  FROM message
9  WHERE from_user_id = 2 OR to_user_id = 2
10),
11t AS (
12  SELECT
13    id,
14    user_id,
15    message,
16    created_at,
17    RANK() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS RNK
18  FROM s
19)
20SELECT
21  t.id,
22  t.user_id,
23  u.name,
24  t.message,
25  t.created_at
26FROM t
27JOIN user AS u ON t.user_id = u.id
28WHERE RNK = 1;

まずユーザ2とやり取りしたメッセージ一覧を取得したのち、RANK関数を使って、やり取りした相手ごとにメッセージに順位を付けます。このとき、より新しいメッセージごとに若い順位をつけるようにします。最後に、最新のメッセージ(=順位が1位のメッセージ)を取得しつつ、userテーブルを結合します。

2021-02-28 12:11 追記

MySQL5ということはWITHもRANKも利用できないと思います。であれば以下のようなSQLになるかと思います。

SQL
1SELECT 
2  s.id,
3  s.to_user_id,
4  u.name,
5  s.message,
6  s.created_at
7FROM (
8  SELECT id, from_user_id, to_user_id, message, created_at FROM message
9  UNION ALL 
10  SELECT id, to_user_id, from_user_id, message, created_at FROM message
11) AS s
12JOIN user AS u ON s.to_user_id = u.id
13WHERE from_user_id = 2
14AND NOT EXISTS (
15  SELECT *
16  FROM (
17    SELECT id, from_user_id, to_user_id, message, created_at FROM message
18    UNION ALL 
19    SELECT id, to_user_id, from_user_id, message, created_at FROM message    
20  ) AS t
21  WHERE s.to_user_id = t.to_user_id AND s.created_at < t.created_at
22)