【PostgreSQL】もう手放せない！最新SQL標準の便利構文を使い倒して開発効率を爆上げしよう🐘✨

WITH departments AS (
    SELECT * FROM (VALUES
        (1, '営業部'),
        (2, '開発部'),
        (3, '人事部')
    ) AS t (department_id, department_name)
),
employees AS (
    SELECT * FROM (VALUES
        (101, '山田太郎', 1),
        (102, '佐藤花子', 2),
        (103, '田中一郎', 1),
        (104, '鈴木次郎', 2),
        (105, '高橋三郎', NULL) -- 所属部署なし
    ) AS t (employee_id, employee_name, department_id)
)
-- この後で実際のクエリを記述

WITH departments AS (
    SELECT * FROM (VALUES
        (1, '営業部'),
        (2, '開発部'),
        (3, '人事部')
    ) AS t (department_id, department_name)
),
employees AS (
    SELECT * FROM (VALUES
        (101, '山田太郎', 1),
        (102, '佐藤花子', 2),
        (103, '田中一郎', 1),
        (104, '鈴木次郎', 2),
        (105, '高橋三郎', NULL)
    ) AS t (employee_id, employee_name, department_id)
)
SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
INNER JOIN
    departments d USING (department_id);

-- データ定義文省略
SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
INNER JOIN
    departments d ON e.department_id = d.department_id;

-- データ定義文省略
SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
NATURAL INNER JOIN -- INNER は省略可能
    departments d;

WITH categories AS (
    SELECT * FROM (VALUES
        (1, '果物'),
        (2, '野菜')
    ) AS t (category_id, category_name)
),
products AS (
    SELECT * FROM (VALUES
        (101, 'りんご', 1, 150, '2024-05-01'),
        (102, 'みかん', 1, 100, '2024-05-10'),
        (103, 'バナナ', 1, 120, '2024-04-20'),
        (201, 'キャベツ', 2, 200, '2024-05-05'),
        (202, 'レタス', 2, 180, '2024-05-12'),
        (203, 'トマト', 2, 220, '2024-04-25'),
        (204, 'きゅうり', 2, 160, '2024-05-15')
    ) AS t (product_id, product_name, category_id, price, registered_date)
)
-- この後で実際のクエリを記述

WITH categories AS (
    SELECT * FROM (VALUES
        (1, '果物'),
        (2, '野菜')
    ) AS t (category_id, category_name)
),
products AS (
    SELECT * FROM (VALUES
        (101, 'りんご', 1, 150, '2024-05-01'),
        (102, 'みかん', 1, 100, '2024-05-10'),
        (103, 'バナナ', 1, 120, '2024-04-20'),
        (201, 'キャベツ', 2, 200, '2024-05-05'),
        (202, 'レタス', 2, 180, '2024-05-12'),
        (203, 'トマト', 2, 220, '2024-04-25'),
        (204, 'きゅうり', 2, 160, '2024-05-15')
    ) AS t (product_id, product_name, category_id, price, registered_date)
)
SELECT
    c.category_name,
    p_lat.product_name,
    p_lat.price,
    p_lat.registered_date
FROM
    categories c
LEFT JOIN LATERAL (
    SELECT
        p.product_name,
        p.price,
        p.registered_date
    FROM
        products p
    WHERE
        p.category_id = c.category_id -- 左側のテーブルの行(c)を参照
    ORDER BY
        p.registered_date DESC
    LIMIT 2
) AS p_lat ON TRUE; -- LATERALの場合、ON TRUE や ON p_lat IS NOT NULL など自明な条件でよい

-- ウィンドウ関数を使った例
WITH categories AS (
    SELECT * FROM (VALUES
        (1, '果物'),
        (2, '野菜')
    ) AS t (category_id, category_name)
),
products AS (
    SELECT * FROM (VALUES
        (101, 'りんご', 1, 150, '2024-05-01'),
        (102, 'みかん', 1, 100, '2024-05-10'),
        (103, 'バナナ', 1, 120, '2024-04-20'),
        (201, 'キャベツ', 2, 200, '2024-05-05'),
        (202, 'レタス', 2, 180, '2024-05-12'),
        (203, 'トマト', 2, 220, '2024-04-25'),
        (204, 'きゅうり', 2, 160, '2024-05-15')
    ) AS t (product_id, product_name, category_id, price, registered_date)
),
ranked_products AS (
    SELECT
        p.product_name,
        p.price,
        p.registered_date,
        p.category_id,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.registered_date DESC) as rn
    FROM
        products p
)
SELECT
    c.category_name,
    rp.product_name,
    rp.price,
    rp.registered_date
FROM
    categories c
INNER JOIN
    ranked_products rp ON c.category_id = rp.category_id
WHERE
    rp.rn <= 2
ORDER BY
    c.category_name, rp.registered_date DESC;

