日环比是按店铺分组后计算当日与前一日销售额的变动比率,必须用PARTITION BY shop_id确保各店独立计算,而非全局排序;需先聚合日销售额再计算,避免明细干扰,并处理时区、退款、跨日等边界问题。

什么是日环比,为什么不能直接用LAG()套两层分组
日环比 = (当日销售额 - 前一日销售额) / 前一日销售额,核心是“按店铺+日期对齐”,不是简单地把所有数据排个序再拉前一行。如果只写 LAG(sales, 1) OVER (ORDER BY date),不同店铺的记录会混在一起——比如店铺B的5月2日可能紧挨着店铺A的5月1日,导致计算完全错乱。
必须让窗口函数在每个店铺内部独立排序和取值,否则结果不可信。
- 必须用
PARTITION BY shop_id隔离店铺维度 -
ORDER BY sale_date要确保日期字段是 DATE 类型(不是 DATETIME,否则同天多条记录排序不稳定) - 若某店铺某日无数据,
LAG()会跳过空缺,直接取更早一天的值——这符合业务逻辑,但需确认是否允许“断日”参与计算
SQL写法:先聚合再计算环比,避免明细行干扰
电商订单表通常含多行明细(一单多商品),直接在订单表上算 LAG() 会导致重复计算。正确做法是先按 shop_id 和 sale_date 汇总日销售额,再在这个中间结果上做环比。
示例(以 PostgreSQL/MySQL 8.0+/SQL Server 2016+ 为例):
WITH daily_sales AS (
SELECT
shop_id,
CAST(order_time AS DATE) AS sale_date,
SUM(amount) AS daily_amount
FROM orders
WHERE order_time >= '2024-05-01'
GROUP BY shop_id, CAST(order_time AS DATE)
),
daily_lag AS (
SELECT
shop_id,
sale_date,
daily_amount,
LAG(daily_amount, 1) OVER (
PARTITION BY shop_id
ORDER BY sale_date
) AS prev_daily_amount
FROM daily_sales
)
SELECT
shop_id,
sale_date,
daily_amount,
ROUND(
(daily_amount - prev_daily_amount) * 100.0 / NULLIF(prev_daily_amount, 0),
2
) AS day_on_day_pct
FROM daily_lag
WHERE prev_daily_amount IS NOT NULL;注意:NULLIF(prev_daily_amount, 0) 防止除零;ROUND(..., 2) 控制小数位,避免浮点误差影响展示。
MySQL 5.7 或旧版数据库怎么处理
这些版本不支持窗口函数,只能用自连接模拟 LAG()。性能差、写法绕,但可行:
- 用子查询或 JOIN 找出每个
(shop_id, sale_date)对应的“前一天”记录 - JOIN 条件必须是
t1.shop_id = t2.shop_id AND t2.sale_date = DATE_SUB(t1.sale_date, INTERVAL 1 DAY) - 不能用
t2.sale_date = t1.sale_date - 1(数值减法在 DATE 上不可靠) - LEFT JOIN 保证当日数据不丢,但需显式过滤
t2.daily_amount IS NOT NULL才算环比
示例片段:
SELECT t1.shop_id, t1.sale_date, t1.daily_amount, ROUND((t1.daily_amount - t2.daily_amount) * 100.0 / NULLIF(t2.daily_amount, 0), 2) AS day_on_day_pct FROM daily_sales t1 LEFT JOIN daily_sales t2 ON t1.shop_id = t2.shop_id AND t2.sale_date = DATE_SUB(t1.sale_date, INTERVAL 1 DAY) WHERE t2.daily_amount IS NOT NULL;
容易被忽略的边界情况
真实电商场景中,这几个点一旦漏掉,报表数字就会“看起来合理,实际全错”:
- 时区:订单时间用的是服务器时区还是用户本地时区?
CAST(order_time AS DATE)依赖当前 session 时区,建议统一转成 UTC 后再截日期 - 退款单:
SUM(amount)是否已剔除已退款订单?最好在 WHERE 中加status NOT IN ('refunded', 'cancelled') - 跨日订单:用户5月1日下单、5月2日支付,该算哪天?要和业务方确认统计口径是“下单日”还是“支付成功日”
- 新店铺首日:没有“前一日”,自然无环比值——这是正常现象,不是 SQL 写错了
最麻烦的其实是数据源本身的时间字段质量。比函数写法更重要的是确认 order_time 是否被清洗过、有没有未来时间、有没有 null 值——这些都会让 PARTITION BY + ORDER BY 的行为变得难以预测。

















