バスケット分析をSQLで行う方法｜一緒に買われる商品を見つけるクエリ

Support(A→B) = AとBが同じ注文に入っている注文数 ÷ 全注文数

Confidence(A→B) = AとBが同時に出る注文数 ÷ Aが出る注文数

Lift(A→B) = Confidence(A→B) ÷ Support(B)

-- order_items テーブル
-- order_id     : 注文ID
-- product_id   : 商品ID
-- product_name : 商品名
-- quantity     : 注文数量
-- unit_price   : 単価

-- STEP1: 同一注文内の商品ペアを生成

WITH item_pairs AS (
    SELECT
        a.order_id,
        a.product_id   AS product_id_a,
        a.product_name AS product_name_a,
        b.product_id   AS product_id_b,
        b.product_name AS product_name_b
    FROM order_items  a
    JOIN order_items  b
        ON  a.order_id    = b.order_id       -- 同じ注文
        AND a.product_id  < b.product_id     -- A < B にすることで重複・同一商品ペアを排除
)
SELECT *
FROM item_pairs
ORDER BY order_id, product_id_a, product_id_b;

-- STEP2: ペアの共起回数と支持度の計算

WITH item_pairs AS (
    SELECT
        a.order_id,
        a.product_id   AS product_id_a,
        a.product_name AS product_name_a,
        b.product_id   AS product_id_b,
        b.product_name AS product_name_b
    FROM order_items  a
    JOIN order_items  b
        ON  a.order_id   = b.order_id
        AND a.product_id < b.product_id
),
total_orders AS (
    -- 全注文数（支持度の分母）
    SELECT COUNT(DISTINCT order_id)  AS total_order_count
    FROM order_items
),
pair_stats AS (
    SELECT
        product_id_a,
        product_name_a,
        product_id_b,
        product_name_b,
        COUNT(DISTINCT order_id)  AS co_occurrence_count
    FROM item_pairs
    GROUP BY
        product_id_a,
        product_name_a,
        product_id_b,
        product_name_b
)
SELECT
    ps.product_id_a,
    ps.product_name_a,
    ps.product_id_b,
    ps.product_name_b,
    ps.co_occurrence_count,
    -- 支持度（%表示）
    ROUND(ps.co_occurrence_count * 100.0 / t.total_order_count, 2)  AS support_pct
FROM pair_stats    ps
CROSS JOIN total_orders  t
ORDER BY co_occurrence_count DESC;

-- STEP3: 信頼度・リフト値まで含めた完成クエリ

WITH item_pairs AS (
    SELECT
        a.order_id,
        a.product_id   AS product_id_a,
        a.product_name AS product_name_a,
        b.product_id   AS product_id_b,
        b.product_name AS product_name_b
    FROM order_items  a
    JOIN order_items  b
        ON  a.order_id   = b.order_id
        AND a.product_id < b.product_id
),
total_orders AS (
    SELECT COUNT(DISTINCT order_id)  AS total_order_count
    FROM order_items
),
-- 各商品が単独で登場する注文数（信頼度・リフトの計算に使う）
product_order_count AS (
    SELECT
        product_id,
        COUNT(DISTINCT order_id)  AS order_count
    FROM order_items
    GROUP BY product_id
),
pair_stats AS (
    SELECT
        product_id_a,
        product_name_a,
        product_id_b,
        product_name_b,
        COUNT(DISTINCT order_id)  AS co_occurrence_count
    FROM item_pairs
    GROUP BY
        product_id_a, product_name_a,
        product_id_b, product_name_b
)
SELECT
    ps.product_name_a,
    ps.product_name_b,
    ps.co_occurrence_count,

    -- 支持度
    ROUND(ps.co_occurrence_count * 100.0 / t.total_order_count, 2)
        AS support_pct,

    -- 信頼度(A→B): AとBが同時に出る注文数 ÷ Aが出る注文数
    ROUND(ps.co_occurrence_count * 100.0 / pa.order_count, 1)
        AS confidence_a_to_b_pct,

    -- 信頼度(B→A): AとBが同時に出る注文数 ÷ Bが出る注文数
    ROUND(ps.co_occurrence_count * 100.0 / pb.order_count, 1)
        AS confidence_b_to_a_pct,

    -- リフト値: 信頼度(A→B) ÷ BのSupport
    -- 1より大きければ「AがあることでBが通常より多く売れる」
    ROUND(
        (ps.co_occurrence_count * 1.0 / pa.order_count)
        / (pb.order_count * 1.0 / t.total_order_count)
    , 2)  AS lift

FROM pair_stats           ps
CROSS JOIN total_orders   t
JOIN product_order_count  pa  ON ps.product_id_a = pa.product_id
JOIN product_order_count  pb  ON ps.product_id_b = pb.product_id

-- 最低限の出現回数フィルタ（ノイズを除去）
WHERE ps.co_occurrence_count >= 10
ORDER BY lift DESC, co_occurrence_count DESC;

-- カテゴリ限定の例
FROM order_items  a
JOIN order_items  b
    ON  a.order_id    = b.order_id
    AND a.product_id  < b.product_id
    AND a.category    = 'haircare'  -- カテゴリを絞る
    AND b.category    = 'haircare'