不能直接用join查变动，因join仅对齐共有主键，漏掉新增/删除行；union all+group by难区分“改”与“删+增”；窗口函数需先union all合并、按order_id和updated_at排序编号，再用lag跨行比对状态以精准识别插入、删除、修改等变动类型。
为什么不能直接用 JOIN 查变动？
因为两表结构相同但记录可能增删改，JOIN 只能对齐共有的主键，漏掉新增和删除的行；而用 UNION ALL + GROUP BY 统计又难区分“改”和“删+增”。窗口函数本身不解决比对逻辑，但它能帮你把“同一主键在两表中的最新快照”拉到同一行，再配合 LAG 或 LEAD 做跨行比较，效率远高于自连接或临时表。
ROW_NUMBER() 按主键+时间戳分区是关键前提
假设你有 orders_old 和 orders_new 两张表，主键是 order_id，变更依据是 updated_at。必须先合并、排序、编号，否则窗口无法定位“上一次”状态：
SELECT order_id, status, updated_at, source, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at, source) AS rn FROM ( SELECT order_id, status, updated_at, 'old' AS source FROM orders_old UNION ALL SELECT order_id, status, updated_at, 'new' AS source FROM orders_new ) t
-
PARTITION BY order_id确保每个订单独立编号，不被其他订单干扰 -
ORDER BY updated_at, source把旧数据排在新数据前（若时间相同），避免误判为“修改” - 如果两表没有
updated_at，得用CURRENT_TIMESTAMP或加伪时间戳字段，否则ROW_NUMBER()结果不可靠
用 LAG() 提取前一行状态做逐行比对
在上一步结果基础上嵌套查询，用 LAG(status) 和 LAG(source) 拿到“上一条同 order_id 的记录”，就能判断变动类型：
SELECT
order_id,
CASE
WHEN prev_source = 'old' AND curr_source = 'new' AND prev_status != curr_status THEN 'modified'
WHEN prev_source = 'old' AND curr_source = 'new' AND prev_status = curr_status THEN 'unchanged'
WHEN prev_source IS NULL AND curr_source = 'new' THEN 'inserted'
WHEN prev_source = 'old' AND curr_source IS NULL THEN 'deleted'
END AS change_type,
prev_status,
curr_status
FROM (
SELECT
order_id,
status AS curr_status,
source AS curr_source,
LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at, source) AS prev_status,
LAG(source) OVER (PARTITION BY order_id ORDER BY updated_at, source) AS prev_source
FROM (/* 上面的 UNION + ROW_NUMBER 子查询 */)
) t
WHERE curr_source = 'new' OR prev_source = 'old'
- 只保留
curr_source = 'new'或prev_source = 'old'的行，过滤掉中间冗余版本 -
LAG()不会自动跨表对齐——它依赖ORDER BY排序结果，所以时间字段必须可信且不为空 - 如果某
order_id在新表里出现多次，LAG()取的是紧邻的上一条，不是“旧表那条”，因此务必确保合并后每组order_id内顺序可预测
性能和兼容性要注意这三点
窗口函数比自连接快，但不是银弹：
- PostgreSQL / SQL Server / Oracle / BigQuery 都支持完整语法；MySQL 8.0+ 才支持
LAG，5.7 及以下只能用变量模拟，稳定性差 - 如果两表合计千万级，
UNION ALL后的排序成本高，建议提前在order_id和updated_at上建联合索引 - 当变动字段多（比如 10 个列都要比对），
CASE会爆炸式增长，此时更适合用EXCEPT/NOT EXISTS分三路查：插入、删除、修改，再用窗口收口
真正卡住的往往不是语法，而是没意识到：窗口函数比对依赖严格有序的时间线，一旦源数据里存在未更新的脏时间、时区混用、或批量导入导致的时间戳全相同，LAG 就会随机抓错参照行。
