时间:2024-09-01 16:07:08 阅读:542
诺维医学科研官网:https://www.newboat.top 我们的课程比丁香园更香!
Bilibili:文章对应的讲解视频在此。熊大学习社 https://space.bilibili.com/475774512
微信公众号|Bilibili|抖音|小红书|知乎|全网同名:熊大学习社
医学资源网站,https://med.newboat.top/ ,内有医学离线数据库、数据提取、科研神器等高质量资料库
课程相关资料:
(1)课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义;[Day3] 复现论文、复现代码、复现数据、学习资料、讲义[Day4]扩展学习资料和相关源码等。关注公众号“熊大学习社”,回复“mimic01
”,获取全部4天MIMIC复现课程资料链接。
(2)医学公共数据数据库学习训练营已开班,论文指导精英班学员可获取推荐审稿人信息,欢迎咨询课程助理!
(3)关注熊大学习社。您的一键三连是我最大的动力。
Day1:一、MIMIC数据库零基础入门
(1)MIMIC数据库获取
(2)MIMIC数据库软件安装
(3)MIMIC数据表介绍、基础数据提取
Day2:二、MIMIC数据库数据提取与清洗
(1)物化视图安装与简介
(2)关键数据提取与实操
(3)数据清洗实操
Day3:三、MIMIC数据库SCI论文复现上(生存分析SCI论文研究思路复现)
(1)数据提取
(2)PSM倾向评分
(3)数据提取与清洗
Day4:四、MIMIC数据库SCI论文复现下
(1)多模型Logistic回归模型
(2)限制性立方样条图RCS
(3)亚组分析
这次直播课程的特点:上手操作+撸代码,零基础到SCI复现,随时互动交流,快速开启你的医学研究。
MIMIC IV物化视图如何安装,物化视图有哪些,主要有什么用?
Navicat的表显示不出来的问题已解决,是版本过低导致的。升级版本即可,已放课程资料包。
在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'
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”, 如果您没有安装官方函数就会报错!
weight_duration, ICU期间体重的变化,体重是反应患者营养状况的重要因素。具体代码看物化视图生成所调用的sql文件。
GCS评分, 神经系统功能评分。
MIMIC IV的关键数据有哪些,常用提取命令是什么?
mimiciv_derived模块的61个视图具有非常多的提取信息,基本上一个入住ICU病人的90%以上的信息都能在这提取!!
除了61个视图,还有15个函数。
患者的真实年龄 = 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
第一种方法
先使用子查询先查询出来患者的真实年龄,再排除掉小于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
以急性胰腺反复入院的患者为例,仅保留首次入院数据。
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;
第一种方法: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'
在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给出的代码和最终的代码已经很接近了,稍作调整就可以用!
总结一下,某疾病的病人数据,可用如下通用代码。
在前面的代码基础上加上了疾病名称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
百度翻译(上传截图,图文翻译)
上传截图到百度翻译。
脑卒中,英文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')
取第一天入住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
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))
后面结合数据合并进行实操。
第一天入住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通过测量患者的生理指标和临床数据,如血压、呼吸频率、血液学指标等,来评估患者器官功能的紊乱程度。该评分系统可用于评估患者的病情严重程度和疾病预后,并用于重症监护和临床研究中。
物化视图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
首先,查询呼吸衰竭的诊断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
首先,查询肺栓塞的诊断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
红细胞(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
血钠(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)
首先,甘油三酯的编号,在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)
glucose
这个指标在mimiciv_derived.chemistry
表中。参考血钠和肌酐的数据提取。
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)
首先,查询肝硬化的诊断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
癌症,恶性肿瘤,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
从常见的研究主题出发,考虑多个情况的组合。比如高血压患者的血液指标、首次入院时年龄不小于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;
第一种方法
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
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
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
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
在哪儿?看选定的组件。
研究主题:甘油三脂葡萄糖指数与肾脏疾病的关联性
确定提取哪些数据呢?如下。
变量 | 名称 | 表名 |
---|---|---|
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指数 | -- |
新建schema框架,用以保存我们处理的数据。
DROP SCHEMA IF EXISTS work CASCADE; CREATE SCHEMA work;
上面的数据提取后保存为csv文件。
基准数据:首次进ICU时年龄不小于18岁的患者
思路:
(a)基准数据的基础上逐个加入变量数据;
(b)检查每次加入前后的数据量变化,如果变化太多,要注意并视情况采取适当的方法;
(c)检查合并后的数据重复项。
具体实操如下:
STEP1:合并一部分基础数据,处理速度较快,后面保存为work.icu_gcs
表。sql代码每次都从头运行,挺费时间!
CREATE TABLE work.icu_gcs AS( -- 用age和icustay_detail两个物化视图 with t1 as ( select i.subject_id, i.hadm_id, i.stay_id,i.gender, i.icu_intime, ROUND(a.age,0) as age, rank() over (partition by i.subject_id order by i.icu_intime) as icu_order from mimiciv_derived.icustay_detail as i LEFT JOIN mimiciv_derived.age as a ON i.hadm_id = a.hadm_id and a.age>=18 and a.age is not null ), -- 首次ICU且不小于18岁的人员信息,50920 icu_base as ( SELECT * FROM t1 WHERE icu_order in (1) ), -- 身高,25178 icu_height as ( SELECT i.*,c.charttime charttime_height,c.height FROM icu_sex i LEFT JOIN mimiciv_derived.height c ON i.subject_id=c.subject_id AND i.stay_id=c.stay_id WHERE c.height is not NULL ), -- 体重,25143 t2 as ( SELECT i.*,c.starttime as starttime_weight,c.weight, rank() over (partition by c.stay_id order by c.starttime) as weight_order FROM icu_height i LEFT JOIN mimiciv_derived.weight_durations c ON i.stay_id=c.stay_id WHERE c.weight is not NULL ), -- 找出既准确又尽量多的体重数据 t3 as ( SELECT * FROM t2 WHERE (starttime_weight BETWEEN icu_intime AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR)) OR (starttime_weight NOT BETWEEN icu_intime AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR) AND weight_order=1) ), -- 删除重复项,准确优先 t4 as ( SELECT *, rank() over (partition by stay_id order by weight_order DESC) as rn FROM t3 ), icu_weight as ( SELECT * FROM t4 WHERE rn=1 ), -- GCS评分,25053 t5 as ( SELECT i.*, g.charttime as charttime_gcs, g.gcs, ROW_NUMBER () OVER(PARTITION BY g.SUBJECT_ID ORDER BY g.charttime) AS CHARTTIME_RANK FROM icu_weight 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 t5 where CHARTTIME_RANK = 1 )
STEP2:加入红细胞数据,这里处理时间较长,保存为work.icu_rbc
表
CREATE TABLE work.icu_rbc AS( -- rbc,红细胞, 24713 with t6 as ( SELECT i.*,c.charttime as charttime_rbc,c.rbc, rank() over (partition by i.stay_id order by c.charttime) as rbc_order FROM work.icu_gcs 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 ) SELECT * FROM t6 WHERE rbc_order=1 )
STEP3:加入肌酐和葡萄糖数据,保存为work.icu_chemistry
表
DROP TABLE IF EXISTS work.icu_chemistry; -- 肌酐和葡萄糖,chemistry,24494 CREATE TABLE work.icu_chemistry as ( WITH t7 as ( SELECT i.*,c.charttime as charttime_chem, c.creatinine, c.glucose, rank() over (partition by i.stay_id order by c.charttime) as chem_order FROM work.icu_rbc 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) AND c.creatinine is not null and c.glucose is not null ) SELECT * FROM t7 WHERE chem_order=1 )
STEP4:加入肾脏病和甘油三脂数据,形成最后的数据集work.icu_data
,保存为icu_data.csv
,作为后面数据清洗的输入。
先把甘油三脂的实验数据提取,时间最长,10分钟以上。
CREATE TABLE work.icu_tg as ( -- 甘油三酯 SELECT subject_id, hadm_id, charttime, valuenum FROM mimiciv_hosp.labevents WHERE itemid in ('51000') and valuenum is not null )
最后的数据合并,先合并肾脏病,后甘油三酯。
DROP TABLE IF EXISTS work.icu_data; CREATE TABLE work.icu_data as ( with icu_renal as ( -- 肾脏病,renal_disease,24494 SELECT i.*,c.renal_disease FROM work.icu_chemistry i INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id ), t8 as ( SELECT i.*,t.charttime as charttime_tg,t.valuenum as tg, rank() over (partition by i.subject_id order by t.charttime) as tg_order FROM icu_renal i LEFT JOIN work.icu_tg t ON i.subject_id= t.subject_id AND i.hadm_id=t.hadm_id WHERE t.valuenum is not null ), -- 找出既准确又尽量多的甘油三脂数据,4742 t9 as ( SELECT * FROM t8 WHERE (charttime_tg BETWEEN mimiciv_derived.DATETIME_SUB(icu_intime, INTERVAL '6' HOUR) AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR)) OR (charttime_tg NOT BETWEEN mimiciv_derived.DATETIME_SUB(icu_intime, INTERVAL '6' HOUR) AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR) AND tg_order=1) ), -- 删除重复项,准确优先 t10 as ( SELECT *, rank() over (partition by stay_id order by tg_order DESC) as rn2 FROM t9 ) SELECT * FROM t10 WHERE rn2=1 )
里面有不少空值,后面数据清洗。导出向导,选择csv文件。
数据转换和格式检查
BMI和TyG的计算
数据纳排
基线分析
####公众号:熊大学习社#### # 手动设置工作目录为代码和数据所在文件夹 # 步骤方法:点菜单栏“session”->"Set Work Directory"->"Choose Directory" # 选择代码和数据所在文件夹即可 # 查看工作目录 getwd() # 安装库 if(!require(foreign)) install.packages('foreign') if(!require(haven)) install.packages('haven') if(!require(dplyr)) install.packages('dplyr') if(!require(purrr)) install.packages('purrr') if(!require(lubridate)) install.packages('lubridate') if(!require(readxl)) install.packages('readxl') if(!require(tableone)) install.packages('tableone') # 加载库 library(foreign) library(haven) library(dplyr) library(purrr) library(lubridate) library(readxl) library(tableone) # 1 数据准备------ ## 1.1 数据转换和格式检查------ d <- read.csv('icu_data.csv') # 去除列 colnames(d) d <- subset(d, select = -c(icu_intime, charttime_height, starttime_weight, weight_order, rn, charttime_gcs,charttime_rank, charttime_rbc, charttime_chem, chem_order,charttime_tg, tg_order,rbc_order,rn2)) # 列名变更 colnames(d) d <- plyr::rename(d, c(renal_disease='Renal_disease', tg='TG', gender='Gender', age='Age', gcs='GCS', rbc='RBC', creatinine='Creatinine', glucose='Glucose')) # 数据格式 str(d) # 性别 d$Gender[d$Gender=='M'] <- 'Male' d$Gender[d$Gender=='F'] <- 'Female' d$Gender <- factor(d$Gender, levels = c('Male', 'Female')) table(d$Gender, useNA = 'ifan') str(d) ## 1.2 数据纳排------ nrow(d) # 574010 # (1)暴露因素, TyG指数 d <- subset(d, !is.na(d$TG)) 4742 # (2)结局变量,Renal_disease 肾病 d <- subset(d, !is.na(d$Renal_disease)) # 0 d$Renal_disease[d$Renal_disease==1] <- 'Renal' d$Renal_disease[d$Renal_disease==0] <- 'Non-renal' d$Renal_disease <- factor(d$Renal_disease, levels = c('Renal', 'Non-renal')) table(d$Renal_disease, useNA = 'ifan') # (3)协变量,性别 d <- subset(d, !is.na(d$Gender)) ## 1.3 BMI和TyG的计算------ # 体重:lbs # 身高:英寸 # BMI: bmi = weight_kg / (height_m ** 2) d$BMI <- round(d$weight/(d$height/100)**2,2) table(d$BMI, useNA = 'ifan') d1 <- subset(d, is.na(d$BMI)) # 删除不需要的列 d <- subset(d, select = -c(weight,height)) # TyG d$TyG <- round(log(d$TG * d$Glucose/2),2) table(d$TG, useNA = 'ifan') table(d$Glucose, useNA = 'ifan') table(d$TyG, useNA = 'ifan') # 2 基线分析------ ##2.1 基线分析 str(d) var <- c('Gender', 'Age', 'BMI','TG', 'TyG') # 结局变量 t1 <- CreateTableOne(vars = var, strata = c('Renal_disease'), data =d) print(t1, showAllLevels = TRUE, nonnormal = '', smd = F) |> write.csv('..Table 1 基线分析.csv')
SELECT * FROM "mimiciv_hosp"."admissions" SELECT * FROM mimiciv_hosp.admissions SELECT * FROM admissions SELECT subject_id,hadm_id FROM mimiciv_hosp.admissions SELECT distinct drug FROM "mimiciv_hosp"."prescriptions"
# 需要使用where语句 SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title='hypertension' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title='Secondary hypertension' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title like '%hypertension%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%hypertension%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%acute heart failure%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%heart failure%' SELECT * FROM "mimic_core"."patients" where anchor_age >50 SELECT * FROM "mimic_core"."patients" where anchor_age between 40 and 50 SELECT * FROM "mimic_core"."patients" where anchor_age>=40 and anchor_age<=50 SELECT * FROM "mimic_core"."patients" where anchor_age>40 and anchor_age<50 # 正则表达式 SELECT * FROM "mimic_hosp"."d_labitems" where label ~*'.*wbc.*|.*white.*blood.*cell.*'
SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' and icd_version=9 SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code='3965' SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code='3965' or icd_code='5A15223' SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code in ('3965' ,'5A15223')
SELECT max(los) FROM "mimic_icu"."icustays" SELECT min(los) FROM "mimic_icu"."icustays" SELECT avg(los) FROM "mimic_icu"."icustays" SELECT sum(los) FROM "mimic_icu"."icustays"
SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' order by fluid ASC SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' order by fluid DESC
$y$<- ("正确代码请联系VX:Yi11446699 You buy the second-hand pirated version, first-hand information please contact VX:Yi11446699") print(y)
DROP TABLE IF EXISTS lung_cancer_patient; CREATE TABLE lung_cancer_patient as WITH lc as (select subject_id,hadm_id, icd_code , CASE when icd_code = '1623' then 1 when icd_code = '1624' then 1 when icd_code = '1625' then 1 when icd_code = '1628' then 1 when icd_code = '1629' then 1 when icd_code = '20921' then 1 else 0 end as lung_cancer FROM diagnoses_icd group by subject_id, hadm_id, icd_code order by subject_id,hadm_id, icd_code) SELECT * FROM lc where lung_cancer=1 MAX(CASE WHEN itemid in (51244, 51245) THEN valuenum ELSE NULL END) AS lymphocytes
SELECT "mimic_hosp"."lung_cancer_patient".subject_id,"mimic_hosp"."lung_cancer_patient".hadm_id FROM "mimic_hosp"."lung_cancer_patient" left join "mimic_icu"."icustays" on "mimic_hosp"."lung_cancer_patient".hadm_id="mimic_icu"."icustays".hadm_id SELECT "mimic_icu"."icustays".subject_id, "mimic_icu"."icustays".hadm_id, "mimic_icu"."icustays".stay_id, "mimic_icu"."icustays".first_careunit, "mimic_icu"."icustays".last_careunit, "mimic_icu"."icustays".intime, "mimic_icu"."icustays".outtime, "mimic_icu"."icustays".los FROM "mimic_icu"."icustays" right join "mimic_hosp"."lung_cancer_patient" on "mimic_hosp"."lung_cancer_patient".hadm_id="mimic_icu"."icustays".hadm_id SELECT * FROM "mimic_hosp"."diagnoses_icd" inner join "mimic_hosp"."d_icd_diagnoses" on "mimic_hosp"."diagnoses_icd".icd_code="mimic_hosp"."d_icd_diagnoses".icd_code and "mimic_hosp"."diagnoses_icd".icd_version="mimic_hosp"."d_icd_diagnoses".icd_version order by subject_id
SELECT table_alias.column column_alias FROM table table_alias SELECT subject_id,hadm_id FROM mimic_core.admissions SELECT mca.subject_id si,hadm_id hi FROM mimic_core.admissions mca
DATE(),提取日期或日期/时间表达式的日期部分,比如DATE()对字符串“2154-03-03 04:11:00”处理后,返回的结果是“2154-03-03”;对“2180/9/9”处理后,返回“2180-09-09”,就是标准的年、月、日格式 DATETIME_DIFF(endtime,starttime,'HOUR'): 求endtime-starttime,单位是小时,单位可以根据实际情况换成'SECOND','MINUTE','DAY','YEAR' DATETIME_ADD (datetime, INTERVAL '1' DAY): 该代码是求datetime加1天的时间点,其中,1和DAY都可以改变,比如改成'2' YEAR DATETIME_SUB (datetime, INTERVAL '6' HOUR): 该代码是求datetime减去6小时,也就是datetime前6小时的时间点
以提取ECMO为例,思路:从d_icd_procedures中找到ECMO的icd_code。
SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' 3965 5A15223 DROP TABLE IF EXISTS ecmo; CREATE TABLE ECMO AS with ec AS( select subject_id,hadm_id, icd_code , CASE when icd_code = '3965' then 1 when icd_code = '5A15223' then 1 else 0 end as ecmo from "mimic_hosp"."procedures_icd" group by subject_id, hadm_id, icd_code order by subject_id,hadm_id, icd_code ) SELECT * from ec where ecmo =1
以提取阿司匹林为例,并与ICU病人关联。
DROP TABLE IF EXISTS aspirin; CREATE TABLE aspirin as with asp as ( SELECT mimic_icu.icustays.subject_id, mimic_icu.icustays.hadm_id, mimic_icu.icustays.stay_id, mimic_hosp.prescriptions.starttime, mimic_hosp.prescriptions.stoptime, mimic_hosp.prescriptions.drug_type, mimic_hosp.prescriptions.drug, mimic_hosp.prescriptions.dose_val_rx, mimic_hosp.prescriptions.dose_unit_rx, mimic_hosp.prescriptions.form_val_disp, mimic_hosp.prescriptions.form_unit_disp, mimic_hosp.prescriptions.doses_per_24_hrs, mimic_hosp.prescriptions.route, CASE WHEN LOWER(mimic_hosp.prescriptions.drug) LIKE '%aspirin%' THEN 1 ELSE 0 END AS aspirin FROM mimic_icu.icustays LEFT JOIN mimic_hosp.prescriptions ON mimic_hosp.prescriptions.subject_id=icustays.subject_id AND mimic_hosp.prescriptions.hadm_id=icustays.hadm_id) SELECT * from asp where aspirin=1
以提取肥胖obesity为例。
DROP TABLE IF EXISTS obesity; CREATE TABLE obesity AS with ob AS( select subject_id,hadm_id, icd_code , CASE when icd_code = '27800' then 1 when icd_code = '27801' then 1 when icd_code = '27803' then 1 when icd_code = '64910' then 1 when icd_code = '64911' then 1 when icd_code = '64912' then 1 when icd_code = '64913' then 1 when icd_code = '64914' then 1 when icd_code = 'E66' then 1 when icd_code = 'E660' then 1 when icd_code = 'E6601' then 1 when icd_code = 'E6609' then 1 when icd_code = 'E661' then 1 when icd_code = 'E662' then 1 when icd_code = 'E668' then 1 when icd_code = 'E669' then 1 when icd_code = 'O9921' then 1 when icd_code = 'O99210' then 1 when icd_code = 'O99211' then 1 when icd_code = 'O99212' then 1 when icd_code = 'O99213' then 1 when icd_code = 'O99214' then 1 when icd_code = 'O99215' then 1 else 0 end as obesity from "mimic_hosp"."diagnoses_icd" group by subject_id, hadm_id, icd_code order by subject_id,hadm_id, icd_code ) SELECT * from ob where obesity=1
提取物化视图没有的实验室检查(以CRP为例)
思路:首先从字典d_labitems表格中找到CRP的itemid,然后与labevents进行关联。
SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%crp%' 51652 SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%c-reactive protein%' 50889 SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%c reactive protein%' SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%reactive protein%' SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%protein%' DROP TABLE IF EXISTS crp; CREATE TABLE crp AS WITH crp as (SELECT subject_id,hadm_id,charttime,itemid,valuenum as crp_level FROM "mimic_hosp"."labevents" WHERE itemid in (51652, 50889) AND valuenum IS NOT NULL) SELECT subject_id,charttime,hadm_id,itemid, crp_level FROM crp GROUP BY subject_id,hadm_id,charttime,itemid,crp_level
提取物化视图已有的实验室检查(以RDW为例)
SELECT icustays.stay_id , MIN(rdw) as rdw_min , MAX(rdw) as rdw_max , AVG(rdw) as rdw_mean FROM icustays LEFT JOIN complete_blood_count ON complete_blood_count.subject_id = icustays.subject_id AND complete_blood_count.charttime <= DATETIME_ADD(icustays.intime, INTERVAL '1' DAY) GROUP BY icustays.stay_id
常用思路的代码:
WITH icustay_order_view as ( SELECT subject_id,hadm_id, stay_id,intime,outtime,los, row_number() over (partition by subject_id order by intime) as icustay_order FROM "mimic_icu"."icustays" ) select * from icustay_order_view where icustay_order = 1 SELECT * FROM "mimic_icu"."icustays" SELECT * FROM "mimic_icu"."icustays" ORDER BY subject_id
以使用NE病人为例,首先提取一下使用NE(去甲肾上腺素)的病人
DROP TABLE IF EXISTS NE; CREATE TABLE NE AS WITH norE as ( SELECT icustays.subject_id, icustays.hadm_id, icustays.stay_id, icustays.intime,icustays.outtime, mimic_hosp.prescriptions.starttime, mimic_hosp.prescriptions.stoptime, mimic_hosp.prescriptions.drug_type, mimic_hosp.prescriptions.drug, mimic_hosp.prescriptions.dose_val_rx, mimic_hosp.prescriptions.dose_unit_rx, mimic_hosp.prescriptions.form_val_disp, mimic_hosp.prescriptions.form_unit_disp, mimic_hosp.prescriptions.doses_per_24_hrs, mimic_hosp.prescriptions.route, CASE WHEN LOWER(mimic_hosp.prescriptions.drug) LIKE '%orepinephrine%' THEN 1 ELSE 0 END AS norepinephrine FROM icustays LEFT JOIN mimic_hosp.prescriptions ON mimic_hosp.prescriptions.subject_id=icustays.subject_id AND mimic_hosp.prescriptions.hadm_id=icustays.hadm_id) SELECT * from norE where norepinephrine=1 WITH stay_order_view as ( SELECT subject_id,hadm_id, stay_id,norepinephrine, row_number() over (partition by stay_id order by subject_id) as stay_order FROM "mimic_icu"."ne" ) select * from stay_order_view where stay_order = 1
SELECT mi.stay_id,ceil(DATETIME_DIFF(mi.starttime, mic.intime,'DAY')) as date_num, sum(mi.totalamount) FROM "mimic_icu"."inputevents" mi left join "mimic_icu"."icustays" mic on mi.stay_id = mic.stay_id where mi.totalamount is not null and mi.totalamount > 0 and mi.starttime > mic.intime GROUP BY mi.stay_id, date_num
(1)MIMIC数据分析研究建议。
一是做好数据检查,有没有遗漏、有没有重复、有没有多删或少删。基础数据(如首次不小于18岁),然后用left join逐个提取合并。合并前后的数据量变化,一定要做好检查。
二是数据提取用SQL完成,数据清洗与分析用R。对于提取时间很长的数据,如实验数据(甘油三酯)、药品数据等,可把初始数据提取后保存为一个表,后面需要处理用这个表就行。
三是熟悉研究的基本思路,如数据纳排(按结局变量、自变量、协变量的顺序)作为数据选取流程图的依据,是论文的一部分,还有数据格式转化、分类变量等。另外,MIMIC数据分析不涉及权重,与NHANES不同。
(2)课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义;[Day3] 复现论文、复现代码、复现数据、学习资料、讲义[Day4]扩展学习资料和相关源码等。关注公众号“熊大学习社”,回复“mimic01
”,获取全部4天MIMIC复现课程资料链接。
(3)医学公共数据数据库学习训练营已开班,论文指导精英班学员可获取推荐审稿人信息,欢迎咨询课程助理!
(4)数据提取和数据分析定制,具体扫码咨询课程助理。