MIMIC数据库SCI复现课程Day2: 数据提取到数据清洗

爱学习的大熊

已于 2024-04-19 16:11:30 修改

阅读量1.9k

点赞数 29

文章标签：数据库

于 2024-04-14 08:51:51 首次发布

本文链接：https://blog.csdn.net/shx13141/article/details/137734752

版权

诺维医学科研官网：https://www.newboat.top 更新换版中！

bilibili：文章对应的讲解视频在此。熊大学习社熊大学习社的个人空间-熊大学习社个人主页-哔哩哔哩视频

微信公众号：熊大学习社、诺维之舟

公益网站，首页 | 公益网站，内有医学资料库

诺维之舟AI：https://gpt4.nwzz.xyz 可在线使用GPT4

课程相关资料：

（1）课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义；[Day3] 待更新[Day4]待更新等。关注公众号“熊大学习社”，回复“mimic01”，获取全部4天MIMIC复现课程资料链接。

我们坚持学以致用，做有质量的分享。关注B站诺维医学（原名熊大学习社），公众号诺维之舟、熊大学习社。您的一键三连是我最大的动力。

（2）一对一论文指导学员免费获取学习课程和专属答疑。了解咨询扫客服二维码。

0 课程的总体框架

Day1：一、MIMIC数据库零基础入门

（1）MIMIC数据库获取

（2）MIMIC数据库软件安装

（3）MIMIC数据表介绍、基础数据提取
Day2：二、MIMIC数据库数据提取与清洗

（1）物化视图安装与简介

（2）关键数据提取与实操

（3）数据清洗实操
Day3：三、MIMIC数据库SCI论文复现上

（1）MIMIC数据库常用的研究方法

（2）MIMIC数据库SCI论文解析

（3）数据提取与清洗
Day4：四、MIMIC数据库SCI论文复现下

（1）多模型Logistic回归模型

（2）限制性立方样条图RCS

（3）亚组分析

这次直播课程的特点：上手操作+撸代码，零基础到SCI复现，随时互动交流，快速开启你的医学研究。

一、MIMIC IV物化视图

MIMIC IV物化视图如何安装，物化视图有哪些，主要有什么用？

1 补充：Navicat中的表显示不出来，怎么办？

Navicat的表显示不出来的问题已解决，是版本过低导致的。升级版本即可，已放课程资料包。

2 建立MIMIC IV数据库物化视图

在G:/program files/MIMIC/mimic-code-main/mimic-iv/concepts_postgres（你的SQL命令文件夹）目录下运行cmd.

 # 访问数据库
 psql -U postgres -p 5432
 
 # 进入mimiciv数据库
 \c mimiciv
 
 # 生成函数
 \i 'postgres-functions.sql'
 
 # 视图
 \i 'postgres-make-concepts.sql'

看一下postgres-make-concepts.sql

里面用的是相对路径，这就是为什么要在上面指定的目录。

mimiciv_derived版块其实是在mimic_hosp及mimic_icu两个模块基础上然后在MIMIC数据库官网下载其提供的可视图化代码，然后运行而得出来的61个视图表。

也可通过命令行查看。

 -- 查看视图
 SELECT * FROM information_schema.views;
 
 -- 查看物化视图
 SELECT mv.relname AS materialized_view,
        pg_get_viewdef(mv.oid) AS definition
 FROM pg_class mv
 WHERE mv.relkind = 'm';

从上面的命令行运行结果发现，有报错信息。最后生成的物化视图58个，还缺3个：icustay_hourly、sofa、sepsis3。

报错的问题是UNNEST函数不存在。改写代码，不用UNNEST函数实现。

