91 lines
2.8 KiB
SQL
91 lines
2.8 KiB
SQL
-- ------------------------------------------------------------------
|
|
-- Title: Sepsis-3 onset
|
|
--
|
|
-- Adapted from the MIMIC-IV upstream
|
|
-- concepts/sepsis/sepsis3.sql
|
|
-- and ported to MIMIC-III v1.3 vanilla PostgreSQL.
|
|
--
|
|
-- Definition (Singer et al., JAMA 2016):
|
|
-- Sepsis-3 = SOFA >= 2 AND suspicion of infection,
|
|
-- where the SOFA window must overlap the suspected-infection time
|
|
-- by at most 48 h before / 24 h after.
|
|
-- The "onset time" is the suspected-infection-time of the earliest
|
|
-- row that satisfies these criteria for each ICU stay.
|
|
--
|
|
-- Implicitly assumes baseline SOFA = 0 prior to ICU admission, since
|
|
-- we do not have premorbid organ-dysfunction data.
|
|
--
|
|
-- Dependencies:
|
|
-- sepsis/suspicion_of_infection.sql
|
|
-- sepsis/sofa_hourly.sql
|
|
-- ------------------------------------------------------------------
|
|
|
|
DROP TABLE IF EXISTS sepsis3;
|
|
CREATE TABLE sepsis3 AS
|
|
|
|
WITH sofa AS
|
|
(
|
|
SELECT icustay_id
|
|
, starttime
|
|
, endtime
|
|
, respiration_24hours AS respiration
|
|
, coagulation_24hours AS coagulation
|
|
, liver_24hours AS liver
|
|
, cardiovascular_24hours AS cardiovascular
|
|
, cns_24hours AS cns
|
|
, renal_24hours AS renal
|
|
, sofa_24hours AS sofa_score
|
|
FROM sofa_hourly
|
|
WHERE sofa_24hours >= 2
|
|
)
|
|
, s1 AS
|
|
(
|
|
SELECT soi.subject_id
|
|
, soi.icustay_id
|
|
, soi.ab_id
|
|
, soi.antibiotic
|
|
, soi.antibiotic_time
|
|
, soi.culture_time
|
|
, soi.suspected_infection
|
|
, soi.suspected_infection_time
|
|
, soi.specimen
|
|
, soi.positive_culture
|
|
, sofa.starttime
|
|
, sofa.endtime
|
|
, sofa.respiration
|
|
, sofa.coagulation
|
|
, sofa.liver
|
|
, sofa.cardiovascular
|
|
, sofa.cns
|
|
, sofa.renal
|
|
, sofa.sofa_score
|
|
, (sofa.sofa_score >= 2 AND soi.suspected_infection = 1) AS sepsis3
|
|
, ROW_NUMBER() OVER (
|
|
PARTITION BY soi.icustay_id
|
|
ORDER BY soi.suspected_infection_time
|
|
, soi.antibiotic_time
|
|
, soi.culture_time
|
|
, sofa.endtime
|
|
) AS rn_sus
|
|
FROM suspicion_of_infection soi
|
|
INNER JOIN sofa
|
|
ON soi.icustay_id = sofa.icustay_id
|
|
AND sofa.endtime >= DATETIME_SUB(soi.suspected_infection_time, INTERVAL '48' HOUR)
|
|
AND sofa.endtime <= DATETIME_ADD(soi.suspected_infection_time, INTERVAL '24' HOUR)
|
|
WHERE soi.icustay_id IS NOT NULL
|
|
AND soi.suspected_infection_time IS NOT NULL
|
|
)
|
|
SELECT subject_id
|
|
, icustay_id
|
|
, antibiotic_time
|
|
, culture_time
|
|
, suspected_infection_time
|
|
, endtime AS sofa_time
|
|
, sofa_score
|
|
, respiration, coagulation, liver, cardiovascular, cns, renal
|
|
, sepsis3
|
|
FROM s1
|
|
WHERE rn_sus = 1;
|
|
|
|
CREATE INDEX IF NOT EXISTS sepsis3_idx ON sepsis3 (icustay_id);
|