为什么 LAG() 比 LEAD() 更适合计算两次随访的间隔
医疗随访数据通常是按时间排序的患者单次记录，比如每次门诊或检查的时间戳。要算“本次随访距上次多久”，本质就是拿当前行和前一行对比——LAG() 直接取上一行值，语义清晰、出错率低；而用 LEAD() 得反向排序再取，多一步还容易漏掉 ORDER BY 方向不一致的问题。
常见错误现象：LEAD(visit_date) OVER (ORDER BY visit_date) 算出来是“下次随访时间”，但若患者最后一次随访后没再就诊，该字段为 NULL，直接减法会得 NULL，导致整个间隔列失效。
- 务必在
OVER子句中明确写ORDER BY patient_id, visit_date：先按人分组，再按时间排，否则跨患者计算间隔毫无意义 LAG(visit_date, 1, '1970-01-01') OVER (...)中的默认值别用NULL，否则DATEDIFF或减法运算全崩- PostgreSQL 和 SQL Server 支持
INTERVAL类型差值，MySQL 8.0+ 需用TIMESTAMPDIFF(DAY, ..., ...)，别硬套visit_date - LAG(...)
用 ROW_NUMBER() 标记首次/末次随访时，如何避免重复就诊干扰
真实数据里常有同一天多次挂号、检验、复诊记录，直接按日期排会出大问题。稳妥的做法是：ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_date, visit_id)。加上一个唯一的 visit_id 作为次级排序键，这样即使同一天有多次记录，序号也不会混乱。
如果只按 visit_date 排，同天多条记录的 ROW_NUMBER() 可能任意分配，导致“首次随访”被误标成当天第二条记录，这在临床分析中是不可接受的。
- 别依赖业务系统自增 ID 作排序依据——有些系统重用 ID 或批量导入时顺序混乱
- 对检验类数据（如 CD4 计数），建议额外加条件过滤：
WHERE test_type = 'CD4'，再套窗口函数，否则把血压、血糖混在一起编号就失去临床意义 ROW_NUMBER()是严格递增，RANK()在并列时会跳号，不适合“第几次随访”这种序数需求
MAX() OVER 和 MIN() OVER 在生存分析中的实际陷阱
想快速拿到每位患者的“最早确诊日期”和“最晚随访日期”，用 MIN(diagnosis_date) OVER (PARTITION BY patient_id) 看似省事，但要注意：如果某患者有多次确诊记录（如初诊、复核、病理回溯），MIN() 拿到的是最早那个，可能早于实际纳入研究的时间点（比如入组标准是“2020 年后确诊”）。
更常见的坑是字段为空：若 diagnosis_date 允许 NULL，MIN() 会忽略它，但 MAX() 同样忽略——结果看似正常，实则丢失了“尚未确诊”的状态信息。
- 必须提前清洗：用
CASE WHEN diagnosis_date IS NOT NULL THEN diagnosis_date END包一层再聚合，或用FILTER (WHERE diagnosis_date IS NOT NULL)（PostgreSQL 9.4+） - 时间范围限定别放在窗口函数外：先
WHERE visit_date >= '2020-01-01'再开窗，比在OVER里加ROWS BETWEEN...更可靠 - SQL Server 不支持
FILTER，得用IIF()或子查询绕过空值干扰
当随访时间轴需要对齐基线（如用药起始日），FIRST_VALUE() 怎么用才不偏移
基线对齐的关键不是找“第一个值”，而是找满足条件的第一个值。比如“从开始抗病毒治疗那天起算随访周期”，不能直接 FIRST_VALUE(treatment_start) OVER (PARTITION BY patient_id ORDER BY visit_date)——这会把所有记录都对齐到最早的 treatment_start，哪怕某次随访发生在用药前。
正确做法是：先用子查询或 CTE 找出每位患者的 treatment_start，再 JOIN 回主表；或者用 MIN(CASE WHEN treatment_status = 'started' THEN visit_date END) OVER (PARTITION BY patient_id) 锁定真正起始日。
FIRST_VALUE()默认是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，如果排序用visit_date，而treatment_start在后面才出现，那前面所有行都会拿到NULL- 别在
FIRST_VALUE()里嵌套复杂逻辑，MySQL 8.0 对窗口函数内子查询支持弱，容易报错This function is not allowed in window functions - 对齐后计算“用药后第 X 天随访”，要用
TIMESTAMPDIFF(DAY, baseline_date, visit_date)，别用visit_date - baseline_date，后者在某些方言里返回秒数而非天数
实际跑通一条随访时间轴分析，难点不在函数本身，而在理解每条记录的临床含义是否匹配窗口的分区与排序逻辑。一个 PARTITION BY 写错，整组患者的间隔、序号、基线就全乱了——比语法错误更难 debug。
