MySQL使用LAG函数计算昨日销售额差值返回NULL问题排查

在MySQL中使用LAG()函数计算当日与昨日销售额差值时返回NULL，目标是搭建自动报表机制，当特定产品销售额低于昨日销售额的20%时自动推送报表，原编写的查询存在多处语法和逻辑错误。

附数据库样例：

• 语法位置错误：窗口函数LAG()属于SELECT阶段的计算逻辑，必须写在SELECT子句内，当前写法将其放在WHERE子句之后，不符合SQL执行顺序，会触发解析异常。
• 日期函数不兼容：MySQL中没有Oracle风格的to_char()函数，日期格式化需要使用内置函数DATE_FORMAT()。
• 分区逻辑错误：PARTITION BY的作用是划定窗口函数的计算范围，当前写法按日期、商品、品牌分区，每个分区内仅包含单日单商品的单条记录，LAG()无法跨分区取到上一日的数据，必然返回NULL。
• 排序逻辑错误：窗口内ORDER BY sales是按销售额排序，无法保证取到的上一条记录是前一天的数据，需要按日期字段排序才能正确匹配时间序列上的上一日记录。
• 别名风险：将日期字段别名设为"date"，date是MySQL保留关键字，容易触发字段解析异常。

SELECT
    DATE_FORMAT(purchase_date, '%Y-%m-%d') AS stat_date,
    item,
    brand,
    sales,
    -- 同商品同品牌分区，按日期升序取上一条的销售额
    LAG(sales, 1) OVER (PARTITION BY item, brand ORDER BY purchase_date) AS last_day_sales,
    -- 计算当日与昨日销售额差值
    sales - LAG(sales, 1) OVER (PARTITION BY item, brand ORDER BY purchase_date) AS sales_diff
FROM consolidated_purchases
-- 测试时可放开日期限制，生产自动报表可按需设置时间范围
-- WHERE purchase_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

注意：如果consolidated_purchases是订单明细表，不是单日单商品单品牌的汇总粒度，需要先按purchase_date、item、brand聚合求和sales，再套用上述窗口函数逻辑，否则会出现取值错误。

要筛选出销售额不足昨日20%的异常商品，直接嵌套一层查询加过滤条件即可，注意排除没有昨日数据的首天记录：

SELECT
    stat_date,
    item,
    brand,
    sales AS current_day_sales,
    last_day_sales,
    CONCAT(ROUND(sales / last_day_sales * 100, 2), '%') AS compare_last_day_ratio
FROM (
    SELECT
        DATE_FORMAT(purchase_date, '%Y-%m-%d') AS stat_date,
        item,
        brand,
        sales,
        LAG(sales, 1) OVER (PARTITION BY item, brand ORDER BY purchase_date) AS last_day_sales
    FROM consolidated_purchases
    -- 定时任务每日调度时，只取最近2天数据计算即可，减少全表扫描
    WHERE purchase_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
) t
WHERE
    last_day_sales IS NOT NULL
    AND sales < last_day_sales * 0.2

将上述查询配置到定时调度任务中，每日营业数据入库后执行，只要查询结果不为空，就将结果作为报表内容推送给对应负责人即可完成自动告警机制。

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