SQL サブクエリの再利用（WITH句）

SQL 目次 - ray88’s diary
■WITH句
WITH句を使用したSQL構文は、一般的に「Common Table Expressions（CTE）」と呼ばれます。
CTEは、SQLのクエリ内で一時的な結果セットを生成し、その結果セットを基にさらにクエリを
行うための非常に強力なツールです。
【構文】

WITH cte_name AS (
    -- サブクエリ
)
-- その後、主クエリでCTEを参照
SELECT ... FROM cte_name ...

【事例1】
あるテーブルから平均値を超えるレコードのみを取得したい場合、以下のように書くことができる

WITH AverageValue AS (
    SELECT AVG(column_name) as avg_value FROM table_name
)
SELECT * FROM table_name WHERE column_name > (SELECT avg_value FROM AverageValue);

【事例２】
　購入明細テーブルより各部署が毎月必ず購入している品目を部署ごとにリストアップする
　※WHERE month_count = 12 の部分の数値は対象期間の月数により調整する
(4か月間の場合→4、1年分の場合→12)

WITH MonthlyCounts AS (
    SELECT 
        department_name, 
        product_name,
        COUNT(DISTINCT EXTRACT(YEAR FROM purchase_date) * 12 + EXTRACT(MONTH FROM purchase_date)) as month_count
    FROM purchase_records
    GROUP BY department_name, product_name
)

SELECT department_name, product_name
FROM MonthlyCounts
WHERE month_count = 4;  -- この数字を変更することで、対象とする月数を変更できます

■↑のロジック解説
STEP１：購入品目を部署ごとにグループ化して重複しない「ｘか月目」でカウントした数値を出して
　　　month_cout列を加えたMontlyCountsテーブルを作成
STEP2：MontlyCountsテーブルより指定した条件の月数分購入されている商品のみを抽出
【WITH句をビューテーブルとして保存する】

CREATE VIEW view_name AS
WITH cte_name AS (
    -- CTEの定義
    SELECT ...
    FROM ...
    WHERE ...
)
-- ビューのメインクエリ
SELECT ...
FROM cte_name
WHERE ...

■テストデータ作成用SQL
事例１のテストデータ

CREATE TABLE table_name (
    idSERIAL PRIMARY KEY,
    column_name INTEGER NOT NULL
);
INSERT INTO table_name (column_name) VALUES (15);
INSERT INTO table_name (column_name) VALUES (25);
INSERT INTO table_name (column_name) VALUES (35);
INSERT INTO table_name (column_name) VALUES (45);
INSERT INTO table_name (column_name) VALUES (50);
INSERT INTO table_name (column_name) VALUES (60);
INSERT INTO table_name (column_name) VALUES (65);
INSERT INTO table_name (column_name) VALUES (75);
INSERT INTO table_name (column_name) VALUES (80);
INSERT INTO table_name (column_name) VALUES (85);

事例２のテストデータ

-- テーブルの作成
CREATE TABLE purchase_records (
    purchase_id SERIAL PRIMARY KEY,
    purchase_date DATE NOT NULL,
    department_name VARCHAR(32) NOT NULL,
    product_name VARCHAR(32) NOT NULL,
    unit_price INTEGER NOT NULL,
    quantity INTEGER NOT NULL
);

-- サンプルデータの挿入
INSERT INTO purchase_records (purchase_date, department_name, product_name, unit_price, quantity) VALUES
('2023-01-01', '部署A', '商品A', 100, 5),
('2023-01-01', '部署A', '商品B', 200, 3),
('2023-01-01', '部署B', '商品A', 100, 2),
('2023-02-01', '部署A', '商品A', 100, 6),
('2023-02-01', '部署B', '商品A', 100, 2),
('2023-02-01', '部署C', '商品B', 200, 4),
('2023-03-01', '部署A', '商品A', 100, 5),
('2023-03-01', '部署B', '商品A', 100, 1),
('2023-03-01', '部署D', '商品B', 200, 3),
('2023-04-01', '部署A', '商品A', 100, 7),
('2023-04-01', '部署B', '商品A', 100, 3),
('2023-04-01', '部署E', '商品C', 300, 2);