如何用SQL按SKU检测连续2天平均销售额达标情况？

现有销售表结构 #

CREATE TABLE sales (
  id int NOT NULL PRIMARY KEY,
  sku text NOT NULL,
  date date NOT NULL,
  amount real NOT NULL,
  CONSTRAINT date_sku UNIQUE (sku,date)
);

需求说明 #

针对每个SKU，检测每连续2天的平均销售额是否大于指定阈值（示例阈值为14），筛选出符合条件的记录并输出：

• SKU编号
• 连续日期的起始日与结束日
• 这两天的平均销售额
• 变化率（平均销售额 / 指定阈值）

示例场景 #

SKU B的销售数据：

• 2022-01-01销售额15、2022-01-02销售额20，平均17.5>14，需纳入结果，变化率为17.5/14=1.25
• 2022-01-02销售额20、2022-01-03销售额13，平均16.5>14，需纳入结果，变化率为16.5/14≈1.17
• 2022-01-03销售额13、2022-01-04销售额12，平均12.5<14，不纳入结果

大数据量适配方案 #

使用窗口函数LEAD()关联同SKU的下一条日期数据，避免低效的自关联或CASE WHEN逻辑，适合年量级大数据场景：

WITH consecutive_sales AS (
  SELECT
    sku,
    date AS start_date,
    LEAD(date) OVER (PARTITION BY sku ORDER BY date) AS end_date,
    amount AS current_amount,
    LEAD(amount) OVER (PARTITION BY sku ORDER BY date) AS next_amount
  FROM sales
)
SELECT
  sku,
  start_date,
  end_date,
  ROUND((current_amount + next_amount) / 2, 2) AS amount_sold,
  ROUND(((current_amount + next_amount) / 2) / 14, 2) AS change_rate
FROM consecutive_sales
WHERE
  end_date IS NOT NULL -- 排除无后续日期的记录
  AND (current_amount + next_amount) / 2 > 14 -- 筛选平均销售额大于阈值的记录
ORDER BY sku, start_date;

代码说明 #

1. CTE部分：通过LEAD()窗口函数，按SKU分组、日期排序，获取每个日期对应的下一天日期和销售额，形成连续两天的配对数据。
2. 主查询部分：计算连续两天的平均销售额，计算变化率，过滤掉无后续日期的记录以及平均销售额未达阈值的记录，最后按SKU和起始日期排序输出。

期望输出示例 #

sku     start_date    end_date    amount_sold    change_rate
B       2022-01-01    2022-01-02      17.5            1.25
B       2022-01-02    2022-01-03      16.5            1.17
D       2022-01-01    2022-01-02      28              2

内容的提问来源于stack exchange，提问作者Mj Ebrahimzadeh