pivoted_sofa.sql

-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciii_derived.pivoted_sofa; CREATE TABLE mimiciii_derived.pivoted_sofa AS
/* ------------------------------------------------------------------ */
/* Title: Sequential Organ Failure Assessment (SOFA) */
/* This query extracts the sequential organ failure assessment (formally: sepsis-related organ failure assessment). */
/* This score is a measure of organ failure for patients in the ICU. */
/* The score is calculated for every hour of the patient's ICU stay. */
/* However, as the calculation window is 24 hours, care should be taken when */
/* using the score before the end of the first day. */
/* ------------------------------------------------------------------ */
/* Reference for SOFA: */
/*    Jean-Louis Vincent, Rui Moreno, Jukka Takala, Sheila Willatts, Arnaldo De Mendonça, */
/*    Hajo Bruining, C. K. Reinhart, Peter M Suter, and L. G. Thijs. */
/*    "The SOFA (Sepsis-related Organ Failure Assessment) score to describe organ dysfunction/failure." */
/*    Intensive care medicine 22, no. 7 (1996): 707-710. */
/* Variables used in SOFA: */
/*  GCS, MAP, FiO2, Ventilation status (sourced FROM `physionet-data.mimiciii_clinical.chartevents`) */
/*  Creatinine, Bilirubin, FiO2, PaO2, Platelets (sourced FROM `physionet-data.mimiciii_clinical.labevents`) */
/*  Dopamine, Dobutamine, Epinephrine, Norepinephrine (sourced FROM `physionet-data.mimiciii_clinical.inputevents_mv` and INPUTEVENTS_CV) */
/*  Urine output (sourced from OUTPUTEVENTS) */
/* The following views required to run this query: */
/*  1) pivoted_bg_art - generated by pivoted-bg.sql */
/*  2) pivoted_uo - generated by pivoted-uo.sql */
/*  3) pivoted_lab - generated by pivoted-lab.sql */
/*  4) pivoted_gcs - generated by pivoted-gcs.sql */
/*  5) ventilation_durations - generated by ../durations/ventilation_durations.sql */
/*  6) norepinephrine_dose - generated by ../durations/norepinephrine-dose.sql */
/*  7) epinephrine_dose - generated by ../durations/epinephrine-dose.sql */
/*  8) dopamine_dose - generated by ../durations/dopamine-dose.sql */
/*  9) dobutamine_dose - generated by ../durations/dobutamine-dose.sql */
/* Note: */
/*  The score is calculated for only adult ICU patients, */
/* generate a row for every hour the patient was in the ICU */
WITH co AS (
  SELECT
    ih.icustay_id,
    ie.hadm_id,
    hr, /* start/endtime can be used to filter to values within this hour */
    ih.endtime - INTERVAL '1' HOUR AS starttime,
    ih.endtime
  FROM mimiciii_derived.icustay_hours AS ih
  INNER JOIN mimiciii.icustays AS ie
    ON ih.icustay_id = ie.icustay_id
), bp /* get minimum blood pressure FROM `physionet-data.mimiciii_clinical.chartevents` */ AS (
  SELECT
    ce.icustay_id,
    ce.charttime,
    MIN(valuenum) AS meanbp_min
  FROM mimiciii.chartevents AS ce
  /* exclude rows marked as error */
  WHERE
    (
      ce.error IS NULL OR ce.error <> 1
    )
    AND ce.itemid IN (
      456, /* MEAN ARTERIAL PRESSURE */ /* "NBP Mean" */
      52, /* "Arterial BP Mean" */
      6702, /*	Arterial BP Mean #2 */
      443, /*	Manual BP Mean(calc) */
      220052, /* "Arterial Blood Pressure mean" */
      220181, /* "Non Invasive Blood Pressure mean" */
      225312 /* "ART BP mean" */
    )
    AND valuenum > 0
    AND valuenum < 300
  GROUP BY
    ce.icustay_id,
    ce.charttime
), pafi AS (
  /* join blood gas to ventilation durations to determine if patient was vent */
  SELECT
    ie.icustay_id,
    bg.charttime, /* because pafi has an interaction between vent/PaO2:FiO2, we need two columns for the score */ /* it can happen that the lowest unventilated PaO2/FiO2 is 68, but the lowest ventilated PaO2/FiO2 is 120 */ /* in this case, the SOFA score is 3, *not* 4. */
    CASE WHEN vd.icustay_id IS NULL THEN pao2fio2ratio ELSE NULL END AS pao2fio2ratio_novent,
    CASE WHEN NOT vd.icustay_id IS NULL THEN pao2fio2ratio ELSE NULL END AS pao2fio2ratio_vent
  FROM mimiciii.icustays AS ie
  INNER JOIN mimiciii_derived.pivoted_bg_art AS bg
    ON ie.icustay_id = bg.icustay_id
  LEFT JOIN mimiciii_derived.ventilation_durations AS vd
    ON ie.icustay_id = vd.icustay_id
    AND bg.charttime >= vd.starttime
    AND bg.charttime <= vd.endtime
), mini_agg AS (
  SELECT
    co.icustay_id,
    co.hr, /* vitals */
    MIN(bp.meanbp_min) AS meanbp_min, /* gcs */
    MIN(gcs.GCS) AS GCS_min, /* labs */
    MAX(labs.bilirubin) AS bilirubin_max,
    MAX(labs.creatinine) AS creatinine_max,
    MIN(labs.platelet) AS platelet_min, /* because pafi has an interaction between vent/PaO2:FiO2, we need two columns for the score */ /* it can happen that the lowest unventilated PaO2/FiO2 is 68, but the lowest ventilated PaO2/FiO2 is 120 */ /* in this case, the SOFA score is 3, *not* 4. */
    MIN(CASE WHEN vd.icustay_id IS NULL THEN pao2fio2ratio ELSE NULL END) AS pao2fio2ratio_novent,
    MIN(CASE WHEN NOT vd.icustay_id IS NULL THEN pao2fio2ratio ELSE NULL END) AS pao2fio2ratio_vent
  FROM co
  LEFT JOIN bp
    ON co.icustay_id = bp.icustay_id
    AND co.starttime < bp.charttime
    AND co.endtime >= bp.charttime
  LEFT JOIN mimiciii_derived.pivoted_gcs AS gcs
    ON co.icustay_id = gcs.icustay_id
    AND co.starttime < gcs.charttime
    AND co.endtime >= gcs.charttime
  LEFT JOIN mimiciii_derived.pivoted_lab AS labs
    ON co.hadm_id = labs.hadm_id
    AND co.starttime < labs.charttime
    AND co.endtime >= labs.charttime
  /* bring in blood gases that occurred during this hour */
  LEFT JOIN mimiciii_derived.pivoted_bg_art AS bg
    ON co.icustay_id = bg.icustay_id
    AND co.starttime < bg.charttime
    AND co.endtime >= bg.charttime
  /* at the time of the blood gas, determine if patient was ventilated */
  LEFT JOIN mimiciii_derived.ventilation_durations AS vd
    ON co.icustay_id = vd.icustay_id
    AND bg.charttime >= vd.starttime
    AND bg.charttime <= vd.endtime
  GROUP BY
    co.icustay_id,
    co.hr
), uo /* sum uo separately to prevent duplicating values */ AS (
  SELECT
    co.icustay_id,
    co.hr, /* uo */
    SUM(uo.urineoutput) AS urineoutput
  FROM co
  LEFT JOIN mimiciii_derived.pivoted_uo AS uo
    ON co.icustay_id = uo.icustay_id
    AND co.starttime < uo.charttime
    AND co.endtime >= uo.charttime
  GROUP BY
    co.icustay_id,
    co.hr
), scorecomp AS (
  SELECT
    co.icustay_id,
    co.hr,
    co.starttime,
    co.endtime,
    ma.pao2fio2ratio_novent,
    ma.pao2fio2ratio_vent,
    epi.vaso_rate AS rate_epinephrine,
    nor.vaso_rate AS rate_norepinephrine,
    dop.vaso_rate AS rate_dopamine,
    dob.vaso_rate AS rate_dobutamine,
    ma.meanbp_min,
    ma.GCS_min, /* uo */
    uo.urineoutput, /* labs */
    ma.bilirubin_max,
    ma.creatinine_max,
    ma.platelet_min
  FROM co
  LEFT JOIN mini_agg AS ma
    ON co.icustay_id = ma.icustay_id AND co.hr = ma.hr
  LEFT JOIN uo
    ON co.icustay_id = uo.icustay_id AND co.hr = uo.hr
  LEFT JOIN pafi
    ON co.icustay_id = pafi.icustay_id
    AND co.starttime < pafi.charttime
    AND co.endtime >= pafi.charttime
  LEFT JOIN mimiciii_derived.epinephrine_dose AS epi
    ON co.icustay_id = epi.icustay_id
    AND co.endtime > epi.starttime
    AND co.endtime <= epi.endtime
  LEFT JOIN mimiciii_derived.norepinephrine_dose AS nor
    ON co.icustay_id = nor.icustay_id
    AND co.endtime > nor.starttime
    AND co.endtime <= nor.endtime
  LEFT JOIN mimiciii_derived.dopamine_dose AS dop
    ON co.icustay_id = dop.icustay_id
    AND co.endtime > dop.starttime
    AND co.endtime <= dop.endtime
  LEFT JOIN mimiciii_derived.dobutamine_dose AS dob
    ON co.icustay_id = dob.icustay_id
    AND co.endtime > dob.starttime
    AND co.endtime <= dob.endtime
), scorecalc AS (
  /* Calculate the final score */
  /* note that if the underlying data is missing, the component is null */
  /* eventually these are treated as 0 (normal), but knowing when data is missing is useful for debugging */
  SELECT
    scorecomp.*, /* Respiration */
    CAST(CASE
      WHEN pao2fio2ratio_vent < 100
      THEN 4
      WHEN pao2fio2ratio_vent < 200
      THEN 3
      WHEN pao2fio2ratio_novent < 300
      THEN 2
      WHEN pao2fio2ratio_novent < 400
      THEN 1
      WHEN COALESCE(pao2fio2ratio_vent, pao2fio2ratio_novent) IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS respiration, /* Coagulation */
    CAST(CASE
      WHEN platelet_min < 20
      THEN 4
      WHEN platelet_min < 50
      THEN 3
      WHEN platelet_min < 100
      THEN 2
      WHEN platelet_min < 150
      THEN 1
      WHEN platelet_min IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS coagulation, /* Liver */
    CAST(CASE
      WHEN Bilirubin_Max >= 12.0
      THEN 4
      WHEN Bilirubin_Max >= 6.0
      THEN 3
      WHEN Bilirubin_Max >= 2.0
      THEN 2
      WHEN Bilirubin_Max >= 1.2
      THEN 1
      WHEN Bilirubin_Max IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS liver, /* Cardiovascular */
    CAST(CASE
      WHEN rate_dopamine > 15 OR rate_epinephrine > 0.1 OR rate_norepinephrine > 0.1
      THEN 4
      WHEN rate_dopamine > 5
      OR (
        rate_epinephrine > 0 AND rate_epinephrine <= 0.1
      )
      OR (
        rate_norepinephrine > 0 AND rate_norepinephrine <= 0.1
      )
      THEN 3
      WHEN rate_dopamine > 0 OR rate_dobutamine > 0
      THEN 2
      WHEN meanbp_min < 70
      THEN 1
      WHEN COALESCE(meanbp_min, rate_dopamine, rate_dobutamine, rate_epinephrine, rate_norepinephrine) IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS cardiovascular, /* Neurological failure (GCS) */
    CAST(CASE
      WHEN (
        GCS_min >= 13 AND GCS_min <= 14
      )
      THEN 1
      WHEN (
        GCS_min >= 10 AND GCS_min <= 12
      )
      THEN 2
      WHEN (
        GCS_min >= 6 AND GCS_min <= 9
      )
      THEN 3
      WHEN GCS_min < 6
      THEN 4
      WHEN GCS_min IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS cns, /* Renal failure - high creatinine or low urine output */
    CAST(CASE
      WHEN (
        Creatinine_Max >= 5.0
      )
      THEN 4
      WHEN SUM(urineoutput) OVER W < 200
      THEN 4
      WHEN (
        Creatinine_Max >= 3.5 AND Creatinine_Max < 5.0
      )
      THEN 3
      WHEN SUM(urineoutput) OVER W < 500
      THEN 3
      WHEN (
        Creatinine_Max >= 2.0 AND Creatinine_Max < 3.5
      )
      THEN 2
      WHEN (
        Creatinine_Max >= 1.2 AND Creatinine_Max < 2.0
      )
      THEN 1
      WHEN COALESCE(SUM(urineoutput) OVER W, Creatinine_Max) IS NULL
      THEN NULL
      ELSE 0
    END AS SMALLINT) AS renal
  FROM scorecomp
  WINDOW W AS (
    PARTITION BY icustay_id
    ORDER BY hr NULLS FIRST
    ROWS BETWEEN 23 PRECEDING AND 0 FOLLOWING
  )
), score_final AS (
  SELECT
    s.*, /* Combine all the scores to get SOFA */ /* Impute 0 if the score is missing */ /* the window function takes the max over the last 24 hours */
    CAST(COALESCE(
      MAX(respiration) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS respiration_24hours,
    CAST(COALESCE(
      MAX(coagulation) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS coagulation_24hours,
    CAST(COALESCE(
      MAX(liver) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS liver_24hours,
    CAST(COALESCE(
      MAX(cardiovascular) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS cardiovascular_24hours,
    CAST(COALESCE(
      MAX(cns) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS cns_24hours,
    CAST(COALESCE(
      MAX(renal) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS renal_24hours, /* sum together data for final SOFA */
    COALESCE(
      MAX(respiration) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) + COALESCE(
      MAX(coagulation) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) + COALESCE(
      MAX(liver) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) + COALESCE(
      MAX(cardiovascular) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) + COALESCE(
      MAX(cns) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) + CAST(COALESCE(
      MAX(renal) OVER (
        PARTITION BY icustay_id
        ORDER BY HR NULLS FIRST
        ROWS BETWEEN 24 PRECEDING AND 0 FOLLOWING
      ),
      0
    ) AS SMALLINT) AS sofa_24hours
  FROM scorecalc AS s
  WINDOW W AS (
    PARTITION BY icustay_id
    ORDER BY hr NULLS FIRST
    ROWS BETWEEN 23 PRECEDING AND 0 FOLLOWING
  )
)
SELECT
  *
FROM score_final
WHERE
  hr >= 0
ORDER BY
  icustay_id NULLS FIRST,
  hr NULLS FIRST