MIMIC_IV数据库 with 临时表的使用

MIMIC数据库查询中，使用WITH 构建一个查询的上下文环境（context）是SQL查询的一种策略，其实不用with也可以完成， 但是WITH的使用使得代码更简洁，可读性更强。在具体撰写SQL代码过程中， 必然会遇到两个问题，1.什么情况下用WITH；2.使用WITH的两种方式（策略）。MIMIC官方SQL代码中为我们提供了以上两个问题的答案，这里尝试进行总结。

1.什么情况下用WITH？

当SQL查询涉及多个表格， 且其中一个（或多个）表格中需要一定的操作（单位转换、异常值排除等），那就对这个（或这些个）表格使用WITH建立临时表，因为 临时表 内处理了数据的变换，重点放在SELECT，这时主查询 的语句就变得相对简洁，其重点放在表的连接（JOIN）。作为一个可能的特点， 多数较为单纯的情况下，可以观察到WITH 建立的临时表往往仅对单个表进行操作，很少使用JOIN语句， 也有部分涉及变量众多的查询（比如，评分的计算），临时表内就有很多的JOIN操作。

2.使用WITH的两种方式。

这里说的是在有多个临时表达的情况下，各个临时表之间的两种关系。

• 1.一个临时表作为另一个临时表数据处理的的基础；这种情况可以理解为某个表的数据处理需要其他表格数据的辅助，如以下代码所示。

--建立临时表格, 诊断信息
WITH diag AS
(
    SELECT 
        hadm_id--医院的ID
        , CASE WHEN icd_version = 9 THEN icd_code ELSE NULL END AS icd9_code
        , CASE WHEN icd_version = 10 THEN icd_code ELSE NULL END AS icd10_code
    FROM mimic_hosp.diagnoses_icd diag
)
--另外一个临时表格，基于上个临时表；
, com AS
(
    SELECT
        ad.hadm_id
        -- Myocardial infarction
        , MAX(CASE WHEN
            SUBSTR(icd9_code, 1, 3) IN ('410','412')
            --substr(string,start,length)函数格式
            OR
            SUBSTR(icd10_code, 1, 3) IN ('I21','I22')
            --从第一个字符开始，匹配前3个，code的解释见d_icd_dianosis表格
            OR
            SUBSTR(icd10_code, 1, 4) = 'I252'
            THEN 1 
            ELSE 0 END) AS myocardial_infarct--心肌梗死
    FROM mimic_core.admissions ad
    LEFT JOIN diag
    ON ad.hadm_id = diag.hadm_id
    GROUP BY ad.hadm_id
)
....

• 2.多个临时表之间是并列关系，相互之间独立，都直接与 主查询 相关联，如以下代码所示。

WITH p as
(
    SELECT 
        ag.subject_id
        , ag.hadm_id
        , ag.age
        , p.gender
        , CASE WHEN p.gender='F' THEN 
            POWER(75.0 / 186.0 / POWER(ag.age, -0.203) / 0.742, -1/1.154)
            ELSE 
            POWER(75.0 / 186.0 / POWER(ag.age, -0.203), -1/1.154)
            END 
            AS MDRD_est
    FROM `physionet-data.mimic_derived.age` ag
    LEFT JOIN `physionet-data.mimic_core.patients` p
    ON ag.subject_id = p.subject_id
    WHERE ag.age >= 18
)
, lab as
(
    SELECT 
        hadm_id
        , MIN(creatinine) AS scr_min
    FROM `physionet-data.mimic_derived.chemistry`
    GROUP BY hadm_id
)
, ckd as 
(
    SELECT hadm_id, MAX(1) AS CKD_flag
    FROM mimic_hosp.diagnoses_icd
    WHERE 
        (
            SUBSTR(icd_code, 1, 3) = '585'
            AND 
            icd_version = 9
        )
    OR 
        (
            SUBSTR(icd_code, 1, 3) = 'N18'
            AND 
            icd_version = 10
        )
    GROUP BY 1
)
SELECT 
    p.hadm_id
    , p.gender
    , p.age
    , lab.scr_min
    , COALESCE(ckd.ckd_flag, 0) AS ckd
    , p.MDRD_est
    , CASE 
    WHEN lab.scr_min<=1.1 THEN scr_min
    WHEN ckd.ckd_flag=1 THEN scr_min
    ELSE MDRD_est END AS scr_baseline
FROM p
LEFT JOIN lab
ON p.hadm_id = lab.hadm_id
LEFT JOIN ckd
ON p.hadm_id = ckd.hadm_id
;

总结

在了解了以上的信息之后， 会使在写SQL代码的时候更加从容：先用WITH 构建 临时表 处理好各个表内的数据， 后用主查询 将各个表格连接在一起， 涉及多个临时表的时候，临时表之间的关系也有两种。