ROLLUP(category, month) の場合：
  1. category × month ごとの集計（明細）
  2. category ごとの集計（小計：monthをNULLで集約）
  3. 全体の集計（総計：category・month両方をNULLで集約）

-- order_items（注文明細）
-- order_id    : 注文ID
-- product_id  : 商品ID
-- category    : カテゴリ名
-- quantity    : 数量
-- unit_price  : 単価

-- orders（注文テーブル）
-- order_id    : 注文ID
-- order_date  : 注文日
-- status      : 'completed' / 'cancelled'

-- STEP1: ROLLUP でカテゴリ別小計・総計を一発で出す

SELECT
    oi.category,
    DATE_TRUNC('month', o.order_date)  AS sales_month,
    SUM(oi.quantity * oi.unit_price)   AS total_sales,
    COUNT(DISTINCT o.order_id)         AS order_count
FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
GROUP BY ROLLUP(oi.category, DATE_TRUNC('month', o.order_date))
ORDER BY
    oi.category   NULLS LAST,  -- NULLを最後に（総計行）
    sales_month   NULLS LAST;  -- NULLを最後に（小計行）

-- STEP2: GROUPING() で集計行を識別する

SELECT
    oi.category,
    DATE_TRUNC('month', o.order_date)  AS sales_month,
    SUM(oi.quantity * oi.unit_price)   AS total_sales,

    -- GROUPING()：ROLLUPによるNULLなら1、データのNULLなら0
    GROUPING(oi.category)                                         AS is_category_subtotal,
    GROUPING(DATE_TRUNC('month', o.order_date))                   AS is_month_subtotal,

    -- 行の種類を人間が読める形で表示
    CASE
        WHEN GROUPING(oi.category) = 1
             AND GROUPING(DATE_TRUNC('month', o.order_date)) = 1  THEN '【総計】'
        WHEN GROUPING(DATE_TRUNC('month', o.order_date)) = 1      THEN '【小計】'
        ELSE                                                            '明細'
    END  AS row_type
FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
GROUP BY ROLLUP(oi.category, DATE_TRUNC('month', o.order_date))
ORDER BY
    GROUPING(oi.category) ASC,
    oi.category NULLS LAST,
    GROUPING(DATE_TRUNC('month', o.order_date)) ASC,
    sales_month NULLS LAST;

-- STEP3: NULL を読みやすいラベルに変換した完成版

SELECT
    -- NULLを集計ラベルに変換
    CASE
        WHEN GROUPING(oi.category) = 1  THEN '【全カテゴリ合計】'
        ELSE oi.category
    END  AS category,

    CASE
        WHEN GROUPING(DATE_TRUNC('month', o.order_date)) = 1  THEN '小計/合計'
        ELSE CAST(DATE_TRUNC('month', o.order_date) AS VARCHAR)
    END  AS sales_month,

    SUM(oi.quantity * oi.unit_price)    AS total_sales,
    COUNT(DISTINCT o.order_id)          AS order_count,
    ROUND(AVG(oi.quantity * oi.unit_price), 0)  AS avg_line_amount,

    -- 行の種類フラグ（後続処理・BIツールへの連携用）
    GROUPING(oi.category)                                         AS is_total_row,
    GROUPING(DATE_TRUNC('month', o.order_date))                   AS is_subtotal_row
FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
GROUP BY ROLLUP(oi.category, DATE_TRUNC('month', o.order_date))
ORDER BY
    GROUPING(oi.category) ASC,
    oi.category NULLS LAST,
    GROUPING(DATE_TRUNC('month', o.order_date)) ASC,
    DATE_TRUNC('month', o.order_date) NULLS LAST;

CUBE(category, month) が生成する集計パターン：
  1. category × month（明細）
  2. category のみ（月をまたいだカテゴリ小計）
  3. month のみ（カテゴリをまたいだ月別合計）  ← ROLLUPにはないパターン
  4. 全体総計

-- CUBE: カテゴリ別・月別の両方向の小計を同時に出す

SELECT
    CASE WHEN GROUPING(oi.category) = 1
         THEN '全カテゴリ' ELSE oi.category END  AS category,

    CASE WHEN GROUPING(DATE_TRUNC('month', o.order_date)) = 1
         THEN '全月'
         ELSE CAST(DATE_TRUNC('month', o.order_date) AS VARCHAR)
    END  AS sales_month,

    SUM(oi.quantity * oi.unit_price)  AS total_sales,

    GROUPING(oi.category)                              AS cat_grouped,
    GROUPING(DATE_TRUNC('month', o.order_date))        AS month_grouped
FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
GROUP BY CUBE(oi.category, DATE_TRUNC('month', o.order_date))
ORDER BY cat_grouped ASC, oi.category NULLS LAST, month_grouped ASC, sales_month NULLS LAST;

-- 地域→カテゴリ→月の3階層 ROLLUP

SELECT
    CASE WHEN GROUPING(c.region)    = 1 THEN '全地域'   ELSE c.region        END  AS region,
    CASE WHEN GROUPING(oi.category) = 1 THEN '全カテゴリ' ELSE oi.category     END  AS category,
    CASE WHEN GROUPING(DATE_TRUNC('month', o.order_date)) = 1
         THEN '小計' ELSE CAST(DATE_TRUNC('month', o.order_date) AS VARCHAR)
    END  AS sales_month,
    SUM(oi.quantity * oi.unit_price)  AS total_sales,
    GROUPING(c.region)                                          AS r_grp,
    GROUPING(oi.category)                                       AS c_grp,
    GROUPING(DATE_TRUNC('month', o.order_date))                 AS m_grp
FROM orders        o
JOIN order_items   oi  ON o.order_id   = oi.order_id
JOIN customers     c   ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY ROLLUP(c.region, oi.category, DATE_TRUNC('month', o.order_date))
ORDER BY r_grp, c.region NULLS LAST, c_grp, oi.category NULLS LAST, m_grp, sales_month NULLS LAST;

1. 地域 × カテゴリ × 月（明細）
2. 地域 × カテゴリ（月をまたいだ小計）
3. 地域（カテゴリ・月をまたいだ小計）
4. 全体総計