阿里巴巴大数据面试SQL-订单金额分段统计

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴淘宝事业部的数据分析岗面试。电商平台的订单金额分布直接关系到运费险定价、包邮门槛设定和会员等级权益设计。运营团队需要持续监控小额、中额、大额订单的占比变化，以判断客单价走势——如果小额订单占比突然上升，可能是新用户补贴过度导致羊毛党涌入；如果大额订单占比下降，则需要排查高价值用户的流失原因。

业务场景：双11大促后，数据分析师需要按金额段统计订单分布，与去年同期对比，产出《大促客单价结构分析报告》。这道题的 SQL 逻辑就是该分析报表的基础查询。

二、题目

现有一张订单表 t5_orders，请按金额区间统计订单数及占比。

区间	标签
0-100	小额
100-500	中额
500+	大额

订单表 t5_orders：

+----------+----------+---------+
| order_id | user_id  | amount  |
+----------+----------+---------+
| O001     | u01      | 50.00   |
| O002     | u02      | 200.00  |
| O003     | u01      | 800.00  |
| O004     | u03      | 80.00   |
| O005     | u02      | 350.00  |
| O006     | u04      | 600.00  |
+----------+----------+---------+

三、思路分析

这是条件分组统计的基础题型，核心思路是 CASE WHEN 分段 + GROUP BY 聚合 + SUM OVER 求占比：

1. 分段：用 CASE WHEN 将 amount 映射为区间标签
2. 计数：按区间标签 GROUP BY，COUNT(*) 得到各段订单数
3. 求占比：用 SUM(COUNT(*)) OVER () 窗口函数在聚合后求总量，再计算各段占比

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

四、逐步推导

步骤1：用 CASE WHEN 将订单金额分段

先将每条订单映射到对应的金额区间标签。

执行SQL

select order_id, amount,
       case when amount < 100 then '0-100（小额）'
            when amount between 100 and 500 then '100-500（中额）'
            else '500+（大额）' end as amount_range
from t5_orders

执行结果

+----------+---------+------------------+
| order_id | amount  | amount_range     |
+----------+---------+------------------+
| O001     | 50.00   | 0-100（小额）     |
| O002     | 200.00  | 100-500（中额）   |
| O003     | 800.00  | 500+（大额）      |
| O004     | 80.00   | 0-100（小额）     |
| O005     | 350.00  | 100-500（中额）   |
| O006     | 600.00  | 500+（大额）      |
+----------+---------+------------------+

步骤2：按区间分组统计并计算占比

分组计数的同时，用 SUM(COUNT(*)) OVER () 在窗口函数中求出总订单数，从而计算每段的占比。

执行SQL

select amount_range, count(1) as order_cnt,
       round(count(1) / sum(count(1)) over (), 4) as rate
from (
    select case when amount < 100 then '0-100（小额）'
                when amount between 100 and 500 then '100-500（中额）'
                else '500+（大额）' end as amount_range
    from t5_orders
) t
group by amount_range
order by amount_range

执行结果

+------------------+-----------+--------+
| amount_range     | order_cnt |  rate  |
+------------------+-----------+--------+
| 0-100（小额）     | 2         | 0.3333 |
| 100-500（中额）   | 2         | 0.3333 |
| 500+（大额）      | 2         | 0.3333 |
+------------------+-----------+--------+

三个金额段的订单数均为2，各占33.33%。

五、常见坑点

坑1：BETWEEN 的边界问题

amount BETWEEN 100 AND 500 是闭区间，包含 100 和 500。如果同时有 amount < 100 和 amount BETWEEN 100 AND 500，金额恰好为 100 的订单只会进入中额区间（CASE WHEN 从上到下匹配先命中 first when）。但如果有 amount <= 100 和 amount BETWEEN 100 AND 500，则金额=100 会同时匹配两段，需要明确边界归属。

坑2：整数除法导致占比为 0

在有些数据库中 COUNT(1) / SUM(COUNT(1)) 如果两个值都是整数，除法结果是整数（截断为0）。解决方案是 COUNT(1) * 1.0 / SUM(COUNT(1)) 或用 ROUND() 保证浮点运算。

坑3：某些分段可能没有数据

如果某金额段没有任何订单，GROUP BY 后该段不会出现在结果中（而不是显示 order_cnt=0）。业务方可能需要补全所有分段做饼图展示，此时需要用 UNION ALL 构造一个包含全部分段的基准表再 LEFT JOIN 实际统计结果。

六、举一反三

1. 按用户维度分段：不是按订单而是按用户累计消费金额分段，先 GROUP BY user_id 求用户总消费 SUM(amount)，再做分段统计
2. 多维度分段：同时按金额区间 + 商品类目做二维交叉统计，需要两个 CASE WHEN 分别分段，GROUP BY 两个维度
3. 动态分桶（NTILE）：如果不想固定分段阈值，可以用 NTILE(5) OVER (ORDER BY amount) 将订单等分为5组，适用于分位数分析
4. 趋势对比：添加 order_date 字段，按月 + 金额区间分组，做出各月各段占比的趋势变化

七、知识点总结

考点	说明
CASE WHEN 分段	根据条件将连续值映射为离散的区间标签
GROUP BY 聚合	按区间标签统计各段的订单数
SUM() OVER () 窗口函数	在聚合结果上求总量，无需额外子查询
ROUND 精度控制	占比保留4位小数，避免精度丢失

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t5_orders (
    order_id string COMMENT '订单ID',
    user_id  string COMMENT '用户ID',
    amount   decimal(10,2) COMMENT '订单金额'
) COMMENT '订单表';

INSERT INTO t5_orders VALUES
('O001','u01',50.00), ('O002','u02',200.00), ('O003','u01',800.00),
('O004','u03',80.00), ('O005','u02',350.00), ('O006','u04',600.00);

📱关注公众号

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

💬加群交流

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