4. SQL复杂查询基础回顾

临床数据查询的复杂度，往往不来自 SQL 语法本身，而来自数据被拆散在多个业务表中，且同一患者在不同时间点会产生多条记录。要回答“某患者最近一次糖化血红蛋白是多少”“哪些受试者在筛选期使用了禁用药物”“每个中心入组人数与筛选失败率”这类问题，单表查询几乎无能为力。本节把四类复杂查询能力放回临床数据语境中重新梳理：多表连接解决“横向拼装”，子查询解决“分步筛选与聚合”，集合运算解决“纵向合并与差异比较”，窗口函数解决“组内排序与跨行计算”。

一、多表连接：把患者、访视、检验、用药拼成一条完整证据链

临床数据库通常遵循 CDISC SDTM 或类似结构：DM 存人口学与入组信息，SV 存访视，LB 存实验室检验，EX 存暴露用药，AE 存不良事件。它们通过 USUBJID（唯一受试者标识）和 VISITNUM（访视序号）关联。连接的核心不是“把表连起来”，而是先明确连接键的粒度：DM 一行一个受试者，LB 一行一个受试者一次访视一个检验项目，EX 一行一个受试者一段用药。粒度不一致时直接连接会产生笛卡尔式膨胀。

例如要列出每位受试者基线访视的糖化血红蛋白（HbA1c）结果，并带上年龄与性别：

SELECT dm.USUBJID, dm.AGE, dm.SEX, lb.LBTESTCD, lb.LBSTRESN, lb.LBDTC
FROM DM dm
LEFT JOIN LB lb
  ON dm.USUBJID = lb.USUBJID
 AND lb.LBTESTCD = 'HBA1C'
 AND lb.VISITNUM = 1
WHERE dm.ARMCD IS NOT NULL;

这里用 LEFT JOIN 而非 INNER JOIN，是因为部分受试者可能缺失基线检验，若用内连接会静默丢人，导致后续统计分母错误。临床查询中“保留主表全部受试者”通常是默认要求。若还要带出该访视的用药情况，应先把 EX 按受试者和访视聚合，再与主查询连接，避免一对多放大。

二、子查询：先算清楚“谁符合条件”，再取明细

子查询适合表达“先筛选、后取数”的两步逻辑。临床场景中常见三类：标量子查询用于取单个值，如每位受试者的基线值；IN/EXISTS 子查询用于筛选集合，如“发生过 3 级以上不良事件的受试者”；派生表（FROM 中的子查询）用于先聚合再连接。

例如找出筛选期使用过禁用药物（CMCAT='禁用药物'）的受试者，并列出其入组信息：

SELECT USUBJID, ARM, RFSTDTC
FROM DM
WHERE USUBJID IN (
  SELECT DISTINCT USUBJID
  FROM CM
  WHERE CMCAT = '禁用药物'
    AND CMSTDTC < RFSTDTC
);

EXISTS 与 IN 在语义上接近，但当子查询表很大且只需判断“是否存在”时，EXISTS 往往更高效，且能自然处理 NULL。需要提醒的是：子查询返回多列或多行时，标量位置会报错；NOT IN 遇到 NULL 会整体失效，临床数据中缺失值普遍，应优先用 NOT EXISTS。

三、集合运算：纵向合并与差异比较

UNION ALL 用于把结构相同的查询结果纵向堆叠，例如把“筛选期不良事件”和“治疗期不良事件”合并成一张事件总表，并保留来源标记。UNION 会去重，代价是排序开销，临床明细查询中通常不需要去重，应优先 UNION ALL。

EXCEPT（或 MINUS）用于差异比较，典型用途是“应做未做”核查：先查出所有计划访视，再减去实际发生的访视，得到缺失访视清单。INTERSECT 则用于找交集，如“既完成基线检验又完成基线心电的受试者”。集合运算要求两侧列数、顺序和数据类型一致，临床中常因日期与字符类型不匹配而报错，需要显式转换。

四、窗口函数：组内排序与跨行计算的主力

窗口函数不折叠行，而是在保留明细的同时增加计算列，非常适合临床数据的“每患者第 N 次”“距基线变化”“组内排名”等需求。常用形式为 函数() OVER (PARTITION BY ... ORDER BY ...)。

例如取每位受试者最近一次 HbA1c：

SELECT USUBJID, LBDTC, LBSTRESN
FROM (
  SELECT USUBJID, LBDTC, LBSTRESN,
         ROW_NUMBER() OVER (
           PARTITION BY USUBJID
           ORDER BY LBDTC DESC
         ) AS rn
  FROM LB
  WHERE LBTESTCD = 'HBA1C'
) t
WHERE rn = 1;

ROW_NUMBER 给组内每行唯一序号，RANK 遇并列会跳号，DENSE_RANK 不跳号。临床中若同一访视有重复检验，需先明确保留规则（如取最新、取均值），否则 rn=1 的结果不稳定。LAG/LEAD 可取上一次/下一次访视值，用于计算相对基线的变化量；SUM() OVER 可做累计入组人数；AVG() OVER (PARTITION BY SITEID) 可算各中心均值而不丢失受试者明细。

窗口函数在 WHERE 中不能直接引用，因为 WHERE 先于窗口计算执行。需要先包一层子查询或 CTE，再在外层过滤 rn=1 或排名条件。

五、四类能力如何协同

真实临床查询往往是组合拳：用 CTE 先算基线值，用窗口函数标记访视序号，用连接拼上人口学与用药，用 EXISTS 排除禁用药物，最后用 UNION ALL 合并不同来源事件。理解每类能力的适用边界，比记住语法更重要：连接管“横向关联”，子查询管“分步条件”，集合运算管“纵向合并与差异”，窗口函数管“组内跨行”。

临床复杂查询四类能力协同关系 以 USUBJID / VISITNUM 为关联主线，从明细到结果逐层收敛 数据源表（SDTM 结构） DM 人口学/入组 SV 访视计划 LB 实验室检验 EX 暴露用药 AE 不良事件 CM 合并用药 ① 多表连接 JOIN ON USUBJID + VISITNUM / LBTESTCD LEFT JOIN 保留主表 先聚合再连接防膨胀 → 横向拼装证据链 ② 子查询 IN / EXISTS 筛选集合 标量子查询取基线值 NOT EXISTS 优于 NOT IN 派生表先聚合后连接 → 分步筛选与聚合 ③ 集合运算 UNION ALL 纵向合并 EXCEPT 找缺失访视 INTERSECT 找交集 列数/类型须一致 → 纵向合并与差异 ④ 窗口函数 ROW_NUMBER 取最近 LAG/LEAD 算变化量 SUM OVER 累计入组 PARTITION BY SITEID → 组内跨行计算 临床查询结果集 受试者明细 + 基线值 + 访视序号 + 事件标记 执行顺序：FROM/JOIN → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY；窗口函数不能直接写在 WHERE 中。

把上述四类能力放进一个可复用的查询骨架中，通常先用 CTE 固定基线人群与基线值，再用窗口函数标记访视顺序，接着用连接补齐人口学与用药，最后用 EXISTS 做安全性排除、用 UNION ALL 合并事件来源。这样写出的 SQL 既保留了受试者粒度，又能逐层解释每个数字的来源，便于与统计师、数据管理员和医学审核人员对齐口径。

← 上一章 📖 返回目录 下一章 →