lag()比join更轻量直观，但必须用partition by分组；join快照表需对齐时间粒度与业务键并建索引；处理null需显式判断，避免隐式错误。
用 LAG() 直接获取上一分组的历史值
窗口函数比 JOIN 更轻量、更直观，尤其当你要对比“当前行”和“同维度下前一次的记录”时。LAG() 是首选，它不依赖额外表连接，也不要求历史数据已预存为独立表。
常见错误是写成 LAG(value) OVER (ORDER BY time) 却忽略分组——结果会把不同用户/设备的数据混在一起拉偏。必须加 PARTITION BY：
SELECT user_id, dt, revenue, LAG(revenue) OVER (PARTITION BY user_id ORDER BY dt) AS prev_revenue FROM sales;
注意：dt 必须能唯一排序（如用 DATE 可能重复，建议升到 TIMESTAMP 或加 id 辅助）；LAG(..., 2) 可取前两期，但跳期易出空值，慎用。
JOIN 历史快照表时必须对齐时间粒度与业务键
当历史数据存在独立快照表（如每日全量用户状态表 user_daily_snapshot），JOIN 是合理选择，但极易因时间或主键不一致导致错配。
典型问题包括：
- 用
LEFT JOIN ... ON t1.user_id = t2.user_id AND t1.dt = t2.dt - INTERVAL '1 day'，但快照表缺失某天数据 →t2字段全为NULL - 快照表按
user_id + dt主键，但源表有重复user_id记录未去重 → JOIN 后行数爆炸 -
dt类型不一致：源表是DATE，快照表是TIMESTAMP→ 隐式转换失败或索引失效
实操建议：
- 先用
SELECT DISTINCT dt FROM user_daily_snapshot ORDER BY dt LIMIT 5确认快照覆盖范围 - JOIN 条件显式转类型：
ON t1.user_id = t2.user_id AND t1.dt = DATE(t2.dt) - 加
AND t2.dt = (SELECT MAX(dt) FROM user_daily_snapshot s2 WHERE s2.user_id = t1.user_id AND s2.dt 可兜底找最近可用快照（性能差，仅限小表）
对比逻辑里 NULL 处理不当会污染结果
无论是 LAG() 还是 LEFT JOIN，首行/缺历史数据时字段都是 NULL。直接写 revenue > prev_revenue 会导致整行被过滤（因为 NULL > X 结果为 UNKNOWN）。
正确做法是显式判断：
- 用
COALESCE(prev_revenue, 0)补零（仅适用于可默认为 0 的场景，如收入） - 更安全的是拆条件：
(prev_revenue IS NOT NULL) AND (revenue > prev_revenue) - 若需标记“新增”“回流”等语义，单独建列：
CASE WHEN prev_revenue IS NULL THEN 'new' WHEN revenue > 0 AND prev_revenue = 0 THEN 'recovered' END
别依赖数据库默认行为——PostgreSQL 和 MySQL 对 NULL 比较的处理表面一致，但在 WHERE / HAVING / ORDER BY 中细节差异大。
性能关键：窗口函数要避免无谓排序，JOIN 要确保关联字段有索引
LAG() 内部会强制排序，如果 PARTITION BY + ORDER BY 组合没有对应索引，大表可能触发磁盘排序，慢得明显。JOIN 同理——没索引的 ON 字段会让执行计划变成 Nested Loop。
检查手段简单直接：
- 在查询前加
EXPLAIN，看是否出现Sort节点或Seq Scanon join table - 对高频分析维度建组合索引：
CREATE INDEX idx_sales_user_dt ON sales(user_id, dt); - 快照表务必在
(user_id, dt)上建唯一索引，否则 JOIN 结果不可靠
窗口函数不是银弹：当需要跨多维对比（比如“当前城市销量 vs 同省均值 vs 全国均值”），LAG() 不够用，得用多个 AVG() OVER (PARTITION BY ...)，此时要注意内存占用和计算顺序。
