mimic提取相关的实验室检查信息

创建表

-- 步骤1：创建表存储符合条件的subject_id（避免使用关键字is作为表名，改用stroke_patients_subjects）
CREATE TABLE stroke_patients_subjects AS
SELECT DISTINCT di.subject_id
FROM mimiciv_hosp.diagnoses_icd di
WHERE 
  (di.icd_version = 9 AND di.icd_code LIKE '434%')  -- ICD-9：缺血性卒中相关编码
  OR 
  (di.icd_version = 10 AND di.icd_code LIKE 'I63%');  -- ICD-10：缺血性卒中相关编码（修正为I63%）

-- 步骤2：从创建的表中查询subject_id，可关联patients表获取详细
SELECT 
  s.subject_id,
  p.gender,
  p.anchor_year,  -- 出生日期（脱敏后）
  p.dod   -- 死亡日期（脱敏后，NULL表示存活）
FROM 
  stroke_patients_subjects s
INNER JOIN mimiciv_hosp.patients p 
  ON s.subject_id = p.subject_id
ORDER BY 
  s.subject_id;

查询成功后，添加新的列和字段

-- 步骤1：为stroke_patients_subjects表添加新字段（需匹配patients表字段的数据类型）
ALTER TABLE stroke_patients_subjects
ADD COLUMN gender VARCHAR(5),  -- 性别（patients表中为'F'/'M'等，字符型）
ADD COLUMN anchor_year INTEGER,  -- 基准年份（脱敏后的年份，整数型）
ADD COLUMN dod TIMESTAMPTZ;  -- 死亡时间（时间戳类型，带时区）


-- 步骤2：通过subject_id关联patients表，更新新添加的字段
UPDATE stroke_patients_subjects s
SET 
  gender = p.gender,
  anchor_year = p.anchor_year,
  dod = p.dod
FROM mimiciv_hosp.patients p
WHERE s.subject_id = p.subject_id;  -- 关联条件：患者ID匹配


-- 验证：查询更新后的表
SELECT * FROM stroke_patients_subjects ORDER BY subject_id;



ALTER TABLE stroke_patients_subjects
ADD COLUMN icd_code VARCHAR(5),  
ADD COLUMN hadm_id INTEGER, 

-- 向stroke_patients_subjects表添加符合条件的记录（避免重复）
INSERT INTO stroke_patients_subjects (subject_id, hadm_id, icd_code)
SELECT DISTINCT 
  di.subject_id,
  di.hadm_id,
  di.icd_code
FROM mimiciv_hosp.diagnoses_icd di
-- 筛选条件：ICD-9 434% 或 ICD-10 I63%
WHERE 
  (di.icd_version = 9 AND di.icd_code LIKE '434%')
  OR 
  (di.icd_version = 10 AND di.icd_code LIKE 'I63%')
-- 去重逻辑：仅插入表中不存在的记录（避免重复）
AND NOT EXISTS (
  SELECT 1 
  FROM stroke_patients_subjects s
  WHERE 
    s.subject_id = di.subject_id 
    AND s.hadm_id = di.hadm_id 
    AND s.icd_code = di.icd_code
);

-- 验证：查询表中所有记录，确认新增记录已添加
SELECT * FROM stroke_patients_subjects 
ORDER BY subject_id, hadm_id;

查询卒中患者实验室检查数量

SELECT 'ascites_chemistry' AS test_type, 
​       (SELECT COUNT(*) 
​        FROM stroke_patients_subjects 
​        WHERE hadm_id IN (SELECT hadm_id FROM ascites_chemistry)) AS patient_count
UNION ALL
SELECT 'ascites_hematology' AS test_type, 
​       (SELECT COUNT(*) 
​        FROM stroke_patients_subjects 
​        WHERE hadm_id IN (SELECT hadm_id FROM ascites_hematology)) AS patient_count
UNION ALL
SELECT 'blood_blood_gas' AS test_type, 
​       (SELECT COUNT(*) 
​        FROM stroke_patients_subjects 
​        WHERE hadm_id IN (SELECT hadm_id FROM blood_blood_gas)) AS patient_count
UNION ALL
SELECT 'blood_chemistry' AS test_type, 
​       (SELECT COUNT(*) 
​        FROM stroke_patients_subjects 
​        WHERE hadm_id IN (SELECT hadm_id FROM blood_chemistry)) AS patient_count
UNION ALL
SE