購買間隔をSQLで分析する方法｜次回購入タイミングを予測するクエリ

-- orders テーブル
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日（DATE型）
-- total_amount : 注文金額
-- status       : 'completed' / 'cancelled'

-- STEP1: 購買間隔の計算

WITH order_history AS (
    SELECT
        customer_id,
        order_date,
        -- 同じ顧客の1つ前の注文日を取得
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS prev_order_date
    FROM orders
    WHERE status = 'completed'
),
purchase_intervals AS (
    SELECT
        customer_id,
        order_date,
        prev_order_date,
        -- 前回購買からの日数（初回購買は NULL）
        DATE_DIFF('day', prev_order_date, order_date)  AS interval_days
    FROM order_history
    WHERE prev_order_date IS NOT NULL  -- 初回購買（比較対象なし）を除外
)
SELECT *
FROM purchase_intervals
ORDER BY customer_id, order_date;

-- STEP2: 顧客ごとの購買間隔統計

WITH order_history AS (
    SELECT
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS prev_order_date
    FROM orders
    WHERE status = 'completed'
),
purchase_intervals AS (
    SELECT
        customer_id,
        order_date,
        DATE_DIFF('day', prev_order_date, order_date)  AS interval_days
    FROM order_history
    WHERE prev_order_date IS NOT NULL
),
interval_stats AS (
    SELECT
        customer_id,
        COUNT(*)                               AS purchase_count,   -- 購買回数（間隔の数）
        ROUND(AVG(interval_days), 1)           AS avg_interval,     -- 平均間隔
        ROUND(STDDEV(interval_days), 1)        AS stddev_interval,  -- 標準偏差
        MIN(interval_days)                     AS min_interval,     -- 最短間隔
        MAX(interval_days)                     AS max_interval,     -- 最長間隔

        -- 中央値（Presto では APPROX_PERCENTILE を使う）
        APPROX_PERCENTILE(interval_days, 0.5)  AS median_interval,

        -- 75パーセンタイル（「少し長め」の間隔）
        APPROX_PERCENTILE(interval_days, 0.75) AS p75_interval,

        -- 90パーセンタイル（「かなり長め」の間隔）
        APPROX_PERCENTILE(interval_days, 0.90) AS p90_interval
    FROM purchase_intervals
    GROUP BY customer_id
    HAVING COUNT(*) >= 2  -- 間隔を計算できる最低回数（2回以上の購買）
)
SELECT *
FROM interval_stats
ORDER BY avg_interval ASC;

予測次回購買日 = 最終購買日 + 平均間隔
異常の閾値    = 最終購買日 + (平均間隔 + 標準偏差 × 2)

-- STEP3: 次回購買予測と異常沈黙の判定（完成版）

WITH order_history AS (
    SELECT
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS prev_order_date
    FROM orders
    WHERE status = 'completed'
),
purchase_intervals AS (
    SELECT
        customer_id,
        order_date,
        DATE_DIFF('day', prev_order_date, order_date)  AS interval_days
    FROM order_history
    WHERE prev_order_date IS NOT NULL
),
last_purchase AS (
    -- 最終購買日と最終購買からの経過日数
    SELECT
        customer_id,
        MAX(order_date)                                     AS last_order_date,
        DATE_DIFF('day', MAX(order_date), CURRENT_DATE)     AS days_since_last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
interval_stats AS (
    SELECT
        customer_id,
        COUNT(*)                                AS purchase_count,
        ROUND(AVG(interval_days), 1)            AS avg_interval,
        ROUND(STDDEV(interval_days), 1)         AS stddev_interval,
        APPROX_PERCENTILE(interval_days, 0.5)   AS median_interval,
        APPROX_PERCENTILE(interval_days, 0.90)  AS p90_interval
    FROM purchase_intervals
    GROUP BY customer_id
    HAVING COUNT(*) >= 2
)
SELECT
    lp.customer_id,
    lp.last_order_date,
    lp.days_since_last_order,
    ist.avg_interval,
    ist.stddev_interval,
    ist.median_interval,

    -- 次回購買予測日（最終購買日 + 平均間隔）
    DATE_ADD('day',
        CAST(ist.avg_interval AS INTEGER),
        lp.last_order_date
    )  AS predicted_next_order_date,

    -- 「そろそろ購買タイミング」フラグ
    -- （現在日が予測日の ±7日以内）
    CASE
        WHEN ABS(
            DATE_DIFF('day',
                DATE_ADD('day', CAST(ist.avg_interval AS INTEGER), lp.last_order_date),
                CURRENT_DATE
            )
        ) <= 7
        THEN 1 ELSE 0
    END  AS in_predicted_window,

    -- 異常な沈黙の閾値（平均 + 標準偏差 × 2）
    CAST(ist.avg_interval + ist.stddev_interval * 2 AS INTEGER)
        AS silence_threshold_days,

    -- 異常フラグ（閾値を超えたら要注意）
    CASE
        WHEN lp.days_since_last_order
             > ist.avg_interval + ist.stddev_interval * 2
        THEN '⚠ 異常な沈黙'
        WHEN lp.days_since_last_order > ist.avg_interval
        THEN '購買タイミング超過'
        ELSE '正常範囲'
    END  AS silence_status

FROM last_purchase    lp
JOIN interval_stats   ist  ON lp.customer_id = ist.customer_id
ORDER BY
    CASE
        WHEN lp.days_since_last_order > ist.avg_interval + ist.stddev_interval * 2
        THEN 1
        WHEN lp.days_since_last_order > ist.avg_interval
        THEN 2
        ELSE 3
    END,
    lp.days_since_last_order DESC;

-- STEP4: 購買間隔のヒストグラム（全顧客）

WITH order_history AS (
    SELECT
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS prev_order_date
    FROM orders
    WHERE status = 'completed'
),
purchase_intervals AS (
    SELECT
        customer_id,
        DATE_DIFF('day', prev_order_date, order_date)  AS interval_days
    FROM order_history
    WHERE prev_order_date IS NOT NULL
),
bucketed AS (
    SELECT
        CASE
            WHEN interval_days <=  7  THEN '01_〜7日'
            WHEN interval_days <= 14  THEN '02_8〜14日'
            WHEN interval_days <= 30  THEN '03_15〜30日'
            WHEN interval_days <= 60  THEN '04_31〜60日'
            WHEN interval_days <= 90  THEN '05_61〜90日'
            WHEN interval_days <= 180 THEN '06_91〜180日'
            WHEN interval_days <= 365 THEN '07_181〜365日'
            ELSE                           '08_365日超'
        END  AS interval_bucket,
        interval_days
    FROM purchase_intervals
)
SELECT
    interval_bucket,
    COUNT(*)                                           AS interval_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct,
    ROUND(AVG(interval_days), 1)                       AS avg_days_in_bucket
FROM bucketed
GROUP BY interval_bucket
ORDER BY interval_bucket;