SQLで特定のグループに所属するユーザーの最新の投稿1件を取得したい


下記のようなテーブル・データが有った場合、特定のグループに所属するユーザーそれぞれの最新の投稿を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]




`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]




`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]

