ELW株式会社 テックブログ

リアルなログをそのままお届けします。

LATERALサブクエリについて

バックエンド勉強会

CTOをしております、村上です。今回のバックエンド勉強会では、Postgres SQLのLATERALサブクエリについて話しましたので、その内容を紹介させていただきます。

LATERALについて

  • SQL標準に含まれている
  • 公式ドキュメントの記載箇所: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL
  • CROSS結合として用いる
    • 補足: CROSS結合の記法
      • FROM *T1* CROSS JOIN *T2*
      • FROM *T1* INNER JOIN *T2* ON TRUE
      • FROM *T1*, *T2*
  • 利点
    • JOINの左側の値をLATERAL句内で使用できる

    • 下記は公式ドキュメントより引用

        SELECT p1.id, p2.id, v1, v2
  FROM polygons p1, polygons p2,
       LATERAL vertices(p1.poly) v1,
       LATERAL vertices(p2.poly) v2
  WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

        SELECT m.name
  FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
  WHERE pname IS NULL;

    • サブクエリで同様に書く場合でもJOINである分PostgresのAnalyzerで最適化されやすいらしい

  • 仕組み

    • 以下記事によるとこのような仕組みとのこと

  • 我々のプロジェクトでの利用箇所

    • 1取引先担当者に対して複数の名刺と紐づいていて、名刺一覧では各取引先担当者の最新の名刺のみ取得する

例: 顧客ごとの最新注文を取得する

テーブル定義

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name TEXT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount NUMERIC
);

投入データ

INSERT INTO customers (customer_id, customer_name) VALUES
(1, '田中太郎'),
(2, '佐藤花子'),
(3, '鈴木一郎');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 1, '2023-01-01', 1000),
(2, 1, '2023-01-15', 2000),
(3, 2, '2023-02-01', 1500),
(4, 2, '2023-02-10', 3000),
(5, 3, '2023-03-01', 2000);

得たいデータ

customer_name order_id order_date amount
田中太郎 2 2023-01-15 2000
佐藤花子 4 2023-02-10 3000
鈴木一郎 5 2023-03-01 2000

データ取得SQL

LATERALを使うパターン

SELECT c.customer_name, lo.*
FROM customers c
JOIN LATERAL (
    SELECT order_id, order_date, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 1
) AS lo ON true;

SELECT句でサブクエリを使うパターン

SELECT c.customer_name,
       (SELECT o.order_id
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_date DESC
        LIMIT 1) AS order_id,
       (SELECT o.order_date
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_date DESC
        LIMIT 1) AS order_date,
       (SELECT o.amount
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_date DESC
        LIMIT 1) AS amount
FROM customers c;

相関サブクエリを用いてJOINするパターン

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    o.amount
FROM
    customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
    AND o.order_date = (
        SELECT o2.order_date
        FROM orders o2
        WHERE o2.customer_id = c.customer_id
        ORDER BY o2.order_date DESC
        LIMIT 1
    );

ROW_NUMBERを使うパターン

WITH RankedOrders AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM
        orders
)
SELECT
    c.customer_name,
    ro.order_id,
    ro.order_date,
    ro.amount
FROM
    customers c
LEFT JOIN
    RankedOrders ro ON c.customer_id = ro.customer_id AND ro.rn = 1;

まとめ

  • LATERALの使い所
    • 相関サブクエリの代替
    • 行ごとの複雑な計算や処理
    • トップNの取得
  • 相関サブクエリで書ける場合でもLATERALを使うと大量データの場合にパフォーマンスがよくなる可能性が高いらしいです
    • 実際にそうなのかは、大量データを投入して確かめる予定です
SELECT
    COUNT(bc."id")
FROM
    account_contact ac
    JOIN business_card bc ON bc.id = (
        SELECT
            bc2.id
        FROM
            business_card bc2
        WHERE
            bc2.account_contact_id = ac.id
        ORDER BY
            bc2.received_date DESC,
            bc2.id DESC FETCH NEXT 1 ROWS ONLY
    )
    JOIN account a ON a.id = ac.account_id
    AND a.deletion_flag = false
    JOIN "user" u ON u.id = bc.creation_user_id
    AND u.deletion_flag = false