前年同月比・前月比をSQLで計算する方法｜売上推移を同時比較するクエリ

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

-- STEP1: 月次売上の集計

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date)      AS sales_month,
        COUNT(DISTINCT order_id)             AS order_count,
        COUNT(DISTINCT customer_id)          AS customer_count,
        SUM(total_amount)                    AS total_sales,
        ROUND(AVG(total_amount), 0)          AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT *
FROM monthly_sales
ORDER BY sales_month;

-- STEP2: LAGで前月・前年同月の値を同じ行に並べる

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date)      AS sales_month,
        COUNT(DISTINCT order_id)             AS order_count,
        COUNT(DISTINCT customer_id)          AS customer_count,
        SUM(total_amount)                    AS total_sales,
        ROUND(AVG(total_amount), 0)          AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
),
with_lag AS (
    SELECT
        sales_month,
        order_count,
        customer_count,
        total_sales,
        avg_order_value,

        -- 前月の値
        LAG(total_sales,    1) OVER (ORDER BY sales_month)  AS prev_month_sales,
        LAG(order_count,    1) OVER (ORDER BY sales_month)  AS prev_month_orders,
        LAG(customer_count, 1) OVER (ORDER BY sales_month)  AS prev_month_customers,

        -- 前年同月の値（12行前）
        LAG(total_sales,    12) OVER (ORDER BY sales_month) AS prev_year_sales,
        LAG(order_count,    12) OVER (ORDER BY sales_month) AS prev_year_orders,
        LAG(customer_count, 12) OVER (ORDER BY sales_month) AS prev_year_customers
    FROM monthly_sales
)
SELECT *
FROM with_lag
ORDER BY sales_month;

-- STEP3: 前月比・前年同月比まで含めた完成版クエリ

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date)      AS sales_month,
        COUNT(DISTINCT order_id)             AS order_count,
        COUNT(DISTINCT customer_id)          AS customer_count,
        SUM(total_amount)                    AS total_sales,
        ROUND(AVG(total_amount), 0)          AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
),
with_lag AS (
    SELECT
        sales_month,
        order_count,
        customer_count,
        total_sales,
        avg_order_value,
        LAG(total_sales,    1)  OVER (ORDER BY sales_month)  AS prev_month_sales,
        LAG(order_count,    1)  OVER (ORDER BY sales_month)  AS prev_month_orders,
        LAG(customer_count, 1)  OVER (ORDER BY sales_month)  AS prev_month_customers,
        LAG(total_sales,    12) OVER (ORDER BY sales_month)  AS prev_year_sales,
        LAG(order_count,    12) OVER (ORDER BY sales_month)  AS prev_year_orders,
        LAG(customer_count, 12) OVER (ORDER BY sales_month)  AS prev_year_customers
    FROM monthly_sales
)
SELECT
    sales_month,

    -- 今月の実績
    total_sales,
    order_count,
    customer_count,
    avg_order_value,

    -- 前月比（売上）
    prev_month_sales,
    ROUND(
        (total_sales - prev_month_sales) * 100.0
        / NULLIF(prev_month_sales, 0)
    , 1)  AS mom_sales_growth_pct,        -- Month over Month

    -- 前年同月比（売上）
    prev_year_sales,
    ROUND(
        (total_sales - prev_year_sales) * 100.0
        / NULLIF(prev_year_sales, 0)
    , 1)  AS yoy_sales_growth_pct,        -- Year over Year

    -- 前月比（注文件数）
    ROUND(
        (order_count - prev_month_orders) * 100.0
        / NULLIF(prev_month_orders, 0)
    , 1)  AS mom_order_growth_pct,

    -- 前年同月比（注文件数）
    ROUND(
        (order_count - prev_year_orders) * 100.0
        / NULLIF(prev_year_orders, 0)
    , 1)  AS yoy_order_growth_pct,

    -- 前月比（購買顧客数）
    ROUND(
        (customer_count - prev_month_customers) * 100.0
        / NULLIF(prev_month_customers, 0)
    , 1)  AS mom_customer_growth_pct,

    -- 前年同月比（購買顧客数）
    ROUND(
        (customer_count - prev_year_customers) * 100.0
        / NULLIF(prev_year_customers, 0)
    , 1)  AS yoy_customer_growth_pct

FROM with_lag
WHERE sales_month >= DATE '2024-01-01'  -- 表示したい期間を絞る
ORDER BY sales_month;

-- カテゴリ別 前年同月比

WITH monthly_category_sales AS (
    SELECT
        DATE_TRUNC('month', o.order_date)    AS sales_month,
        oi.category,
        SUM(oi.quantity * oi.unit_price)     AS total_sales
    FROM orders       o
    JOIN order_items  oi  ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY
        DATE_TRUNC('month', o.order_date),
        oi.category
),
with_yoy AS (
    SELECT
        sales_month,
        category,
        total_sales,
        -- PARTITION BY category で、カテゴリ内の12行前を取得
        LAG(total_sales, 12) OVER (
            PARTITION BY category
            ORDER BY sales_month
        )  AS prev_year_sales
    FROM monthly_category_sales
)
SELECT
    sales_month,
    category,
    total_sales,
    prev_year_sales,
    ROUND(
        (total_sales - prev_year_sales) * 100.0
        / NULLIF(prev_year_sales, 0)
    , 1)  AS yoy_growth_pct
FROM with_yoy
WHERE
    sales_month >= DATE '2024-01-01'
    AND prev_year_sales IS NOT NULL
ORDER BY sales_month, yoy_growth_pct DESC;

-- カレンダーCTEで欠損月を補完するイメージ
WITH calendar AS (
    SELECT DATE_TRUNC('month', dt) AS sales_month
    FROM UNNEST(SEQUENCE(DATE '2023-01-01', DATE '2024-12-01', INTERVAL '1' MONTH)) AS t(dt)
)
SELECT
    c.sales_month,
    COALESCE(m.total_sales, 0)  AS total_sales
FROM calendar          c
LEFT JOIN monthly_sales  m  ON c.sales_month = m.sales_month