MIMIC数据库SCI复现课程Day2: 数据提取到数据洗濯
https://img-blog.csdnimg.cn/img_convert/07dad9314e160a817992d778e7d93642.png
诺维医学科研官网：https://www.newboat.top 更新换版中！
bilibili：文章对应的讲解视频在此。熊大学习社 熊大学习社的个人空间-熊大学习社个人主页-哔哩哔哩视频
微信公众号：熊大学习社、诺维之舟
公益网站，首页 | 公益网站 ，内有医学资料库
诺维之舟AI：https://gpt4.nwzz.xyz 可在线使用GPT4
课程相干资料：
（1）课程资料包罗SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用下令与批处理脚本、课本;MIMIC IV常见数据提取代码-基于sql、数据洗濯-基于R课本； 待更新待更新等。关注公众号“熊大学习社”，复兴“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
https://img-blog.csdnimg.cn/img_convert/48a73480e047e8d2639a311e82eae9c7.png
里面用的是相对路径，这就是为什么要在上面指定的目次。
https://img-blog.csdnimg.cn/img_convert/d4c7c6774771b012a4b5e300263803c8.png
mimiciv_derived版块其实是在mimic_hosp及mimic_icu两个模块基础上然后在MIMIC数据库官网下载其提供的可视图化代码，然后运行而得出来的61个视图表。
https://img-blog.csdnimg.cn/img_convert/06e0e3b8349abc1295654aca9a1271b7.png
也可通过下令行查看。
-- 查看视图
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。
https://img-blog.csdnimg.cn/img_convert/80242a0261a4be4fb244e90ad6382b7c.png
报错的标题是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
;
https://img-blog.csdnimg.cn/img_convert/68f5caa97055c405d273005c0d249a1f.png
这里就使用了官方函数 "mimiciv_derived.DATETIME_DIFF" 和 “mimiciv_derived.DATETIME”， 假如您没有安装官方函数就会报错！
（2）weight_duration，体重变革
weight_duration, ICU期间体重的变革，体重是反应患者营养状态的重要因素。具体代码看物化视图天生所调用的sql文件。
https://img-blog.csdnimg.cn/img_convert/19bcb43418093b475f37098f0882031c.png
（3）GCS， 神经体系功能评分
GCS评分， 神经体系功能评分。
https://img-blog.csdnimg.cn/img_convert/c899097a0b58247cebce3f66698d7ce2.png
二、MIMIC IV数据提取
MIMIC IV的关键数据有哪些，常用提取下令是什么？
1 MIMIC IV关键数据与表格
mimiciv_derived模块的61个视图具有非常多的提取信息，基本上一个入住ICU病人的90%以上的信息都能在这提取！！
除了61个视图，另有15个函数。
https://img-blog.csdnimg.cn/img_convert/5907fbd0b0695a82f75136f78e208b6f.png
（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如下：
SELECTad.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
FROMmimiciv_hosp.admissions AS ad,mimiciv_hosp.patients AS pa
WHEREad.subject_id = pa.subject_id
GROUP BYad.subject_id LIMIT 200;
https://img-blog.csdnimg.cn/img_convert/049908c3fa6b3575dab29a2eb3402f7d.png
第二种方法：物化视图age表
SELECT subject_id, ROUND(age,0) as age
FROM age
LIMIT 100
https://img-blog.csdnimg.cn/img_convert/2b25a7f4b297f55d976d57d00431d0d7.png
（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;
https://img-blog.csdnimg.cn/img_convert/6d1db73c956603c8a0a54910bb2a34f9.png
可以根据代码修改筛选出来其他年龄段的患者。
[*] 第二种方法
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;
https://img-blog.csdnimg.cn/img_convert/b02ce8f328d885f487a03532c9e6ac81.png
[*] 获取首次入院记录
利用子查询，过滤出患者的首次入院记录
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;
https://img-blog.csdnimg.cn/img_convert/b497e5115ebaf33c47ffc880e66f82ab.png
（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
https://img-blog.csdnimg.cn/img_convert/9a5875f110b672d913dff65ec3fb2f87.png
第二种方法：物化视图icustay_detail
-- 每个病人按照进icu时间排序
select subject_id, stay_id
from icustay_detail
where first_icu_stay = 't'
https://img-blog.csdnimg.cn/img_convert/22953cd71d5e0e3bd5eddcdffbb7d5b1.png
（5）高血压（hypertension）患者数据
在d_icd_diagnoses查找高血压的ICD编号和版本。
-- 高血压的icd
SELECT * FROM "D_ICD_DIAGNOSES" WHERE long_title like '%hypertension%' 结果许多个，具体哪一个呢，照旧哪几个。
https://img-blog.csdnimg.cn/img_convert/eb4e46e02b39343b65153765c15a4c8d.png
假如确定高血压的具体种别是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;
https://img-blog.csdnimg.cn/img_convert/55b38536da0afc71add119e2e8485a48.png
假如不确定，查看这些高血压相干的疾病分别都多少患者手术。一种思路是选最多最广泛的群体。
-- 查看这些高血压相关的疾病分别都多少患者
-- 相关疾病保存到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；
五是分组；
六是排序。
https://img-blog.csdnimg.cn/img_convert/f20266d768f4501bfb4baef322fb3647.png
[*] 代码不会写怎么办，借助GPT4来助力。
提问：有两个表，一个是疾病与其编号和编号版本的表D_ICD_DIAGNOSES。二是患者与疾病编号的表DIAGNOSES_ICD。用sql语言，首先模糊查找高血压hypertension的编号和版本，然后根据查出的多个结果，查询DIAGNOSES_ICD表中这些结果对应的患者数量。
https://img-blog.csdnimg.cn/img_convert/2799534b3c888b6f3e9262c5b52787e6.png
https://img-blog.csdnimg.cn/img_convert/5f56c29b144982289a6a9fbbaebcadc7.png
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
https://img-blog.csdnimg.cn/img_convert/0059e64fe1509934d9773d4dbb6031f7.png
[*] 百度翻译（上传截图，图文翻译）
上传截图到百度翻译。
https://img-blog.csdnimg.cn/img_convert/c4e4fbdd7a0cfe44c0b57f69363f9636.png
（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
https://img-blog.csdnimg.cn/img_convert/45420a5f931546cf338fa62b5c307998.png
发现这个结果不太好，数据量不多，而且不怎么相干。
试试脑梗死，这是脑卒中的一种，英文，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
https://img-blog.csdnimg.cn/img_convert/f53e765816716150a8fae985ff760111.png
相干疾病类型挺多，必要筛选一下。前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')
https://img-blog.csdnimg.cn/img_convert/bbb4f13f31147b26139fe0cf3c8c8b7f.png
（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 ONi.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
https://img-blog.csdnimg.cn/img_convert/d1b6bf94307977b6d866073d16e6b933.png
[*] 第一天入住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 ONi.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
https://img-blog.csdnimg.cn/img_convert/46d1e478b077a0d8eaf6b94b378b4c62.png
[*] BMI
可用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
https://img-blog.csdnimg.cn/img_convert/c5514de8992c1a2ea21be2978a9c40c8.png
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:病人IDhadm_id: 住院IDage_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
https://img-blog.csdnimg.cn/img_convert/81069b5f1e14985abaa1f9174ba9c5e3.png
（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
https://img-blog.csdnimg.cn/img_convert/0e825f1ec69f4d84eb73073dab4c6b2a.png
接着，选取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
https://img-blog.csdnimg.cn/img_convert/f4f923973495e6819bd4e1815e47466b.png
（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
https://img-blog.csdnimg.cn/img_convert/272b1c04c7888b817acabc063fd7038d.png
接着，选取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
https://img-blog.csdnimg.cn/img_convert/f20fd30b58924d0bcc06f932a9a1a316.png
（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
https://img-blog.csdnimg.cn/img_convert/a57efa75d78e25002bdbc904a0ee9255.png
（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)
https://img-blog.csdnimg.cn/img_convert/8a23eb34973a08e8d528cf214e259037.png
（15）甘油三脂
首先，甘油三酯的编号，在MIMICIV_HOSP.D_LABITEMS中查找，为51000。
SELECT itemid, label FROM MIMICIV_HOSP.D_LABITEMS WHERE label like '%Triglyceride%'
https://img-blog.csdnimg.cn/img_convert/9426275c3d4d846aac02cd257f592cca.png
接着，查找查验表labevents，甘油三酯的检测值。
SELECT subject_id, hadm_id, charttime, valuenum
FROM mimiciv_hosp.labevents
WHERE itemid in ('51000') and valuenum is not null
https://img-blog.csdnimg.cn/img_convert/21fe91cf59c8617d9031b53f66eb0565.png
继续，获取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 TriglycerideFROM mimiciv_derived.icustay_detail iINNER JOIN t1 ON i.subject_id= t1.subject_id AND i.hadm_id=t1.hadm_idWHERE t1.charttimeBETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR) AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)
https://img-blog.csdnimg.cn/img_convert/0452b3ae83d589374465988e117df569.png
（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)
https://img-blog.csdnimg.cn/img_convert/b2c158f99cd4c55abfe4fffad4969066.png
（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
https://img-blog.csdnimg.cn/img_convert/fc88ea29666664b63d4bdd145491b3e2.png
接着，选取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
https://img-blog.csdnimg.cn/img_convert/7a9ae9d4d51db8573e9e046ee9aa349d.png
（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
https://img-blog.csdnimg.cn/img_convert/e158444a2f28e7c93d9d9de61e025c92.png
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;
https://img-blog.csdnimg.cn/img_convert/655bcf4be2e95a814efb5cc3080ffea2.png
[*] 第二种方法
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;
https://img-blog.csdnimg.cn/img_convert/24de451cfe68ef445ec93f4000932784.png
也可把提取的变量名明确。
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;
https://img-blog.csdnimg.cn/img_convert/c56335c15fad538c4548650ab8fde9ce.png
（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;
https://img-blog.csdnimg.cn/img_convert/aa4f1418527f2d3c8531d2ee63cc6415.png
[*] 第二种方法
-- 用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
https://img-blog.csdnimg.cn/img_convert/27f641c390425ad3211bc3ebaab48ca7.png
（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
https://img-blog.csdnimg.cn/img_convert/85fdc4f18c9c52f8bd5c9f38e28f024c.png
（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
https://img-blog.csdnimg.cn/img_convert/422bb7234850d17f852d97b381fc5677.png
（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
https://img-blog.csdnimg.cn/img_convert/72e081885356b9cfb1fc1ebcd12c583f.png
保存到一个表里。
-- 创建并保存到新表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在哪儿？看选定的组件。
https://img-blog.csdnimg.cn/img_convert/8344eb148c3b69d6498499ac3b7cc3f9.png
https://img-blog.csdnimg.cn/img_convert/cec6940c649e507d55bc9796a3cd7f65.png
三、MIMIC IV数据合并和洗濯
1 数据变量
研究主题：甘油三脂葡萄糖指数与肾脏疾病的关联性
确定提取哪些数据呢？如下。
变量名称表名subject_id病人编号mimiciv_derived.icustay_detailhadm_id住院编号mimiciv_derived.icustay_detailstay_idICU编号mimiciv_derived.icustay_detailage年龄mimiciv_derived.ageintimeICU进入时间mimiciv_derived.icustay_detailgender性别mimiciv_derived.icustay_detailheight身高mimiciv_derived.heightweight体重mimiciv_derived.weightBMI体质指数-GCS神经体系功能评分mimiciv_derived.gcsrbc红细胞mimiciv_derived.complcreatinine肌酐mimiciv_derived.chemistryrenal_disease肾脏病mimiciv_derived.charlsonTriglyceride甘油三酯mimiciv_hosp.labeventsglucose葡萄糖mimiciv_derived.chemistryTyGTyG指数-- 2 数据合并
新建schema框架，用以保存我们处理的数据。
DROP SCHEMA IF EXISTS work CASCADE;
CREATE SCHEMA work; 更多内容和课程资料在公众号！
五 小结
[*] （1）MIMIC数据分析研究发起。
一是做好数据检查，有没有遗漏、有没有重复、有没有多删或少删。基础数据（如首次不小于18岁），然后用left join逐个提取合并。合并前后的数据量变革，一定要做好检查。
二是数据提取用SQL完成，数据洗濯与分析用R。对于提取时间很长的数据，如实验数据（甘油三酯）、药品数据等，可把初始数据提取后保存为一个表，后面必要处理用这个表就行。
三是熟悉研究的基本思路，如数据纳排（按了局变量、自变量、协变量的序次）作为数据选取流程图的依据，是论文的一部门，另有数据格式转化、分类变量等。另外，MIMIC数据分析不涉及权重，与NHANES不同。
[*] （2）课程福利。
[*] （3）课程资料获取。课程资料包罗SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用下令与批处理脚本、课本;MIMIC IV常见数据提取代码-基于sql、数据洗濯-基于R课本； 待更新待更新等。关注公众号“熊大学习社”，复兴“mimic01”，获取全部MIMIC复现课程资料链接。
https://img-blog.csdnimg.cn/img_convert/d81e258e201136457d5b388ea6dc69a7.png
服务互助见客服二维码。关注B站熊大学习社，公众号诺维之舟、熊大学习社。您的一键三连是我最大的动力。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]