MIMIC2V26.ICUSTAY_DETAIL

Columns

Query:

Note: Syntax highlighting off

WITH WaveformMatches as (
  select icu.subject_id, count(pm.param2_str) as matched_waveforms_num
    from mimic2v26.d_patients icu
    join (select param1_num, param2_str
                       from mimic2v26.parameter_mapping
                      where category = 'SUBJECT_ID_TO_CASE_ID'
                       ) pm
      on (icu.subject_id = pm.param1_num)
  GROUP BY icu.subject_id
)
--select * from WaveformMatches
, RawData AS (
	SELECT DISTINCT i.subject_id, p.sex AS gender, p.dob,
    nvl2(p.dod, 'Y', 'N') AS expire_flg,
    p.dod,
    had.hadm_id,
    CASE
      WHEN ( had.hadm_id = first_value(had.hadm_id) OVER (PARTITION BY i.subject_id ORDER BY had.admit_dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) )
      THEN 'Y'
      ELSE 'N'
    END AS hospital_first_flg,
    CASE
      WHEN ( had.hadm_id = last_value(had.hadm_id) OVER (PARTITION BY i.subject_id ORDER BY had.admit_dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) )
      THEN 'Y'
      ELSE 'N'
    END                                      AS hospital_last_flg,
    had.admit_dt                             AS hospital_admit_dt,
    had.disch_dt                             AS hospital_disch_dt,
--    months_between(had.admit_dt, p.dob) / 12 AS hospital_admit_age,
--    CASE
--      WHEN had.admit_dt is null
--      THEN null
--      WHEN (months_between(had.admit_dt, p.dob) / 12) >= 15
--      THEN 'adult'
--      WHEN (months_between(had.admit_dt, p.dob) / 12) <= 1
--      THEN 'neonate'
--      ELSE 'middle'
--    END                                     AS hospital_age_group,
    (had.disch_dt - had.admit_dt) * 24 * 60 AS hospital_los,
    CASE
      WHEN had.hadm_id is null
      THEN null
      WHEN (had.hadm_id = last_value(had.hadm_id IGNORE NULLS) OVER (PARTITION BY i.subject_id ORDER BY had.admit_dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AND p.hospital_expire_flg = 'Y')
      THEN 'Y'
      ELSE 'N'
    END                                     AS hospital_expire_flg,
    i.icustay_id,
    CASE
      WHEN ( i.icustay_id = first_value(i.icustay_id) OVER (PARTITION BY i.subject_id, had.hadm_id ORDER BY i.intime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) )
      THEN 'Y'
      ELSE 'N'
    END AS icustay_first_flg,
    CASE
      WHEN ( i.icustay_id = last_value(i.icustay_id) OVER (PARTITION BY i.subject_id, had.hadm_id ORDER BY i.intime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) )
      THEN 'Y'
      ELSE 'N'
    END                                                                                                                                                                                                                                                        AS icustay_last_flg,
    i.intime                                                                                                                                                                                                                                                   AS icustay_intime,
    i.outtime                                                                                                                                                                                                                                                  AS icustay_outtime,
    i.los                                                                                                                                                                                                                                                      AS icustay_los,
    first_value(DECODE(NVL(ce.value1, 'Unknown'), 'CSICU', 'SICU', 'NSICU', 'SICU', 'MSICU', 'MICU', NVL(ce.value1, 'Unknown'))) over (partition BY i.subject_id, i.icustay_id order by ce.charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS icustay_first_service,
    last_value(DECODE(NVL(ce.value1, 'Unknown'), 'CSICU', 'SICU', 'NSICU', 'SICU', 'MSICU', 'MICU', NVL(ce.value1, 'Unknown'))) over (partition BY i.subject_id, i.icustay_id order by ce.charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  AS icustay_last_service,
    DECODE(NVL(cuf.label, 'Unknown'), 'C-SICU', 'SICU', 'T-SICU', 'SICU', 'CSRU-A', 'CSRU', 'CSRU-B', 'CSRU', 'M-CCU' , 'CCU', 'MICU-' , 'MICU', 'MSICU' , 'MICU', 'MICU-EAST' , 'MICU', 'MICU-A' , 'MICU', NVL(cuf.label, 'Unknown'))                         AS icustay_first_careunit,
    DECODE(NVL(cul.label, 'Unknown'), 'C-SICU', 'SICU', 'T-SICU', 'SICU', 'CSRU-A', 'CSRU', 'CSRU-B', 'CSRU', 'M-CCU' , 'CCU', 'MICU-' , 'MICU', 'MSICU' , 'MICU', 'MICU-EAST' , 'MICU', 'MICU-A' , 'MICU', NVL(cul.label, 'Unknown'))                         AS icustay_last_careunit,
    months_between(i.intime, p.dob) / 12                                                                                                                                                                                                                       AS icustay_admit_age,
    CASE
      WHEN (months_between(i.intime, p.dob) / 12) >= 15
      THEN 'adult'
      WHEN (months_between(i.intime, p.dob) / 12) <= 1
      THEN 'neonate'
      ELSE 'middle'
    END AS icustay_age_group,
    CASE
      WHEN (p.dod               IS NOT NULL)
      AND i.icustay_id = last_value(i.icustay_id) OVER (PARTITION BY i.subject_id ORDER BY i.outtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      AND p.hospital_expire_flg = 'Y'
      AND (i.outtime            >= had.disch_dt)
      THEN 'Y'
      ELSE 'N'
    END AS icustay_expire_flg,
    wm.matched_waveforms_num
  FROM mimic2v26.icustayevents i
  JOIN mimic2v26.d_patients p ON (p.subject_id = i.subject_id)
  LEFT OUTER JOIN mimic2v26.d_careunits cuf ON (cuf.cuid = i.first_careunit)
  LEFT OUTER JOIN mimic2v26.d_careunits cul ON (cul.cuid = i.last_careunit)
  LEFT OUTER JOIN mimic2v26.admissions had ON (had.subject_id = i.subject_id AND i.intime BETWEEN had.admit_dt AND had.disch_dt)
  LEFT OUTER JOIN mimic2v26.chartevents ce ON (ce.subject_id = i.subject_id AND ce.itemid = 1125 AND ce.charttime BETWEEN i.intime AND i.outtime)
  LEFT OUTER JOIN WaveformMatches wm ON (i.subject_id = wm.subject_id)
  --WHERE i.subject_id < 100
  --WHERE i.subject_id = 14611
  --WHERE i.subject_id = 1594
  --WHERE i.subject_id in (10,21,31,68,91,94)
    --       AND i.subject_id in (13, 17, 21, 41, 61, 68, 91, 109, 377, 4412, 21369)
    --       AND i.subject_id in (13, 17, 21)
    --       i.subject_id < 10
--  ORDER BY i.subject_id,
--    had.hadm_id
  )
  --select * from RawData;
  ,
  ICUStays AS
  (SELECT subject_id,
    gender,
    dob,
    dod,
    expire_flg,
    COUNT(DISTINCT icustay_id) OVER (PARTITION BY subject_id)           AS subject_icustay_total_num, -- Subject's number of icustays
    row_number() OVER (PARTITION BY subject_id ORDER BY icustay_intime) AS subject_icustay_seq,       -- Order of subject's icustays
    COUNT(DISTINCT hadm_id) OVER (PARTITION BY subject_id)              AS hospital_total_num,        -- Subject's number of hospital admissions
    hadm_id,
    CASE
      WHEN hadm_id is null
      THEN null
      ELSE dense_rank() OVER (PARTITION BY subject_id ORDER BY hospital_admit_dt) -- Order of subject's hospital admissions
    END AS hospital_seq,
    hospital_first_flg,
    hospital_last_flg,
    hospital_admit_dt,
    hospital_disch_dt,
--    hospital_admit_age,
--    hospital_age_group,
    hospital_los,
    hospital_expire_flg,
    COUNT(DISTINCT icustay_id) OVER (PARTITION BY subject_id, hadm_id) AS icustay_total_num, -- Admission icustays
    icustay_id,
    row_number() OVER (PARTITION BY subject_id, hadm_id ORDER BY icustay_intime) AS icustay_seq, -- Order of admission's icustays
    icustay_first_flg,
    icustay_last_flg,
    icustay_intime,
    icustay_outtime,
    icustay_admit_age,
    icustay_age_group,
    icustay_los,
    icustay_expire_flg,
    icustay_first_careunit,
    icustay_last_careunit,
    CASE
      WHEN icustay_first_service = 'Unknown'
      THEN icustay_first_careunit
      ELSE icustay_first_service
    END AS icustay_first_service,
    CASE
      WHEN icustay_last_service = 'Unknown'
      THEN icustay_last_careunit
      ELSE icustay_last_service
    END AS icustay_last_service,
    matched_waveforms_num
  FROM RawData
--  ORDER BY subject_id,
--    icustay_intime
  )
  --select * from IcuStays;
  -- For the following parameter, we only took the first measurement of
  -- the ICU stay.
  --
, FirstVRawData AS
  (SELECT c.subject_id,
    c.icustay_id,
    c.charttime,
    c.itemid,
    CASE
      WHEN c.itemid IN (762, 763, 3723, 3580, 3581, 3582)
      THEN 'WEIGHT'
      WHEN c.itemid IN (920, 1394, 4187, 3486, 3485, 4188)
      THEN 'HEIGHT'
      WHEN c.itemid IN (20001)
      THEN 'SAPS-I'
      WHEN c.itemid IN (20009)
      THEN 'SOFA'
    END AS parameter,
    CASE
      WHEN c.itemid   IN (3581)
      THEN c.value1num * 0.45359237
      WHEN c.itemid   IN (3582)
      THEN c.value1num * 0.0283495231
      WHEN c.itemid   IN (920, 1394, 4187, 3486)
      THEN c.value1num * 2.54
      ELSE c.value1num
    END AS valuenum
  FROM mimic2v26.chartevents c
  WHERE c.value1num   IS NOT NULL
  AND ( ( c.itemid  IN (762, 763, 3723, 3580, -- Weight Kg
    3581,                                     -- Weight lb
    3582,                                     -- Weight oz
    920, 1394, 4187, 3486,                    -- Height inches
    3485, 4188                                -- Height cm
    )
  AND c.value1num <> 0 )
  OR c.itemid     IN (20001, -- SAPS-I
    20009                    -- SOFA
    ) )
  AND c.icustay_id IS NOT NULL
  )
--select * from FirstVRawData;--7154
, SingleParameters AS (
  SELECT DISTINCT subject_id,
         icustay_id,
         parameter,
         first_value(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_valuenum,
         MIN(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS min_valuenum,
         MAX(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS max_valuenum
    FROM FirstVRawData
--   ORDER BY subject_id,
--            icustay_id,
--            parameter
  )
--select * from SingleParameters
, PivotParameters AS (SELECT subject_id,
    icustay_id,
    MAX(DECODE(parameter, 'HEIGHT', first_valuenum, NULL)) AS height_first,
    MAX(DECODE(parameter, 'HEIGHT', min_valuenum, NULL))   AS height_min,
    MAX(DECODE(parameter, 'HEIGHT', max_valuenum, NULL))   AS height_max,
    MAX(DECODE(parameter, 'WEIGHT', first_valuenum, NULL)) AS weight_first,
    MAX(DECODE(parameter, 'WEIGHT', min_valuenum, NULL))   AS weight_min,
    MAX(DECODE(parameter, 'WEIGHT', max_valuenum, NULL))   AS weight_max,
    MAX(DECODE(parameter, 'SAPS-I', first_valuenum, NULL)) AS sapsi_first,
    MAX(DECODE(parameter, 'SAPS-I', min_valuenum, NULL))   AS sapsi_min,
    MAX(DECODE(parameter, 'SAPS-I', max_valuenum, NULL))   AS sapsi_max,
    MAX(DECODE(parameter, 'SOFA', first_valuenum, NULL))   AS sofa_first,
    MAX(DECODE(parameter, 'SOFA', min_valuenum, NULL))     AS sofa_min,
    MAX(DECODE(parameter, 'SOFA', max_valuenum, NULL))     AS sofa_max
  FROM SingleParameters
  GROUP BY subject_id,
    icustay_id
  )
--select * from PivotParameters
SELECT  CAST(i.icustay_id AS NUMBER(7,0)) as icustay_id,
  CAST(i.subject_id AS NUMBER(7,0)) as subject_id,
  i.gender,
  i.dob,
  i.dod,
  i.expire_flg,
  i.subject_icustay_total_num,
  i.subject_icustay_seq,
  --
  CAST(i.hadm_id AS NUMBER(7,0)) as hadm_id,
  i.hospital_total_num,
  i.hospital_seq,
  i.hospital_first_flg,
  i.hospital_last_flg,
  i.hospital_admit_dt,
  i.hospital_disch_dt,
--  ROUND(i.hospital_admit_age, 5) AS hospital_admit_age,
--  i.hospital_age_group,
  i.hospital_los,
  i.hospital_expire_flg,
  --
  i.icustay_total_num,
  i.icustay_seq,
  i.icustay_first_flg,
  i.icustay_last_flg,
  i.icustay_intime,
  i.icustay_outtime,
  ROUND(i.icustay_admit_age, 5) AS icustay_admit_age,
  i.icustay_age_group,
  i.icustay_los,
  i.icustay_expire_flg,
  i.icustay_first_careunit,
  i.icustay_last_careunit,
  i.icustay_first_service,
  i.icustay_last_service,
  ROUND(f.height_first, 2) AS height,
  ROUND(f.weight_first, 2) AS weight_first,
  ROUND(f.weight_min, 2)   AS weight_min,
  ROUND(f.weight_max, 2)   AS weight_max,
  f.sapsi_first,
  f.sapsi_min,
  f.sapsi_max,
  f.sofa_first,
  f.sofa_min,
  f.sofa_max,
  i.matched_waveforms_num
FROM ICUStays i
LEFT OUTER JOIN PivotParameters f ON (i.icustay_id = f.icustay_id)