icustay_hourly.sql的代码改写后如下：

 -- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
 DROP TABLE IF EXISTS mimiciv_derived.icustay_hourly; CREATE TABLE mimiciv_derived.icustay_hourly AS
 /* This query generates a row for every hour the patient is in the ICU. */ /* The hours are based on clock-hours (i.e. 02:00, 03:00). */ /* The hour clock starts 24 hours before the first heart rate measurement. */ /* Note that the time of the first heart rate measurement is ceilinged to */ /* the hour. */ /* this query extracts the cohort and every possible hour they were in the ICU */ /* this table can be to other tables on stay_id and (ENDTIME - 1 hour,ENDTIME] */ /* get first/last measurement time */
 WITH all_hours AS (
   SELECT
     it.stay_id, 
     CASE
       WHEN DATE_TRUNC('HOUR', it.intime_hr) = it.intime_hr
       THEN it.intime_hr
       ELSE DATE_TRUNC('HOUR', it.intime_hr) + INTERVAL '1 HOUR'
     END AS endtime, 
     GENERATE_SERIES(-24, CAST(CEIL(EXTRACT(EPOCH FROM it.outtime_hr - it.intime_hr) / 3600.0) AS INT)) AS hrs,
     row_number() over (partition by it.stay_id order by it.intime_hr) as rn
   FROM mimiciv_derived.icustay_times AS it
 )
 SELECT
   a.stay_id,
   CAST(a.hrs AS BIGINT) AS hr,
   a.endtime + CAST(b.hrs AS BIGINT) * INTERVAL '1 HOUR' AS endtime
 FROM all_hours a
 JOIN all_hours b ON a.stay_id = b.stay_id AND a.rn = b.rn;

进入到三个文件的上一级目录，再运行cmd，然后依次运行sql代码就行。

 -- cmd
 psql -U postgres -p 5432
 
 -- 进入mimiciv数据库
 \c mimiciv
 
 -- 物化视图：demographics/icustay_hourly.sql
 \i 'demographics/icustay_hourly.sql'
 
 
 -- 物化视图：score/sofa.sql
 \i 'score/sofa.sql'
 
 -- 物化视图：sepsis/sepsis3.sql
 \i 'sepsis/sepsis3.sql'

3 物化视图

（1）age，年龄

 SELECT   
   ad.subject_id
   , ad.hadm_id
   , ad.admittime
   , pa.anchor_age
   , pa.anchor_year
   , mimiciv_derived.DATETIME_DIFF(ad.admittime, mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0,0),'YEAR') + pa.anchor_age AS age
 FROM mimiciv_hosp.admissions  AS ad
 INNER JOIN mimiciv_hosp.patients  AS pa
 ON ad.subject_id = pa.subject_id
 ;

这里就使用了官方函数 "mimiciv_derived.DATETIME_DIFF" 和 “mimiciv_derived.DATETIME”，如果您没有安装官方函数就会报错！

（2）weight_duration，体重变化

weight_duration, ICU期间体重的变化，体重是反应患者营养状况的重要因素。具体代码看物化视图生成所调用的sql文件。

（3）GCS，神经系统功能评分

GCS评分，神经系统功能评分。

二、MIMIC IV数据提取

MIMIC IV的关键数据有哪些，常用提取命令是什么？

1 MIMIC IV关键数据与表格

mimiciv_derived模块的61个视图具有非常多的提取信息，基本上一个入住ICU病人的90%以上的信息都能在这提取！！

除了61个视图，还有15个函数。

（1）计算患者的真实年龄

患者的真实年龄 = anchor_age + admittime - anchor_year

anchor_age，anchor_year可以在mimiciv_hosp.patients表中找到

患者的入院年龄可以在mimiciv_hosp.admissions表中找到。

第一种方法：patients表和admissions表联合查询

公式用sql代码表示：

 pa.anchor_age + mimiciv_derived.DATETIME_DIFF(ad.admittime, 
     mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0, 0)

这里就使用了官方函数 "mimiciv_derived.DATETIME_DIFF" 和 “mimiciv_derived.DATETIME”，如果您没有安装官方函数就会报错！

这个算法算出来的年龄是小数，我们可以使用ROUND函数转成整数，完整SQL如下：

 SELECT  ad.subject_id,  
 MAX (ROUND(pa.anchor_age + mimiciv_derived.datetime_diff ( ad.admittime, mimiciv_derived.DATETIME ( pa.anchor_year, 1, 1, 0, 0, 0 ), 'YEAR' ), 0)) AS Age_real
 FROM  mimiciv_hosp.admissions AS ad,  mimiciv_hosp.patients AS pa 
 WHERE  ad.subject_id = pa.subject_id 
 GROUP BY  ad.subject_id   LIMIT 200;

