如何用SQL窗口函数分析医疗数据随访时间轴

在分析医疗随访数据时，我们经常需要计算“这次就诊离上次过去多久”。这里有一个非常实际的问题：到底是该用 `LAG()` 还是 `LEAD()`？很多人可能会觉得它们互为镜像，用哪个都差不多。但实战经验告诉我们，`LAG()` 才是更稳妥的选择。 原因很简单：每次随访记录都是按时间顺序一行一行排的。要算当前行和上一行的差值，`LAG()` 直接往前看，语义直观，逻辑清晰。如果用 `LEAD()`，你得先把数据倒排一遍，不仅多了一道工序，还很容易在 `ORDER BY` 方向这件事上栽跟头。 举个典型的翻车案例：直接写 `LEAD(visit_date) OVER (ORDER BY visit_date)` 看起来是拿到了下次随访时间，但问题出在最后一次就诊的患者——他后面没有记录了，算出来是个 `NULL`。这时候一减，整列间隔全崩。

为什么 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。