阿里巴巴大数据面试SQL-双11每分钟GMV
一、题目背景
每年双11是阿里巴巴技术系统的终极压力测试。大促作战室的大屏上实时滚动着累计GMV、订单峰值TPS等核心指标，背后的数据链路从用户下单到实时计算再到前端展示，延迟控制在秒级。这道题是阿里数据开发岗面试的高频考题，考察窗口函数累计计算和时间序列处理能力。
业务场景：双11当天0点刚过，作战室大屏上GMV数字以秒为单位跳动。CEO盯着的不仅是总GMV，还有"相比去年同期的增长率"和"当前每分钟的订单TPS"。这道SQL就是大屏背后的核心查询。
二、题目
现有一张天猫双11订单表 t2_tmall_order，记录了每笔订单的支付时间和金额。请统计双11当天（2023-11-11）每分钟的GMV及截止该分钟的累计GMV。
GMV口径：仅统计已支付且未退款的订单（status = 'paid'）
天猫订单表 t2_tmall_order：
+-------------+----------+----------+-----------+---------------------+--------+
| order_id | user_id | item_id | amount | pay_time | status |
+-------------+----------+----------+-----------+---------------------+--------+
| O2023111101 | u001 | I1001 | 299.00 | 2023-11-11 00:01:30 | paid |
| O2023111102 | u002 | I1002 | 599.00 | 2023-11-11 00:01:45 | paid |
| O2023111103 | u001 | I1003 | 199.00 | 2023-11-11 00:02:10 | paid |
| O2023111104 | u003 | I1001 | 299.00 | 2023-11-11 00:02:30 | paid |
| O2023111105 | u004 | I1004 | 1299.00 | 2023-11-11 00:02:55 | refund |
| O2023111106 | u002 | I1005 | 899.00 | 2023-11-11 00:03:10 | paid |
| O2023111107 | u005 | I1002 | 599.00 | 2023-11-11 00:03:25 | paid |
| O2023111108 | u006 | I1006 | 1999.00 | 2023-11-11 00:04:00 | paid |
+-------------+----------+----------+-----------+---------------------+--------+
三、思路分析
核心是时间粒度聚合 + 窗口累计：
- 截取分钟级时间：
substr(pay_time, 1, 16)将秒级时间截断到分钟粒度，形如2023-11-11 00:01 - 按分钟聚合GMV：GROUP BY 分钟粒度，SUM(amount) 得到每分钟的GMV
- 窗口累计：
SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)实现从当天第一分钟累加到当前分钟
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：过滤双11当天已支付订单，截取分钟级时间
select order_id, amount, pay_time,
substr(pay_time, 1, 16) as pay_minute
from t2_tmall_order
where substr(pay_time, 1, 10) = '2023-11-11'
and status = 'paid'
执行结果
+-------------+-----------+---------------------+------------------+
| order_id | amount | pay_time | pay_minute |
+-------------+-----------+---------------------+------------------+
| O2023111101 | 299.00 | 2023-11-11 00:01:30 | 2023-11-11 00:01 |
| O2023111102 | 599.00 | 2023-11-11 00:01:45 | 2023-11-11 00:01 |
| O2023111103 | 199.00 | 2023-11-11 00:02:10 | 2023-11-11 00:02 |
| O2023111104 | 299.00 | 2023-11-11 00:02:30 | 2023-11-11 00:02 |
| O2023111106 | 899.00 | 2023-11-11 00:03:10 | 2023-11-11 00:03 |
| O2023111107 | 599.00 | 2023-11-11 00:03:25 | 2023-11-11 00:03 |
| O2023111108 | 1999.00 | 2023-11-11 00:04:00 | 2023-11-11 00:04 |
+-------------+-----------+---------------------+------------------+
O2023111105（u004购买1299元）已被过滤，因为 status = 'refund'，不计入GMV。
步骤2：按分钟聚合GMV，用窗口函数计算累计值
执行SQL
select pay_minute,
minute_gmv,
sum(minute_gmv) over (order by pay_minute
rows between unbounded preceding and current row) as cumulative_gmv
from (
select substr(pay_time, 1, 16) as pay_minute,
sum(amount) as minute_gmv
from t2_tmall_order
where substr(pay_time, 1, 10) = '2023-11-11'
and status = 'paid'
group by substr(pay_time, 1, 16)
) t
order by pay_minute
执行结果
+------------------+------------+----------------+
| pay_minute | minute_gmv | cumulative_gmv |
+------------------+------------+----------------+
| 2023-11-11 00:01 | 898.00 | 898.00 |
| 2023-11-11 00:02 | 498.00 | 1396.00 |
| 2023-11-11 00:03 | 1498.00 | 2894.00 |
| 2023-11-11 00:04 | 1999.00 | 4893.00 |
+------------------+------------+----------------+
00:01分GMV=898元，到00:04分累计已达4893元。实际双11前几分钟是全天的支付高峰——用户蹲守0点秒杀，系统TPS在第一分钟达到峰值。
五、常见坑点
坑1：没有订单的分钟会缺失行
如果某分钟（如00:05）没有任何已支付订单，GROUP BY 结果不会包含这一行，累计值跳过该分钟直接累到下一分钟。如果需要1440行完整分钟序列（每分钟一行），用递归CTE生成0-1439分钟序列再 LEFT JOIN。
坑2：status 口径不一致导致GMV偏差
GMV通常只统计'paid'。但面试官可能追问"如果考虑退款呢？"——大促结束后退款率通常在5%-15%，实际有效GMV = 总GMV − 退款GMV。如果在GROUP BY时不过滤status，后续分析会更灵活。
坑3：substr 字符串操作 vs date_trunc 的差异
substr(pay_time, 1, 16) 假设 pay_time 是严格格式化的字符串。如果是真正的 TIMESTAMP 类型，date_trunc('minute', pay_time) 更可靠且可以利用分区裁剪。
六、举一反三
- 同比去年双11：复制同一SQL但替换日期为去年，LEFT JOIN 按同分钟对比每分钟GMV的同比增长率
- 分品类独立累计：GROUP BY 加
category_id，SUM() OVER (PARTITION BY category ORDER BY minute)看各品类贡献趋势 - 滑动5分钟平滑曲线：
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW替代 UNBOUNDED 窗口，消除秒杀带来的毛刺 - 实时异常告警：
LAG(minute_gmv) OVER (ORDER BY minute)计算环比，标记GMV暴跌>50%的分钟（可能支付系统故障）触发钉钉告警
七、知识点总结
| 考点 | 说明 |
|---|---|
| substr 时间截断 | 将秒级精度聚合到分钟粒度 |
| SUM OVER 累计窗口 | ROWS UNBOUNDED PRECEDING 实现截止当前的累积求和 |
| GROUP BY + 子查询分层 | 先聚合到分钟→再窗口计算累计值 |
| 数据质量过滤 | WHERE status='paid' 确保GMV统计口径准确 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t2_tmall_order (
order_id string COMMENT '订单ID',
user_id string COMMENT '用户ID',
item_id string COMMENT '商品ID',
amount decimal(10,2) COMMENT '订单金额',
pay_time string COMMENT '支付时间',
status string COMMENT '订单状态: paid-已支付, refund-已退款'
) COMMENT '天猫双11订单表';
INSERT INTO t2_tmall_order VALUES
('O2023111101', 'u001', 'I1001', 299.00, '2023-11-11 00:01:30', 'paid'),
('O2023111102', 'u002', 'I1002', 599.00, '2023-11-11 00:01:45', 'paid'),
('O2023111103', 'u001', 'I1003', 199.00, '2023-11-11 00:02:10', 'paid'),
('O2023111104', 'u003', 'I1001', 299.00, '2023-11-11 00:02:30', 'paid'),
('O2023111105', 'u004', 'I1004', 1299.00, '2023-11-11 00:02:55', 'refund'),
('O2023111106', 'u002', 'I1005', 899.00, '2023-11-11 00:03:10', 'paid'),
('O2023111107', 'u005', 'I1002', 599.00, '2023-11-11 00:03:25', 'paid'),
('O2023111108', 'u006', 'I1006', 1999.00, '2023-11-11 00:04:00', 'paid');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