第二种方法：物化视图age表

 SELECT subject_id, ROUND(age,0) as age 
 FROM age 
 LIMIT 100

（2）排除小于18岁的患者

第一种方法

先使用子查询先查询出来患者的真实年龄，再排除掉小于18岁的患者。

with base as (
    SELECT ad.subject_id,
    MAX(ROUND(pa.anchor_age + mimiciv_derived.DATETIME_DIFF(ad.admittime,  
        mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0, 0), 'YEAR'),0)) AS age
    FROM mimiciv_hosp.admissions ad, mimiciv_hosp.patients pa
    WHERE ad.subject_id = pa.subject_id
    GROUP BY ad.subject_id
)
SELECT * FROM base WHERE base.age >=18 limit 100;

可以根据代码修改筛选出来其他年龄段的患者。

第二种方法

SELECT subject_id, ROUND(age,0) as age 
FROM mimic_derived.age 
WHERE anchor_age >=18
LIMIT 100

（3）首次入院记录

以急性胰腺反复入院的患者为例，仅保留首次入院数据。

MIMIV IV数据库记录了29万个病人的43万条入院记录，但是在做数据分析和提取的时候，通常只需要提取某个病人的首次入院记录。

入院时间排序

postgres数据库的内置函数ROW_NUMBER，可将患者进行分组，并可以按照入院时间排序。

SELECT ADM.SUBJECT_ID, ADM.HADM_ID, ADM.ADMITTIME, 
-- 分组排序
ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK
FROM MIMICIV_HOSP.ADMISSIONS AS ADM
LIMIT 100;

获取首次入院记录

利用子查询，过滤出患者的首次入院记录

WITH base AS (
    SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,  
    ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK
    FROM MIMICIV_HOSP.ADMISSIONS AS ADM
)
SELECT * FROM base WHERE base.ADMITTIME_RANK = 1 LIMIT 100;

（4）首次进ICU记录

第一种方法：icustays表

一个患者可以多次住ICU，一个subject_id对应着多个icustay_id。研究通常只选择该患者第一次住ICU的记录。

入ICU记录按照intime排序，这样就可以看到每个icustay_id是对应患者的第几次入ICU。

-- 每个病人按照进icu时间排序
select stay_id,
rank() over (partition by subject_id order by intime) as icu_order 
from icustays

第二种方法：物化视图icustay_detail

-- 每个病人按照进icu时间排序
select subject_id, stay_id
from icustay_detail
where first_icu_stay = 't'

（5）高血压（hypertension）患者数据

在d_icd_diagnoses查找高血压的ICD编号和版本。

-- 高血压的icd
SELECT * FROM "D_ICD_DIAGNOSES" WHERE long_title like '%hypertension%'

结果很多个，具体哪一个呢，还是哪几个。

如果确定高血压的具体类别是Unspecified essential hypertension（原发性高血压），就确定下来了：icd_code=4019, icd_version=9。

-- 查看Unspecified essential hypertension的患者
SELECT DISTINCT (HADM_ID)
FROM MIMICIV_HOSP.DIAGNOSES_ICD
WHERE ICD_CODE = '4019' and ICD_VERSION = 9
LIMIT 100;

如果不确定，查看这些高血压相关的疾病分别都多少患者手术。一种思路是选最多最广泛的群体。

