📚 课程目录第3章多表数据接入与探查

3. 多表数据接入与探查

临床数据关联分析的第一步，不是急着写 JOIN，而是把来自 HIS、LIS、PACS 的多张导出表“摸清楚”。这些表通常由不同厂商、不同时期、不同导出方式产生：HIS 的住院主表可能叫 PAT_VISIT，LIS 的检验结果表可能叫 LAB_RESULT，PACS 的检查报告表可能叫 RAD_REPORT。字段命名风格、编码规则、时间精度、空值表示往往不统一。如果跳过探查直接关联，很容易出现“行数暴涨”“大量 NULL”“时间对不上”等问题，而且很难定位原因。因此本节的核心任务是：先建立每张表的字段画像，再识别可用的主键与外键，最后对数据质量做初步判断，为后续关联分析打下可信基础。

一、字段探查：先看清每张表“长什么样”

字段探查的目标是回答四个问题：表有多少行、每个字段是什么类型、有多少空值、取值分布是否合理。对 HIS、LIS、PACS 导出的 CSV 或数据库表，可以先用 SQL 做结构级探查，再用 Python 做分布级探查。

以一张示例 LIS 检验结果表 lab_result 为例，假设其字段包括：result_id（结果流水号）、visit_id（就诊号）、patient_id（患者号）、item_code（检验项目编码）、item_name（项目名称）、result_value（结果值）、unit（单位）、report_time（报告时间）。在 SQL 中可先执行：

SELECT COUNT(*) AS row_cnt,
       COUNT(DISTINCT visit_id) AS visit_cnt,
       COUNT(DISTINCT patient_id) AS patient_cnt,
       SUM(CASE WHEN result_value IS NULL THEN 1 ELSE 0 END) AS null_value_cnt
FROM lab_result;

这条语句一次性给出总行数、就诊号基数、患者号基数和结果值空值数。若 row_cnt 远大于 visit_cnt，说明一个就诊对应多条检验结果，这是正常的一对多关系；若 patient_cnt 明显小于 visit_cnt，说明同一患者多次就诊，也符合临床实际。但若 null_value_cnt 占比超过 30%，就要警惕导出截断或字段错位。

Python 侧可用 pandas 做更细的分布探查：

import pandas as pd
df = pd.read_csv("lab_result.csv", dtype=str)
print(df.shape)
print(df.isna().mean().sort_values(ascending=False).head(10))
print(df["item_code"].value_counts().head(10))
print(df["report_time"].str.len().value_counts())

这里把 dtype=str 是刻意为之：临床导出数据中，患者号、就诊号常含前导零，检验结果可能混有 “<0.05”“阴性” 等非数值文本。若直接让 pandas 推断类型，前导零会丢失，混合列会变成 object 并掩盖真实问题。先以字符串读入，再逐列判断是否可安全转换，是更稳妥的做法。report_time 的长度分布能快速暴露时间格式是否统一：若同时出现 10 位、19 位、24 位，说明存在 “YYYY-MM-DD”“YYYY-MM-DD HH:MM:SS” 和带毫秒或时区的混合格式，后续关联前必须统一。

二、主外键识别：找到表与表之间的“连接锚点”

多表关联的可靠性，取决于连接键是否唯一、是否稳定、是否语义一致。主键识别的常用判据是：该字段（或字段组合）在表中唯一且非空。外键识别的判据是：该字段在本表中可重复，但其取值集合基本被另一张表的主键集合覆盖。

对 HIS 住院主表 pat_visit，可用 SQL 验证 visit_id 是否为主键：

SELECT COUNT(*) AS total,
       COUNT(DISTINCT visit_id) AS distinct_cnt,
       SUM(CASE WHEN visit_id IS NULL THEN 1 ELSE 0 END) AS null_cnt
FROM pat_visit;

若 total = distinct_cnt 且 null_cnt = 0，则 visit_id 可作为主键。对 LIS 的 lab_result，同样验证 visit_id 是否被 pat_visit 覆盖：

SELECT COUNT(*) AS lab_visit_cnt,
       COUNT(DISTINCT l.visit_id) AS lab_distinct_visit,
       SUM(CASE WHEN p.visit_id IS NULL THEN 1 ELSE 0 END) AS not_matched
FROM lab_result l
LEFT JOIN pat_visit p ON l.visit_id = p.visit_id;

若 not_matched 为 0，说明 LIS 的就诊号都能在 HIS 主表中找到对应记录，lab_result.visit_id 可作为外键。若 not_matched 较大，则要检查是否存在就诊号补零、前缀差异、大小写差异或跨系统编码不一致。PACS 的 rad_report 通常还带有 accession_no（检查号），它可能与 LIS 的 visit_id 并非同一体系，需要单独验证其覆盖关系，不能想当然地直接 JOIN。

三、数据质量初判：把问题挡在关联之前

字段探查和主外键识别完成后，需要对四类高频问题做初判。第一类是主键不唯一：如果 visit_id 在 HIS 主表中出现重复，JOIN 后行数会成倍膨胀。此时应查看重复记录的 admit_time、dept_code 是否不同，判断是“同一就诊多次转科”还是“导出重复”。第二类是外键不覆盖：LIS 或 PACS 中部分 visit_id 在 HIS 中找不到，常见原因是门诊与住院编码体系不同、补零位数不同或测试数据混入。第三类是时间格式混杂：不同系统导出的时间字段精度不同，直接字符串比较会得到错误顺序，应先统一解析为 datetime 再比较。第四类是数值列混文本：检验结果中的 “<0.05”“阴性”“未检出” 不能直接参与均值计算，需要先标记可转换行，再决定是截断、保留还是单独归类。

一个实用的初判顺序是：先看行数与主键唯一性，再看外键覆盖率，然后看时间字段格式一致性，最后看关键数值列的可转换比例。每一步都保留中间计数，例如 total、distinct_cnt、not_matched、parse_fail_cnt。这些计数不仅是质量证据，也是后续关联分析时判断“结果是否可信”的依据。只有把字段、主外键和质量问题都探查清楚，HIS、LIS、PACS 的多表关联才不是“碰运气”，而是有据可依的工程过程。