🔧

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

に公開
PostgreSQL
tech

概要

表題のようにサブクエリの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

Discussion