SQL集計の一つの選択肢。LATERAL JOINで見えた構造化とトレードオフ

はじめに

こんにちは。エンジニアとして日々コードを書いていると、ふとした瞬間に「この書き方、もっと良くできないか？」と立ち止まることがあります。

動いているし、要件も満たしている。
でも、どこか気持ち悪さが残る。

今回書くのは、そんな違和感から始まった、あるSQLのリファクタリングの話です。

「本棚を往復する」ようなクエリ

題材にしたのは、「社員ごとの有給休暇実績」を集計するSQLです。

社員マスタに対して、有給休暇の履歴テーブルから

付与日数
消化日数
最新付与日

といった複数の値をまとめて取得したい、といったケースです。

最初に書いたのは、いわゆるスカラーサブクエリの形でした。

SELECT
    e.employee_name,
    (
        SELECT SUM(days)
        FROM pto_transactions b
        WHERE b.emp_id = e.emp_id
          AND b.code = '1'
    ) AS granted_days,
    (
        SELECT SUM(days)
        FROM pto_transactions b
        WHERE b.emp_id = e.emp_id
          AND b.code = '2'
    ) AS used_days,
    (
        SELECT MAX(grant_date)
        FROM pto_transactions b
        WHERE b.emp_id = e.emp_id
    ) AS latest_grant_date
FROM employees e;

この書き方は直感的で分かりやすい一方で、同じテーブルに対する参照が何度も繰り返されます。

イメージとしては、
「調べ物をするたびに本棚まで走って、1冊だけ確認して戻ってくる」ような状態です。

「これ、もう少しまとめて取りに行けないだろうか？」そんな疑問が出発点でした。

LATERAL JOINという選択肢

調べていく中で出会ったのが、LATERAL JOIN です。

LATERALを使うと、左側の行（ここでは社員ID）をサブクエリに引き渡し、行ごとに動的に生成した結果を結合することができます。

相関サブクエリを「FROM句側に持ってきた」ようなイメージに近いです。

※ LATERAL JOINはPostgreSQL（9.3以降）やMySQL（8.0.14以降）で利用可能です。SQL Serverでは CROSS APPLY / OUTER APPLY が同等の機能になります。

整理してみたメリットとトレードオフ

理解を深める中で、自分なりに整理すると以下のようになりました。

メリット

1回の集計で複数の値をまとめて算出できる（スキャン回数が減る可能性がある）
サブクエリ内で計算した値をそのまま再利用できる

デメリット

FROM句が肥大化し、慣れないと読みにくい
インデックスやデータ量によってはパフォーマンスが逆転する可能性もある

※実行計画やDBの最適化によっては、スカラーサブクエリと同等のパフォーマンスになるケースもあります

最適解というより、「自分の中で選択肢が一つ増えた」という感覚がしっくりきました。

実際に書き換えてみる

上記を踏まえて、クエリを書き換えるとこうなります。

SELECT
    e.employee_name,
    pto.granted_days,
    pto.used_days,
    pto.latest_grant_date
FROM employees e
LEFT JOIN LATERAL (
    SELECT
        SUM(CASE WHEN code = '1' THEN days ELSE 0 END) AS granted_days,
        SUM(CASE WHEN code = '2' THEN days ELSE 0 END) AS used_days,
        MAX(grant_date) AS latest_grant_date
    FROM pto_transactions b
    WHERE b.emp_id = e.emp_id
) AS pto ON TRUE;

一度のアクセスで必要な情報をまとめて取得し、
それを「ひとまとまりの結果」として扱える形になります。

一番の発見は「DRYが効くこと」

今回一番価値を感じたのは、パフォーマンスそのものよりも、変更に強い構造を作りやすいことでした。

ここでいう「DRY（Don't Repeat Yourself）」とは、
同じロジックや定義を複数箇所に書かないようにする設計の考え方です。

実は、スカラーサブクエリでも工夫すればDRYに保つことは可能です。
そのため、「LATERALだから必ずDRYになる」というわけではありません。

ただしLATERALを使うと、関連する集計処理をひとつのサブクエリに閉じ込められるため、結果としてロジックを1箇所に集約しやすい構造になります。

例えば「残日数（付与 - 消化）」を追加する場合。

SELECT
    e.employee_name,
    pto.granted_days,
    pto.used_days,
    pto.latest_grant_date,
    -- 項目「残日数」を追加
    pto.remaining_days
FROM employees e
LEFT JOIN LATERAL (
    SELECT
        SUM(CASE WHEN code = '1' THEN days ELSE 0 END) AS granted_days,
        SUM(CASE WHEN code = '2' THEN days ELSE 0 END) AS used_days,
        MAX(grant_date) AS latest_grant_date,
        -- 「残日数（付与 - 消化）」の集計を追加
        SUM(CASE WHEN code = '1' THEN days ELSE 0 END)
          - SUM(CASE WHEN code = '2' THEN days ELSE 0 END) AS remaining_days
    FROM pto_transactions b
    WHERE b.emp_id = e.emp_id
) AS pto ON TRUE;