跳到主要内容

SQL 潜力爆款筛选：LAG环比增长+滑动窗口连续判断（SHEIN面试题）

一、题目​

SHEIN需要从周销量数据中挖掘"潜力爆款"商品。定义"潜力爆款"为：连续3周销量环比正增长的商品。请编写SQL计算每个SKU每周较上周的销量环比增长率，并筛选出满足连续3周保持正增长条件的商品。

假设有周销量表 t2_weekly_sales：

+---------+---------+-----------+
| sku_id  |  week   | sale_qty  |
+---------+---------+-----------+
| SKU001  | 202523  | 100       |
| SKU001  | 202524  | 120       |
| SKU001  | 202525  | 150       |
| SKU001  | 202526  | 180       |
| SKU002  | 202523  | 80        |
| SKU002  | 202524  | 70        |
| SKU002  | 202525  | 90        |
| SKU002  | 202526  | 95        |
| SKU003  | 202523  | 200       |
| SKU003  | 202524  | 210       |
| SKU003  | 202525  | 215       |
| SKU003  | 202526  | 220       |
+---------+---------+-----------+

二、思路分析​

  1. 使用 LAG 窗口函数按SKU分组、按周排序，获取每个SKU上周的销量（prev_qty）；
  2. 计算环比增长率 = (本周销量 - 上周销量) / 上周销量，注意处理首周NULL和除零情况；
  3. 使用滑动窗口 rows between 2 preceding and current row 统计最近3周中正增长的周数；
  4. 筛选至少出现过一次连续3周正增长（即滑动窗口计数=3）的SKU。
维度评分
题目难度⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

三、逐步推导​

1. 使用LAG获取上周销量并计算环比增长率​

使用lag(sale_qty, 1)按sku_id分组获取上周销量prev_qty，case when处理首周NULL和除零情况后计算增长率。

执行SQL

select sku_id,
       week,
       sale_qty,
       prev_qty,
       case
           when prev_qty is null or prev_qty = 0 then null
           else round((sale_qty - prev_qty) * 1.0 / prev_qty, 4)
       end as growth_rate
from (
    select sku_id,
           week,
           sale_qty,
           lag(sale_qty, 1) over (partition by sku_id order by week) as prev_qty
    from t2_weekly_sales
) t

查询结果

+---------+---------+-----------+-----------+--------------+
| sku_id  |  week   | sale_qty  | prev_qty  | growth_rate  |
+---------+---------+-----------+-----------+--------------+
| SKU001  | 202523  | 100       | NULL      | NULL         |
| SKU001  | 202524  | 120       | 100       | 0.2000       |
| SKU001  | 202525  | 150       | 120       | 0.2500       |
| SKU001  | 202526  | 180       | 150       | 0.2000       |
| SKU002  | 202523  | 80        | NULL      | NULL         |
| SKU002  | 202524  | 70        | 80        | -0.1250      |
| SKU002  | 202525  | 90        | 70        | 0.2857       |
| SKU002  | 202526  | 95        | 90        | 0.0556       |
| SKU003  | 202523  | 200       | NULL      | NULL         |
| SKU003  | 202524  | 210       | 200       | 0.0500       |
| SKU003  | 202525  | 215       | 210       | 0.0238       |
| SKU003  | 202526  | 220       | 215       | 0.0233       |
+---------+---------+-----------+-----------+--------------+
12 rows selected (0.86 seconds)(https://www.dwsql.com)

2. 使用滑动窗口统计连续增长周数，筛选潜力爆款​

使用rows between 2 preceding and current row滑动窗口统计最近3周的正增长周数，having max = 3筛选出连续3周正增长的SKU。

执行SQL

select sku_id,
       max(consecutive_growth_weeks) as max_consecutive_weeks
from (
    select sku_id,
           week,
           growth_rate,
           sum(case when growth_rate > 0 then 1 else 0 end)
             over (partition by sku_id order by week
                   rows between 2 preceding and current row) as consecutive_growth_weeks
    from (
        select sku_id,
               week,
               sale_qty,
               case
                   when prev_qty is null or prev_qty = 0 then null
                   else round((sale_qty - prev_qty) * 1.0 / prev_qty, 4)
               end as growth_rate
        from (
            select sku_id,
                   week,
                   sale_qty,
                   lag(sale_qty, 1) over (partition by sku_id order by week) as prev_qty
            from t2_weekly_sales
        ) t1
    ) t2
) t3
group by sku_id
having max(consecutive_growth_weeks) = 3

查询结果

+---------+------------------------+
| sku_id  | max_consecutive_weeks  |
+---------+------------------------+
| SKU001  | 3                      |
| SKU003  | 3                      |
+---------+------------------------+
2 rows selected (1.277 seconds)(https://www.dwsql.com)

四、常见坑点​

坑1：LAG首周返回NULL导致growth_rate为NULL — lag(sale_qty, 1)在每个SKU的第一周没有"上一周"数据，返回NULL，进而growth_rate也为NULL。在"连续增长"判断中，NULL值不会被case when growth_rate > 0捕获（NULL > 0的结果是NULL而非true/false），因此sum不会计入。换句话说，首个数据周的NULL增长不会干扰连续判断。但需确保代码中使用case when或where显式排除NULL，避免NULL参与比较产生意外结果。

坑2：滑动窗口rows between 2 preceding and current row的范围 — 该窗口定义表示取"当前行+前2行"共最多3行。对于每个SKU的前两周，实际窗口行数不足3行（第1周只有1行，第2周只有2行），此时sum统计的是实际窗口内的正增长周数，必然小于3。只有从第3周开始，窗口才包含完整的3行，才有可能达到3。因此having max(consecutive_growth_weeks) = 3的筛选逻辑是正确的——只要某SKU的任意连续3周窗口都为正，max就会等于3。

坑3：除零错误 — 当上周销量为0时，(sale_qty - 0) / 0会产生除零错误（division by zero）。即使数据库不报错，结果也会是NULL或无穷大，导致增长率失去业务意义。应在计算增长率前用case when判断prev_qty是否为0或NULL，将这两种情况直接设为NULL，避免除零。

五、知识点总结​

考点说明
LAG窗口函数获取分组内前一行的值，用于环比计算、状态变更检测
case when条件判断灵活处理NULL值、除零等边界情况，确保计算安全
滑动窗口 rows between ... preceding and current row限定窗口函数的计算范围为最近N行，用于连续模式检测
sum + case when条件聚合将条件判断嵌入聚合，实现分段统计和模式计数
NULL值在比较运算中的行为NULL参与比较运算结果为NULL（非true/false），需显式处理

六、建表语句和数据插入​

点击展开 DDL & DML
-- 建表语句
create table t2_weekly_sales (
    sku_id   string comment 'sku编码',
    week     string comment '周标识（格式yyyyww）',
    sale_qty int comment '周销量'
) comment 'sku周销量表';

-- 插入数据
insert into t2_weekly_sales(sku_id, week, sale_qty) values
('SKU001', '202523', 100),
('SKU001', '202524', 120),
('SKU001', '202525', 150),
('SKU001', '202526', 180),
('SKU002', '202523', 80),
('SKU002', '202524', 70),
('SKU002', '202525', 90),
('SKU002', '202526', 95),
('SKU003', '202523', 200),
('SKU003', '202524', 210),
('SKU003', '202525', 215),
('SKU003', '202526', 220);
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

专题
窗口函数专题
相关题目
01. 各国家地区GMV排名03. SKU库存周转天数04. 上新商品首周销量