ABC分析をSQLで実装する方法｜売上上位80%の商品を抽出するクエリ

-- order_items テーブルのイメージ
-- order_id     : 注文ID
-- product_id   : 商品ID
-- product_name : 商品名
-- quantity     : 数量
-- unit_price   : 単価
-- order_date   : 注文日

-- STEP1: 商品別売上集計
SELECT
    product_id,
    product_name,
    SUM(quantity * unit_price)  AS total_sales
FROM order_items
GROUP BY
    product_id,
    product_name
ORDER BY total_sales DESC;

-- STEP2: 構成比を追加
WITH product_sales AS (
    SELECT
        product_id,
        product_name,
        SUM(quantity * unit_price)  AS total_sales
    FROM order_items
    GROUP BY
        product_id,
        product_name
)
SELECT
    product_id,
    product_name,
    total_sales,
    SUM(total_sales) OVER ()                              AS grand_total,
    ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 2)  AS sales_ratio
FROM product_sales
ORDER BY total_sales DESC;

-- STEP3: 累計構成比を追加
WITH product_sales AS (
    SELECT
        product_id,
        product_name,
        SUM(quantity * unit_price)  AS total_sales
    FROM order_items
    GROUP BY
        product_id,
        product_name
)
SELECT
    product_id,
    product_name,
    total_sales,
    ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 2)                        AS sales_ratio,
    ROUND(SUM(total_sales) OVER (ORDER BY total_sales DESC) * 100.0
          / SUM(total_sales) OVER (), 2)                                             AS cumulative_ratio
FROM product_sales
ORDER BY total_sales DESC;

-- STEP4: ABCランク付け（完成版）
WITH product_sales AS (
    SELECT
        product_id,
        product_name,
        SUM(quantity * unit_price)  AS total_sales
    FROM order_items
    GROUP BY
        product_id,
        product_name
),
product_with_ratio AS (
    SELECT
        product_id,
        product_name,
        total_sales,
        ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 2)  AS sales_ratio,
        ROUND(
            SUM(total_sales) OVER (ORDER BY total_sales DESC) * 100.0
            / SUM(total_sales) OVER ()
        , 2)                                                       AS cumulative_ratio
    FROM product_sales
)
SELECT
    product_id,
    product_name,
    total_sales,
    sales_ratio,
    cumulative_ratio,
    CASE
        WHEN cumulative_ratio <= 80 THEN 'A'
        WHEN cumulative_ratio <= 95 THEN 'B'
        ELSE                             'C'
    END  AS abc_rank
FROM product_with_ratio
ORDER BY total_sales DESC;

-- ランク別サマリ
WITH product_sales AS (
    SELECT
        product_id,
        product_name,
        SUM(quantity * unit_price)  AS total_sales
    FROM order_items
    GROUP BY product_id, product_name
),
product_with_ratio AS (
    SELECT
        product_id,
        product_name,
        total_sales,
        ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 2)  AS sales_ratio,
        ROUND(
            SUM(total_sales) OVER (ORDER BY total_sales DESC) * 100.0
            / SUM(total_sales) OVER ()
        , 2)                                                       AS cumulative_ratio
    FROM product_sales
),
abc_classified AS (
    SELECT
        *,
        CASE
            WHEN cumulative_ratio <= 80 THEN 'A'
            WHEN cumulative_ratio <= 95 THEN 'B'
            ELSE                             'C'
        END  AS abc_rank
    FROM product_with_ratio
)
SELECT
    abc_rank,
    COUNT(*)                    AS sku_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)   AS sku_ratio,
    SUM(total_sales)            AS rank_total_sales,
    ROUND(SUM(sales_ratio), 1)  AS rank_sales_ratio
FROM abc_classified
GROUP BY abc_rank
ORDER BY abc_rank;

SUM(total_sales) OVER (
    PARTITION BY category
    ORDER BY total_sales DESC
)