PostgreSQLのLATERAL JOINを用いた行ごとの反復処理のシミュレーション

はじめに

リレーショナルデータベースの世界では、SQLは主にセットベースの操作のために設計されており、データセット全体を一度に処理します。このパラダイムは、従来のプログラミング言語の「for-each」ループに似た、行ごとの処理に適したシナリオに遭遇した場合、しばしば課題となります。従来のSQL構文でこの動作を模倣しようとすると、特定のユースケースでは複雑になったり、非効率になったりすることがよくあります。そこで、PostgreSQL の LATERAL JOIN が強力でエレガントなソリューションとして登場します。これは、サブクエリが前の FROM 句の項の列を参照できるようにし、実質的に行ごとの計算を可能にし、複雑なデータ操作の新しい可能性を解き放ちます。この記事では、LATERAL JOIN が SQL におけるセットベース処理と行ベース処理のギャップをどのように埋めるのか、実践的な例と有用性についての洞察を提供します。

行ごとのロジックのための LATERAL JOIN の理解

LATERAL JOIN の力を十分に理解するには、まずいくつかのコアコンセプトを理解することが不可欠です。

主要な概念

• サブクエリの独立性: 一般的に、JOIN 条件や SELECT リストで実行されるサブクエリは、メインクエリの FROM 句の列を参照できません。ただし、行ごとに動作する相関サブクエリは例外ですが、複雑な操作では効率が低いことがよくあります。
• セットベース処理 vs. 行ベース処理: SQL は基本的にデータのセットで動作します。単純な JOIN はセットを結合します。「行ごと」という場合、SQL では、サブクエリまたは関数の結果が、別のテーブルの各個々の行の値に依存する操作を指します。
• LATERAL キーワード: LATERAL キーワードは、サブクエリまたは FROM 句の項に適用されると、その項が、同じ FROM リストでそれより前に出現する FROM 項から提供された列を参照できるようになります。これは、「for-each」のような動作を可能にする重要な要素です。

LATERAL JOIN の原則

ユーザーのテーブルがあり、各ユーザーの最新の 3 つの注文を見つけたいと想像してください。LATERAL JOIN がない場合、複雑なウィンドウ関数や複数の相関サブクエリに頼るかもしれません。LATERAL JOIN を使用すると、各ユーザー行を反復処理し、各ユーザーについて、最新の 3 つの注文を取得するサブクエリを実行すると考えることができます。

SQL エンジンは、LATERAL JOIN に遭遇すると、まず左側のテーブル（LATERAL JOIN の前にあるテーブル）を処理します。左側テーブルによって生成された各行について、次に LATERAL サブクエリ（右側）を実行し、現在の行の値をサブクエリに渡します。このサブクエリの結果は、左側テーブルの現在の行と結合されます。これにより、実質的に「for-each」ループがシミュレートされます。外側のクエリの各行について、その行のデータを使用して操作を実行します。

実装と応用シナリオ

実践的な例で説明しましょう。

シナリオ 1: グループごとの上位 N 件の関連レコードの検索

users テーブルと orders テーブルの 2 つのテーブルがあるとします。各ユーザーの最新の 3 件の注文を見つけたいとします。

users テーブル:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users (user_id, username) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

orders テーブル:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO orders (order_id, user_id, order_date, amount) VALUES
(101, 1, '2023-01-10', 50.00),
(102, 1, '2023-01-15', 75.00),
(103, 2, '2023-01-12', 20.00),
(104, 1, '2023-01-20', 100.00),
(105, 3, '2023-01-05', 30.00),
(106, 2, '2023-01-18', 45.00),
(107, 1, '2023-01-25', 120.00),
(108, 3, '2023-01-10', 60.00),
(109, 2, '2023-01-22', 90.00),
(110, 1, '2023-01-28', 80.00);

LATERAL JOIN を使用:

SELECT
    u.username,
    o.order_id,
    o.order_date,
    o.amount
FROM
    users AS u,
    LATERAL (
        SELECT *
        FROM orders AS ord
        WHERE ord.user_id = u.user_id
        ORDER BY ord.order_date DESC
        LIMIT 3
    ) AS o;

この例では、各 user 行 (u) について、LATERAL サブクエリ（エイリアス o）が実行されます。このサブクエリは、現在の u.user_id で orders をフィルタリングし、order_date の降順で並べ替え、LIMIT 3 を取得します。結果は user 行に結合されます。これにより、一般的な分析タスクである、グループごとの上位 N 件の関連レコードを取得するクリーンな方法が提供されます。

シナリオ 2: 動的な列生成または複雑な計算

