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

概要

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