53731


下記のようなテーブル・データが有った場合、特定のグループに所属するユーザーそれぞれの最新の投稿を1件だけ取得したい、と考えています。

例えば、group「イ」(group.id 1)の場合、グループ「イ」に属するAさん・Dさん・Eさんそれぞれの最新の投稿を

[code]

    +---------+-----------+-----------+--------------------+
    | user_id | user_name |  content  |     posted_at      |
    +---------+-----------+-----------+--------------------+
    |       1 | A         | contentA2 | 2019-3-25 15:25:00 |
    |       4 | D         | contentD2 | 2019-3-25 17:00:00 |
    |       5 | E         | contentE2 | 2019-3-25 17:05:00 |
    +---------+-----------+-----------+--------------------+
    
[/code]

このように取得したいと考えています。

この場合、どのようなSQLにすれば良いのでしょうか?

ご教授よろしくお願いします。

* * *

## テーブル・データ

### users

[code]

    +----+------+
    | id | name |
    +----+------+
    |  1 | A    |
    |  2 | B    |
    |  3 | C    |
    |  4 | D    |
    |  5 | E    |
    +----+------+
    
[/code]

* * *

### groups

[code]

    +----+------+
    | id | name |
    +----+------+
    |  1 | イ   |
    |  2 | ロ   |
    +----+------+
    
[/code]

* * *

### groups_users

[code]

    +----+----------+---------+
    | id | group_id | user_id |
    +----+----------+---------+
    |  1 |        1 |       1 |
    |  2 |        2 |       1 |
    |  3 |        2 |       2 |
    |  4 |        2 |       3 |
    |  5 |        1 |       4 |
    |  6 |        1 |       5 |
    +----+----------+---------+
    
[/code]

* * *

### posts

[code]

    +----+---------+-----------+--------------------+
    | id | user_id |  content  |     posted_at      |
    +----+---------+-----------+--------------------+
    |  1 |       1 | contentA1 | 2019-3-25 15:00:00 |
    |  2 |       2 | contentB1 | 2019-3-25 15:15:00 |
    |  3 |       5 | contentE1 | 2019-3-25 15:15:00 |
    |  4 |       3 | contentC1 | 2019-3-25 15:20:00 |
    |  5 |       1 | contentA2 | 2019-3-25 15:25:00 |
    |  6 |       4 | contentD1 | 2019-3-25 16:30:00 |
    |  7 |       4 | contentD2 | 2019-3-25 17:00:00 |
    |  8 |       5 | contentE2 | 2019-3-25 17:05:00 |
    |  9 |       3 | contentC2 | 2019-3-25 17:30:00 |
    | 10 |       2 | contentB2 | 2019-3-25 20:00:00 |
    +----+---------+-----------+--------------------+
    
[/code]


CC BY-SA 4.0
2019-03-25T12:35:58.783
53720
2019-03-27T02:36:47.497
2019-03-26T02:56:54.977
3060
32667
question
sql
SQLで特定のグループに所属するユーザーの最新の投稿1件を取得したい


下記のようなSQLにすれば動きます。[MySQL 5.6 にて確認](http://sqlfiddle.com/#!9/484f48/7)

[code]

    select gu.user_id,
           u.name,
           p.content,
           p.posted_at
    from   users u,
           groups g,
           groups_users gu,
           posts p
    where  u.id = gu.user_id
    and    g.id = gu.group_id
    and    u.id = p.user_id
    and    p.posted_at = (select   max(tmp.posted_at)
                          from     posts tmp
                          where    tmp.user_id = p.user_id
                          group by tmp.user_id)
    and    g.name = 'イ'
    
[/code]

この解法はwhere句でサブクエリしています。  
`posts`テーブルの`posted_at`カラムが、`user_id`でグループ化した時の最大値(`max`関数で取得します)に合致するレコードのみ抽出しているのがポイントです。


CC BY-SA 4.0
2019-03-26T00:38:31.057
53729
2019-03-26T00:38:31.057
9820
53720
answer


`posted_at`でグループ化し最新の投稿を取得しようとすると`user_id`と`posted_at`しか得られません。他のカラムを取得するためには再度`posts`と自己結合する必要があります。

[code]

    SELECT post.user_id, users.name AS user_name, post.content, post.posted_at
    FROM posts AS post
    INNER JOIN users ON post.user_id = users.id
    INNER JOIN (
      SELECT posts.user_id, MAX(posts.posted_at) AS posted_at
      FROM posts
      INNER JOIN groups_users ON posts.user_id = groups_users.user_id AND groups_users.group_id = 1
      GROUP BY posts.user_id
    ) AS newpost ON post.user_id = newpost.user_id AND post.posted_at = newpost.posted_at
    
[/code]

これでもいいのですが、発想の転換で、「最新の投稿」を「自身より新しい投稿が存在しない」と考えてやると集計を使わないもう少しシンプルな記述ができます。

[code]

    SELECT post.user_id, users.name AS user_name, post.content, post.posted_at
    FROM posts AS post
    INNER JOIN users ON post.user_id = users.id
    INNER JOIN groups_users ON posts.user_id = groups_users.user_id AND groups_users.group_id = 1
    WHERE NOT EXISTS (
      SELECT *
      FROM posts AS newpost
      WHERE post.user_id = newpost.user_id AND post.posted_at < newpost.posted_at
    )
    
[/code]

更に`NOT EXISTS`は`LEFT OUTER JOIN`で書き直せます。

[code]

    SELECT post.user_id, users.name AS user_name, post.content, post.posted_at
    FROM posts AS post
    INNER JOIN users ON post.user_id = users.id
    INNER JOIN groups_users ON post.user_id = groups_users.user_id AND groups_users.group_id = 1
    LEFT OUTER JOIN posts AS newpost ON post.user_id = newpost.user_id AND post.posted_at < newpost.posted_at
    WHERE newpost.posted_at IS NULL
    
[/code]


CC BY-SA 4.0
2019-03-26T02:51:31.870
53731
2019-03-27T02:36:47.497
2019-03-27T02:36:47.497
4236
4236
53720
answer


ウィンドウ関数が使えるなら、テーブル `posts` だけ FIRST_VALUE を取得して重複排除する手もあります。

[code]

    SELECT DISTINCT p.user_id user_id
    ,(SELECT name FROM users WHERE id = p.user_id) user_name
    , FIRST_VALUE(p.content) OVER (PARTITION BY p.user_id ORDER BY p.posted_at DESC) "content"
    , FIRST_VALUE(p.posted_at) OVER (PARTITION BY p.user_id ORDER BY p.posted_at DESC) posted_at
    FROM posts p
    INNER JOIN groups_users gu ON gu.user_id = p.user_id
    WHERE gu.group_id = 1
    ORDER BY p.user_id
    
[/code]


CC BY-SA 4.0
2019-03-26T04:19:57.257
53732
2019-03-26T04:19:57.257
8431
53720
answer