経緯
ウィンドウ関数の ROW_NUMBER はPostgreSQLやDWH系のRDBMSで古くから使えたこともあり、JOIN時にグループの最新1件を一緒にとりたいときなど重宝していた。
ROW_NUMBER駆使の例
例えば、ユーザーの一覧＋各ユーザーごとの最新注文をとりたいケースだと以下のようなクエリになる。
- 全ユーザーの一覧を取得する
- 各ユーザーに紐づく注文のうち、
created_atが一番新しい1件を一緒に取得する - 注文が1件もないユーザーも結果に含める（LEFT JOINする）
SELECT *
FROM users
LEFT JOIN (
SELECT
orders.*,
-- ユーザー単位で最新注文順に連番を振る
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM orders
) latest_orders
ON latest_orders.user_id = users.id
-- 注文の連番 `1` に絞り込み ＝ 最新1件
AND latest_orders.rn = 1;
ROW_NUMBER駆使の課題
「ユーザーごとの最新注文1件」をとれればよいのだが、
- 全注文に対してユーザー単位で順位を振る
- 順位が1のものだけを残す
になるので、間接的な取り方になっているのと、注文データ数が多いと latest_orders の無駄が大きくなる。
実はLATERAL JOINが使えた
最近になって、ROW_NUMBER の代わりに LATERAL を使ってJOINすると、もっと直感的に書けることを知った。
It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them. For example, if get_product_names() returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this:
SELECT m.name FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true WHERE pname IS NULL;
PostgreSQLでは9系（9.3）から、MySQLでは8系（8.0.14）から入っていたようだがまったく気づいていなかった。
LATERAL JOINに置き換えた例
先ほどのユーザーの一覧＋各ユーザーごとの最新注文をとりたいケースは、以下のようなクエリに置き換えられる。
SELECT *
FROM users
LEFT JOIN LATERAL (
SELECT *
FROM orders
-- 外側のusersのidでユーザーを絞り込み、最新注文1件を取得
WHERE orders.user_id = users.id
ORDER BY orders.created_at DESC
LIMIT 1
) latest_order ON TRUE;
-
LATERALをつけると、サブクエリの中で外側のテーブルを参照できる（ここではusers） - 「ユーザーごとの最新を1件」を
WHERE+ORDER BY+LIMITで素直に表現できる - JOIN条件は不要になるため
ON TRUEを書く（LATERAL のイディオム）
サブクエリの中から外側の users を直接参照できるため、ユーザーごとに「最新順に並べて1件」をそのまま書けるのが大きい。
注文データ数が多かったとしても、ユーザーごとに取得する注文は1件なので無駄はない。
LATERAL JOIN 覚えておこう 