先讲一个有趣的背景。这道题源自阿里巴巴淘宝与天猫商家运营数据分析岗位的真实面试题。淘宝平台拥有上千万活跃商家，平台需要精准识别出“高活跃商家”以提供重点运营扶持。题目特意强调“连续出单天数”而非“累计出单天数”，是因为从业务角度来看，连续出单代表商家每天都在积极维护店铺，这种经营健康度的指标更具参考价值。
一、题目背景
本题来源于阿里巴巴淘宝/天猫商家运营数据分析岗位的面试。淘宝平台上有数千万活跃商家，平台需要筛选出“高活跃商家”并给予重点运营扶持。相比于“累计出单天数”，“连续出单天数”更能真实反映商家的经营健康度——因为连续出单意味着商家每天都在坚持运营店铺。
二、题目
现有商家订单表 t8_shop_orders，请找出连续7天及以上每天都有订单的商家。
商家订单表 t8_shop_orders：
----------- ---------- ----------- -------------
| order_id| shop_id| category| order_date|
----------- ---------- ----------- -------------
| O1001 | S001 | 电器| 2023-03-01|
| O1002 | S001 | 家电| 2023-03-01|
| O1003 | S001 | 服饰| 2023-03-02|
| O1004 | S001 | 电器| 2023-03-03|
| O1005 | S001 | 服饰| 2023-03-03|
| O1006 | S001 | 家电| 2023-03-04|
| O1007 | S001 | 电器| 2023-03-05|
| O1008 | S001 | 服饰| 2023-03-06|
| O1009 | S001 | 家电| 2023-03-07|
| O1010 | S002 | 食品| 2023-03-01|
| O1011 | S002 | 食品| 2023-03-02|
| O1012 | S002 | 食品| 2023-03-03|
| O1013 | S002 | 美妆| 2023-03-05|
| O1014 | S002 | 美妆| 2023-03-05|
----------- ---------- ----------- -------------
三、思路分析
这是“连续日期”问题的经典题型，核心解法是 ROW_NUMBER 差值法：首先按天聚合，确保每天最多只有一条记录；接着为每个商家的日期生成连续行号；然后利用日期减去行号，连续日期产生的差值相同，一旦出现断天则差值发生变化；最后按照商家和差值分组统计，筛选出连续天数大于等于7的组。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：按天聚合——从订单粒度聚合到天粒度
原始数据是订单级别，同一商家同一天可能包含多笔订单。连续判断只需知道“当天是否有订单”，因此需要先按天聚合。
执行SQL
select shop_id, order_date from t8_shop_orders group by shop_id, order_date
执行结果
---------- -------------
| shop_id| order_date|
---------- -------------
| S001 | 2023-03-01|
| S001 | 2023-03-04|
| S001 | 2023-03-02|
| S001 | 2023-03-05|
| S001 | 2023-03-03|
| S001 | 2023-03-07|
| S002 | 2023-03-03|
| S002 | 2023-03-05|
| S002 | 2023-03-02|
| S001 | 2023-03-06|
| S002 | 2023-03-01|
---------- -------------
11 rows selected (7.665 seconds)
步骤2：差值法分组——生成行号并计算分组标识
执行SQL
select shop_id, order_date, date_sub(order_date, row_number() over (partition by shop_id order by order_date)) as grp
from (select shop_id, order_date from t8_shop_orders group by shop_id, order_date) t0
执行结果
---------- ------------- -------------
| shop_id| order_date| grp |
---------- ------------- -------------
| S001 | 2023-03-01| 2023-02-28|
| S001 | 2023-03-02| 2023-02-28|
| S001 | 2023-03-03| 2023-02-28|
| S001 | 2023-03-04| 2023-02-28|
| S001 | 2023-03-05| 2023-02-28|
| S001 | 2023-03-06| 2023-02-28|
| S001 | 2023-03-07| 2023-02-28|
| S002 | 2023-03-01| 2023-02-28|
| S002 | 2023-03-02| 2023-02-28|
| S002 | 2023-03-03| 2023-02-28|
| S002 | 2023-03-05| 2023-03-01|
---------- ------------- -------------
11 rows selected (0.7 seconds)
步骤3：分组统计并筛选连续>=7天
执行SQL
select shop_id, start_date, end_date, consecutive_days
from (
select shop_id, grp,
min(order_date) as start_date,
max(order_date) as end_date,
count(1) as consecutive_days
from (
select shop_id, order_date,
date_sub(order_date, row_number() over (partition by shop_id order by order_date)) as grp
from (select shop_id, order_date from t8_shop_orders group by shop_id, order_date) t0
) t1
group by shop_id, grp
) t2
where consecutive_days >= 7
执行结果
---------- ------------- ------------- -------------------
| shop_id| start_date|end_date | consecutive_days|
---------- ------------- ------------- -------------------
| S001 | 2023-03-01| 2023-03-07| 7 |
---------- ------------- ------------- -------------------
1 row selected (8.44 seconds)
五、常见坑点
坑1：忘记先按天聚合
在订单粒度下，同一商家同一天可能有多笔订单。如果直接使用 ROW_NUMBER()，同一天的订单会被分配不同的行号，导致 date_sub 计算的 grp 值不一致，从而将连续日期错误地拆分开。解决方法很简单：最内层先执行 GROUP BY shop_id, order_date，确保每天最多保留一条记录。
坑2：date_sub 跨月无需特殊处理
date_sub('2023-03-01', 3) 会自动返回 2023-02-26，月份边界由日期函数内部自动处理。但需要注意：如果图省事使用 order_date - rn 进行字符串运算，结果将不可控。因此始终应该使用 date_sub() 函数。
坑3：HA VING vs WHERE 的执行时机
筛选条件 consecutive_days >= 7 必须放在 GROUP BY 之后的 HA VING 或外层的 WHERE 中，不能放在 GROUP BY 之前——因为连续天数是在分组之后才计算出来的，提前过滤将无法获取任何数据。
六、举一反三
这道题的变体非常多：可以设置可变 N 天阈值，将 >= 7 改为参数 >= N，配合不同的 N（如 7/15/30）实现多级商家分层；也可以反过来统计连续未出单天数，用于识别“即将流失”的商家；或者将 PARTITION BY 改为 shop_id, category_id，分析每个商家在每个品类下的连续出单情况；还可以在 HA VING 中增加 AND COUNT(*) >= 7 AND SUM(order_cnt) >= 100，要求不仅连续出单，日均单量也要达标。
七、知识点总结
| 考点 | 说明 |
|---|---|
| GROUP BY 按天聚合 | 订单粒度先按天聚合，保证每天至多一条 |
| ROW_NUMBER + date_sub 差值法 | 连续日期产生相同 grp，断天时 grp 改变 |
| GROUP BY + HA VING | 分组统计连续天数后过滤 >=7 的组 |
| 三层子查询嵌套 | 逐层处理：按天聚合→打标→分组→筛选，逻辑清晰 |
八、建表语句和数据插入
CREATE TABLE t8_shop_orders (
order_id string COMMENT '订单ID',
shop_id string COMMENT '商家ID',
category string COMMENT '商品品类',
order_date string COMMENT '下单日期'
);
INSERT INTO t8_shop_orders VALUES
('O1001','S001','电器','2023-03-01'),
('O1002','S001','家电','2023-03-01'),
('O1003','S001','服饰','2023-03-02'),
('O1004','S001','电器','2023-03-03'),
('O1005','S001','服饰','2023-03-03'),
('O1006','S001','家电','2023-03-04'),
('O1007','S001','电器','2023-03-05'),
('O1008','S001','服饰','2023-03-06'),
('O1009','S001','家电','2023-03-07'),
('O1010','S002','食品','2023-03-01'),
('O1011','S002','食品','2023-03-02'),
('O1012','S002','食品','2023-03-03'),
('O1013','S002','美妆','2023-03-05'),
('O1014','S002','美妆','2023-03-05');