WITH sales AS (
    SELECT * FROM (VALUES
        ('2024-01-10'::date, '商品A', 100, 3),
        ('2024-01-12', '商品B', 150, 2),
        ('2024-01-15', '商品A', 100, 5),
        ('2024-02-05', '商品C', 200, 1),
        ('2024-02-10', '商品B', 150, 4),
        ('2024-02-20', '商品A', 100, 2)
    ) AS t (sale_date, product_name, unit_price, quantity)
)
-- この後で実際のクエリを記述

WITH sales AS (
    SELECT * FROM (VALUES
        ('2024-01-10'::date, '商品A', 100, 3),
        ('2024-01-12', '商品B', 150, 2),
        ('2024-01-15', '商品A', 100, 5),
        ('2024-02-05', '商品C', 200, 1),
        ('2024-02-10', '商品B', 150, 4),
        ('2024-02-20', '商品A', 100, 2)
    ) AS t (sale_date, product_name, unit_price, quantity)
),
-- CTE 1: 各売上の売上金額を計算
daily_sales_amount AS (
    SELECT
        sale_date,
        product_name,
        unit_price * quantity AS amount
    FROM
        sales
),
-- CTE 2: 商品別・月別の売上合計を計算
monthly_product_sales AS (
    SELECT
        EXTRACT(YEAR FROM sale_date) AS sale_year,
        EXTRACT(MONTH FROM sale_date) AS sale_month,
        product_name,
        SUM(amount) AS total_monthly_amount
    FROM
        daily_sales_amount
    GROUP BY
        1, 2, 3 -- PostgreSQLではSELECTリストの序数を指定可能
),
-- CTE 3: 全体の月間平均売上を計算（これは月ごとの商品別売上の平均）
avg_monthly_product_sales AS (
    SELECT
        product_name,
        AVG(total_monthly_amount) AS avg_monthly_sale
    FROM
        monthly_product_sales
    GROUP BY
        product_name
)
-- メインクエリ: CTEの結果を結合して表示
SELECT
    mps.sale_year,
    mps.sale_month,
    mps.product_name,
    mps.total_monthly_amount,
    amps.avg_monthly_sale
FROM
    monthly_product_sales mps
JOIN
    avg_monthly_product_sales amps ON mps.product_name = amps.product_name
ORDER BY
    mps.sale_year, mps.sale_month, mps.product_name;

SELECT
    EXTRACT(YEAR FROM s.sale_date) AS sale_year,
    EXTRACT(MONTH FROM s.sale_date) AS sale_month,
    s.product_name,
    SUM(s.unit_price * s.quantity) AS total_monthly_amount,
    (
        SELECT AVG(monthly_sum)
        FROM (
            SELECT SUM(s_inner.unit_price * s_inner.quantity) as monthly_sum
            FROM (VALUES
                ('2024-01-10', '商品A', 100, 3),
                ('2024-01-12', '商品B', 150, 2),
                ('2024-01-15', '商品A', 100, 5),
                ('2024-02-05', '商品C', 200, 1),
                ('2024-02-10', '商品B', 150, 4),
                ('2024-02-20', '商品A', 100, 2)
            ) AS s_inner (sale_date::date, product_name, unit_price, quantity)
            WHERE s_inner.product_name = s.product_name -- 相関
            GROUP BY EXTRACT(YEAR FROM s_inner.sale_date), EXTRACT(MONTH FROM s_inner.sale_date)
        ) as product_avg_sales
    ) as avg_monthly_sale_for_product -- 商品ごとの月平均売上
FROM (VALUES
    ('2024-01-10', '商品A', 100, 3),
    ('2024-01-12', '商品B', 150, 2),
    ('2024-01-15', '商品A', 100, 5),
    ('2024-02-05', '商品C', 200, 1),
    ('2024-02-10', '商品B', 150, 4),
    ('2024-02-20', '商品A', 100, 2)
) AS s (sale_date::date, product_name, unit_price, quantity)
GROUP BY
    EXTRACT(YEAR FROM s.sale_date),
    EXTRACT(MONTH FROM s.sale_date),
    s.product_name
ORDER BY
    sale_year, sale_month, s.product_name;

WITH sales AS ( /* ... 前述のsalesデータ ... */ ),
expensive_summary AS MATERIALIZED ( -- このCTEを実体化する
    SELECT
        product_name,
        SUM(unit_price * quantity) AS total_sales,
        AVG(unit_price * quantity) AS avg_sale_value
    FROM sales
    GROUP BY product_name
    -- 仮にこの集計が非常に重い処理だとする
)
SELECT
    s.product_name,
    s.sale_date,
    (s.unit_price * s.quantity) AS sale_amount,
    es.total_sales,
    es.avg_sale_value
FROM
    sales s
JOIN
    expensive_summary es ON s.product_name = es.product_name
WHERE
    (s.unit_price * s.quantity) > es.avg_sale_value; -- 平均売上より大きい売上のみ抽出

WITH employees_hierarchy AS (
    SELECT * FROM (VALUES
        (1, 'CEO', NULL),
        (2, '部長A (CEO直下)', 1),
        (3, '部長B (CEO直下)', 1),
        (4, '課長A1 (部長A配下)', 2),
        (5, '課長A2 (部長A配下)', 2),
        (6, '課長B1 (部長B配下)', 3),
        (7, '社員A1-1 (課長A1配下)', 4),
        (8, '社員A1-2 (課長A1配下)', 4),
        (9, '社員B1-1 (課長B1配下)', 6)
    ) AS t (employee_id, employee_name, manager_id)
)
-- この後で実際のクエリを記述

