阿里巴巴大数据面试SQL-连续7天以上有订单的商家

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴淘宝/天猫的商家运营数据分析岗面试。淘宝有上千万活跃商家，平台需要识别出"高活跃商家"做重点运营扶持。连续出单天数比"累计出单天数"更能衡量商家的经营健康度——连续出单意味着商家每天都在积极运营店铺。

业务场景：平台有个"金牌卖家"认证体系，其中一个硬性条件就是"连续30天每天有订单"。这道题的逻辑就是认证系统背后的实际SQL——先将"连续天数>7"作为一个入门级筛选条件。

二、题目

现有一张商家订单表 t8_shop_orders，请找出连续7天以上（含7天）每天都有订单的商家。

商家订单表 t8_shop_orders：

+----------+-------------+
| shop_id  | order_date  |
+----------+-------------+
| S001     | 2023-03-01  |
| S001     | 2023-03-02  |
| S001     | 2023-03-03  |
| S001     | 2023-03-04  |
| S001     | 2023-03-05  |
| S001     | 2023-03-06  |
| S001     | 2023-03-07  |
| S002     | 2023-03-01  |
| S002     | 2023-03-02  |
| S002     | 2023-03-03  |
| S002     | 2023-03-05  |
+----------+-------------+

三、思路分析

这是"连续日期"问题的标准题型，核心是 ROW_NUMBER 差值法：

1. 先去重：同一商家同一天可能有多条订单记录，先 SELECT DISTINCT 保证每天至多一条
2. 生成行号：ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY order_date) 为每个商家的日期排号
3. 差值分组：date_sub(order_date, rn) —— 连续日期的差值相同，一旦断天差值改变
4. 分组统计：按 shop_id + grp 分组，COUNT(*) 得到每段连续天数
5. 筛选条件：HAVING COUNT(*) >= 7 取出连续7天及以上的记录

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

四、逐步推导

步骤1：去重——每天每个商家只保留一条记录

同一商家同一天可能有多笔订单，连续判断只需要知道"这一天是否有订单"，所以先做去重。

select distinct shop_id, order_date from t8_shop_orders

步骤2：差值法分组——生成行号 + 计算分组标识

select shop_id, order_date,
       date_sub(order_date, row_number() over (partition by shop_id order by order_date)) as grp
from (select distinct shop_id, order_date from t8_shop_orders) t0

S001 的 03-0103-07 连续7天，grp 都相同。S002 的 03-0103-03 是一组（grp相同），03-05 是另一组（grp 跳变），说明中间断了一天。

步骤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 distinct shop_id, order_date from t8_shop_orders) 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                |
+----------+-------------+-------------+------------------+

S001 从 03-01 到 03-07 连续7天有订单，符合条件。S002 最长只有连续3天（03-01~03-03），不满足条件。

五、常见坑点

坑1：忘记先去重

同一商家同一天有多笔订单时，ROW_NUMBER() 会为它们分配不同的行号，导致同一天的记录被分到两个不同的 grp。解决方案：最内层先 SELECT DISTINCT shop_id, order_date。

坑2：date_sub 跨月无需特殊处理

date_sub('2023-03-01', 3) 自动返回 2023-02-26，月份边界由日期函数内部处理。但如果用 order_date - rn（字符串运算），结果不确定。始终使用 date_sub() 函数。

坑3：HAVING vs WHERE 的执行时机

筛选 consecutive_days >= 7 必须放在 GROUP BY 之后的 HAVING 或外层 WHERE 中，不能放在 GROUP BY 之前——因为连续天数在分组后才计算出来。

六、举一反三

1. 可变N天阈值：把 >= 7 改为参数 >= N，配合不同的 N（7/15/30）实现多级商家分层
2. 最大连续未出单天数：反过来看——连续没有订单的天数，用于识别"即将流失"的商家
3. 带品类维度的连续：PARTITION BY 换成 shop_id, category_id，统计每个商家在每个品类的连续出单情况
4. 加上"最小订单数"约束：在 HAVING 中加 AND COUNT(*) >= 7 AND SUM(order_cnt) >= 100，不仅连续出单还要日均单量达标

七、知识点总结

考点	说明
SELECT DISTINCT 去重	同天多条记录先去重，保证日期唯一
ROW_NUMBER + date_sub 差值法	连续日期产生相同 grp，断天时 grp 改变
GROUP BY + HAVING	分组统计连续天数后过滤 >=7 的组
三层子查询嵌套	逐层处理：去重→打标→分组→筛选，逻辑清晰

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t8_shop_orders (
    shop_id    string COMMENT '商家ID',
    order_date string COMMENT '下单日期'
);

INSERT INTO t8_shop_orders VALUES
('S001','2023-03-01'),('S001','2023-03-02'),('S001','2023-03-03'),
('S001','2023-03-04'),('S001','2023-03-05'),('S001','2023-03-06'),
('S001','2023-03-07'),('S002','2023-03-01'),('S002','2023-03-02'),
('S002','2023-03-03'),('S002','2023-03-05');

📱关注公众号

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

💬加群交流

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