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

数据仓库晨曦

发布于 2026-07-07 16:28:09

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

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

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

二、题目

现有一张商家订单表 t8_shop_orders，请找出连续7天以上（含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 差值法：

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

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

四、逐步推导

步骤1：按天聚合——从订单粒度聚合到天粒度

原始数据是订单粒度（同一商家同一天可能有多笔订单，如 S001 在 03-01 有 2 单、03-03 有 2 单）。连续判断只需要知道"这一天是否有订单"，所以先按天聚合。

执行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)(https://www.dwsql.com)

步骤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)(https://www.dwsql.com)

步骤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 (partitionby shop_id orderby order_date)) as grp
        from (select shop_id, order_date from t8_shop_orders groupby shop_id, order_date) t0
    ) t1
    groupby 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)(https://www.dwsql.com)

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

五、常见坑点

坑1：忘记先按天聚合

订单粒度下，同一商家同一天有多笔订单（如 S001 在 03-01 有 2 条、03-03 有 2 条）。如果直接 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：HAVING vs WHERE 的执行时机

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

六、举一反三

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

七、知识点总结

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

八、建表语句和数据插入

CREATE TABLE t8_shop_orders (
    order_id   stringCOMMENT'订单ID',
    shop_id    stringCOMMENT'商家ID',
    category   stringCOMMENT'商品品类',
    order_date stringCOMMENT'下单日期'
);

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

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

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

阿里巴巴