79 lines
2.8 KiB
SQL
79 lines
2.8 KiB
SQL
-- ------------------------------------------------------------------
|
|
-- All-time GCS pivot.
|
|
--
|
|
-- Adapted from the upstream MIMIC-III concepts_postgres file
|
|
-- firstday/gcs_first_day.sql
|
|
-- with the day-1 time predicate removed and the row reduced to one
|
|
-- row per (icustay_id, charttime, gcs) for the entire ICU stay.
|
|
--
|
|
-- The carry-forward logic (impute missing components from the
|
|
-- immediately preceding charttime within 6 h) is preserved. GCS
|
|
-- during sedation/intubation is set to 15, matching upstream.
|
|
-- ------------------------------------------------------------------
|
|
|
|
DROP TABLE IF EXISTS gcs_all;
|
|
CREATE TABLE gcs_all AS
|
|
|
|
WITH base AS
|
|
(
|
|
SELECT pvt.icustay_id
|
|
, pvt.charttime
|
|
, MAX(CASE WHEN pvt.itemid = 454 THEN pvt.valuenum END) AS gcsmotor
|
|
, MAX(CASE WHEN pvt.itemid = 723 THEN pvt.valuenum END) AS gcsverbal
|
|
, MAX(CASE WHEN pvt.itemid = 184 THEN pvt.valuenum END) AS gcseyes
|
|
, CASE
|
|
WHEN MAX(CASE WHEN pvt.itemid = 723 THEN pvt.valuenum END) = 0
|
|
THEN 1 ELSE 0
|
|
END AS endotrachflag
|
|
, ROW_NUMBER() OVER (PARTITION BY pvt.icustay_id ORDER BY pvt.charttime ASC) AS rn
|
|
FROM (
|
|
SELECT l.icustay_id
|
|
, CASE
|
|
WHEN l.itemid IN (723, 223900) THEN 723
|
|
WHEN l.itemid IN (454, 223901) THEN 454
|
|
WHEN l.itemid IN (184, 220739) THEN 184
|
|
ELSE l.itemid
|
|
END AS itemid
|
|
, CASE
|
|
WHEN l.itemid = 723 AND l.value = '1.0 ET/Trach' THEN 0
|
|
WHEN l.itemid = 223900 AND l.value = 'No Response-ETT' THEN 0
|
|
ELSE l.valuenum
|
|
END AS valuenum
|
|
, l.charttime
|
|
FROM chartevents l
|
|
INNER JOIN icustays b
|
|
ON l.icustay_id = b.icustay_id
|
|
WHERE l.itemid IN (184, 454, 723, 223900, 223901, 220739)
|
|
AND l.charttime BETWEEN b.intime AND b.outtime
|
|
AND COALESCE(l.error, 0) = 0
|
|
) pvt
|
|
GROUP BY pvt.icustay_id, pvt.charttime
|
|
)
|
|
, gcs AS
|
|
(
|
|
SELECT b.icustay_id
|
|
, b.charttime
|
|
, CASE
|
|
WHEN b.gcsverbal = 0 THEN 15
|
|
WHEN b.gcsverbal IS NULL AND b2.gcsverbal = 0 THEN 15
|
|
WHEN b2.gcsverbal = 0 THEN
|
|
COALESCE(b.gcsmotor , 6)
|
|
+ COALESCE(b.gcsverbal, 5)
|
|
+ COALESCE(b.gcseyes , 4)
|
|
ELSE
|
|
COALESCE(b.gcsmotor , COALESCE(b2.gcsmotor , 6))
|
|
+ COALESCE(b.gcsverbal, COALESCE(b2.gcsverbal, 5))
|
|
+ COALESCE(b.gcseyes , COALESCE(b2.gcseyes , 4))
|
|
END AS gcs
|
|
, b.endotrachflag
|
|
FROM base b
|
|
LEFT JOIN base b2
|
|
ON b.icustay_id = b2.icustay_id
|
|
AND b.rn = b2.rn + 1
|
|
AND b2.charttime > DATETIME_SUB(b.charttime, INTERVAL '6' HOUR)
|
|
)
|
|
SELECT icustay_id, charttime, gcs, endotrachflag
|
|
FROM gcs;
|
|
|
|
CREATE INDEX IF NOT EXISTS gcs_all_idx ON gcs_all (icustay_id, charttime);
|