-- 查看这些高血压相关的疾病分别都多少患者
-- 相关疾病保存到hypertension_diseases
WITH hypertension_diseases AS (
    SELECT icd_code, icd_version FROM D_ICD_DIAGNOSES WHERE long_title like '%hypertension%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, COUNT(*) AS count
FROM DIAGNOSES_ICD d
JOIN hypertension_diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version ORDER BY count DESC

阅读SQL代码，掌握其中的知识点。

一是结果赋值；

二是别名；

三是统计数count等函数，还有sum等；

四是联合查询join on；

五是分组；

六是排序。

代码不会写怎么办，借助GPT4来助力。

提问：有两个表，一个是疾病与其编号和编号版本的表D_ICD_DIAGNOSES。二是患者与疾病编号的表DIAGNOSES_ICD。用sql语言，首先模糊查找高血压hypertension的编号和版本，然后根据查出的多个结果，查询DIAGNOSES_ICD表中这些结果对应的患者数量。

GPT给出的代码和最终的代码已经很接近了，稍作调整就可以用！

（6）脓毒症（sepsis）患者数据

总结一下，某疾病的病人数据，可用如下通用代码。

在前面的代码基础上加上了疾病名称long_title。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- %疾病名称英文%对应修改！
    SELECT icd_code, icd_version, long_title FROM D_ICD_DIAGNOSES WHERE long_title like '%疾病名称英文%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

以脓毒症（Sepsis）为例。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- Sepsis
    SELECT icd_code, icd_version, long_title FROM D_ICD_DIAGNOSES WHERE long_title like '%Sepsis%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

百度翻译（上传截图，图文翻译）

上传截图到百度翻译。

（7）脑梗（cerebral infarction）患者数据

脑卒中，英文stroke

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- stroke
    SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%stroke%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM MIMICIV_HOSP.DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

发现这个结果不太好，数据量不多，而且不怎么相关。

试试脑梗死，这是脑卒中的一种，英文，cerebral infarction。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- cerebral infarction`
    SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%cerebral infarction%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM MIMICIV_HOSP.DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

相关疾病类型挺多，需要筛选一下。前2个V1254、Z8673是个人病史，不相关。最后几个G43501、G43509等是没有脑梗的，不纳入。

-- 脑梗患者数据
SELECT d.subject_id,d.hadm_id
from mimiciv_hosp.diagnoses_icd d
WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411',
'I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631',
'I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212',
'I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312',
'I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341',
'I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433',
'I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364')

（8）生命体征信息（身高、体重、BMI）

取第一天入住ICU身高

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime charttime_height,c.height
FROM mimiciv_derived.icustay_detail i
INNER JOIN mimiciv_derived.height c ON  i.subject_id=c.subject_id AND i.stay_id=c.stay_id
WHERE c.charttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)
AND c.height is not NULL

第一天入住ICU体重

SELECT i.subject_id,i.stay_id,i.icu_intime,c.starttime as starttime_weight,c.weight
FROM mimiciv_derived.icustay_detail i
INNER JOIN mimiciv_derived.weight_durations c ON  i.stay_id=c.stay_id
WHERE c.starttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)
AND c.weight is not NULL

可用R语言实现。

# BMI: bmi = weight_kg / (height_m ** 2)
d$BMI <- round(d$weight_kg/(d$height_m/100)**2,2)
table(d$weight_kg, useNA = 'ifan')
table(d$height_m , useNA = 'ifan')
table(d$BMI      , useNA = 'ifan')
d1 <- subset(d, is.na(d$BMI))

后面结合数据合并进行实操。

（9）GCS神经系统功能评分

第一天入住ICU的GCS评分

with t1 as(
SELECT i.subject_id,i.stay_id,i.icu_intime, g.charttime as charttime_gcs, g.gcs,
ROW_NUMBER () OVER(PARTITION BY g.SUBJECT_ID ORDER BY g.charttime) AS CHARTTIME_RANK
FROM mimiciv_derived.icustay_detail i
INNER JOIN mimiciv_derived.gcs g ON i.stay_id=g.stay_id
WHERE g.charttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)
AND g.gcs is not NULL
)
select * from t1 where CHARTTIME_RANK = 1

GCS、APS III、SAPS II、OASIS这四个指标在物化视图mimiciv_derived中都有对应的表格。稍作修改就能提取。

GCS（Glasgow Coma Scale）：格拉斯哥昏迷评分，用于评估患者的意识状态和神经系统功能。该评分系统包括眼睛反应、言语能力和运动反应等项目，将每个项目的得分相加得出总分，总分越低表示患者的意识状态越低，常用于评估颅脑损伤和中风等疾病的严重程度。
APS III（Acute Physiology Score III）：急性生理学评分III，是一种用于评估重症患者生理状态的评分系统。APS III通过测量患者的生理指标，如血压、心率、呼吸频率等，以及评估患者的年龄、慢性疾病等因素，来评估患者的疾病严重程度和预后。
SAPS II（Simplified Acute Physiology Score II）：简化急性生理学评分II，也是一种用于评估重症患者生理状态的评分系统。SAPS II通过测量患者的生理指标，如血压、体温、血氧饱和度等，以及评估患者的年龄、慢性疾病等因素，来评估患者的疾病严重程度和预后。SAPS II通常用于评估重症监护患者的预后和病情监测。
OASIS（Oxford Acute Severity of Illness Score）：牛津急性疾病严重度评分，是一种用于评估急性疾病患者严重程度的评分系统。OASIS考虑了患者的年龄、生理指标（如血压、呼吸频率等）、病情持续时间等因素，并通过计算得出一个综合评分，用于衡量患者的疾病严重程度和预后。
ICP（Intra Cranial Pressure）：颅内压力，指颅骨内的压力。ICP是衡量颅脑疾病严重程度的重要指标。正常情况下，颅内压力应处于一定的范围内，但在某些情况下，如颅脑损伤、脑肿瘤等，颅内压力可能升高。监测ICP可以提供有关脑功能和血流情况的重要信息，以及指导治疗和预防并发症的发生。
LODS（Logistic Organ Dysfunction Score）：逻辑器官功能紊乱评分，是一种用于评估重症患者多器官功能障碍的评分系统。LODS通过测量患者的生理指标和临床数据，如血压、呼吸频率、血液学指标等，来评估患者器官功能的紊乱程度。该评分系统可用于评估患者的病情严重程度和疾病预后，并用于重症监护和临床研究中。

（10）合并症数据

物化视图charlson表为合并症常用数据表，包括18种疾病和1个指数。

列名称
subject_id:病人ID
hadm_id: 住院ID
age_score:年龄得分
myocardial infarct: 心肌梗死
congestive_heart_failure: 充血性心力衰竭
peripheral_vascular_disease: 外周血管疾病
cerebrovascular_disease:脑血管疾病
dementia:痴呆症
hronic_pulmonary_disease: 慢性肺部疾病
heumatic_disease:风湿病
charlson entic_ulcer_disease:消化性溃疡病
mild liver disease:轻度肝病
diabetsithout_.cc:无并发症的糖尿病
diabetes with ce:有并发症的糖尿病
paraplegia:截瘫
renal disease:肾脏疾病
malignant_cancer:恶性肿瘤
severe_liver_disease: 重度肝病
metastatic_solid-tumor:转移性实体痘
aids:艾滋病
charlson comorbidity index:查尔森共病指数

以Paraplegia(截瘫)、Renal disease(肾脏病)、CCI(charlson_comorbidity_index)为例。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.paraplegia,c.renal_disease,c.charlson_comorbidity_index
FROM mimiciv_derived.icustay_detail i
INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id

（11）呼吸衰竭

首先，查询呼吸衰竭的诊断icd-code。呼吸衰竭：respiratory failure。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- respiratory failure
    SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%respiratory failure%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM MIMICIV_HOSP.DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

接着，选取ICD编号和版本，提取相应的数据。这里不妨以急性呼吸衰竭（Acute respiratory failure）为例。

with t1 as (
SELECT d.subject_id,d.hadm_id,
case when d.icd_code is not null then 1
ELSE 0
END AS Respiratory_failure
from mimiciv_hosp.diagnoses_icd d
WHERE (d.icd_code in ('51851', '51853', '51881', '51883','51884') and d.icd_version=9) OR
(d.icd_code in ('J95821', 'J95822', 'J9600', 'J9601','J9602','J9620','J9621','J9622') and d.icd_version=10)
)

-- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息
SELECT i.subject_id,i.stay_id,i.icu_intime,t1.Respiratory_failure
FROM mimiciv_derived.icustay_detail i
LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

（12）肺栓塞

首先，查询肺栓塞的诊断icd-code。肺栓塞：pulmonary embolism。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- pulmonary embolism
    SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%pulmonary embolism%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM MIMICIV_HOSP.DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

接着，选取ICD编号和版本，提取相应的数据。

('41519' , 'I2699' , '41511', '41512', 'I2692', 'I2698' , '41513' , 'I2609', 'I2694', 'I2602', 'I2693', '67382')

with t1 as (
SELECT d.subject_id,d.hadm_id,
case when d.icd_code is not null then 1
ELSE 0
END AS pulmonary_embolism
from mimiciv_hosp.diagnoses_icd d
WHERE d.icd_code in ('41519' , 'I2699' , '41511', '41512', 'I2692', 'I2698' , '41513' , 'I2609', 'I2694', 'I2602', 'I2693', '67382')
)

-- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息
SELECT i.subject_id,i.stay_id,i.icu_intime,t1.pulmonary_embolism
FROM mimiciv_derived.icustay_detail i
LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

（13）实验室检查指标：红细胞rbc、wbc、plt、hb

红细胞(rbc)
白细胞(wbc)
血小板(plt, platelet)
血红蛋白浓度(HB, hemoglobin)

这几个指标都在mimiciv_derived.complete_blood_count表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime,c.rbc, c.wbc, c.platelet, c.hemoglobin
FROM mimiciv_derived.icustay_detail i
LEFT JOIN mimiciv_derived.complete_blood_count c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id
WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR) 
AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)
AND c.rbc is not null

（14）实验室检查指标：血钠、肌酐

血钠(sodium)
肌酐(creatinine)
葡萄糖(glucose)

这两个指标在mimiciv_derived.chemistry表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime, c.sodium, c.creatinine, c.glucose
FROM mimiciv_derived.icustay_detail i
LEFT JOIN mimiciv_derived.chemistry c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id
WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR) 
AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)

（15）甘油三脂

首先，甘油三酯的编号，在MIMICIV_HOSP.D_LABITEMS中查找，为51000。

SELECT itemid, label FROM MIMICIV_HOSP.D_LABITEMS WHERE label like '%Triglyceride%'

接着，查找检验表labevents，甘油三酯的检测值。

SELECT subject_id, hadm_id, charttime, valuenum
FROM mimiciv_hosp.labevents
WHERE itemid in ('51000') and valuenum is not null

继续，获取ICU病人的数据信息。

WITH t1 as (
SELECT subject_id, hadm_id, charttime, valuenum
FROM mimiciv_hosp.labevents
WHERE itemid in ('51000') and valuenum is not null
)
SELECT i.subject_id,i.stay_id,i.icu_intime,t1.charttime charttime_Triglyceride,t1.valuenum Triglyceride
FROM mimiciv_derived.icustay_detail i
INNER JOIN t1 ON i.subject_id= t1.subject_id AND i.hadm_id=t1.hadm_id
WHERE t1.charttime  BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR) AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)

（16）葡萄糖

glucose这个指标在mimiciv_derived.chemistry表中。参考血钠和肌酐的数据提取。

（17）终末期肾功能不全

AKI：急性肾损伤，在在mimiciv_derived.kdigo_stages表中。

aki_stage ：疾病状态，3表示终末期。

SELECT i.subject_id,i.stay_id,i.icu_intime,i.first_icu_stay,
CASE WHEN c.aki_stage is not null THEN 1
ELSE 0
END AS end_stage_renal_disease
FROM mimiciv_derived.icustay_detail i
LEFT JOIN mimiciv_derived.kdigo_stages c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id
WHERE c.charttime BETWEEN i.icu_intime AND i.icu_outtime
AND c.aki_stage in (3)

（18）肝硬化

首先，查询肝硬化的诊断icd-code。肝硬化：cirrhosis。

-- 相关疾病信息保存到diseases
WITH diseases AS (
    -- cirrhosis
    SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%cirrhosis%'
)
-- 然后，我们可以使用这个子查询来联合查询用户表，并计算指定疾病编号和版本的病人手术数量
SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count
FROM MIMICIV_HOSP.DIAGNOSES_ICD d
JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version
GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

接着，选取ICD编号和版本，提取相应的数据。

with t1 as (
SELECT d.subject_id,d.hadm_id,
case when d.icd_code is not null then 1
ELSE 0
END AS cirrhosis
from mimiciv_hosp.diagnoses_icd d
WHERE d.icd_code in ('5712','5715','5716','K703','K7030','K7031','K717','K74','K741','K742','K743',
                     'K744','K745','K746','K7460','K7469','P7881')
)
-- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息
SELECT i.subject_id,i.stay_id,i.icu_intime,t1.cirrhosis
FROM mimiciv_derived.icustay_detail i
LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

（19）癌症（恶性肿瘤）

癌症，恶性肿瘤，malignant_cancer，在mimiciv_derived.charlson表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.malignant_cancer
FROM mimiciv_derived.icustay_detail i
INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id

2 MIMIC IV数据提取实操

从常见的研究主题出发，考虑多个情况的组合。比如高血压患者的血液指标、首次入院时年龄不小于18岁的高血压患者。

（1）首次入院时年龄不小于18岁的患者

第一种方法

WITH BASE0 AS (
    SELECT AD.hadm_id,  MAX(ROUND(pa.anchor_age + mimiciv_derived.datetime_diff(ad.admittime,
    mimiciv_derived.datetime(pa.anchor_year, 1, 1, 0, 0, 0), 'YEAR'), 0)) AS age
    FROM mimiciv_hosp.admissions AS AD, mimiciv_hosp.patients AS PA
    WHERE ad.subject_id = pa.subject_id
    GROUP BY ad.hadm_id 
    LIMIT 1000
),
BASE1 AS (
    SELECT ADM.subject_id, ADM.hadm_id, ADM.admittime, 
    ROW_NUMBER() OVER( PARTITION BY ADM.subject_id ORDER BY ADM.admittime) AS admittime_rank
    FROM mimiciv_hosp.admissions AS ADM
)
SELECT * FROM BASE0, BASE1
WHERE BASE0.age >= 18 AND BASE1.admittime_rank = 1 AND BASE0.hadm_id = base1.hadm_id
LIMIT 200;

第二种方法

WITH base AS (
    SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,  
    ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK
    FROM MIMICIV_HOSP.ADMISSIONS AS ADM
)
SELECT * FROM base, age
WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id
LIMIT 100;

也可把提取的变量名明确。

WITH base AS (
    SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,  
    ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK
    FROM MIMICIV_HOSP.ADMISSIONS AS ADM
)
SELECT base.subject_id, base.hadm_id, base.admittime, base.admittime_rank, ROUND(age.age, 0) as age
FROM base, age
WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id
LIMIT 100;

（2）首次进ICU时年龄不小于18岁的患者

第一种方法

WITH base AS (
    SELECT ICU.SUBJECT_ID,  ICU.STAY_ID,  ICU.intime,  
    ROW_NUMBER () OVER(PARTITION BY ICU.SUBJECT_ID ORDER BY ICU.intime) AS ADMITTIME_RANK
    FROM MIMICIV_ICU.ICUSTAYS AS ICU
)
SELECT base.SUBJECT_ID, base.STAY_ID, base.intime, base.ADMITTIME_RANK, ROUND(age.age, 0) as age
FROM base, age
WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id
LIMIT 100;

第二种方法

-- 用age和icustay_detail两个物化视图
select i.subject_id, i.stay_id, ROUND(a.age,0) as age
from icustay_detail as i
LEFT JOIN age as a
ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18

（3）首次入院时年龄不小于18岁的脓毒症患者

WITH base AS (
    SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,  
    ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK
    FROM MIMICIV_HOSP.ADMISSIONS AS ADM
),
t1 as (
SELECT base.subject_id, base.hadm_id, base.admittime, ROUND(age.age, 0)
FROM base, age
WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id;
),
t2 as (
-- 查看脓毒症的患者
SELECT DISTINCT (HADM_ID)
FROM MIMICIV_HOSP.DIAGNOSES_ICD
WHERE ICD_CODE = 'A419' and ICD_VERSION = 10
)

SELECT * from t1,t2 
WHERE t1.HADM_ID = t2.HADM_ID

（4）首次进ICU时年龄不小于18岁的高血压患者

with t1 as(
    -- 用age和icustay_detail两个物化视图
    select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age
    from icustay_detail as i
    LEFT JOIN age as a
    ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18
),
t2 as (
-- 查看高血压的患者
SELECT DISTINCT (HADM_ID)
FROM MIMICIV_HOSP.DIAGNOSES_ICD
WHERE ICD_CODE = '4019' and ICD_VERSION = 9
)

SELECT * from t1,t2 
WHERE t1.HADM_ID = t2.HADM_ID

（5）首次进ICU时年龄不小于18岁的脑梗患者

with t1 as(
    -- 用age和icustay_detail两个物化视图
    select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age
    from icustay_detail as i
    LEFT JOIN age as a
    ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18
),
t2 as (
-- 脑梗患者
SELECT d.subject_id,d.hadm_id
from mimiciv_hosp.diagnoses_icd d
WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411','I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631','I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212','I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312','I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341','I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433','I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364')
)
SELECT t1.* from t1,t2 
WHERE t1.HADM_ID = t2.HADM_ID

保存到一个表里。

-- 创建并保存到新表icu_ci
CREATE TABLE icu_ci as (
with t1 as(
    -- 用age和icustay_detail两个物化视图
    select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age
    from mimiciv_derived.icustay_detail as i
    LEFT JOIN mimiciv_derived.age as a
    ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18
),
t2 as (
-- 脑梗患者
SELECT d.subject_id,d.hadm_id
from mimiciv_hosp.diagnoses_icd d
WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411','I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631','I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212','I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312','I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341','I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433','I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364')
)
SELECT t1.* from t1,t2 
WHERE t1.HADM_ID = t2.HADM_ID
)

数据表icu_ci在哪儿？看选定的组件。

三、MIMIC IV数据合并和清洗

1 数据变量

研究主题：甘油三脂葡萄糖指数与肾脏疾病的关联性

确定提取哪些数据呢？如下。

变量	名称	表名
subject_id	病人编号	mimiciv_derived.icustay_detail
hadm_id	住院编号	mimiciv_derived.icustay_detail
stay_id	ICU编号	mimiciv_derived.icustay_detail
age	年龄	mimiciv_derived.age
intime	ICU进入时间	mimiciv_derived.icustay_detail
gender	性别	mimiciv_derived.icustay_detail
height	身高	mimiciv_derived.height
weight	体重	mimiciv_derived.weight
BMI	体质指数	-
GCS	神经系统功能评分	mimiciv_derived.gcs
rbc	红细胞	mimiciv_derived.compl
creatinine	肌酐	mimiciv_derived.chemistry
renal_disease	肾脏病	mimiciv_derived.charlson
Triglyceride	甘油三酯	mimiciv_hosp.labevents
glucose	葡萄糖	mimiciv_derived.chemistry
TyG	TyG指数	--

2 数据合并

新建schema框架，用以保存我们处理的数据。

DROP SCHEMA IF EXISTS work CASCADE;
CREATE SCHEMA work;

更多内容和课程资料在公众号！

五小结

（1）MIMIC数据分析研究建议。

一是做好数据检查，有没有遗漏、有没有重复、有没有多删或少删。基础数据（如首次不小于18岁），然后用left join逐个提取合并。合并前后的数据量变化，一定要做好检查。

二是数据提取用SQL完成，数据清洗与分析用R。对于提取时间很长的数据，如实验数据（甘油三酯）、药品数据等，可把初始数据提取后保存为一个表，后面需要处理用这个表就行。

三是熟悉研究的基本思路，如数据纳排（按结局变量、自变量、协变量的顺序）作为数据选取流程图的依据，是论文的一部分，还有数据格式转化、分类变量等。另外，MIMIC数据分析不涉及权重，与NHANES不同。

（2）课程福利。
（3）课程资料获取。课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义；[Day3] 待更新[Day4]待更新等。关注公众号“熊大学习社”，回复“mimic01”，获取全部MIMIC复现课程资料链接。

服务合作见客服二维码。关注B站熊大学习社，公众号诺维之舟、熊大学习社。您的一键三连是我最大的动力。

爱学习的大熊

关注

29
点赞
踩
34

收藏

觉得还不错? 一键收藏
0
评论
MIMIC数据库SCI复现课程Day2: 数据提取到数据清洗

课程资料包括**[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义；[Day3] 待更新[Day4]待更新等**。关注公众号“熊大学习社”，回复“`mimic01`”，获取全部MIMIC复现课程资料链接。
复制链接

扫一扫