WITH RECURSIVE employees_hierarchy AS (
    SELECT * FROM (VALUES
        (1, 'CEO', NULL),
        (2, '部長A (CEO直下)', 1),
        (3, '部長B (CEO直下)', 1),
        (4, '課長A1 (部長A配下)', 2),
        (5, '課長A2 (部長A配下)', 2),
        (6, '課長B1 (部長B配下)', 3),
        (7, '社員A1-1 (課長A1配下)', 4),
        (8, '社員A1-2 (課長A1配下)', 4),
        (9, '社員B1-1 (課長B1配下)', 6)
    ) AS t (employee_id, employee_name, manager_id)
)
, subordinates AS (
    -- 非再帰項 (Anchor Member): 開始点となる従業員 (部長A)
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS level -- 階層レベル
    FROM
        employees_hierarchy
    WHERE
        employee_id = 2 -- 部長AのID

    UNION ALL

    -- 再帰項 (Recursive Member): 直前のステップで見つかった部下の、さらに部下を検索
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        s.level + 1
    FROM
        employees_hierarchy e
    INNER JOIN
        subordinates s ON e.manager_id = s.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    level
FROM
    subordinates
ORDER BY
    level, employee_id;

-- 例えば3階層までなら... (非常に冗長で柔軟性がない)
SELECT e1.employee_name as level0_name, e2.employee_name as level1_name, e3.employee_name as level2_name
FROM employees_hierarchy e1
LEFT JOIN employees_hierarchy e2 ON e1.employee_id = e2.manager_id
LEFT JOIN employees_hierarchy e3 ON e2.employee_id = e3.manager_id
WHERE e1.employee_id = 2;

WITH monthly_sales AS (
    SELECT * FROM (VALUES
        ('2024-01-01'::date, 1000),
        ('2024-02-01'::date, 1200),
        ('2024-03-01'::date, 1100),
        ('2024-04-01'::date, 1500),
        ('2024-05-01'::date, 1300),
        ('2024-06-01'::date, 1600)
    ) AS t (sale_month, amount)
)
-- この後で実際のクエリを記述

WITH monthly_sales AS (
    SELECT * FROM (VALUES
        ('2024-01-01'::date, 1000),
        ('2024-02-01'::date, 1200),
        ('2024-03-01'::date, 1100),
        ('2024-04-01'::date, 1500),
        ('2024-05-01'::date, 1300),
        ('2024-06-01'::date, 1600)
    ) AS t (sale_month, amount)
)
SELECT
    sale_month,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 現在の行と、その前の2行 (合計3行)
    ) AS moving_avg_3months,
    SUM(amount) OVER (
        ORDER BY sale_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 最初から現在の行まで（累積合計）
    ) AS cumulative_sum
FROM
    monthly_sales
ORDER BY
    sale_month;

WITH monthly_sales AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY sale_month) as rn -- 行番号を振っておく
    FROM (VALUES
        ('2024-01-01'::date, 1000),
        ('2024-02-01'::date, 1200),
        ('2024-03-01'::date, 1100),
        ('2024-04-01'::date, 1500),
        ('2024-05-01'::date, 1300),
        ('2024-06-01'::date, 1600)
    ) AS t (sale_month, amount)
)
SELECT
    m1.sale_month,
    m1.amount,
    (
        SELECT AVG(m2.amount)
        FROM monthly_sales m2
        WHERE m2.rn BETWEEN m1.rn - 2 AND m1.rn -- 行番号で範囲指定
          AND m2.rn <= m1.rn -- 先行する行のみ
    ) AS moving_avg_3months_subquery
FROM
    monthly_sales m1
ORDER BY
    m1.sale_month;

WITH monthly_sales AS (
    SELECT * FROM (VALUES
        ('2024-01-01'::date, 1000),
        ('2024-02-01'::date, 1200),
        ('2024-03-01'::date, 1100),
        ('2024-03-01'::date, 900), -- 同じ日付のデータを追加
        ('2024-04-01'::date, 1500)
    ) AS t (sale_month, amount)
)
SELECT
    sale_month,
    amount,
    -- ORDER BY があるので RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW がデフォルト
    SUM(amount) OVER (ORDER BY sale_month) AS cumulative_sum_default_range,
    -- 明示的にROWSで累積合計 (こちらの方が直感的で安全な場合が多い)
    SUM(amount) OVER (ORDER BY sale_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_rows,
    -- ORDER BY があるので RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW がデフォルト
    AVG(amount) OVER (ORDER BY sale_month) AS avg_default_range,
    -- 3ヶ月移動平均 (直近3行。同じ日付は別行としてカウント)
    AVG(amount) OVER (ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3rows
FROM
    monthly_sales
ORDER BY
    sale_month;