各行に計算のパラメータが含まれており、行ごとに異なる計算を実行したいテーブルがあるとします。

CREATE TABLE calculation_params (
    param_id INT PRIMARY KEY,
    value1 INT,
    value2 INT
);

INSERT INTO calculation_params (param_id, value1, value2) VALUES
(1, 10, 5),
(2, 20, 4),
(3, 15, 3);

次に、各行に対して value1 * value2 と value1 + value2 を計算し、それらを関数または派生テーブルによって返される構造に格納したいとします。

SELECT
    cp.param_id,
    calcs.product_result,
    calcs.sum_result
FROM
    calculation_params AS cp,
    LATERAL (
        SELECT
            cp.value1 * cp.value2 AS product_result,
            cp.value1 + cp.value2 AS sum_result
    ) AS calcs;

ここでは、LATERAL クエリは実質的に行ごとに適用される関数として機能し、元の行のデータに基づいて派生列を生成します。これは、テーブル返却関数や VALUES 句と組み合わせると特に便利です。

シナリオ 3: 行ごとの配列または JSONB の展開

LATERAL JOIN の一般的なユースケースは、テーブル返却関数、特に unnest() や jsonb_array_elements() と連携する場合です。これは、配列または JSONB データがメインテーブルの列に格納されている場合に役立ちます。

CREATE TABLE product_tags (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[] -- タグの配列
);

INSERT INTO product_tags (product_id, name, tags) VALUES
(1, 'Laptop', ARRAY['electronics', 'computing', 'portable']),
(2, 'Keyboard', ARRAY['electronics', 'peripherals']),
(3, 'Mouse', ARRAY['electronics', 'peripherals', 'wireless']);

各製品を個別のタグとともにリストするには：

SELECT
    pt.product_id,
    pt.name,
    tag_element AS tag
FROM
    product_tags AS pt,
    LATERAL unnest(pt.tags) AS tag_element;

LATERAL なしでは、unnest() はすべての行のすべてのタグを 1 つのセットに展開するため、それらを効率的に元の製品に関連付けることが困難になります。LATERAL は、各 product_tags 行について unnest() が呼び出され、その行の tags 配列が渡されることを保証します。

LEFT LATERAL JOIN（オプションの結果）

LEFT JOIN と同様に、LEFT LATERAL JOIN は、LATERAL サブクエリが行を生成しない場合でも、プライマリテーブルの行が返されることを可能にします。「for-each」操作で結果が得られない場合でも、外側の行を含めたい場合に重要です。

-- ユーザー 3 が 'orders' テーブルに注文がないと仮定します。
-- Charlie (user_id = 3) の注文が 1 件しかなく、3 件取得しようとした場合、1 件しか返されません。
-- Charlie が 0 件の注文の場合、単純な LATERAL JOIN は Charlie を除外します。

ALTER TABLE orders DISABLE TRIGGER ALL; -- 例のために一時的に外部キーチェックを無効化
DELETE FROM orders WHERE user_id = 3;
ALTER TABLE orders ENABLE TRIGGER ALL;

SELECT
    u.username,
    o.order_id,
    o.order_date,
    o.amount
FROM
    users AS u
LEFT JOIN LATERAL (
        SELECT *
        FROM orders AS ord
        WHERE ord.user_id = u.user_id
        ORDER BY ord.order_date DESC
        LIMIT 3
    ) AS o ON TRUE; -- ON TRUE は、join 条件が LATERAL サブクエリの WHERE 句で処理されることが多いため、一般的です。

このクエリでは、 no associated orders がなくても 'Charlie' が含まれ、order columns には NULL 値が表示されます。

結論

PostgreSQL の LATERAL JOIN は、SQL で真に行依存の操作を可能にする、非常に汎用性が高く強力な機能であり、効果的に「for-each」ループセマンティクスをセットベースのクエリにもたらします。前の FROM 項の列を参照できる能力は、グループごとの N 件の相関アイテムの検索、行ごとの派生データの動的な生成、行固有の配列または JSONB データの効率的な展開などの問題に対するエレガントなソリューションを解き放ちます。LATERAL JOIN を理解し活用することにより、開発者は、複雑な分析およびデータ変換タスクに対して、より簡潔で読みやすく、しばしばよりパフォーマンスの高いクエリを作成でき、典型的な手続型ロジックと SQL の宣言型性質の間のギャップを埋めることができます。これにより、SQL は以前は手間がかかっていたり非効率的だったシナリオを処理できるようになり、高度な PostgreSQL ユーザーにとって不可欠なツールとなっています。