跳到主要内容

京东大数据面试SQL-GMV按品类和地区双维度统计

一、题目背景​

这道题来自京东商业分析部的数据分析师面试。每周一的经营分析会上，品类运营和区域经理各需要一份报表——品类运营想看"数码在全国各区域分别卖了多少，合计多少"，区域经理想看"华北区各品类分别卖了多少，合计多少"。

如果写两条 SQL 分别出报表，不仅麻烦而且数据可能不一致。有没有一条 SQL 同时满足两个视角？这就是多维聚合（Multi-Dimensional Aggregation）要解决的问题。

多维分析场景

品类运营视角：                    区域经理视角：
数码                             华北
├── 华北 7000                   ├── 数码 7000
├── 华东 3000                   ├── 家电 8000
├── 华南 3500                   ├── 服饰 1000
└── 合计 13500                  └── 合计 16000

一条 SQL 同时出两张表的数据 → GROUPING SETS 或 CUBE。

二、题目​

现有一张订单明细表 t5_jd_order_detail，记录了每笔订单的品类、地区和销售额。请统计：

  1. 每个（品类, 地区）组合的 GMV（明细）
  2. 每个品类的合计（品类小计）
  3. 每个地区的合计（地区小计）
  4. 全部总计

t5_jd_order_detail 表

+-----------+-----------+---------+--------+
| order_id  | category  | region  | sales  |
+-----------+-----------+---------+--------+
| 1         | 数码        | 华北      | 5000   |
| 2         | 数码        | 华东      | 3000   |
| 3         | 数码        | 华北      | 2000   |
| 4         | 家电        | 华北      | 8000   |
| 5         | 家电        | 华东      | 6000   |
| 6         | 家电        | 华南      | 4000   |
| 7         | 服饰        | 华东      | 1500   |
| 8         | 服饰        | 华南      | 2500   |
| 9         | 服饰        | 华北      | 1000   |
| 10        | 数码        | 华南      | 3500   |
+-----------+-----------+---------+--------+
10 rows selected (0.911 seconds)(dwsql.com)

三、思路分析​

本题核心是选择正确的多维聚合语法。先把三个选项理清：

ROLLUP(a, b)：
    层级上卷 — (a,b) → (a) → ()
    ✅ 有品类小计
    ❌ 没有地区小计

CUBE(a, b)：
    全交叉 — (a,b) → (a) → (b) → ()
    ✅ 有品类小计
    ✅ 有地区小计
    ⚠️ 生成所有组合，数据量大时慢

GROUPING SETS： 
    精确指定 — 只生成你要的组合
    ✅ 灵活
    ✅ 性能最好（只算需要的）

本题需要同时有品类小计和地区小计，所以 ROLLUP 不满足，需要 CUBE 或 GROUPING SETS。

维度评分
题目难度⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️

四、逐步推导​

方法1：CUBE — 完整交叉汇总​

SELECT
    COALESCE(category, '全部品类') AS category,
    COALESCE(region, '全部地区') AS region,
    SUM(sales) AS gmv
FROM t5_jd_order_detail
GROUP BY CUBE(category, region)
ORDER BY
    category NULLS LAST,
    region NULLS LAST;

执行结果（完整 16 行）：

+-----------+---------+--------+
| category  | region  |  gmv   |
+-----------+---------+--------+
| 全部品类      | 全部地区    | 36500  |
| 全部品类      | 华东      | 10500  |
| 全部品类      | 华北      | 16000  |
| 全部品类      | 华南      | 10000  |
| 家电        | 全部地区    | 18000  |
| 家电        | 华东      | 6000   |
| 家电        | 华北      | 8000   |
| 家电        | 华南      | 4000   |
| 数码        | 全部地区    | 13500  |
| 数码        | 华东      | 3000   |
| 数码        | 华北      | 7000   |
| 数码        | 华南      | 3500   |
| 服饰        | 全部地区    | 5000   |
| 服饰        | 华东      | 1500   |
| 服饰        | 华北      | 1000   |
| 服饰        | 华南      | 2500   |
+-----------+---------+--------+
16 rows selected (9.624 seconds)(dwsql.com)

方法2：GROUPING SETS — 精确控制（推荐生产使用）​

CUBE 虽然简洁，但生成了所有 2³=8 种维度组合（含重复）。当维度多时，CUBE 的计算量指数增长。GROUPING SETS 只算你指定的组合，性能更好。

SELECT
    COALESCE(category, '全部品类') AS category,
    COALESCE(region, '全部地区') AS region,
    SUM(sales) AS gmv
FROM t5_jd_order_detail
GROUP BY category, region
GROUPING SETS (
    (category, region),   -- 明细
    (category),           -- 品类小计
    (region),             -- 地区小计
    ()                    -- 总计
)
ORDER BY
    category asc NULLS LAST,
    region asc NULLS LAST;

