如何用SQL在报表中标记变动项_使用LAG对比新旧值-SQL-PHP中文网

lag需显式指定partition by和order by，否则跨组混取或顺序错乱；比较时须处理null，推荐case判断或is distinct from；性能优化依赖联合索引与前置过滤。

如何用sql在报表中标记变动项_使用lag对比新旧值

LAG函数怎么写才能正确取到上一行的旧值

直接用 LAG(column_name) 往往拿不到想要的“前一条记录”，因为默认按表物理顺序取，而报表通常需要按时间或版本排序后对比。必须显式指定 ORDER BY，且该排序字段要能唯一确定业务上的“先后”关系（比如 update_time 或 version_id）。

常见错误是漏掉 PARTITION BY：如果报表涉及多条业务线（如多个 product_id），不加分区会导致跨产品混比。例如：

SELECT
  product_id,
  sale_date,
  sales_amount,
  LAG(sales_amount) OVER (
    PARTITION BY product_id 
    ORDER BY sale_date
  ) AS prev_sales_amount
FROM sales_report;

没加 PARTITION BY product_id → A产品的最新值可能被B产品的上一条覆盖
ORDER BY sale_date 有重复？需补二级排序，如 ORDER BY sale_date, id 避免非确定性结果
窗口函数不能在 WHERE 中直接引用别名 prev_sales_amount，要用子查询或 CTE

如何标记“变动项”并避免NULL干扰

LAG返回首行的NULL，直接 sales_amount != prev_sales_amount 会把首行判为“变动”（实际无旧值可比）。必须显式排除NULL或用 IS DISTINCT FROM（PostgreSQL）或构造安全比较逻辑。

MySQL/SQL Server 推荐写法：

SELECT *,
  CASE 
    WHEN prev_sales_amount IS NULL THEN 'first_record'
    WHEN sales_amount != prev_sales_amount THEN 'changed'
    ELSE 'unchanged'
  END AS change_flag
FROM (
  SELECT *,
    LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY update_time) AS prev_sales_amount
  FROM report_data
) t;

别用 != 直接比，NULL != 任何值都为UNKNOWN，导致首行进不了 WHEN 分支
如果字段允许NULL，sales_amount != prev_sales_amount 在两边都是NULL时判为FALSE（不是TRUE），需改用 NOT (sales_amount prev_sales_amount)（MySQL）或 sales_amount IS DISTINCT FROM prev_sales_amount（PG）
变动标记建议用字符串而非布尔，方便后续筛选或导出时肉眼识别

性能卡在哪？大表跑不动怎么办

LAG本身不慢，但全表扫描+排序在百万级以上数据时会明显拖慢报表生成。瓶颈几乎总在 ORDER BY 字段没索引，或 PARTITION BY 字段选择不当。

确保 PARTITION BY + ORDER BY 组合字段上有联合索引，例如 (product_id, update_time)
如果只查最近N天变动，先用 WHERE update_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) 过滤，再开窗口，别让LAG扫全表
Oracle/PG 支持 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 显式限定帧范围，但多数场景默认行为已足够，不必画蛇添足
某些BI工具（如Tableau）会在SQL外再套一层聚合，导致LAG失效——确认最终执行的SQL是否被重写