阿里巴巴大数据面试SQL-双11每分钟累计GMV和实时TPS峰值统计

数据仓库晨曦

发布于 2026-06-30 12:31:26

890

一、题目背景

每年双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  |
+--------------+----------+----------------------+-------------------+
7 rows selected (0.295 seconds)(https://www.dwsql.com)

O2023111105（u004购买1299元）已被过滤，因为 status = 'refund'，不计入GMV。

步骤2：按分钟聚合GMV，用窗口函数计算累计值

执行SQL

select pay_minute,
       minute_gmv,
       sum(minute_gmv) over (orderby pay_minute
           rowsbetweenunboundedprecedingandcurrentrow) as cumulative_gmv
from (
    selectsubstr(pay_time, 1, 16) as pay_minute,
           sum(amount) as minute_gmv
    from t2_tmall_order
    wheresubstr(pay_time, 1, 10) = '2023-11-11'
      andstatus = 'paid'
    groupbysubstr(pay_time, 1, 16)
) t
orderby 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         |
+-------------------+-------------+-----------------+
4 rows selected (1.253 seconds)(https://www.dwsql.com)

00:01分GMV=898元，到00:04分累计已达4893元。实际双11前几分钟是全天的支付高峰——用户蹲守0点秒杀，系统TPS在第一分钟达到峰值。

五、常见坑点

坑1：没有订单的分钟会缺失行

如果某分钟（如00:05）没有任何已支付订单，GROUP BY 结果不会包含这一行，累计值跳过该分钟直接累到下一分钟。如果需要1440行完整分钟序列（每分钟一行），用递归CTE生成0-1439分钟序列再 LEFT JOIN。但是这种方式不是最优解，带来额外的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统计口径准确

八、建表语句和数据插入

CREATE TABLE t2_tmall_order (
    order_id  stringCOMMENT'订单ID',
    user_id   stringCOMMENT'用户ID',
    item_id   stringCOMMENT'商品ID',
    amount    decimal(10,2) COMMENT'订单金额',
    pay_time  stringCOMMENT'支付时间',
    status    stringCOMMENT'订单状态: paid-已支付, refund-已退款'
) COMMENT'天猫双11订单表';

INSERTINTO 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');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-06-28，如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自数据仓库技术微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度