结果与 CUBE 完全一致，但执行计划更高效——只扫描 4 次而非 8 次。

五、常见坑点​

⚠️ NULL vs '全部品类' 聚合行中，不参与分组的列会被置为 NULL。一定要用 COALESCE 替换，否则报表中全是 NULL，完全看不懂。

-- ❌ 不处理 NULL，报表不可读
SELECT category, region, SUM(sales)
FROM t5_jd_order_detail GROUP BY CUBE(category, region);

-- ✅ COALESCE 替换
SELECT COALESCE(category, '全部品类'), COALESCE(region, '全部地区'), SUM(sales)

排序问题​

我们上面执行 添加 ORDER BY category asc NULLS LAST,region asc NULLS LAST语句，是希望能将小计都放到最后，方便查看。但是实际执行效果是，汇总行排在最前面，而不是最后。这里涉及到了SQL语句的执行顺序，FROM → WHERE → GROUP BY (含 CUBE) → HAVING → SELECT → ORDER BY 因为是先 GROUP BY 后 ORDER BY，在执行完成之后，select 的别名已经替换为实际值，所以排序是根据实际值进行的，这应该属于是新的一列了。而不是原来的列。 验证语句如下

 SELECT
      COALESCE(category, '全部品类') AS category1,
      COALESCE(region, '全部地区') AS region1,
      SUM(sales) AS gmv
  FROM t5_jd_order_detail t
  GROUP BY CUBE(category, region)
  ORDER BY
      category,
      region;

该语句会提示，没有category和region这两个列;

想要实现真正的排序，需要增加排序列，这里可以使用grouping函数。

SELECT
      COALESCE(cat, '全部品类') AS category,
      COALESCE(reg, '全部地区') AS region,
      gmv
  FROM (
      SELECT
          category AS cat,
          region AS reg,
          SUM(sales) AS gmv,
          GROUPING(category) AS cat_grp,
          GROUPING(region) AS reg_grp
      FROM t5_jd_order_detail
      GROUP BY CUBE(category, region)
  ) t
  ORDER BY
      cat_grp,
      cat,
      reg_grp,
      reg;

执行结果

+-----------+---------+--------+
| category  | region  |  gmv   |
+-----------+---------+--------+
| 家电        | 华东      | 6000   |
| 家电        | 华北      | 8000   |
| 家电        | 华南      | 4000   |
| 家电        | 全部地区    | 18000  |
| 数码        | 华东      | 3000   |
| 数码        | 华北      | 7000   |
| 数码        | 华南      | 3500   |
| 数码        | 全部地区    | 13500  |
| 服饰        | 华东      | 1500   |
| 服饰        | 华北      | 1000   |
| 服饰        | 华南      | 2500   |
| 服饰        | 全部地区    | 5000   |
| 全部品类      | 华东      | 10500  |
| 全部品类      | 华北      | 16000  |
| 全部品类      | 华南      | 10000  |
| 全部品类      | 全部地区    | 36500  |
+-----------+---------+--------+
16 rows selected (0.441 seconds)(www.dwsql.com)

六、举一反三​

  1. 加时间维度：如果再加上 month 维度，GROUPING SETS((category, region, month), (category, month), (region, month), (month)) 可以看各月趋势和各月小计
  2. GROUPING 函数：如果想区分"这一行是明细还是小计"，用 GROUPING(category) ——返回 1 表示该行是 category 维度的聚合行
  3. Pivot 表形式：如果想展示成交叉表（品类×地区矩阵），可以用 SUM(CASE WHEN region='华北' THEN sales END) 的方式手动做行列转换

七、知识点总结​

💡 选择指南

需求用哪个
只需要层级汇总（品类→总计）ROLLUP
需要全部交叉组合（维度少）CUBE
需要部分组合（维度多/性能敏感）GROUPING SETS
不确定 → 默认选GROUPING SETS
考点说明
ROLLUP / CUBE / GROUPING SETS多维聚合的三种语法，越往后越灵活
COALESCE将聚合行的 NULL 替换为可读标签
NULLS LAST汇总行排在明细行之后
GROUPING SETS 性能只算指定组合，避免 CUBE 的计算爆炸

八、建表语句和数据插入​

点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t5_jd_order_detail (
    order_id INT,
    category STRING,
    region STRING,
    sales BIGINT
);

INSERT INTO t5_jd_order_detail VALUES
(1, '数码', '华北', 5000),
(2, '数码', '华东', 3000),
(3, '数码', '华北', 2000),
(4, '家电', '华北', 8000),
(5, '家电', '华东', 6000),
(6, '家电', '华南', 4000),
(7, '服饰', '华东', 1500),
(8, '服饰', '华南', 2500),
(9, '服饰', '华北', 1000),
(10, '数码', '华南', 3500);
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
04. 商品评论情感分析统计03. 物流时效统计01. 合并数据