【PostgreSQL】JOIN LATERALを使用してサブクエリのGROUP BYの単位でLIMITを設定する

5191 ワード
テキストリンク

概要

表題のようにサブクエリのGROUP BYの単位でLIMITをかけたいという場合、window関数を利用されることがあると思います。PostgreSQLの場合はGroup by limit per group in PostgreSQLの記事にある通り、JOIN LATERALという機能があり、window関数を利用しなくても実現が可能です。
今回はJOIN LATERALを使用したSQLを紹介します。

前提とするテーブル

【投稿テーブル(posts)】

id name user_id created_at updated_at
post1 投稿1 user1 2022-04-20 14:57:31.156 +0900 2022-04-20 14:57:31.156 +0900
post2 投稿2 user2 2022-04-21 14:57:31.156 +0900 2022-04-21 14:57:31.156 +0900
post3 投稿3 user3 2022-04-22 14:57:31.156 +0900 2022-04-22 14:57:31.156 +0900

【コメントテーブル(comments)】

id comment post_id user_id created_at updated_at
comment1 コメント1-1 post1 user2 2022-04-20 15:57:31.156 +0900 2022-04-20 15:57:31.156 +0900
comment2 コメント1-2 post1 user3 2022-04-20 16:57:31.156 +0900 2022-04-20 16:57:31.156 +0900
comment3 コメント1-3 post1 user1 2022-04-20 17:57:31.156 +0900 2022-04-20 17:57:31.156 +0900
comment4 コメント1-4 post1 user3 2022-04-20 18:57:31.156 +0900 2022-04-20 18:57:31.156 +0900
comment5 コメント2-1 post2 user3 2022-04-21 17:57:31.156 +0900 2022-04-20 17:57:31.156 +0900

【ユーザーテーブル(users)】

id name created_at updated_at
user1 ユーザ1 2022-03-20 15:57:31.156 +0900 2022-03-20 15:57:31.156 +0900
user2 ユーザ2 2022-03-21 15:57:31.156 +0900 2022-03-21 15:57:31.156 +0900
user3 ユーザ3 2022-03-22 15:57:31.156 +0900 2022-03-22 15:57:31.156 +0900

SQL

投稿へ紐づくコメントのうち、投稿毎に早くコメントした人から2人まで取得し、ユーザ情報を取得してみます。
SQLは以下です。

SELECT
  p.id as post_id,
  p.name as post_name,
  u.id as user_id,
  u.name as user_name
FROM
  posts p
  JOIN LATERAL (
    SELECT
     c.post_id,
     c.user_id
    FROM
     comments c
    WHERE
      c.post_id = p.id
    GROUP BY
      c.post_id,
      c.user_id
    ORDER BY 
      MIN(c.created_at) ASC
    LIMIT 2
  ) cu ON true
  INNER JOIN users u on u.id = cu.user_id
ORDER BY post_id

結果は以下の通りになります。

post_id post_name user_id user_name
post1 投稿1 user2 ユーザ2
post1 投稿1 user3 ユーザ3
post2 投稿2 user3 ユーザ3
Flutter - iOSアプリでライセンスplistを半自動で生成して設定画面に表示できるライブラリを作りました
【Flutter】charts_flutterでグラフを作成してみる