发布于 2024-04-18 11:23:21

MIMIC-IV数据库，如何查询脑梗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')