8 脓毒症队列-mimiciv
postgresql数据库：http://www.mmphcrc.com:5050/
2024-02-29 15:54:13 CST
8.1 生成mimiciv-derived里的表
8.1.1 探索实践
去这里找：https://github.com/MIT-LCP/mimic-code/releases
2024-02-29 21:35:37 CST 没有答案
去这里找： https://github.com/alistairewj/sepsis3-mimic/issues/19、https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/sepsis/sepsis3.sql、https://github.com/microsoft/mimic_sepsis
2024-03-01 00:23:03 CST 没用。
8.1.2 成功实践
还是要用到官网github提供的代码（已下载至项目）
8.1.2.1 psql终端一键完成（未测试）
cd mimic-code/mimic-iv/concepts_postgres
psql
\i postgres-make-concepts.sql # 批量执行脚本
\q # 退出8.1.2.2 在pgAdmin分步执行
安装顺序
-- dependencies
\i demographics/icustay_times.sql
\i demographics/icustay_hourly.sql
\i demographics/weight_durations.sql
\i measurement/urine_output.sql
\i organfailure/kdigo_uo.sql
-- demographics
\i demographics/age.sql
\i demographics/icustay_detail.sql
-- measurement
\i measurement/bg.sql
\i measurement/blood_differential.sql
\i measurement/cardiac_marker.sql
\i measurement/chemistry.sql
\i measurement/coagulation.sql
\i measurement/complete_blood_count.sql
\i measurement/creatinine_baseline.sql
\i measurement/enzyme.sql
\i measurement/gcs.sql
\i measurement/height.sql
\i measurement/icp.sql
\i measurement/inflammation.sql
\i measurement/oxygen_delivery.sql
\i measurement/rhythm.sql
\i measurement/urine_output_rate.sql
\i measurement/ventilator_setting.sql
\i measurement/vitalsign.sql
-- comorbidity
\i comorbidity/charlson.sql
-- medication
\i medication/antibiotic.sql
\i medication/dobutamine.sql
\i medication/dopamine.sql
\i medication/epinephrine.sql
\i medication/milrinone.sql
\i medication/neuroblock.sql
\i medication/norepinephrine.sql
\i medication/phenylephrine.sql
\i medication/vasopressin.sql
-- treatment
\i treatment/crrt.sql
\i treatment/invasive_line.sql
\i treatment/rrt.sql
\i treatment/ventilation.sql
-- firstday
\i firstday/first_day_bg.sql
\i firstday/first_day_bg_art.sql
\i firstday/first_day_gcs.sql
\i firstday/first_day_height.sql
\i firstday/first_day_lab.sql
\i firstday/first_day_rrt.sql
\i firstday/first_day_urine_output.sql
\i firstday/first_day_vitalsign.sql
\i firstday/first_day_weight.sql
-- organfailure
\i organfailure/kdigo_creatinine.sql
\i organfailure/meld.sql
-- score
\i score/apsiii.sql
\i score/lods.sql
\i score/oasis.sql
\i score/sapsii.sql
\i score/sirs.sql
\i score/sofa.sql
-- sepsis
\i sepsis/suspicion_of_infection.sql
-- final tables which were dependent on one or more prior tables
\i organfailure/kdigo_stages.sql
\i firstday/first_day_sofa.sql
\i sepsis/sepsis3.sql
\i medication/vasoactive_agent.sql
\i medication/norepinephrine_equivalent_dose.sql文件下载
sql文件存放目录为：
/var/lib/pgadmin/storage/hulinhui_live.cn/，通过find / -name "step-001-set_path.sql命令可查找到该存放路径。
- 下载路径：sepsis_sql.zip
step-001 设置路径
每次打开paAdmin都要执行1次，或者放在每个sql查询代码的开头
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;step-002 创建函数
bigquery有，但postgres没有的函数。每次新打开pgAdmin时均需要执行一次。
-- Functions TODO:
-- FROM table CROSS JOIN UNNEST(table.column) AS col -> ???? (see icustay-hours)
-- ???(column) -> PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) (not sure how to do median in BQ)
-- Set the search_path so all functions are generated on the mimiciv_derived schema
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
CREATE OR REPLACE FUNCTION REGEXP_EXTRACT(str TEXT, pattern TEXT) RETURNS TEXT AS $$
BEGIN
RETURN substring(str from pattern);
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION REGEXP_CONTAINS(str TEXT, pattern TEXT) RETURNS BOOL AS $$
BEGIN
RETURN str ~ pattern;
END; $$
LANGUAGE PLPGSQL;
-- alias generate_series with generate_array
CREATE OR REPLACE FUNCTION GENERATE_ARRAY(i INTEGER, j INTEGER)
RETURNS setof INTEGER language sql as $$
SELECT GENERATE_SERIES(i, j)
$$;
-- datetime functions
CREATE OR REPLACE FUNCTION DATETIME(dt DATE) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN CAST(dt AS TIMESTAMP(3));
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION DATETIME(year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minute INTEGER, second INTEGER) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN TO_TIMESTAMP(
TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),
'yyyymmddHH24MISS'
);
END; $$
LANGUAGE PLPGSQL;
-- note: in bigquery, `INTERVAL 1 YEAR` is a valid interval
-- but in postgres, it must be `INTERVAL '1' YEAR`
-- DATETIME_ADD(datetime, INTERVAL 'n' DATEPART) -> datetime + INTERVAL 'n' DATEPART
CREATE OR REPLACE FUNCTION DATETIME_ADD(datetime_val TIMESTAMP(3), intvl INTERVAL) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN datetime_val + intvl;
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION DATE_ADD(dt DATE, intvl INTERVAL) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN CAST(dt AS TIMESTAMP(3)) + intvl;
END; $$
LANGUAGE PLPGSQL;
-- DATETIME_SUB(datetime, INTERVAL 'n' DATEPART) -> datetime - INTERVAL 'n' DATEPART
CREATE OR REPLACE FUNCTION DATETIME_SUB(datetime_val TIMESTAMP(3), intvl INTERVAL) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN datetime_val - intvl;
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION DATE_SUB(dt DATE, intvl INTERVAL) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN CAST(dt AS TIMESTAMP(3)) - intvl;
END; $$
LANGUAGE PLPGSQL;
-- TODO:
-- DATETIME_TRUNC(datetime, PART) -> DATE_TRUNC('datepart', datetime)
-- below requires a regex to convert datepart from primitive to a string
-- i.e. encapsulate it in single quotes
CREATE OR REPLACE FUNCTION DATETIME_DIFF(endtime TIMESTAMP(3), starttime TIMESTAMP(3), datepart TEXT) RETURNS NUMERIC AS $$
BEGIN
RETURN
EXTRACT(EPOCH FROM endtime - starttime) /
CASE
WHEN datepart = 'SECOND' THEN 1.0
WHEN datepart = 'MINUTE' THEN 60.0
WHEN datepart = 'HOUR' THEN 3600.0
WHEN datepart = 'DAY' THEN 24*3600.0
WHEN datepart = 'YEAR' THEN 365.242*24*3600.0
ELSE NULL END;
END; $$
LANGUAGE PLPGSQL;
-- BigQuery has a custom data type, PART
-- It's difficult to replicate this in postgresql, which recognizes the PART as a column name,
-- unless it is within an EXTRACT() function.
CREATE OR REPLACE FUNCTION BIGQUERY_FORMAT_TO_PSQL(format_str VARCHAR(255)) RETURNS TEXT AS $$
BEGIN
RETURN
-- use replace to convert BigQuery string format to postgres string format
-- only handles a few cases since we don't extensively use this function
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
format_str
, '%S', 'SS'
)
, '%M', 'MI'
)
, '%H', 'HH24'
)
, '%d', 'dd'
)
, '%m', 'mm'
)
, '%Y', 'yyyy'
)
;
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION FORMAT_DATE(format_str VARCHAR(255), datetime_val TIMESTAMP(3)) RETURNS TEXT AS $$
BEGIN
RETURN TO_CHAR(
datetime_val,
-- use replace to convert BigQuery string format to postgres string format
-- only handles a few cases since we don't extensively use this function
BIGQUERY_FORMAT_TO_PSQL(format_str)
);
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION PARSE_DATE(format_str VARCHAR(255), string_val VARCHAR(255)) RETURNS DATE AS $$
BEGIN
RETURN TO_DATE(
string_val,
-- use replace to convert BigQuery string format to postgres string format
-- only handles a few cases since we don't extensively use this function
BIGQUERY_FORMAT_TO_PSQL(format_str)
);
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION FORMAT_DATETIME(format_str VARCHAR(255), datetime_val TIMESTAMP(3)) RETURNS TEXT AS $$
BEGIN
RETURN TO_CHAR(
datetime_val,
-- use replace to convert BigQuery string format to postgres string format
-- only handles a few cases since we don't extensively use this function
BIGQUERY_FORMAT_TO_PSQL(format_str)
);
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION PARSE_DATETIME(format_str VARCHAR(255), string_val VARCHAR(255)) RETURNS TIMESTAMP(3) AS $$
BEGIN
RETURN TO_TIMESTAMP(
string_val,
-- use replace to convert BigQuery string format to postgres string format
-- only handles a few cases since we don't extensively use this function
BIGQUERY_FORMAT_TO_PSQL(format_str)
);
END; $$
LANGUAGE PLPGSQL;step-003 生成icustay_times表
耗时12分左右
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS icustay_times; CREATE TABLE icustay_times AS
-- create a table which has fuzzy boundaries on hospital admission
-- involves first creating a lag/lead version of disch/admit time
-- get first/last heart rate measurement during hospitalization for each stay_id
WITH t1 AS (
SELECT ce.stay_id
, MIN(charttime) AS intime_hr
, MAX(charttime) AS outtime_hr
FROM mimiciv_icu.chartevents ce
-- only look at heart rate
WHERE ce.itemid = 220045
GROUP BY ce.stay_id
)
-- add in subject_id/hadm_id
SELECT
ie.subject_id, ie.hadm_id, ie.stay_id
, t1.intime_hr
, t1.outtime_hr
FROM mimiciv_icu.icustays ie
LEFT JOIN t1
ON ie.stay_id = t1.stay_id;step-004 生成icustay_hourly表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS icustay_hourly; CREATE TABLE icustay_hourly AS
-- This query generates a row for every hour the patient is in the ICU.
-- The hours are based on clock-hours (i.e. 02:00, 03:00).
-- The hour clock starts 24 hours before the first heart rate measurement.
-- Note that the time of the first heart rate measurement is ceilinged to
-- the hour.
-- this query extracts the cohort and every possible hour they were in the ICU
-- this table can be to other tables on stay_id and (ENDTIME - 1 hour,ENDTIME]
-- get first/last measurement time
WITH all_hours AS (
SELECT
it.stay_id
-- ceiling the intime to the nearest hour by adding 59 minutes,
-- then applying truncate by parsing as string
-- string truncate is done to enable compatibility with psql
, PARSE_DATETIME(
'%Y-%m-%d %H:00:00'
, FORMAT_DATETIME(
'%Y-%m-%d %H:00:00'
, DATETIME_ADD(it.intime_hr, INTERVAL '59' MINUTE)
)) AS endtime
-- create integers for each charttime in hours from admission
-- so 0 is admission time, 1 is one hour after admission, etc,
-- up to ICU disch
-- we allow 24 hours before ICU admission (to grab labs before admit)
, ARRAY(SELECT * FROM generate_series(-24, CEIL(DATETIME_DIFF(it.outtime_hr, it.intime_hr, 'HOUR')))) AS hrs -- noqa: L016
FROM mimiciv_derived.icustay_times it
)
SELECT stay_id
, CAST(hr AS bigint) AS hr
, DATETIME_ADD(endtime, interval '1' hour * CAST(hr AS bigint)) AS endtime
FROM all_hours
CROSS JOIN UNNEST(all_hours.hrs) AS hr;step-005 生成weight_durations表
耗时约3分钟
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS weight_durations; CREATE TABLE weight_durations AS
-- This query extracts weights for adult ICU patients with start/stop times
-- if an admission weight is given, then this is assigned from intime to outtime
WITH wt_stg AS (
SELECT
c.stay_id
, c.charttime
, CASE WHEN c.itemid = 226512 THEN 'admit'
ELSE 'daily' END AS weight_type
-- TODO: eliminate obvious outliers if there is a reasonable weight
, c.valuenum AS weight
FROM mimiciv_icu.chartevents c
WHERE c.valuenum IS NOT NULL
AND c.itemid IN
(
226512 -- Admit Wt
, 224639 -- Daily Weight
)
AND c.valuenum > 0
)
-- assign ascending row number
, wt_stg1 AS (
SELECT
stay_id
, charttime
, weight_type
, weight
, ROW_NUMBER() OVER (
PARTITION BY stay_id, weight_type ORDER BY charttime
) AS rn
FROM wt_stg
WHERE weight IS NOT NULL
)
-- change charttime to intime for the first admission weight recorded
, wt_stg2 AS (
SELECT
wt_stg1.stay_id
, ie.intime, ie.outtime
, wt_stg1.weight_type
, CASE WHEN wt_stg1.weight_type = 'admit' AND wt_stg1.rn = 1
THEN DATETIME_SUB(ie.intime, INTERVAL '2' HOUR)
ELSE wt_stg1.charttime END AS starttime
, wt_stg1.weight
FROM wt_stg1
INNER JOIN mimiciv_icu.icustays ie
ON ie.stay_id = wt_stg1.stay_id
)
, wt_stg3 AS (
SELECT
stay_id
, intime, outtime
, starttime
, COALESCE(
LEAD(starttime) OVER (PARTITION BY stay_id ORDER BY starttime)
, DATETIME_ADD(outtime, INTERVAL '2' HOUR)
) AS endtime
, weight
, weight_type
FROM wt_stg2
)
-- this table is the start/stop times from admit/daily weight in charted data
, wt1 AS (
SELECT
stay_id
, starttime
, COALESCE(
endtime
, LEAD(
starttime
) OVER (PARTITION BY stay_id ORDER BY starttime)
-- impute ICU discharge as the end of the final weight measurement
-- plus a 2 hour "fuzziness" window
, DATETIME_ADD(outtime, INTERVAL '2' HOUR)
) AS endtime
, weight
, weight_type
FROM wt_stg3
)
-- if the intime for the patient is < the first charted daily weight
-- then we will have a "gap" at the start of their stay
-- to prevent this, we look for these gaps and backfill the first weight
-- this adds (153255-149657)=3598 rows, meaning this fix helps for up
-- to 3598 stay_id
, wt_fix AS (
SELECT ie.stay_id
-- we add a 2 hour "fuzziness" window
, DATETIME_SUB(ie.intime, INTERVAL '2' HOUR) AS starttime
, wt.starttime AS endtime
, wt.weight
, wt.weight_type
FROM mimiciv_icu.icustays ie
INNER JOIN
-- the below subquery returns one row for each unique stay_id
-- the row contains: the first starttime and the corresponding weight
(
SELECT wt1.stay_id, wt1.starttime, wt1.weight
, weight_type
, ROW_NUMBER() OVER (
PARTITION BY wt1.stay_id ORDER BY wt1.starttime
) AS rn
FROM wt1
) wt
ON ie.stay_id = wt.stay_id
AND wt.rn = 1
AND ie.intime < wt.starttime
)
-- add the backfill rows to the main weight table
SELECT
wt1.stay_id
, wt1.starttime
, wt1.endtime
, wt1.weight
, wt1.weight_type
FROM wt1
UNION ALL
SELECT
wt_fix.stay_id
, wt_fix.starttime
, wt_fix.endtime
, wt_fix.weight
, wt_fix.weight_type
FROM wt_fix;step-006 生成urine_output表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS urine_output; CREATE TABLE urine_output AS
WITH uo AS (
SELECT
-- patient identifiers
oe.stay_id
, oe.charttime
-- volumes associated with urine output ITEMIDs
-- note we consider input of GU irrigant as a negative volume
-- GU irrigant volume in usually has a corresponding volume out
-- so the net is often 0, despite large irrigant volumes
, CASE
WHEN oe.itemid = 227488 AND oe.value > 0 THEN -1 * oe.value
ELSE oe.value
END AS urineoutput
FROM mimiciv_icu.outputevents oe
WHERE itemid IN
(
226559 -- Foley
, 226560 -- Void
, 226561 -- Condom Cath
, 226584 -- Ileoconduit
, 226563 -- Suprapubic
, 226564 -- R Nephrostomy
, 226565 -- L Nephrostomy
, 226567 -- Straight Cath
, 226557 -- R Ureteral Stent
, 226558 -- L Ureteral Stent
, 227488 -- GU Irrigant Volume In
, 227489 -- GU Irrigant/Urine Volume Out
)
)
SELECT
stay_id
, charttime
, SUM(urineoutput) AS urineoutput
FROM uo
GROUP BY stay_id, charttime
;step-007 生成kdigo_uo表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS kdigo_uo; CREATE TABLE kdigo_uo AS
WITH uo_stg1 AS (
SELECT ie.stay_id, uo.charttime
, DATETIME_DIFF(charttime, intime, 'SECOND') AS seconds_since_admit
, COALESCE(
DATETIME_DIFF(charttime, LAG(charttime) OVER (PARTITION BY ie.stay_id ORDER BY charttime), 'SECOND') / 3600.0 -- noqa: L016
, 1
) AS hours_since_previous_row
, urineoutput
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.urine_output uo
ON ie.stay_id = uo.stay_id
)
, uo_stg2 AS (
SELECT stay_id, charttime
, hours_since_previous_row
, urineoutput
-- Use the RANGE partition to limit the summation to the last X hours.
-- RANGE operates using numeric, so we convert the charttime into
-- seconds since admission, and then filter to X seconds prior to the
-- current row, where X can be 21600 (6 hours), 43200 (12 hours),
-- or 86400 (24 hours).
, SUM(urineoutput) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
1 ROWS BETWEEN 21600 PRECEDING AND CURRENT ROW
) AS urineoutput_6hr
, SUM(urineoutput) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
ROWS BETWEEN 43200 PRECEDING AND CURRENT ROW
) AS urineoutput_12hr
, SUM(urineoutput) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
2 ROWS BETWEEN 86400 PRECEDING AND CURRENT ROW
) AS urineoutput_24hr
-- repeat the summations using the hours_since_previous_row column
-- this gives us the amount of time the UO was calculated over
, SUM(hours_since_previous_row) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
3 ROWS BETWEEN 21600 PRECEDING AND CURRENT ROW
) AS uo_tm_6hr
, SUM(hours_since_previous_row) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
4 ROWS BETWEEN 43200 PRECEDING AND CURRENT ROW
) AS uo_tm_12hr
, SUM(hours_since_previous_row) OVER
(
PARTITION BY stay_id
ORDER BY seconds_since_admit
5 ROWS BETWEEN 86400 PRECEDING AND CURRENT ROW
) AS uo_tm_24hr
FROM uo_stg1
)
SELECT
ur.stay_id
, ur.charttime
, wd.weight
, ur.urineoutput_6hr
, ur.urineoutput_12hr
, ur.urineoutput_24hr
-- calculate rates while requiring UO documentation over at least N hours
-- as specified in KDIGO guidelines 2012 pg19
, CASE
WHEN uo_tm_6hr >= 6 AND uo_tm_6hr < 12
THEN ROUND(
CAST((ur.urineoutput_6hr / wd.weight / uo_tm_6hr) AS NUMERIC), 4
)
ELSE NULL END AS uo_rt_6hr
, CASE
WHEN uo_tm_12hr >= 12
THEN ROUND(
CAST((ur.urineoutput_12hr / wd.weight / uo_tm_12hr) AS NUMERIC)
, 4
)
ELSE NULL END AS uo_rt_12hr
, CASE
WHEN uo_tm_24hr >= 24
THEN ROUND(
CAST((ur.urineoutput_24hr / wd.weight / uo_tm_24hr) AS NUMERIC)
, 4
)
ELSE NULL END AS uo_rt_24hr
-- number of hours between current UO time and earliest charted UO
-- within the X hour window
, uo_tm_6hr
, uo_tm_12hr
, uo_tm_24hr
FROM uo_stg2 ur
LEFT JOIN mimiciv_derived.weight_durations wd
ON ur.stay_id = wd.stay_id
AND ur.charttime >= wd.starttime
AND ur.charttime < wd.endtime
;- 1
-
Github给的原代码是
RANGE BETWEEN，但会报错。 - 2
- 相同错误
- 3
- 相同错误
- 4
- 相同错误
- 5
- 相同错误
报错内容：
LINE 31: RANGE BETWEEN 21600 PRECEDING AND CURRENT ROW
^
ERROR: RANGE PRECEDING is only supported with UNBOUNDEDstep-008 生成age表
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
DROP TABLE IF EXISTS age; CREATE TABLE age AS
-- This query calculates the age of a patient on admission to the hospital.
-- The columns of the table patients: anchor_age, anchor_year, anchor_year_group
-- provide information regarding the actual patient year for the patient
-- admission, and the patient's age at that time.
-- anchor_year is a shifted year for the patient.
-- anchor_year_group is a range of years - the patient's anchor_year occurred
-- during this range.
-- anchor_age is the patient's age in the anchor_year.
-- Example: a patient has an anchor_year of 2153,
-- anchor_year_group of 2008 - 2010, and an anchor_age of 60.
-- The year 2153 for the patient corresponds to 2008, 2009, or 2010.
-- The patient was 60 in the shifted year of 2153,
-- i.e. they were 60 in 2008, 2009, or 2010.
-- A patient admission in 2154 will occur in 2009-2011,
-- an admission in 2155 will occur in 2010-2012, and so on.
-- Therefore, the age of a patient = admission time - anchor_year + anchor_age
SELECT
ad.subject_id
, ad.hadm_id
, ad.admittime
, pa.anchor_age
, pa.anchor_year
-- calculate the age as anchor_age (60) plus difference between
-- admit year and the anchor year.
-- the noqa retains the extra long line so the
-- convert to postgres bash script works
, pa.anchor_age + DATETIME_DIFF(ad.admittime, DATETIME(pa.anchor_year, 1, 1, 0, 0, 0), 'YEAR') AS age -- noqa: L016
FROM mimiciv_hosp.admissions ad
INNER JOIN mimiciv_hosp.patients pa
ON ad.subject_id = pa.subject_id
;step-009 生成icu_stay表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS icustay_detail; CREATE TABLE icustay_detail AS
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
-- patient level factors
, pat.gender, pat.dod
-- hospital level factors
, adm.admittime, adm.dischtime
, DATETIME_DIFF(adm.dischtime, adm.admittime, 'DAY') AS los_hospital
-- calculate the age as anchor_age (60) plus difference between
-- admit year and the anchor year.
-- the noqa retains the extra long line so the
-- convert to postgres bash script works
, pat.anchor_age + DATETIME_DIFF(adm.admittime, DATETIME(pat.anchor_year, 1, 1, 0, 0, 0), 'YEAR') AS admission_age -- noqa: L016
, adm.race
, adm.hospital_expire_flag
, DENSE_RANK() OVER (
PARTITION BY adm.subject_id ORDER BY adm.admittime
) AS hospstay_seq
, CASE
WHEN
DENSE_RANK() OVER (
PARTITION BY adm.subject_id ORDER BY adm.admittime
) = 1 THEN True
ELSE False END AS first_hosp_stay
-- icu level factors
, ie.intime AS icu_intime, ie.outtime AS icu_outtime
, ROUND(
CAST(DATETIME_DIFF(ie.outtime, ie.intime, 'HOUR') / 24.0 AS NUMERIC), 2
) AS los_icu
, DENSE_RANK() OVER (
PARTITION BY ie.hadm_id ORDER BY ie.intime
) AS icustay_seq
-- first ICU stay *for the current hospitalization*
, CASE
WHEN
DENSE_RANK() OVER (
PARTITION BY ie.hadm_id ORDER BY ie.intime
) = 1 THEN True
ELSE False END AS first_icu_stay
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_hosp.admissions adm
ON ie.hadm_id = adm.hadm_id
INNER JOIN mimiciv_hosp.patients pat
ON ie.subject_id = pat.subject_idstep-010 生成bg表
耗时约17 min 58 secs
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS bg; CREATE TABLE bg AS
-- The aim of this query is to pivot entries related to blood gases
-- which were found in LABEVENTS
WITH bg AS (
SELECT
-- specimen_id only ever has 1 measurement for each itemid
-- so, we may simply collapse rows using MAX()
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
-- specimen_id *may* have different storetimes, so this
-- is taking the latest
, MAX(storetime) AS storetime
, le.specimen_id
, MAX(CASE WHEN itemid = 52033 THEN value ELSE NULL END) AS specimen
, MAX(CASE WHEN itemid = 50801 THEN valuenum ELSE NULL END) AS aado2
, MAX(
CASE WHEN itemid = 50802 THEN valuenum ELSE NULL END
) AS baseexcess
, MAX(
CASE WHEN itemid = 50803 THEN valuenum ELSE NULL END
) AS bicarbonate
, MAX(CASE WHEN itemid = 50804 THEN valuenum ELSE NULL END) AS totalco2
, MAX(
CASE WHEN itemid = 50805 THEN valuenum ELSE NULL END
) AS carboxyhemoglobin
, MAX(CASE WHEN itemid = 50806 THEN valuenum ELSE NULL END) AS chloride
, MAX(CASE WHEN itemid = 50808 THEN valuenum ELSE NULL END) AS calcium
, MAX(
CASE
WHEN
itemid = 50809 AND valuenum <= 10000 THEN valuenum
ELSE NULL
END
) AS glucose
, MAX(
CASE
WHEN itemid = 50810 AND valuenum <= 100 THEN valuenum ELSE NULL
END
) AS hematocrit
, MAX(
CASE WHEN itemid = 50811 THEN valuenum ELSE NULL END
) AS hemoglobin
, MAX(
CASE
WHEN
itemid = 50813 AND valuenum <= 10000 THEN valuenum
ELSE NULL
END
) AS lactate
, MAX(
CASE WHEN itemid = 50814 THEN valuenum ELSE NULL END
) AS methemoglobin
, MAX(CASE WHEN itemid = 50815 THEN valuenum ELSE NULL END) AS o2flow
-- fix a common unit conversion error for fio2
-- atmospheric o2 is 20.89%, so any value <= 20 is unphysiologic
-- usually this is a misplaced O2 flow measurement
, MAX(CASE WHEN itemid = 50816 THEN
CASE
WHEN valuenum > 20 AND valuenum <= 100 THEN valuenum
WHEN
valuenum > 0.2 AND valuenum <= 1.0 THEN valuenum * 100.0
ELSE NULL END
ELSE NULL END) AS fio2
, MAX(
CASE
WHEN itemid = 50817 AND valuenum <= 100 THEN valuenum ELSE NULL
END
) AS so2
, MAX(CASE WHEN itemid = 50818 THEN valuenum ELSE NULL END) AS pco2
, MAX(CASE WHEN itemid = 50819 THEN valuenum ELSE NULL END) AS peep
, MAX(CASE WHEN itemid = 50820 THEN valuenum ELSE NULL END) AS ph
, MAX(CASE WHEN itemid = 50821 THEN valuenum ELSE NULL END) AS po2
, MAX(CASE WHEN itemid = 50822 THEN valuenum ELSE NULL END) AS potassium
, MAX(
CASE WHEN itemid = 50823 THEN valuenum ELSE NULL END
) AS requiredo2
, MAX(CASE WHEN itemid = 50824 THEN valuenum ELSE NULL END) AS sodium
, MAX(
CASE WHEN itemid = 50825 THEN valuenum ELSE NULL END
) AS temperature
, MAX(CASE WHEN itemid = 50807 THEN value ELSE NULL END) AS comments
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
-- blood gases
(
52033 -- specimen
, 50801 -- aado2
, 50802 -- base excess
, 50803 -- bicarb
, 50804 -- calc tot co2
, 50805 -- carboxyhgb
, 50806 -- chloride
-- , 52390 -- chloride, WB CL-
, 50807 -- comments
, 50808 -- free calcium
, 50809 -- glucose
, 50810 -- hct
, 50811 -- hgb
, 50813 -- lactate
, 50814 -- methemoglobin
, 50815 -- o2 flow
, 50816 -- fio2
, 50817 -- o2 sat
, 50818 -- pco2
, 50819 -- peep
, 50820 -- pH
, 50821 -- pO2
, 50822 -- potassium
-- , 52408 -- potassium, WB K+
, 50823 -- required O2
, 50824 -- sodium
-- , 52411 -- sodium, WB NA +
, 50825 -- temperature
)
GROUP BY le.specimen_id
)
, stg_spo2 AS (
SELECT subject_id, charttime
-- avg here is just used to group SpO2 by charttime
, AVG(valuenum) AS spo2
FROM mimiciv_icu.chartevents
WHERE itemid = 220277 -- O2 saturation pulseoxymetry
AND valuenum > 0 AND valuenum <= 100
GROUP BY subject_id, charttime
)
, stg_fio2 AS (
SELECT subject_id, charttime
-- pre-process the FiO2s to ensure they are between 21-100%
, MAX(
CASE
WHEN valuenum > 0.2 AND valuenum <= 1
THEN valuenum * 100
-- improperly input data - looks like O2 flow in litres
WHEN valuenum > 1 AND valuenum < 20
THEN NULL
WHEN valuenum >= 20 AND valuenum <= 100
THEN valuenum
ELSE NULL END
) AS fio2_chartevents
FROM mimiciv_icu.chartevents
WHERE itemid = 223835 -- Inspired O2 Fraction (FiO2)
AND valuenum > 0 AND valuenum <= 100
GROUP BY subject_id, charttime
)
, stg2 AS (
SELECT bg.*
, ROW_NUMBER() OVER (
PARTITION BY bg.subject_id, bg.charttime ORDER BY s1.charttime DESC
) AS lastrowspo2
, s1.spo2
FROM bg
LEFT JOIN stg_spo2 s1
-- same hospitalization
ON bg.subject_id = s1.subject_id
-- spo2 occurred at most 2 hours before this blood gas
AND s1.charttime
BETWEEN DATETIME_SUB(bg.charttime, INTERVAL '2' HOUR)
AND bg.charttime
WHERE bg.po2 IS NOT NULL
)
, stg3 AS (
SELECT bg.*
, ROW_NUMBER() OVER (
PARTITION BY bg.subject_id, bg.charttime ORDER BY s2.charttime DESC
) AS lastrowfio2
, s2.fio2_chartevents
FROM stg2 bg
LEFT JOIN stg_fio2 s2
-- same patient
ON bg.subject_id = s2.subject_id
-- fio2 occurred at most 4 hours before this blood gas
AND s2.charttime >= DATETIME_SUB(bg.charttime, INTERVAL '4' HOUR)
AND s2.charttime <= bg.charttime
AND s2.fio2_chartevents > 0
-- only the row with the most recent SpO2 (if no SpO2 found lastRowSpO2 = 1)
WHERE bg.lastrowspo2 = 1
)
SELECT
stg3.subject_id
, stg3.hadm_id
, stg3.charttime
-- drop down text indicating the specimen type
, specimen
-- oxygen related parameters
, so2
, po2
, pco2
, fio2_chartevents, fio2
, aado2
-- also calculate AADO2
, CASE
WHEN po2 IS NULL
OR pco2 IS NULL
THEN NULL
WHEN fio2 IS NOT NULL
-- multiple by 100 because fio2 is in a % but should be a fraction
THEN (fio2 / 100) * (760 - 47) - (pco2 / 0.8) - po2
WHEN fio2_chartevents IS NOT NULL
THEN (fio2_chartevents / 100) * (760 - 47) - (pco2 / 0.8) - po2
ELSE NULL
END AS aado2_calc
, CASE
WHEN po2 IS NULL
THEN NULL
WHEN fio2 IS NOT NULL
-- multiply by 100 because fio2 is in a % but should be a fraction
THEN 100 * po2 / fio2
WHEN fio2_chartevents IS NOT NULL
-- multiply by 100 because fio2 is in a % but should be a fraction
THEN 100 * po2 / fio2_chartevents
ELSE NULL
END AS pao2fio2ratio
-- acid-base parameters
, ph, baseexcess
, bicarbonate, totalco2
-- blood count parameters
, hematocrit
, hemoglobin
, carboxyhemoglobin
, methemoglobin
-- chemistry
, chloride, calcium
, temperature
, potassium, sodium
, lactate
, glucose
-- ventilation stuff that's sometimes input
-- , intubated, tidalvolume, ventilationrate, ventilator
-- , peep, o2flow
-- , requiredo2
FROM stg3
WHERE lastrowfio2 = 1 -- only the most recent FiO2
;step-011 生成blood_differntial表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS blood_differential; CREATE TABLE blood_differential AS
-- For reference, some common unit conversions:
-- 10^9/L == K/uL == 10^3/uL
WITH blood_diff AS (
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- create one set of columns for percentages, one set for counts
-- we harmonize all count units into K/uL == 10^9/L
-- counts have an "_abs" suffix, percentages do not
-- absolute counts
, MAX(
CASE
WHEN itemid IN (51300, 51301, 51755) THEN valuenum ELSE NULL
END
) AS wbc
, MAX(
CASE WHEN itemid = 52069 THEN valuenum ELSE NULL END
) AS basophils_abs
-- 52073 in K/uL, 51199 in #/uL
, MAX(
CASE
WHEN
itemid = 52073 THEN valuenum
WHEN itemid = 51199 THEN valuenum / 1000.0 ELSE NULL
END
) AS eosinophils_abs
-- 51133 in K/uL, 52769 in #/uL
, MAX(
CASE
WHEN
itemid = 51133 THEN valuenum
WHEN itemid = 52769 THEN valuenum / 1000.0 ELSE NULL
END
) AS lymphocytes_abs
-- 52074 in K/uL, 51253 in #/uL
, MAX(
CASE
WHEN
itemid = 52074 THEN valuenum
WHEN itemid = 51253 THEN valuenum / 1000.0 ELSE NULL
END
) AS monocytes_abs
, MAX(
CASE WHEN itemid = 52075 THEN valuenum ELSE NULL END
) AS neutrophils_abs
-- convert from #/uL to K/uL
, MAX(
CASE WHEN itemid = 51218 THEN valuenum / 1000.0 ELSE NULL END
) AS granulocytes_abs
-- percentages, equal to cell count / white blood cell count
, MAX(CASE WHEN itemid = 51146 THEN valuenum ELSE NULL END) AS basophils
, MAX(
CASE WHEN itemid = 51200 THEN valuenum ELSE NULL END
) AS eosinophils
, MAX(
CASE WHEN itemid IN (51244, 51245) THEN valuenum ELSE NULL END
) AS lymphocytes
, MAX(CASE WHEN itemid = 51254 THEN valuenum ELSE NULL END) AS monocytes
, MAX(
CASE WHEN itemid = 51256 THEN valuenum ELSE NULL END
) AS neutrophils
-- other cell count percentages
, MAX(
CASE WHEN itemid = 51143 THEN valuenum ELSE NULL END
) AS atypical_lymphocytes
, MAX(CASE WHEN itemid = 51144 THEN valuenum ELSE NULL END) AS bands
, MAX(
CASE WHEN itemid = 52135 THEN valuenum ELSE NULL END
) AS immature_granulocytes
, MAX(
CASE WHEN itemid = 51251 THEN valuenum ELSE NULL END
) AS metamyelocytes
, MAX(CASE WHEN itemid = 51257 THEN valuenum ELSE NULL END) AS nrbc
-- utility flags which determine whether imputation is possible
, CASE
-- WBC is available
WHEN
MAX(
CASE
WHEN
itemid IN (51300, 51301, 51755) THEN valuenum
ELSE NULL
END
) > 0
-- and we have at least one percentage from the diff
-- sometimes the entire diff is 0%, which looks like bad data
AND SUM(
CASE
WHEN
itemid IN (
51146, 51200, 51244, 51245, 51254, 51256
) THEN valuenum
ELSE NULL
END
) > 0
THEN 1 ELSE 0 END AS impute_abs
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
51146 -- basophils
, 52069 -- Absolute basophil count
, 51199 -- Eosinophil Count
, 51200 -- Eosinophils
, 52073 -- Absolute Eosinophil count
, 51244 -- Lymphocytes
, 51245 -- Lymphocytes, Percent
, 51133 -- Absolute Lymphocyte Count
, 52769 -- Absolute Lymphocyte Count
, 51253 -- Monocyte Count
, 51254 -- Monocytes
, 52074 -- Absolute Monocyte Count
, 51256 -- Neutrophils
, 52075 -- Absolute Neutrophil Count
, 51143 -- Atypical lymphocytes
, 51144 -- Bands (%)
, 51218 -- Granulocyte Count
, 52135 -- Immature granulocytes (%)
, 51251 -- Metamyelocytes
, 51257 -- Nucleated Red Cells
-- wbc totals measured in K/uL
-- 52220 (wbcp) is percentage
, 51300, 51301, 51755
-- below are point of care tests which are extremely infrequent
-- and usually low quality
-- 51697, -- Neutrophils (mmol/L)
-- below itemid do not have data as of MIMIC-IV v1.0
-- 51536, -- Absolute Lymphocyte Count
-- 51537, -- Absolute Neutrophil
-- 51690, -- Lymphocytes
-- 52151, -- NRBC
)
AND valuenum IS NOT NULL
-- differential values cannot be negative
AND valuenum >= 0
GROUP BY le.specimen_id
)
SELECT
subject_id, hadm_id, charttime, specimen_id
, wbc
-- impute absolute count if percentage & WBC is available
, ROUND(CAST(CASE
WHEN basophils_abs IS NULL
AND basophils IS NOT NULL
AND impute_abs = 1
THEN basophils * wbc / 100
ELSE basophils_abs
END AS NUMERIC), 4) AS basophils_abs
, ROUND(CAST(CASE
WHEN eosinophils_abs IS NULL
AND eosinophils IS NOT NULL
AND impute_abs = 1
THEN eosinophils * wbc / 100
ELSE eosinophils_abs
END AS NUMERIC), 4) AS eosinophils_abs
, ROUND(CAST(CASE
WHEN lymphocytes_abs IS NULL
AND lymphocytes IS NOT NULL
AND impute_abs = 1
THEN lymphocytes * wbc / 100
ELSE lymphocytes_abs
END AS NUMERIC), 4) AS lymphocytes_abs
, ROUND(CAST(CASE
WHEN monocytes_abs IS NULL
AND monocytes IS NOT NULL
AND impute_abs = 1
THEN monocytes * wbc / 100
ELSE monocytes_abs
END AS NUMERIC), 4) AS monocytes_abs
, ROUND(CAST(CASE
WHEN neutrophils_abs IS NULL
AND neutrophils IS NOT NULL
AND impute_abs = 1
THEN neutrophils * wbc / 100
ELSE neutrophils_abs
END AS NUMERIC), 4) AS neutrophils_abs
, basophils
, eosinophils
, lymphocytes
, monocytes
, neutrophils
-- impute bands/blasts?
, atypical_lymphocytes
, bands
, immature_granulocytes
, metamyelocytes
, nrbc
FROM blood_diff
;step-012 生成cardiac_marker表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS cardiac_marker; CREATE TABLE cardiac_marker AS
-- begin query that extracts the data
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- convert from itemid into a meaningful column
, MAX(CASE WHEN itemid = 51003 THEN value ELSE NULL END) AS troponin_t
, MAX(CASE WHEN itemid = 50911 THEN valuenum ELSE NULL END) AS ck_mb
, MAX(CASE WHEN itemid = 50963 THEN valuenum ELSE NULL END) AS ntprobnp
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
-- 51002, -- Troponin I (troponin-I is not measured in MIMIC-IV)
-- 52598, -- Troponin I, point of care, rare/poor quality
51003 -- Troponin T
, 50911 -- Creatinine Kinase, MB isoenzyme
, 50963 -- N-terminal (NT)-pro hormone BNP (NT-proBNP)
)
AND valuenum IS NOT NULL
GROUP BY le.specimen_id
;step-013 生成chemistry表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS chemistry; CREATE TABLE chemistry AS
-- extract chemistry labs
-- excludes point of care tests (very rare)
-- blood gas measurements are *not* included in this query
-- instead they are in bg.sql
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- convert from itemid into a meaningful column
, MAX(
CASE WHEN itemid = 50862 AND valuenum <= 10 THEN valuenum ELSE NULL END
) AS albumin
, MAX(
CASE WHEN itemid = 50930 AND valuenum <= 10 THEN valuenum ELSE NULL END
) AS globulin
, MAX(
CASE WHEN itemid = 50976 AND valuenum <= 20 THEN valuenum ELSE NULL END
) AS total_protein
, MAX(
CASE
WHEN itemid = 50868 AND valuenum <= 10000 THEN valuenum ELSE NULL
END
) AS aniongap
, MAX(
CASE
WHEN itemid = 50882 AND valuenum <= 10000 THEN valuenum ELSE NULL
END
) AS bicarbonate
, MAX(
CASE WHEN itemid = 51006 AND valuenum <= 300 THEN valuenum ELSE NULL END
) AS bun
, MAX(
CASE
WHEN itemid = 50893 AND valuenum <= 10000 THEN valuenum ELSE NULL
END
) AS calcium
, MAX(
CASE
WHEN itemid = 50902 AND valuenum <= 10000 THEN valuenum ELSE NULL
END
) AS chloride
, MAX(
CASE WHEN itemid = 50912 AND valuenum <= 150 THEN valuenum ELSE NULL END
) AS creatinine
, MAX(
CASE
WHEN itemid = 50931 AND valuenum <= 10000 THEN valuenum ELSE NULL
END
) AS glucose
, MAX(
CASE WHEN itemid = 50983 AND valuenum <= 200 THEN valuenum ELSE NULL END
) AS sodium
, MAX(
CASE WHEN itemid = 50971 AND valuenum <= 30 THEN valuenum ELSE NULL END
) AS potassium
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
-- comment is: LABEL | CATEGORY | FLUID | NUMBER OF ROWS IN LABEVENTS
50862 -- ALBUMIN | CHEMISTRY | BLOOD | 146697
, 50930 -- Globulin
, 50976 -- Total protein
-- 52456, -- Anion gap, point of care test
, 50868 -- ANION GAP | CHEMISTRY | BLOOD | 769895
, 50882 -- BICARBONATE | CHEMISTRY | BLOOD | 780733
, 50893 -- Calcium
-- 52502, Creatinine, point of care
, 50912 -- CREATININE | CHEMISTRY | BLOOD | 797476
, 50902 -- CHLORIDE | CHEMISTRY | BLOOD | 795568
, 50931 -- GLUCOSE | CHEMISTRY | BLOOD | 748981
-- 52525, Glucose, point of care
-- 52566, -- Potassium, point of care
, 50971 -- POTASSIUM | CHEMISTRY | BLOOD | 845825
-- 52579, -- Sodium, point of care
, 50983 -- SODIUM | CHEMISTRY | BLOOD | 808489
-- 52603, Urea, point of care
, 51006 -- UREA NITROGEN | CHEMISTRY | BLOOD | 791925
)
AND valuenum IS NOT NULL
-- lab values cannot be 0 and cannot be negative
-- .. except anion gap.
AND (valuenum > 0 OR itemid = 50868)
GROUP BY le.specimen_id
;
step-014 生成coagulation表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS coagulation; CREATE TABLE coagulation AS
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- convert from itemid into a meaningful column
, MAX(CASE WHEN itemid = 51196 THEN valuenum ELSE NULL END) AS d_dimer
, MAX(CASE WHEN itemid = 51214 THEN valuenum ELSE NULL END) AS fibrinogen
, MAX(CASE WHEN itemid = 51297 THEN valuenum ELSE NULL END) AS thrombin
, MAX(CASE WHEN itemid = 51237 THEN valuenum ELSE NULL END) AS inr
, MAX(CASE WHEN itemid = 51274 THEN valuenum ELSE NULL END) AS pt
, MAX(CASE WHEN itemid = 51275 THEN valuenum ELSE NULL END) AS ptt
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
-- Bleeding Time, no data as of MIMIC-IV v0.4
-- 51149, 52750, 52072, 52073
51196 -- D-Dimer
, 51214 -- Fibrinogen
-- Reptilase Time, no data as of MIMIC-IV v0.4
-- 51280, 52893,
-- Reptilase Time Control, no data as of MIMIC-IV v0.4
-- 51281, 52161,
, 51297 -- thrombin
, 51237 -- INR
, 51274 -- PT
, 51275 -- PTT
)
AND valuenum IS NOT NULL
GROUP BY le.specimen_id
;step-015 生成complete_blood_count表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS height; CREATE TABLE height AS
-- prep height
WITH ht_in AS (
SELECT
c.subject_id, c.stay_id, c.charttime
-- Ensure that all heights are in centimeters
, ROUND(CAST(c.valuenum * 2.54 AS NUMERIC), 2) AS height
, c.valuenum AS height_orig
FROM mimiciv_icu.chartevents c
WHERE c.valuenum IS NOT NULL
-- Height (measured in inches)
AND c.itemid = 226707
)
, ht_cm AS (
SELECT
c.subject_id, c.stay_id, c.charttime
-- Ensure that all heights are in centimeters
, ROUND(CAST(c.valuenum AS NUMERIC), 2) AS height
FROM mimiciv_icu.chartevents c
WHERE c.valuenum IS NOT NULL
-- Height cm
AND c.itemid = 226730
)
-- merge cm/height, only take 1 value per charted row
, ht_stg0 AS (
SELECT
COALESCE(h1.subject_id, h1.subject_id) AS subject_id
, COALESCE(h1.stay_id, h1.stay_id) AS stay_id
, COALESCE(h1.charttime, h1.charttime) AS charttime
, COALESCE(h1.height, h2.height) AS height
FROM ht_cm h1
FULL OUTER JOIN ht_in h2
ON h1.subject_id = h2.subject_id
AND h1.charttime = h2.charttime
)
SELECT subject_id, stay_id, charttime, height
FROM ht_stg0
WHERE height IS NOT NULL
-- filter out bad heights
AND height > 120 AND height < 230;step-016 生成creatinine_baseline表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS creatinine_baseline; CREATE TABLE creatinine_baseline AS
-- This query extracts the serum creatinine baselines of adult patients
-- on each hospital admission.
-- The baseline is determined by the following rules:
-- i. if the lowest creatinine value during this admission is normal (<1.1),
-- then use the value
-- ii. if the patient is diagnosed with chronic kidney disease (CKD),
-- then use the lowest creatinine value during the admission,
-- although it may be rather large.
-- iii. Otherwise, we estimate the baseline using Simplified MDRD:
-- eGFR = 186 × Scr^(-1.154) × Age^(-0.203) × 0.742Female
WITH p AS (
SELECT
ag.subject_id
, ag.hadm_id
, ag.age
, p.gender
, CASE WHEN p.gender = 'F' THEN
POWER(75.0 / 186.0 / POWER(ag.age, -0.203) / 0.742, -1 / 1.154)
ELSE
POWER(75.0 / 186.0 / POWER(ag.age, -0.203), -1 / 1.154)
END
AS mdrd_est
FROM mimiciv_derived.age ag
LEFT JOIN mimiciv_hosp.patients p
ON ag.subject_id = p.subject_id
WHERE ag.age >= 18
)
, lab AS (
SELECT
hadm_id
, MIN(creatinine) AS scr_min
FROM mimiciv_derived.chemistry
GROUP BY hadm_id
)
, ckd AS (
SELECT hadm_id, MAX(1) AS ckd_flag
FROM mimiciv_hosp.diagnoses_icd
WHERE
(
SUBSTR(icd_code, 1, 3) = '585'
AND
icd_version = 9
)
OR
(
SUBSTR(icd_code, 1, 3) = 'N18'
AND
icd_version = 10
)
GROUP BY hadm_id
)
SELECT
p.hadm_id
, p.gender
, p.age
, lab.scr_min
, COALESCE(ckd.ckd_flag, 0) AS ckd
, p.mdrd_est
, CASE
WHEN lab.scr_min <= 1.1 THEN scr_min
WHEN ckd.ckd_flag = 1 THEN scr_min
ELSE mdrd_est END AS scr_baseline
FROM p
LEFT JOIN lab
ON p.hadm_id = lab.hadm_id
LEFT JOIN ckd
ON p.hadm_id = ckd.hadm_id
;
step-017 生成enzyme表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS enzyme; CREATE TABLE enzyme AS
-- begin query that extracts the data
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- convert from itemid into a meaningful column
, MAX(CASE WHEN itemid = 50861 THEN valuenum ELSE NULL END) AS alt
, MAX(CASE WHEN itemid = 50863 THEN valuenum ELSE NULL END) AS alp
, MAX(CASE WHEN itemid = 50878 THEN valuenum ELSE NULL END) AS ast
, MAX(CASE WHEN itemid = 50867 THEN valuenum ELSE NULL END) AS amylase
, MAX(
CASE WHEN itemid = 50885 THEN valuenum ELSE NULL END
) AS bilirubin_total
, MAX(
CASE WHEN itemid = 50883 THEN valuenum ELSE NULL END
) AS bilirubin_direct
, MAX(
CASE WHEN itemid = 50884 THEN valuenum ELSE NULL END
) AS bilirubin_indirect
, MAX(CASE WHEN itemid = 50910 THEN valuenum ELSE NULL END) AS ck_cpk
, MAX(CASE WHEN itemid = 50911 THEN valuenum ELSE NULL END) AS ck_mb
, MAX(CASE WHEN itemid = 50927 THEN valuenum ELSE NULL END) AS ggt
, MAX(CASE WHEN itemid = 50954 THEN valuenum ELSE NULL END) AS ld_ldh
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
50861 -- Alanine transaminase (ALT)
, 50863 -- Alkaline phosphatase (ALP)
, 50878 -- Aspartate transaminase (AST)
, 50867 -- Amylase
, 50885 -- total bili
, 50884 -- indirect bili
, 50883 -- direct bili
, 50910 -- ck_cpk
, 50911 -- CK-MB
, 50927 -- Gamma Glutamyltransferase (GGT)
, 50954 -- ld_ldh
)
AND valuenum IS NOT NULL
-- lab values cannot be 0 and cannot be negative
AND valuenum > 0
GROUP BY le.specimen_id
;step-018 生成gcs表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS gcs; CREATE TABLE gcs AS
-- This query extracts the Glasgow Coma Scale, a measure of neurological
-- function.
-- The query has a few special rules:
-- (1) The verbal component can be set to 0 if the patient is ventilated.
-- This is corrected to 5 - the overall GCS is set to 15 in these cases.
-- (2) Often only one of three components is documented. The other components
-- are carried forward.
-- ITEMIDs used:
-- METAVISION
-- 223900 GCS - Verbal Response
-- 223901 GCS - Motor Response
-- 220739 GCS - Eye Opening
-- Note:
-- The GCS for sedated patients is defaulted to 15 in this code.
-- This is in line with how the data is meant to be collected.
-- e.g., from the SAPS II publication:
-- For sedated patients, the Glasgow Coma Score before sedation was used.
-- This was ascertained either from interviewing the physician who ordered
-- the sedation, or by reviewing the patient's medical record.
WITH base AS (
SELECT
subject_id
, ce.stay_id, ce.charttime
-- pivot each value into its own column
, MAX(
CASE WHEN ce.itemid = 223901 THEN ce.valuenum ELSE null END
) AS gcsmotor
, MAX(CASE
WHEN ce.itemid = 223900 AND ce.value = 'No Response-ETT' THEN 0
WHEN ce.itemid = 223900 THEN ce.valuenum
ELSE null
END) AS gcsverbal
, MAX(
CASE WHEN ce.itemid = 220739 THEN ce.valuenum ELSE null END
) AS gcseyes
-- convert the data into a number, reserving a value of 0 for ET/Trach
, MAX(CASE
-- endotrach/vent is assigned a value of 0
-- flag it here to later parse specially
-- metavision
WHEN ce.itemid = 223900 AND ce.value = 'No Response-ETT' THEN 1
ELSE 0 END)
AS endotrachflag
, ROW_NUMBER()
OVER (PARTITION BY ce.stay_id ORDER BY ce.charttime ASC) AS rn
FROM mimiciv_icu.chartevents ce
-- Isolate the desired GCS variables
WHERE ce.itemid IN
(
-- GCS components, Metavision
223900, 223901, 220739
)
GROUP BY ce.subject_id, ce.stay_id, ce.charttime
)
, gcs AS (
SELECT b.*
, b2.gcsverbal AS gcsverbalprev
, b2.gcsmotor AS gcsmotorprev
, b2.gcseyes AS gcseyesprev
-- Calculate GCS, factoring in special case when they are intubated
-- note that the coalesce are used to implement the following if:
-- if current value exists, use it
-- if previous value exists, use it
-- otherwise, default to normal
, CASE
-- replace GCS during sedation with 15
WHEN b.gcsverbal = 0
THEN 15
WHEN b.gcsverbal IS NULL AND b2.gcsverbal = 0
THEN 15
-- if previously they were intub, but they aren't now,
-- do not use previous GCS values
WHEN b2.gcsverbal = 0
THEN
COALESCE(b.gcsmotor, 6)
+ COALESCE(b.gcsverbal, 5)
+ COALESCE(b.gcseyes, 4)
-- otherwise, add up score normally, imputing previous value
-- if none available at current time
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
FROM base b
-- join to itself within 6 hours to get previous value
LEFT JOIN base b2
ON b.stay_id = b2.stay_id
AND b.rn = b2.rn + 1
AND b2.charttime > DATETIME_SUB(b.charttime, INTERVAL '6' HOUR)
)
-- combine components with previous within 6 hours
-- filter down to cohort which is not excluded
-- truncate charttime to the hour
, gcs_stg AS (
SELECT
subject_id
, gs.stay_id, gs.charttime
, gcs
, COALESCE(gcsmotor, gcsmotorprev) AS gcsmotor
, COALESCE(gcsverbal, gcsverbalprev) AS gcsverbal
, COALESCE(gcseyes, gcseyesprev) AS gcseyes
, CASE WHEN COALESCE(gcsmotor, gcsmotorprev) IS NULL THEN 0 ELSE 1 END
+ CASE WHEN COALESCE(gcsverbal, gcsverbalprev) IS NULL THEN 0 ELSE 1 END
+ CASE WHEN COALESCE(gcseyes, gcseyesprev) IS NULL THEN 0 ELSE 1 END
AS components_measured
, endotrachflag
FROM gcs gs
)
SELECT
gs.subject_id
, gs.stay_id
, gs.charttime
, gcs AS gcs
, gcsmotor AS gcs_motor
, gcsverbal AS gcs_verbal
, gcseyes AS gcs_eyes
, endotrachflag AS gcs_unable
FROM gcs_stg gs
;step-019 生成height表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS height; CREATE TABLE height AS
-- prep height
WITH ht_in AS (
SELECT
c.subject_id, c.stay_id, c.charttime
-- Ensure that all heights are in centimeters
, ROUND(CAST(c.valuenum * 2.54 AS NUMERIC), 2) AS height
, c.valuenum AS height_orig
FROM mimiciv_icu.chartevents c
WHERE c.valuenum IS NOT NULL
-- Height (measured in inches)
AND c.itemid = 226707
)
, ht_cm AS (
SELECT
c.subject_id, c.stay_id, c.charttime
-- Ensure that all heights are in centimeters
, ROUND(CAST(c.valuenum AS NUMERIC), 2) AS height
FROM mimiciv_icu.chartevents c
WHERE c.valuenum IS NOT NULL
-- Height cm
AND c.itemid = 226730
)
-- merge cm/height, only take 1 value per charted row
, ht_stg0 AS (
SELECT
COALESCE(h1.subject_id, h1.subject_id) AS subject_id
, COALESCE(h1.stay_id, h1.stay_id) AS stay_id
, COALESCE(h1.charttime, h1.charttime) AS charttime
, COALESCE(h1.height, h2.height) AS height
FROM ht_cm h1
FULL OUTER JOIN ht_in h2
ON h1.subject_id = h2.subject_id
AND h1.charttime = h2.charttime
)
SELECT subject_id, stay_id, charttime, height
FROM ht_stg0
WHERE height IS NOT NULL
-- filter out bad heights
AND height > 120 AND height < 230;step-020 生成icp表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS icp; CREATE TABLE icp AS
WITH ce AS (
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
-- TODO: handle high ICPs when monitoring two ICPs
, CASE
WHEN valuenum > 0 AND valuenum < 100 THEN valuenum ELSE null
END AS icp
FROM mimiciv_icu.chartevents ce
-- exclude rows marked as error
WHERE ce.itemid IN
(
220765 -- Intra Cranial Pressure -- 92306
, 227989 -- Intra Cranial Pressure #2 -- 1052
)
)
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
, MAX(icp) AS icp
FROM ce
GROUP BY ce.subject_id, ce.stay_id, ce.charttime
;step-021 生成inflammation表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS inflammation; CREATE TABLE inflammation AS
SELECT
MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, MAX(charttime) AS charttime
, le.specimen_id
-- convert from itemid into a meaningful column
, MAX(CASE WHEN itemid = 50889 THEN valuenum ELSE NULL END) AS crp
FROM mimiciv_hosp.labevents le
WHERE le.itemid IN
(
-- 51652 -- high sensitivity CRP
50889 -- crp
)
AND valuenum IS NOT NULL
-- lab values cannot be 0 and cannot be negative
AND valuenum > 0
GROUP BY le.specimen_id
;step-022 生成oxygen_delivery表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS oxygen_delivery; CREATE TABLE oxygen_delivery AS
WITH ce_stg1 AS (
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
, CASE
-- merge o2 flows into a single row
WHEN itemid IN (223834, 227582) THEN 223834
ELSE itemid END AS itemid
, value
, valuenum
, valueuom
, storetime
FROM mimiciv_icu.chartevents ce
WHERE ce.value IS NOT NULL
AND ce.itemid IN
(
223834 -- o2 flow
, 227582 -- bipap o2 flow
-- below flow rate is *not* o2 flow, and not included
-- , 224691 -- Flow Rate (L)
-- additional o2 flow is its own column
, 227287 -- additional o2 flow
)
)
, ce_stg2 AS (
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
, itemid
, value
, valuenum
, valueuom
-- retain only 1 row per charttime
-- prioritizing the last documented value
-- primarily used to subselect o2 flows
, ROW_NUMBER() OVER (
PARTITION BY subject_id, charttime, itemid ORDER BY storetime DESC
) AS rn
FROM ce_stg1 ce
)
, o2 AS (
-- The below ITEMID can have multiple entries for charttime/storetime
-- These are valid entries, and should be retained in derived tables.
-- 224181 -- Small Volume Neb Drug #1 | Respiratory | Text
-- , 227570 -- Small Volume Neb Drug/Dose #1 | Respiratory | Text
-- , 224833 -- SBT Deferred | Respiratory | Text
-- , 224716 -- SBT Stopped | Respiratory | Text
-- , 224740 -- RSBI Deferred | Respiratory | Text
-- , 224829 -- Trach Tube Type | Respiratory | Text
-- , 226732 -- O2 Delivery Device(s) | Respiratory | Text
-- , 226873 -- Inspiratory Ratio | Respiratory | Numeric
-- , 226871 -- Expiratory Ratio | Respiratory | Numeric
-- maximum of 4 o2 devices on at once
SELECT
subject_id
, stay_id
, charttime
, itemid
, value AS o2_device
, ROW_NUMBER() OVER (
PARTITION BY subject_id, charttime, itemid ORDER BY value
) AS rn
FROM mimiciv_icu.chartevents
WHERE itemid = 226732 -- oxygen delivery device(s)
)
, stg AS (
SELECT
COALESCE(ce.subject_id, o2.subject_id) AS subject_id
, COALESCE(ce.stay_id, o2.stay_id) AS stay_id
, COALESCE(ce.charttime, o2.charttime) AS charttime
, COALESCE(ce.itemid, o2.itemid) AS itemid
, ce.value
, ce.valuenum
, o2.o2_device
, o2.rn
FROM ce_stg2 ce
FULL OUTER JOIN o2
ON ce.subject_id = o2.subject_id
AND ce.charttime = o2.charttime
-- limit to 1 row per subject_id/charttime/itemid from ce_stg2
WHERE ce.rn = 1
)
SELECT
subject_id
, MAX(stay_id) AS stay_id
, charttime
, MAX(CASE WHEN itemid = 223834 THEN valuenum ELSE NULL END) AS o2_flow
, MAX(
CASE WHEN itemid = 227287 THEN valuenum ELSE NULL END
) AS o2_flow_additional
-- ensure we retain all o2 devices for the patient
, MAX(CASE WHEN rn = 1 THEN o2_device ELSE NULL END) AS o2_delivery_device_1
, MAX(CASE WHEN rn = 2 THEN o2_device ELSE NULL END) AS o2_delivery_device_2
, MAX(CASE WHEN rn = 3 THEN o2_device ELSE NULL END) AS o2_delivery_device_3
, MAX(CASE WHEN rn = 4 THEN o2_device ELSE NULL END) AS o2_delivery_device_4
FROM stg
GROUP BY subject_id, charttime
;step-023 生成rhythm表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS rhythm; CREATE TABLE rhythm AS
-- Heart rhythm related documentation
SELECT
ce.subject_id
, ce.charttime
, MAX(CASE WHEN itemid = 220048 THEN value ELSE NULL END) AS heart_rhythm
, MAX(CASE WHEN itemid = 224650 THEN value ELSE NULL END) AS ectopy_type
, MAX(
CASE WHEN itemid = 224651 THEN value ELSE NULL END
) AS ectopy_frequency
, MAX(
CASE WHEN itemid = 226479 THEN value ELSE NULL END
) AS ectopy_type_secondary
, MAX(
CASE WHEN itemid = 226480 THEN value ELSE NULL END
) AS ectopy_frequency_secondary
FROM mimiciv_icu.chartevents ce
WHERE ce.stay_id IS NOT NULL
AND ce.itemid IN
(
220048 -- Heart Rhythm
, 224650 -- Ectopy Type 1
, 224651 -- Ectopy Frequency 1
, 226479 -- Ectopy Type 2
, 226480 -- Ectopy Frequency 2
)
GROUP BY ce.subject_id, ce.charttime
;step-024 生成urine_output_rate表
耗时约耗时19 分 54 秒
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS urine_output_rate; CREATE TABLE urine_output_rate AS
-- attempt to calculate urine output per hour
-- rate/hour is the interpretable measure of kidney function
-- though it is difficult to estimate from aperiodic point measures
-- first we get the earliest heart rate documented for the stay
WITH tm AS (
SELECT ie.stay_id
, MIN(charttime) AS intime_hr
, MAX(charttime) AS outtime_hr
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_icu.chartevents ce
ON ie.stay_id = ce.stay_id
AND ce.itemid = 220045
AND ce.charttime > DATETIME_SUB(ie.intime, INTERVAL '1' MONTH)
AND ce.charttime < DATETIME_ADD(ie.outtime, INTERVAL '1' MONTH)
GROUP BY ie.stay_id
)
-- now calculate time since last UO measurement
, uo_tm AS (
SELECT tm.stay_id
, CASE
WHEN LAG(charttime) OVER w IS NULL
THEN DATETIME_DIFF(charttime, intime_hr, 'MINUTE')
ELSE DATETIME_DIFF(charttime, LAG(charttime) OVER w, 'MINUTE')
END AS tm_since_last_uo
, uo.charttime
, uo.urineoutput
FROM tm
INNER JOIN mimiciv_derived.urine_output uo
ON tm.stay_id = uo.stay_id
WINDOW w AS (PARTITION BY tm.stay_id ORDER BY charttime)
)
, ur_stg AS (
SELECT io.stay_id, io.charttime
-- we have joined each row to all rows preceding within 24 hours
-- we can now sum these rows to get total UO over the last 24 hours
-- we can use case statements to restrict it to only the last 6/12 hours
-- therefore we have three sums:
-- 1) over a 6 hour period
-- 2) over a 12 hour period
-- 3) over a 24 hour period
, SUM(DISTINCT io.urineoutput) AS uo
-- note that we assume data charted at charttime corresponds
-- to 1 hour of UO, therefore we use '5' and '11' to restrict the
-- period, rather than 6/12 this assumption may overestimate UO rate
-- when documentation is done less than hourly
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, 'HOUR') <= 5
THEN iosum.urineoutput
ELSE null END) AS urineoutput_6hr
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, 'HOUR') <= 5
THEN iosum.tm_since_last_uo
ELSE null END) / 60.0 AS uo_tm_6hr
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, 'HOUR') <= 11
THEN iosum.urineoutput
ELSE null END) AS urineoutput_12hr
, SUM(CASE WHEN DATETIME_DIFF(io.charttime, iosum.charttime, 'HOUR') <= 11
THEN iosum.tm_since_last_uo
ELSE null END) / 60.0 AS uo_tm_12hr
-- 24 hours
, SUM(iosum.urineoutput) AS urineoutput_24hr
, SUM(iosum.tm_since_last_uo) / 60.0 AS uo_tm_24hr
FROM uo_tm io
-- this join gives you all UO measurements over a 24 hour period
LEFT JOIN uo_tm iosum
ON io.stay_id = iosum.stay_id
AND io.charttime >= iosum.charttime
AND io.charttime <= (
DATETIME_ADD(iosum.charttime, INTERVAL '23' HOUR)
)
GROUP BY io.stay_id, io.charttime
)
SELECT
ur.stay_id
, ur.charttime
, wd.weight
, ur.uo
, ur.urineoutput_6hr
, ur.urineoutput_12hr
, ur.urineoutput_24hr
, CASE
WHEN
uo_tm_6hr >= 6 THEN ROUND(
CAST((ur.urineoutput_6hr / wd.weight / uo_tm_6hr) AS NUMERIC), 4
)
END AS uo_mlkghr_6hr
, CASE
WHEN
uo_tm_12hr >= 12 THEN ROUND(
CAST((ur.urineoutput_12hr / wd.weight / uo_tm_12hr) AS NUMERIC)
, 4
)
END AS uo_mlkghr_12hr
, CASE
WHEN
uo_tm_24hr >= 24 THEN ROUND(
CAST((ur.urineoutput_24hr / wd.weight / uo_tm_24hr) AS NUMERIC)
, 4
)
END AS uo_mlkghr_24hr
-- time of earliest UO measurement that was used to calculate the rate
, ROUND(CAST(uo_tm_6hr AS NUMERIC), 2) AS uo_tm_6hr
, ROUND(CAST(uo_tm_12hr AS NUMERIC), 2) AS uo_tm_12hr
, ROUND(CAST(uo_tm_24hr AS NUMERIC), 2) AS uo_tm_24hr
FROM ur_stg ur
LEFT JOIN mimiciv_derived.weight_durations wd
ON ur.stay_id = wd.stay_id
AND ur.charttime > wd.starttime
AND ur.charttime <= wd.endtime
AND wd.weight > 0
;
step-025 生成ventilator_setting表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS ventilator_setting; CREATE TABLE ventilator_setting AS
WITH ce AS (
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
, itemid
-- TODO: clean
, value
, CASE
-- begin fio2 cleaning
WHEN itemid = 223835
THEN
CASE
WHEN valuenum >= 0.20 AND valuenum <= 1
THEN valuenum * 100
-- improperly input data - looks like O2 flow in litres
WHEN valuenum > 1 AND valuenum < 20
THEN null
WHEN valuenum >= 20 AND valuenum <= 100
THEN valuenum
ELSE null END
-- end of fio2 cleaning
-- begin peep cleaning
WHEN itemid IN (220339, 224700)
THEN
CASE
WHEN valuenum > 100 THEN null
WHEN valuenum < 0 THEN null
ELSE valuenum END
-- end peep cleaning
ELSE valuenum END AS valuenum
, valueuom
, storetime
FROM mimiciv_icu.chartevents ce
WHERE ce.value IS NOT NULL
AND ce.stay_id IS NOT NULL
AND ce.itemid IN
(
224688 -- Respiratory Rate (Set)
, 224689 -- Respiratory Rate (spontaneous)
, 224690 -- Respiratory Rate (Total)
, 224687 -- minute volume
, 224685, 224684, 224686 -- tidal volume
, 224696 -- PlateauPressure
, 220339, 224700 -- PEEP
, 223835 -- fio2
, 223849 -- vent mode
, 229314 -- vent mode (Hamilton)
, 223848 -- vent type
, 224691 -- Flow Rate (L)
)
)
SELECT
subject_id
, MAX(stay_id) AS stay_id
, charttime
, MAX(
CASE WHEN itemid = 224688 THEN valuenum ELSE null END
) AS respiratory_rate_set
, MAX(
CASE WHEN itemid = 224690 THEN valuenum ELSE null END
) AS respiratory_rate_total
, MAX(
CASE WHEN itemid = 224689 THEN valuenum ELSE null END
) AS respiratory_rate_spontaneous
, MAX(
CASE WHEN itemid = 224687 THEN valuenum ELSE null END
) AS minute_volume
, MAX(
CASE WHEN itemid = 224684 THEN valuenum ELSE null END
) AS tidal_volume_set
, MAX(
CASE WHEN itemid = 224685 THEN valuenum ELSE null END
) AS tidal_volume_observed
, MAX(
CASE WHEN itemid = 224686 THEN valuenum ELSE null END
) AS tidal_volume_spontaneous
, MAX(
CASE WHEN itemid = 224696 THEN valuenum ELSE null END
) AS plateau_pressure
, MAX(
CASE WHEN itemid IN (220339, 224700) THEN valuenum ELSE null END
) AS peep
, MAX(CASE WHEN itemid = 223835 THEN valuenum ELSE null END) AS fio2
, MAX(CASE WHEN itemid = 224691 THEN valuenum ELSE null END) AS flow_rate
, MAX(CASE WHEN itemid = 223849 THEN value ELSE null END) AS ventilator_mode
, MAX(
CASE WHEN itemid = 229314 THEN value ELSE null END
) AS ventilator_mode_hamilton
, MAX(CASE WHEN itemid = 223848 THEN value ELSE null END) AS ventilator_type
FROM ce
GROUP BY subject_id, charttime
;step-026 生成vitalsign表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS vitalsign; CREATE TABLE vitalsign AS
-- This query pivots the vital signs for the entire patient stay.
-- The result is a tabler with stay_id, charttime, and various
-- vital signs, with one row per charted time.
SELECT
ce.subject_id
, ce.stay_id
, ce.charttime
, AVG(CASE WHEN itemid IN (220045)
AND valuenum > 0
AND valuenum < 300
THEN valuenum END
) AS heart_rate
, AVG(CASE WHEN itemid IN (220179, 220050, 225309)
AND valuenum > 0
AND valuenum < 400
THEN valuenum END
) AS sbp
, AVG(CASE WHEN itemid IN (220180, 220051, 225310)
AND valuenum > 0
AND valuenum < 300
THEN valuenum END
) AS dbp
, AVG(CASE WHEN itemid IN (220052, 220181, 225312)
AND valuenum > 0
AND valuenum < 300
THEN valuenum END
) AS mbp
, AVG(CASE WHEN itemid = 220179
AND valuenum > 0
AND valuenum < 400
THEN valuenum END
) AS sbp_ni
, AVG(CASE WHEN itemid = 220180
AND valuenum > 0
AND valuenum < 300
THEN valuenum END
) AS dbp_ni
, AVG(CASE WHEN itemid = 220181
AND valuenum > 0
AND valuenum < 300
THEN valuenum END
) AS mbp_ni
, AVG(CASE WHEN itemid IN (220210, 224690)
AND valuenum > 0
AND valuenum < 70
THEN valuenum END
) AS resp_rate
, ROUND(CAST(
AVG(CASE
-- converted to degC in valuenum call
WHEN itemid IN (223761)
AND valuenum > 70
AND valuenum < 120
THEN (valuenum - 32) / 1.8
-- already in degC, no conversion necessary
WHEN itemid IN (223762)
AND valuenum > 10
AND valuenum < 50
THEN valuenum END)
AS NUMERIC), 2) AS temperature
, MAX(CASE WHEN itemid = 224642 THEN value END
) AS temperature_site
, AVG(CASE WHEN itemid IN (220277)
AND valuenum > 0
AND valuenum <= 100
THEN valuenum END
) AS spo2
, AVG(CASE WHEN itemid IN (225664, 220621, 226537)
AND valuenum > 0
THEN valuenum END
) AS glucose
FROM mimiciv_icu.chartevents ce
WHERE ce.stay_id IS NOT NULL
AND ce.itemid IN
(
220045 -- Heart Rate
, 225309 -- ART BP Systolic
, 225310 -- ART BP Diastolic
, 225312 -- ART BP Mean
, 220050 -- Arterial Blood Pressure systolic
, 220051 -- Arterial Blood Pressure diastolic
, 220052 -- Arterial Blood Pressure mean
, 220179 -- Non Invasive Blood Pressure systolic
, 220180 -- Non Invasive Blood Pressure diastolic
, 220181 -- Non Invasive Blood Pressure mean
, 220210 -- Respiratory Rate
, 224690 -- Respiratory Rate (Total)
, 220277 -- SPO2, peripheral
-- GLUCOSE, both lab and fingerstick
, 225664 -- Glucose finger stick
, 220621 -- Glucose (serum)
, 226537 -- Glucose (whole blood)
-- TEMPERATURE
-- 226329 -- Blood Temperature CCO (C)
, 223762 -- "Temperature Celsius"
, 223761 -- "Temperature Fahrenheit"
, 224642 -- Temperature Site
)
GROUP BY ce.subject_id, ce.stay_id, ce.charttime
;step-027 生成charlson表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS charlson; CREATE TABLE charlson AS
-- ------------------------------------------------------------------
-- This query extracts Charlson Comorbidity Index (CCI) based on the
-- recorded ICD-9 and ICD-10 codes.
--
-- Reference for CCI:
-- (1) Charlson ME, Pompei P, Ales KL, MacKenzie CR. (1987) A new method
-- of classifying prognostic comorbidity in longitudinal studies:
-- development and validation.J Chronic Dis; 40(5):373-83.
--
-- (2) Charlson M, Szatrowski TP, Peterson J, Gold J. (1994) Validation
-- of a combined comorbidity index. J Clin Epidemiol; 47(11):1245-51.
--
-- Reference for ICD-9-CM and ICD-10 Coding Algorithms for Charlson
-- Comorbidities:
-- (3) Quan H, Sundararajan V, Halfon P, et al. Coding algorithms for
-- defining Comorbidities in ICD-9-CM and ICD-10 administrative data.
-- Med Care. 2005 Nov; 43(11): 1130-9.
-- ------------------------------------------------------------------
WITH diag AS (
SELECT
hadm_id
, CASE WHEN icd_version = 9 THEN icd_code ELSE NULL END AS icd9_code
, CASE WHEN icd_version = 10 THEN icd_code ELSE NULL END AS icd10_code
FROM mimiciv_hosp.diagnoses_icd
)
, com AS (
SELECT
ad.hadm_id
-- Myocardial infarction
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('410', '412')
OR
SUBSTR(icd10_code, 1, 3) IN ('I21', 'I22')
OR
SUBSTR(icd10_code, 1, 4) = 'I252'
THEN 1
ELSE 0 END) AS myocardial_infarct
-- Congestive heart failure
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '428'
OR
SUBSTR(
icd9_code, 1, 5
) IN ('39891', '40201', '40211', '40291', '40401', '40403'
, '40411', '40413', '40491', '40493')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '4254' AND '4259'
OR
SUBSTR(icd10_code, 1, 3) IN ('I43', 'I50')
OR
SUBSTR(
icd10_code, 1, 4
) IN ('I099', 'I110', 'I130', 'I132', 'I255', 'I420'
, 'I425', 'I426', 'I427', 'I428', 'I429', 'P290'
)
THEN 1
ELSE 0 END) AS congestive_heart_failure
-- Peripheral vascular disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('440', '441')
OR
SUBSTR(
icd9_code, 1, 4
) IN ('0930', '4373', '4471', '5571', '5579', 'V434')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '4431' AND '4439'
OR
SUBSTR(icd10_code, 1, 3) IN ('I70', 'I71')
OR
SUBSTR(icd10_code, 1, 4) IN ('I731', 'I738', 'I739', 'I771', 'I790'
, 'I792'
, 'K551'
, 'K558'
, 'K559'
, 'Z958'
, 'Z959'
)
THEN 1
ELSE 0 END) AS peripheral_vascular_disease
-- Cerebrovascular disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '430' AND '438'
OR
SUBSTR(icd9_code, 1, 5) = '36234'
OR
SUBSTR(icd10_code, 1, 3) IN ('G45', 'G46')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'I60' AND 'I69'
OR
SUBSTR(icd10_code, 1, 4) = 'H340'
THEN 1
ELSE 0 END) AS cerebrovascular_disease
-- Dementia
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '290'
OR
SUBSTR(icd9_code, 1, 4) IN ('2941', '3312')
OR
SUBSTR(icd10_code, 1, 3) IN ('F00', 'F01', 'F02', 'F03', 'G30')
OR
SUBSTR(icd10_code, 1, 4) IN ('F051', 'G311')
THEN 1
ELSE 0 END) AS dementia
-- Chronic pulmonary disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '490' AND '505'
OR
SUBSTR(icd9_code, 1, 4) IN ('4168', '4169', '5064', '5081', '5088')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'J40' AND 'J47'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'J60' AND 'J67'
OR
SUBSTR(icd10_code, 1, 4) IN ('I278', 'I279', 'J684', 'J701', 'J703')
THEN 1
ELSE 0 END) AS chronic_pulmonary_disease
-- Rheumatic disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '725'
OR
SUBSTR(icd9_code, 1, 4) IN ('4465', '7100', '7101', '7102', '7103'
, '7104', '7140', '7141', '7142', '7148'
)
OR
SUBSTR(icd10_code, 1, 3) IN ('M05', 'M06', 'M32', 'M33', 'M34')
OR
SUBSTR(icd10_code, 1, 4) IN ('M315', 'M351', 'M353', 'M360')
THEN 1
ELSE 0 END) AS rheumatic_disease
-- Peptic ulcer disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('531', '532', '533', '534')
OR
SUBSTR(icd10_code, 1, 3) IN ('K25', 'K26', 'K27', 'K28')
THEN 1
ELSE 0 END) AS peptic_ulcer_disease
-- Mild liver disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('570', '571')
OR
SUBSTR(
icd9_code, 1, 4
) IN ('0706', '0709', '5733', '5734', '5738', '5739', 'V427')
OR
SUBSTR(
icd9_code, 1, 5
) IN ('07022', '07023', '07032', '07033', '07044', '07054')
OR
SUBSTR(icd10_code, 1, 3) IN ('B18', 'K73', 'K74')
OR
SUBSTR(
icd10_code, 1, 4
) IN ('K700', 'K701', 'K702', 'K703', 'K709', 'K713'
, 'K714', 'K715', 'K717', 'K760', 'K762'
, 'K763', 'K764', 'K768', 'K769', 'Z944')
THEN 1
ELSE 0 END) AS mild_liver_disease
-- Diabetes without chronic complication
, MAX(CASE WHEN
SUBSTR(
icd9_code, 1, 4
) IN ('2500', '2501', '2502', '2503', '2508', '2509')
OR
SUBSTR(
icd10_code, 1, 4
) IN ('E100', 'E10l', 'E106', 'E108', 'E109', 'E110', 'E111'
, 'E116'
, 'E118'
, 'E119'
, 'E120'
, 'E121'
, 'E126'
, 'E128'
, 'E129'
, 'E130'
, 'E131'
, 'E136'
, 'E138'
, 'E139'
, 'E140'
, 'E141', 'E146', 'E148', 'E149')
THEN 1
ELSE 0 END) AS diabetes_without_cc
-- Diabetes with chronic complication
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 4) IN ('2504', '2505', '2506', '2507')
OR
SUBSTR(
icd10_code, 1, 4
) IN ('E102', 'E103', 'E104', 'E105', 'E107', 'E112', 'E113'
, 'E114'
, 'E115'
, 'E117'
, 'E122'
, 'E123'
, 'E124'
, 'E125'
, 'E127'
, 'E132'
, 'E133'
, 'E134'
, 'E135'
, 'E137'
, 'E142'
, 'E143', 'E144', 'E145', 'E147')
THEN 1
ELSE 0 END) AS diabetes_with_cc
-- Hemiplegia or paraplegia
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('342', '343')
OR
SUBSTR(icd9_code, 1, 4) IN ('3341', '3440', '3441', '3442'
, '3443', '3444', '3445', '3446', '3449'
)
OR
SUBSTR(icd10_code, 1, 3) IN ('G81', 'G82')
OR
SUBSTR(icd10_code, 1, 4) IN ('G041', 'G114', 'G801', 'G802', 'G830'
, 'G831'
, 'G832'
, 'G833'
, 'G834'
, 'G839'
)
THEN 1
ELSE 0 END) AS paraplegia
-- Renal disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('582', '585', '586', 'V56')
OR
SUBSTR(icd9_code, 1, 4) IN ('5880', 'V420', 'V451')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '5830' AND '5837'
OR
SUBSTR(
icd9_code, 1, 5
) IN (
'40301'
, '40311'
, '40391'
, '40402'
, '40403'
, '40412'
, '40413'
, '40492'
, '40493'
)
OR
SUBSTR(icd10_code, 1, 3) IN ('N18', 'N19')
OR
SUBSTR(icd10_code, 1, 4) IN ('I120', 'I131', 'N032', 'N033', 'N034'
, 'N035'
, 'N036'
, 'N037'
, 'N052'
, 'N053'
, 'N054'
, 'N055'
, 'N056'
, 'N057'
, 'N250'
, 'Z490'
, 'Z491'
, 'Z492'
, 'Z940'
, 'Z992'
)
THEN 1
ELSE 0 END) AS renal_disease
-- Any malignancy, including lymphoma and leukemia,
-- except malignant neoplasm of skin.
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '140' AND '172'
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '1740' AND '1958'
OR
SUBSTR(icd9_code, 1, 3) BETWEEN '200' AND '208'
OR
SUBSTR(icd9_code, 1, 4) = '2386'
OR
SUBSTR(icd10_code, 1, 3) IN ('C43', 'C88')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C00' AND 'C26'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C30' AND 'C34'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C37' AND 'C41'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C45' AND 'C58'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C60' AND 'C76'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C81' AND 'C85'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C90' AND 'C97'
THEN 1
ELSE 0 END) AS malignant_cancer
-- Moderate or severe liver disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 4) IN ('4560', '4561', '4562')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '5722' AND '5728'
OR
SUBSTR(
icd10_code, 1, 4
) IN ('I850', 'I859', 'I864', 'I982', 'K704', 'K711'
, 'K721', 'K729', 'K765', 'K766', 'K767')
THEN 1
ELSE 0 END) AS severe_liver_disease
-- Metastatic solid tumor
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('196', '197', '198', '199')
OR
SUBSTR(icd10_code, 1, 3) IN ('C77', 'C78', 'C79', 'C80')
THEN 1
ELSE 0 END) AS metastatic_solid_tumor
-- AIDS/HIV
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('042', '043', '044')
OR
SUBSTR(icd10_code, 1, 3) IN ('B20', 'B21', 'B22', 'B24')
THEN 1
ELSE 0 END) AS aids
FROM mimiciv_hosp.admissions ad
LEFT JOIN diag
ON ad.hadm_id = diag.hadm_id
GROUP BY ad.hadm_id
)
, ag AS (
SELECT
hadm_id
, age
, CASE WHEN age <= 50 THEN 0
WHEN age <= 60 THEN 1
WHEN age <= 70 THEN 2
WHEN age <= 80 THEN 3
ELSE 4 END AS age_score
FROM mimiciv_derived.age
)
SELECT
ad.subject_id
, ad.hadm_id
, ag.age_score
, myocardial_infarct
, congestive_heart_failure
, peripheral_vascular_disease
, cerebrovascular_disease
, dementia
, chronic_pulmonary_disease
, rheumatic_disease
, peptic_ulcer_disease
, mild_liver_disease
, diabetes_without_cc
, diabetes_with_cc
, paraplegia
, renal_disease
, malignant_cancer
, severe_liver_disease
, metastatic_solid_tumor
, aids
-- Calculate the Charlson Comorbidity Score using the original
-- weights from Charlson, 1987.
, age_score
+ myocardial_infarct + congestive_heart_failure
+ peripheral_vascular_disease + cerebrovascular_disease
+ dementia + chronic_pulmonary_disease
+ rheumatic_disease + peptic_ulcer_disease
+ GREATEST(mild_liver_disease, 3 * severe_liver_disease)
+ GREATEST(2 * diabetes_with_cc, diabetes_without_cc)
+ GREATEST(2 * malignant_cancer, 6 * metastatic_solid_tumor)
+ 2 * paraplegia + 2 * renal_disease
+ 6 * aids
AS charlson_comorbidity_index
FROM mimiciv_hosp.admissions ad
LEFT JOIN com
ON ad.hadm_id = com.hadm_id
LEFT JOIN ag
ON com.hadm_id = ag.hadm_id
;
step-028 生成antibiotic表
耗时12 分 31 秒
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS antibiotic; CREATE TABLE antibiotic AS
WITH abx AS (
SELECT DISTINCT
drug
, route
, CASE
WHEN LOWER(drug) LIKE '%adoxa%' THEN 1
WHEN LOWER(drug) LIKE '%ala-tet%' THEN 1
WHEN LOWER(drug) LIKE '%alodox%' THEN 1
WHEN LOWER(drug) LIKE '%amikacin%' THEN 1
WHEN LOWER(drug) LIKE '%amikin%' THEN 1
WHEN LOWER(drug) LIKE '%amoxicill%' THEN 1
WHEN LOWER(drug) LIKE '%amphotericin%' THEN 1
WHEN LOWER(drug) LIKE '%anidulafungin%' THEN 1
WHEN LOWER(drug) LIKE '%ancef%' THEN 1
WHEN LOWER(drug) LIKE '%clavulanate%' THEN 1
WHEN LOWER(drug) LIKE '%ampicillin%' THEN 1
WHEN LOWER(drug) LIKE '%augmentin%' THEN 1
WHEN LOWER(drug) LIKE '%avelox%' THEN 1
WHEN LOWER(drug) LIKE '%avidoxy%' THEN 1
WHEN LOWER(drug) LIKE '%azactam%' THEN 1
WHEN LOWER(drug) LIKE '%azithromycin%' THEN 1
WHEN LOWER(drug) LIKE '%aztreonam%' THEN 1
WHEN LOWER(drug) LIKE '%axetil%' THEN 1
WHEN LOWER(drug) LIKE '%bactocill%' THEN 1
WHEN LOWER(drug) LIKE '%bactrim%' THEN 1
WHEN LOWER(drug) LIKE '%bactroban%' THEN 1
WHEN LOWER(drug) LIKE '%bethkis%' THEN 1
WHEN LOWER(drug) LIKE '%biaxin%' THEN 1
WHEN LOWER(drug) LIKE '%bicillin l-a%' THEN 1
WHEN LOWER(drug) LIKE '%cayston%' THEN 1
WHEN LOWER(drug) LIKE '%cefazolin%' THEN 1
WHEN LOWER(drug) LIKE '%cedax%' THEN 1
WHEN LOWER(drug) LIKE '%cefoxitin%' THEN 1
WHEN LOWER(drug) LIKE '%ceftazidime%' THEN 1
WHEN LOWER(drug) LIKE '%cefaclor%' THEN 1
WHEN LOWER(drug) LIKE '%cefadroxil%' THEN 1
WHEN LOWER(drug) LIKE '%cefdinir%' THEN 1
WHEN LOWER(drug) LIKE '%cefditoren%' THEN 1
WHEN LOWER(drug) LIKE '%cefepime%' THEN 1
WHEN LOWER(drug) LIKE '%cefotan%' THEN 1
WHEN LOWER(drug) LIKE '%cefotetan%' THEN 1
WHEN LOWER(drug) LIKE '%cefotaxime%' THEN 1
WHEN LOWER(drug) LIKE '%ceftaroline%' THEN 1
WHEN LOWER(drug) LIKE '%cefpodoxime%' THEN 1
WHEN LOWER(drug) LIKE '%cefpirome%' THEN 1
WHEN LOWER(drug) LIKE '%cefprozil%' THEN 1
WHEN LOWER(drug) LIKE '%ceftibuten%' THEN 1
WHEN LOWER(drug) LIKE '%ceftin%' THEN 1
WHEN LOWER(drug) LIKE '%ceftriaxone%' THEN 1
WHEN LOWER(drug) LIKE '%cefuroxime%' THEN 1
WHEN LOWER(drug) LIKE '%cephalexin%' THEN 1
WHEN LOWER(drug) LIKE '%cephalothin%' THEN 1
WHEN LOWER(drug) LIKE '%cephapririn%' THEN 1
WHEN LOWER(drug) LIKE '%chloramphenicol%' THEN 1
WHEN LOWER(drug) LIKE '%cipro%' THEN 1
WHEN LOWER(drug) LIKE '%ciprofloxacin%' THEN 1
WHEN LOWER(drug) LIKE '%claforan%' THEN 1
WHEN LOWER(drug) LIKE '%clarithromycin%' THEN 1
WHEN LOWER(drug) LIKE '%cleocin%' THEN 1
WHEN LOWER(drug) LIKE '%clindamycin%' THEN 1
WHEN LOWER(drug) LIKE '%cubicin%' THEN 1
WHEN LOWER(drug) LIKE '%dicloxacillin%' THEN 1
WHEN LOWER(drug) LIKE '%dirithromycin%' THEN 1
WHEN LOWER(drug) LIKE '%doryx%' THEN 1
WHEN LOWER(drug) LIKE '%doxycy%' THEN 1
WHEN LOWER(drug) LIKE '%duricef%' THEN 1
WHEN LOWER(drug) LIKE '%dynacin%' THEN 1
WHEN LOWER(drug) LIKE '%ery-tab%' THEN 1
WHEN LOWER(drug) LIKE '%eryped%' THEN 1
WHEN LOWER(drug) LIKE '%eryc%' THEN 1
WHEN LOWER(drug) LIKE '%erythrocin%' THEN 1
WHEN LOWER(drug) LIKE '%erythromycin%' THEN 1
WHEN LOWER(drug) LIKE '%factive%' THEN 1
WHEN LOWER(drug) LIKE '%flagyl%' THEN 1
WHEN LOWER(drug) LIKE '%fortaz%' THEN 1
WHEN LOWER(drug) LIKE '%furadantin%' THEN 1
WHEN LOWER(drug) LIKE '%garamycin%' THEN 1
WHEN LOWER(drug) LIKE '%gentamicin%' THEN 1
WHEN LOWER(drug) LIKE '%kanamycin%' THEN 1
WHEN LOWER(drug) LIKE '%keflex%' THEN 1
WHEN LOWER(drug) LIKE '%kefzol%' THEN 1
WHEN LOWER(drug) LIKE '%ketek%' THEN 1
WHEN LOWER(drug) LIKE '%levaquin%' THEN 1
WHEN LOWER(drug) LIKE '%levofloxacin%' THEN 1
WHEN LOWER(drug) LIKE '%lincocin%' THEN 1
WHEN LOWER(drug) LIKE '%linezolid%' THEN 1
WHEN LOWER(drug) LIKE '%macrobid%' THEN 1
WHEN LOWER(drug) LIKE '%macrodantin%' THEN 1
WHEN LOWER(drug) LIKE '%maxipime%' THEN 1
WHEN LOWER(drug) LIKE '%mefoxin%' THEN 1
WHEN LOWER(drug) LIKE '%metronidazole%' THEN 1
WHEN LOWER(drug) LIKE '%meropenem%' THEN 1
WHEN LOWER(drug) LIKE '%methicillin%' THEN 1
WHEN LOWER(drug) LIKE '%minocin%' THEN 1
WHEN LOWER(drug) LIKE '%minocycline%' THEN 1
WHEN LOWER(drug) LIKE '%monodox%' THEN 1
WHEN LOWER(drug) LIKE '%monurol%' THEN 1
WHEN LOWER(drug) LIKE '%morgidox%' THEN 1
WHEN LOWER(drug) LIKE '%moxatag%' THEN 1
WHEN LOWER(drug) LIKE '%moxifloxacin%' THEN 1
WHEN LOWER(drug) LIKE '%mupirocin%' THEN 1
WHEN LOWER(drug) LIKE '%myrac%' THEN 1
WHEN LOWER(drug) LIKE '%nafcillin%' THEN 1
WHEN LOWER(drug) LIKE '%neomycin%' THEN 1
WHEN LOWER(drug) LIKE '%nicazel doxy 30%' THEN 1
WHEN LOWER(drug) LIKE '%nitrofurantoin%' THEN 1
WHEN LOWER(drug) LIKE '%norfloxacin%' THEN 1
WHEN LOWER(drug) LIKE '%noroxin%' THEN 1
WHEN LOWER(drug) LIKE '%ocudox%' THEN 1
WHEN LOWER(drug) LIKE '%ofloxacin%' THEN 1
WHEN LOWER(drug) LIKE '%omnicef%' THEN 1
WHEN LOWER(drug) LIKE '%oracea%' THEN 1
WHEN LOWER(drug) LIKE '%oraxyl%' THEN 1
WHEN LOWER(drug) LIKE '%oxacillin%' THEN 1
WHEN LOWER(drug) LIKE '%pc pen vk%' THEN 1
WHEN LOWER(drug) LIKE '%pce dispertab%' THEN 1
WHEN LOWER(drug) LIKE '%panixine%' THEN 1
WHEN LOWER(drug) LIKE '%pediazole%' THEN 1
WHEN LOWER(drug) LIKE '%penicillin%' THEN 1
WHEN LOWER(drug) LIKE '%periostat%' THEN 1
WHEN LOWER(drug) LIKE '%pfizerpen%' THEN 1
WHEN LOWER(drug) LIKE '%piperacillin%' THEN 1
WHEN LOWER(drug) LIKE '%tazobactam%' THEN 1
WHEN LOWER(drug) LIKE '%primsol%' THEN 1
WHEN LOWER(drug) LIKE '%proquin%' THEN 1
WHEN LOWER(drug) LIKE '%raniclor%' THEN 1
WHEN LOWER(drug) LIKE '%rifadin%' THEN 1
WHEN LOWER(drug) LIKE '%rifampin%' THEN 1
WHEN LOWER(drug) LIKE '%rocephin%' THEN 1
WHEN LOWER(drug) LIKE '%smz-tmp%' THEN 1
WHEN LOWER(drug) LIKE '%septra%' THEN 1
WHEN LOWER(drug) LIKE '%septra ds%' THEN 1
WHEN LOWER(drug) LIKE '%septra%' THEN 1
WHEN LOWER(drug) LIKE '%solodyn%' THEN 1
WHEN LOWER(drug) LIKE '%spectracef%' THEN 1
WHEN LOWER(drug) LIKE '%streptomycin%' THEN 1
WHEN LOWER(drug) LIKE '%sulfadiazine%' THEN 1
WHEN LOWER(drug) LIKE '%sulfamethoxazole%' THEN 1
WHEN LOWER(drug) LIKE '%trimethoprim%' THEN 1
WHEN LOWER(drug) LIKE '%sulfatrim%' THEN 1
WHEN LOWER(drug) LIKE '%sulfisoxazole%' THEN 1
WHEN LOWER(drug) LIKE '%suprax%' THEN 1
WHEN LOWER(drug) LIKE '%synercid%' THEN 1
WHEN LOWER(drug) LIKE '%tazicef%' THEN 1
WHEN LOWER(drug) LIKE '%tetracycline%' THEN 1
WHEN LOWER(drug) LIKE '%timentin%' THEN 1
WHEN LOWER(drug) LIKE '%tobramycin%' THEN 1
WHEN LOWER(drug) LIKE '%trimethoprim%' THEN 1
WHEN LOWER(drug) LIKE '%unasyn%' THEN 1
WHEN LOWER(drug) LIKE '%vancocin%' THEN 1
WHEN LOWER(drug) LIKE '%vancomycin%' THEN 1
WHEN LOWER(drug) LIKE '%vantin%' THEN 1
WHEN LOWER(drug) LIKE '%vibativ%' THEN 1
WHEN LOWER(drug) LIKE '%vibra-tabs%' THEN 1
WHEN LOWER(drug) LIKE '%vibramycin%' THEN 1
WHEN LOWER(drug) LIKE '%zinacef%' THEN 1
WHEN LOWER(drug) LIKE '%zithromax%' THEN 1
WHEN LOWER(drug) LIKE '%zosyn%' THEN 1
WHEN LOWER(drug) LIKE '%zyvox%' THEN 1
ELSE 0
END AS antibiotic
FROM mimiciv_hosp.prescriptions
-- excludes vials/syringe/normal saline, etc
WHERE drug_type NOT IN ('BASE')
-- we exclude routes via the eye, ears, or topically
AND route NOT IN ('OU', 'OS', 'OD', 'AU', 'AS', 'AD', 'TP')
AND LOWER(route) NOT LIKE '%ear%'
AND LOWER(route) NOT LIKE '%eye%'
-- we exclude certain types of antibiotics: topical creams,
-- gels, desens, etc
AND LOWER(drug) NOT LIKE '%cream%'
AND LOWER(drug) NOT LIKE '%desensitization%'
AND LOWER(drug) NOT LIKE '%ophth oint%'
AND LOWER(drug) NOT LIKE '%gel%'
-- other routes not sure about...
-- for sure keep: ('IV','PO','PO/NG','ORAL', 'IV DRIP', 'IV BOLUS')
-- ? VT, PB, PR, PL, NS, NG, NEB, NAS, LOCK, J TUBE, IVT
-- ? IT, IRR, IP, IO, INHALATION, IN, IM
-- ? IJ, IH, G TUBE, DIALYS
-- ?? enemas??
)
SELECT
pr.subject_id, pr.hadm_id
, ie.stay_id
, pr.drug AS antibiotic
, pr.route
, pr.starttime
, pr.stoptime
FROM mimiciv_hosp.prescriptions pr
-- inner join to subselect to only antibiotic prescriptions
INNER JOIN abx
ON pr.drug = abx.drug
-- route is never NULL for antibiotics
-- only ~4000 null rows in prescriptions total.
AND pr.route = abx.route
-- add in stay_id as we use this table for sepsis-3
LEFT JOIN mimiciv_icu.icustays ie
ON pr.hadm_id = ie.hadm_id
AND pr.starttime >= ie.intime
AND pr.starttime < ie.outtime
WHERE abx.antibiotic = 1
;step-029 生成dobutamine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS dobutamine; CREATE TABLE dobutamine AS
-- This query extracts dose+durations of dobutamine administration
-- Local hospital dosage guidance: 2 mcg/kg/min (low) - 40 mcg/kg/min (max)
SELECT
stay_id, linkorderid
-- all rows in mcg/kg/min
, rate AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221653 -- dobutaminestep-030 生成dopamine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS dopamine; CREATE TABLE dopamine AS
-- This query extracts dose+durations of dopamine administration
-- Local hospital dosage guidance: 2 mcg/kg/min (low) - 10 mcg/kg/min (high)
SELECT
stay_id, linkorderid
-- all rows in mcg/kg/min
, rate AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221662 -- dopaminestep-031 生成epinephrine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS epinephrine; CREATE TABLE epinephrine AS
-- This query extracts dose+durations of epinephrine administration
-- Local hospital dosage guidance: 0.2 mcg/kg/min (low) - 2 mcg/kg/min (high)
SELECT
stay_id, linkorderid
-- all rows in mcg/kg/min
, rate AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221289 -- epinephrinestep-032 生成milrinon表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS milrinone; CREATE TABLE milrinone AS
-- This query extracts dose+durations of milrinone administration
-- Local hospital dosage guidance: 0.5 mcg/kg/min (usual)
SELECT
stay_id, linkorderid
-- all rows in mcg/kg/min
, rate AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221986 -- milrinonestep-033 生成neuroblock表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS neuroblock; CREATE TABLE neuroblock AS
-- This query extracts dose+durations of neuromuscular blocking agents
SELECT
stay_id, orderid
, rate AS drug_rate
, amount AS drug_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid IN
(
222062 -- Vecuronium (664 rows, 154 infusion rows)
, 221555 -- Cisatracurium (9334 rows, 8970 infusion rows)
)
AND rate IS NOT NULL -- only continuous infusionsstep-034 生成norepinephrine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS norepinephrine; CREATE TABLE norepinephrine AS
-- This query extracts dose+durations of norepinephrine administration
-- Local hospital dosage guidance: 0.03 mcg/kg/min (low), 0.5 mcg/kg/min (high)
SELECT
stay_id, linkorderid
-- two rows in mg/kg/min... rest in mcg/kg/min
-- the rows in mg/kg/min are documented incorrectly
-- all rows converted into mcg/kg/min (equiv to ug/kg/min)
, CASE WHEN rateuom = 'mg/kg/min' AND patientweight = 1 THEN rate
-- below row is written for completion, but doesn't impact rows
WHEN rateuom = 'mg/kg/min' THEN rate * 1000.0
ELSE rate END AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221906 -- norepinephrinestep-035 生成phenylephrine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS phenylephrine; CREATE TABLE phenylephrine AS
-- This query extracts dose+durations of phenylephrine administration
-- Local hospital dosage guidance: 0.5 mcg/kg/min (low) - 5 mcg/kg/min (high)
SELECT
stay_id, linkorderid
-- one row in mcg/min, the rest in mcg/kg/min
, CASE WHEN rateuom = 'mcg/min' THEN rate / patientweight
ELSE rate END AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 221749 -- phenylephrinestep-036 生成vasopressin表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS vasopressin; CREATE TABLE vasopressin AS
-- This query extracts dose+durations of vasopressin administration
-- Local hospital dosage guidance: 1.2 units/hour (low) - 2.4 units/hour (high)
SELECT
stay_id, linkorderid
-- three rows in units/min, rest in units/hour
-- the three rows in units/min look reasonable and
-- fit with the patient course
-- convert all rows to units/hour
, CASE WHEN rateuom = 'units/min' THEN rate * 60.0
ELSE rate END AS vaso_rate
, amount AS vaso_amount
, starttime
, endtime
FROM mimiciv_icu.inputevents
WHERE itemid = 222315 -- vasopressinstep-037 生成crrt表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS crrt; CREATE TABLE crrt AS
WITH crrt_settings AS (
SELECT ce.stay_id, ce.charttime
, CASE WHEN ce.itemid = 227290 THEN ce.value END AS crrt_mode
, CASE
WHEN ce.itemid = 224149 THEN ce.valuenum ELSE NULL
END AS accesspressure
-- (ml/min)
, CASE
WHEN ce.itemid = 224144 THEN ce.valuenum ELSE NULL
END AS bloodflow
-- (ACD-A)
, CASE WHEN ce.itemid = 228004 THEN ce.valuenum ELSE NULL END AS citrate
, CASE
WHEN ce.itemid = 225183 THEN ce.valuenum ELSE NULL
END AS currentgoal
, CASE
WHEN ce.itemid = 225977 THEN ce.value ELSE NULL
END AS dialysatefluid
, CASE
WHEN ce.itemid = 224154 THEN ce.valuenum ELSE NULL
END AS dialysaterate
, CASE
WHEN ce.itemid = 224151 THEN ce.valuenum ELSE NULL
END AS effluentpressure
, CASE
WHEN ce.itemid = 224150 THEN ce.valuenum ELSE NULL
END AS filterpressure
-- (units/mL)
, CASE
WHEN ce.itemid = 225958 THEN ce.value ELSE NULL
END AS heparinconcentration
-- (per hour)
, CASE
WHEN ce.itemid = 224145 THEN ce.valuenum ELSE NULL
END AS heparindose
-- below may not account for drug infusion,
-- hyperalimentation, and/or anticoagulants infused
, CASE
WHEN ce.itemid = 224191 THEN ce.valuenum ELSE NULL
END AS hourlypatientfluidremoval
, CASE
WHEN ce.itemid = 228005 THEN ce.valuenum ELSE NULL
END AS prefilterreplacementrate
, CASE
WHEN ce.itemid = 228006 THEN ce.valuenum ELSE NULL
END AS postfilterreplacementrate
, CASE
WHEN ce.itemid = 225976 THEN ce.value ELSE NULL
END AS replacementfluid
, CASE
WHEN ce.itemid = 224153 THEN ce.valuenum ELSE NULL
END AS replacementrate
, CASE
WHEN ce.itemid = 224152 THEN ce.valuenum ELSE NULL
END AS returnpressure
, CASE
WHEN ce.itemid = 226457 THEN ce.valuenum
END AS ultrafiltrateoutput
-- separate system integrity into sub components
-- need to do this as 224146 has multiple unique values
-- for a single charttime
-- e.g. "Clots Present" and "Active" at same time
, CASE
WHEN ce.itemid = 224146
AND ce.value IN (
'Active', 'Initiated', 'Reinitiated', 'New Filter'
)
THEN 1
WHEN ce.itemid = 224146
AND ce.value IN ('Recirculating', 'Discontinued')
THEN 0
ELSE NULL END AS system_active
, CASE
WHEN ce.itemid = 224146
AND ce.value IN ('Clots Present', 'Clots Present')
THEN 1
WHEN ce.itemid = 224146
AND ce.value IN ('No Clot Present', 'No Clot Present')
THEN 0
ELSE NULL END AS clots
, CASE
WHEN ce.itemid = 224146
AND ce.value IN ('Clots Increasing', 'Clot Increasing')
THEN 1
ELSE NULL END AS clots_increasing
, CASE
WHEN ce.itemid = 224146
AND ce.value IN ('Clotted')
THEN 1
ELSE NULL END AS clotted
FROM mimiciv_icu.chartevents ce
WHERE ce.itemid IN
(
-- MetaVision ITEMIDs
227290 -- CRRT Mode
, 224146 -- System Integrity
-- 225956, -- Reason for CRRT Filter Change
-- above itemid is one of: Clotted, Line Changed, Procedure
-- only ~200 rows, not super useful
, 224149 -- Access Pressure
, 224144 -- Blood Flow (ml/min)
, 228004 -- Citrate (ACD-A)
, 225183 -- Current Goal
, 225977 -- Dialysate Fluid
, 224154 -- Dialysate Rate
, 224151 -- Effluent Pressure
, 224150 -- Filter Pressure
, 225958 -- Heparin Concentration (units/mL)
, 224145 -- Heparin Dose (per hour)
, 224191 -- Hourly Patient Fluid Removal
, 228005 -- PBP (Prefilter) Replacement Rate
, 228006 -- Post Filter Replacement Rate
, 225976 -- Replacement Fluid
, 224153 -- Replacement Rate
, 224152 -- Return Pressure
, 226457 -- Ultrafiltrate Output
)
AND ce.value IS NOT NULL
)
-- use MAX() to collapse to a single row
-- there is only ever 1 row for unique combinations of stay_id/charttime/itemid
SELECT stay_id
, charttime
, MAX(crrt_mode) AS crrt_mode
, MAX(accesspressure) AS access_pressure
, MAX(bloodflow) AS blood_flow
, MAX(citrate) AS citrate
, MAX(currentgoal) AS current_goal
, MAX(dialysatefluid) AS dialysate_fluid
, MAX(dialysaterate) AS dialysate_rate
, MAX(effluentpressure) AS effluent_pressure
, MAX(filterpressure) AS filter_pressure
, MAX(heparinconcentration) AS heparin_concentration
, MAX(heparindose) AS heparin_dose
, MAX(hourlypatientfluidremoval) AS hourly_patient_fluid_removal
, MAX(prefilterreplacementrate) AS prefilter_replacement_rate
, MAX(postfilterreplacementrate) AS postfilter_replacement_rate
, MAX(replacementfluid) AS replacement_fluid
, MAX(replacementrate) AS replacement_rate
, MAX(returnpressure) AS return_pressure
, MAX(ultrafiltrateoutput) AS ultrafiltrate_output
, MAX(system_active) AS system_active
, MAX(clots) AS clots
, MAX(clots_increasing) AS clots_increasing
, MAX(clotted) AS clotted
FROM crrt_settings
GROUP BY stay_id, charttimestep-038 生成invasive_line表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS invasive_line; CREATE TABLE invasive_line AS
-- metavision
WITH mv AS (
SELECT
stay_id
-- since metavision separates lines using itemid,
-- we can use it as the line number
, mv.itemid AS line_number
, di.label AS line_type
, mv.location AS line_site
, starttime, endtime
FROM mimiciv_icu.procedureevents mv
INNER JOIN mimiciv_icu.d_items di
ON mv.itemid = di.itemid
WHERE mv.itemid IN
(
227719 -- AVA Line
, 225752 -- Arterial Line
, 224269 -- CCO PAC
, 224267 -- Cordis/Introducer
, 224270 -- Dialysis Catheter
, 224272 -- IABP line
, 226124 -- ICP Catheter
, 228169 -- Impella Line
, 225202 -- Indwelling Port (PortaCath)
, 228286 -- Intraosseous Device
, 225204 -- Midline
, 224263 -- Multi Lumen
, 224560 -- PA Catheter
, 224264 -- PICC Line
, 225203 -- Pheresis Catheter
, 224273 -- Presep Catheter
, 225789 -- Sheath
, 225761 -- Sheath Insertion
, 228201 -- Tandem Heart Access Line
, 228202 -- Tandem Heart Return Line
, 224268 -- Trauma line
, 225199 -- Triple Introducer
, 225315 -- Tunneled (Hickman) Line
, 225205 -- RIC
)
)
-- as a final step, combine any similar terms together
SELECT
stay_id
, CASE
WHEN line_type IN ('Arterial Line', 'A-Line') THEN 'Arterial'
WHEN
line_type IN (
'CCO PA Line', 'CCO PAC'
) THEN 'Continuous Cardiac Output PA'
WHEN line_type IN ('Dialysis Catheter', 'Dialysis Line') THEN 'Dialysis'
WHEN line_type IN ('Hickman', 'Tunneled (Hickman) Line') THEN 'Hickman'
WHEN line_type IN ('IABP', 'IABP line') THEN 'IABP'
WHEN line_type IN ('Multi Lumen', 'Multi-lumen') THEN 'Multi Lumen'
WHEN line_type IN ('PA Catheter', 'PA line') THEN 'PA'
WHEN line_type IN ('PICC Line', 'PICC line') THEN 'PICC'
WHEN line_type IN ('Pre-Sep Catheter', 'Presep Catheter') THEN 'Pre-Sep'
WHEN line_type IN ('Trauma Line', 'Trauma line') THEN 'Trauma'
WHEN
line_type IN (
'Triple Introducer', 'TripleIntroducer'
) THEN 'Triple Introducer'
WHEN
line_type IN (
'Portacath', 'Indwelling Port (PortaCath)'
) THEN 'Portacath'
-- the following lines were not merged with another line:
-- AVA Line
-- Camino Bolt
-- Cordis/Introducer
-- ICP Catheter
-- Impella Line
-- Intraosseous Device
-- Introducer
-- Lumbar Drain
-- Midline
-- Other/Remarks
-- PacerIntroducer
-- PermaCath
-- Pheresis Catheter
-- RIC
-- Sheath
-- Tandem Heart Access Line
-- Tandem Heart Return Line
-- Venous Access
-- Ventriculostomy
ELSE line_type END AS line_type
, CASE
WHEN line_site IN ('Left Antecub', 'Left Antecube') THEN 'Left Antecube'
WHEN line_site IN ('Left Axilla', 'Left Axilla.') THEN 'Left Axilla'
WHEN
line_site IN (
'Left Brachial', 'Left Brachial.'
) THEN 'Left Brachial'
WHEN line_site IN ('Left Femoral', 'Left Femoral.') THEN 'Left Femoral'
WHEN
line_site IN (
'Right Antecub', 'Right Antecube'
) THEN 'Right Antecube'
WHEN line_site IN ('Right Axilla', 'Right Axilla.') THEN 'Right Axilla'
WHEN
line_site IN (
'Right Brachial', 'Right Brachial.'
) THEN 'Right Brachial'
WHEN
line_site IN (
'Right Femoral', 'Right Femoral.'
) THEN 'Right Femoral'
-- the following sites were not merged with other sites:
-- 'Left Foot'
-- 'Left IJ'
-- 'Left Radial'
-- 'Left Subclavian'
-- 'Left Ulnar'
-- 'Left Upper Arm'
-- 'Right Foot'
-- 'Right IJ'
-- 'Right Radial'
-- 'Right Side Head'
-- 'Right Subclavian'
-- 'Right Ulnar'
-- 'Right Upper Arm'
-- 'Transthoracic'
-- 'Other/Remarks'
ELSE line_site END AS line_site
, starttime
, endtime
FROM mv
ORDER BY stay_id, starttime, line_type, line_site;step-039 生成rrt表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS rrt; CREATE TABLE rrt AS
-- Creates a table with stay_id / time / dialysis type (if present)
WITH ce AS (
SELECT ce.stay_id
, ce.charttime
-- when ce.itemid in (152,148,149,146,147,151,150)
-- and value is not null then 1
-- when ce.itemid in (229,235,241,247,253,259,265,271)
-- and value = 'Dialysis Line' then 1
-- when ce.itemid = 466 and value = 'Dialysis RN' then 1
-- when ce.itemid = 927 and value = 'Dialysis Solutions' then 1
-- when ce.itemid = 6250 and value = 'dialys' then 1
-- when ce.
-- when ce.itemid = 582 and value in ('CAVH Start','CAVH D/C',
-- 'CVVHD Start','CVVHD D/C',
-- 'Hemodialysis st','Hemodialysis end') then 1
, CASE
-- metavision itemids
-- checkboxes
WHEN ce.itemid IN
(
226118 -- | Dialysis Catheter placed in outside facility
, 227357 -- | Dialysis Catheter Dressing Occlusive
, 225725 -- | Dialysis Catheter Tip Cultured
) THEN 1
-- numeric data
WHEN ce.itemid IN
(
-- | Hemodialysis Output
226499
-- | Dialysate Rate
, 224154
-- | Dwell Time (Peritoneal Dialysis)
, 225810
-- | Medication Added Amount #1 (Peritoneal Dialysis)
, 225959
-- | Medication Added Amount #2 (Peritoneal Dialysis)
, 227639
, 225183 -- | Current Goal
, 227438 -- | Volume not removed
, 224191 -- | Hourly Patient Fluid Removal
, 225806 -- | Volume In (PD)
, 225807 -- | Volume Out (PD)
, 228004 -- | Citrate (ACD-A)
, 228005 -- | PBP (Prefilter) Replacement Rate
, 228006 -- | Post Filter Replacement Rate
, 224144 -- | Blood Flow (ml/min)
, 224145 -- | Heparin Dose (per hour)
, 224149 -- | Access Pressure
, 224150 -- | Filter Pressure
, 224151 -- | Effluent Pressure
, 224152 -- | Return Pressure
, 224153 -- | Replacement Rate
, 224404 -- | ART Lumen Volume
, 224406 -- | VEN Lumen Volume
, 226457 -- | Ultrafiltrate Output
) THEN 1
-- text fields
WHEN ce.itemid IN
(
224135 -- | Dialysis Access Site
, 224139 -- | Dialysis Site Appearance
, 224146 -- | System Integrity
-- | Dialysis Catheter Site Appear
, 225323
-- | Dialysis Catheter Discontinued
, 225740
-- | Dialysis Catheter Dressing Type
, 225776
-- | Peritoneal Dialysis Fluid Appearance
, 225951
-- | Medication Added #1 (Peritoneal Dialysis)
, 225952
, 225953 -- | Solution (Peritoneal Dialysis)
, 225954 -- | Dialysis Access Type
, 225956 -- | Reason for CRRT Filter Change
, 225958 -- | Heparin Concentration (units/mL)
-- | Medication Added Units #1 (Peritoneal Dialysis)
, 225961
, 225963 -- | Peritoneal Dialysis Catheter Type
, 225965 -- | Peritoneal Dialysis Catheter Status
, 225976 -- | Replacement Fluid
, 225977 -- | Dialysate Fluid
-- | Dialysis Catheter Type | Access Lines - Invasive
, 227124
, 227290 -- | CRRT mode
-- | Medication Added #2 (Peritoneal Dialysis)
, 227638
-- | Medication Added Units #2 (Peritoneal Dialysis)
, 227640
-- | Dialysis Catheter Placement Confirmed by X-ray
, 227753
) THEN 1
ELSE 0 END
AS dialysis_present
, CASE
WHEN ce.itemid = 225965 -- Peritoneal Dialysis Catheter Status
AND value = 'In use' THEN 1
WHEN ce.itemid IN
(
226499 -- | Hemodialysis Output
, 224154 -- | Dialysate Rate
, 225183 -- | Current Goal
, 227438 -- | Volume not removed
, 224191 -- | Hourly Patient Fluid Removal
, 225806 -- | Volume In (PD)
, 225807 -- | Volume Out (PD)
, 228004 -- | Citrate (ACD-A)
, 228005 -- | PBP (Prefilter) Replacement Rat
, 228006 -- | Post Filter Replacement Rate
, 224144 -- | Blood Flow (ml/min)
, 224145 -- | Heparin Dose (per hour)
, 224153 -- | Replacement Rate
, 226457 -- | Ultrafiltrate Output
) THEN 1
ELSE 0 END
AS dialysis_active
, CASE
-- dialysis mode
-- we try to set dialysis mode to one of:
-- CVVH
-- CVVHD
-- CVVHDF
-- SCUF
-- Peritoneal
-- IHD
-- these are the modes in itemid 227290
WHEN ce.itemid = 227290 THEN value
-- itemids which imply a certain dialysis mode
-- peritoneal dialysis
WHEN ce.itemid IN
(
225810 -- | Dwell Time (Peritoneal Dialysis)
, 225806 -- | Volume In (PD)
, 225807 -- | Volume Out (PD)
-- | Dwell Time (Peritoneal Dialysis)
, 225810
-- | Medication Added Amount #2 (Peritoneal Dialysis)
, 227639
-- | Medication Added Amount #1 (Peritoneal Dialysis)
, 225959
-- | Peritoneal Dialysis Fluid Appearance
, 225951
-- | Medication Added #1 (Peritoneal Dialysis)
, 225952
-- | Medication Added Units #1 (Peritoneal Dialysis)
, 225961
, 225953 -- | Solution (Peritoneal Dialysis)
, 225963 -- | Peritoneal Dialysis Catheter Type
, 225965 -- | Peritoneal Dialysis Catheter Status
-- | Medication Added #2 (Peritoneal Dialysis)
, 227638
-- | Medication Added Units #2 (Peritoneal Dialysis)
, 227640
)
THEN 'Peritoneal'
WHEN ce.itemid = 226499
THEN 'IHD'
ELSE NULL END AS dialysis_type
FROM mimiciv_icu.chartevents ce
WHERE ce.itemid IN
(
-- === MetaVision itemids === --
-- Checkboxes
226118 -- | Dialysis Catheter placed in outside facility
, 227357 -- | Dialysis Catheter Dressing Occlusive
, 225725 -- | Dialysis Catheter Tip Cultured
-- Numeric values
, 226499 -- | Hemodialysis Output
, 224154 -- | Dialysate Rate
, 225810 -- | Dwell Time (Peritoneal Dialysis)
, 227639 -- | Medication Added Amount #2 (Peritoneal Dialysis)
, 225183 -- | Current Goal
, 227438 -- | Volume not removed
, 224191 -- | Hourly Patient Fluid Removal
, 225806 -- | Volume In (PD)
, 225807 -- | Volume Out (PD)
, 228004 -- | Citrate (ACD-A)
, 228005 -- | PBP (Prefilter) Replacement Rate
, 228006 -- | Post Filter Replacement Rate
, 224144 -- | Blood Flow (ml/min)
, 224145 -- | Heparin Dose (per hour)
, 224149 -- | Access Pressure
, 224150 -- | Filter Pressure
, 224151 -- | Effluent Pressure
, 224152 -- | Return Pressure
, 224153 -- | Replacement Rate
, 224404 -- | ART Lumen Volume
, 224406 -- | VEN Lumen Volume
, 226457 -- | Ultrafiltrate Output
, 225959 -- | Medication Added Amount #1 (Peritoneal Dialysis)
-- Text values
, 224135 -- | Dialysis Access Site
-- | Dialysis Site Appearance
, 224139
, 224146 -- | System Integrity
, 225323 -- | Dialysis Catheter Site Appear
, 225740 -- | Dialysis Catheter Discontinued
, 225776 -- | Dialysis Catheter Dressing Type
, 225951 -- | Peritoneal Dialysis Fluid Appearance
, 225952 -- | Medication Added #1 (Peritoneal Dialysis)
-- | Solution (Peritoneal Dialysis)
, 225953
, 225954 -- | Dialysis Access Type
-- | Reason for CRRT Filter Change
, 225956
-- | Heparin Concentration (units/mL)
, 225958
, 225961 -- | Medication Added Units #1 (Peritoneal Dialysis)
-- | Peritoneal Dialysis Catheter Type
, 225963
-- | Peritoneal Dialysis Catheter Status
, 225965
, 225976 -- | Replacement Fluid
, 225977 -- | Dialysate Fluid
, 227124 -- | Dialysis Catheter Type
, 227290 -- | CRRT mode
, 227638 -- | Medication Added #2 (Peritoneal Dialysis)
, 227640 -- | Medication Added Units #2 (Peritoneal Dialysis)
, 227753 -- | Dialysis Catheter Placement Confirmed by X-ray
)
AND ce.value IS NOT NULL
)
-- TODO:
-- charttime + dialysis_present + dialysis_active
-- for inputevents_cv, outputevents
-- for procedures_mv, left join and set the dialysis_type
-- , oe AS (
-- SELECT stay_id
-- , charttime
-- , 1 AS dialysis_present
-- , 0 AS dialysis_active
-- , NULL AS dialysis_type
-- FROM mimiciv_icu.outputevents
-- WHERE itemid IN
-- (
-- 40386 -- hemodialysis
-- )
-- AND value > 0 -- also ensures it's not null
-- )
, mv_ranges AS (
SELECT stay_id
, starttime, endtime
, 1 AS dialysis_present
, 1 AS dialysis_active
, 'CRRT' AS dialysis_type
FROM mimiciv_icu.inputevents
WHERE itemid IN
(
227536 -- KCl (CRRT) Medications inputevents_mv Solution
-- Calcium Gluconate (CRRT) Medications inputevents_mv Solution
, 227525
)
AND amount > 0 -- also ensures it's not null
UNION DISTINCT
SELECT stay_id
, starttime, endtime
, 1 AS dialysis_present
, CASE
WHEN itemid NOT IN (224270, 225436) THEN 1 ELSE 0
END AS dialysis_active
, CASE
WHEN itemid = 225441 THEN 'IHD'
-- CVVH (Continuous venovenous hemofiltration)
WHEN itemid = 225802 THEN 'CRRT'
-- CVVHD (Continuous venovenous hemodialysis)
WHEN itemid = 225803 THEN 'CVVHD'
WHEN itemid = 225805 THEN 'Peritoneal'
-- CVVHDF (Continuous venovenous hemodiafiltration)
WHEN itemid = 225809 THEN 'CVVHDF'
-- SCUF (Slow continuous ultra filtration)
WHEN itemid = 225955 THEN 'SCUF'
ELSE NULL END AS dialysis_type
FROM mimiciv_icu.procedureevents
WHERE itemid IN
(
225441 -- | Hemodialysis
, 225802 -- | Dialysis - CRRT
, 225803 -- | Dialysis - CVVHD
, 225805 -- | Peritoneal Dialysis
, 224270 -- | Dialysis Catheter
, 225809 -- | Dialysis - CVVHDF
, 225955 -- | Dialysis - SCUF
, 225436 -- | CRRT Filter Change
)
AND value IS NOT NULL
)
-- union together the charttime tables;
-- append times from mv_ranges to guarantee they exist
, stg0 AS (
SELECT
stay_id, charttime, dialysis_present, dialysis_active, dialysis_type
FROM ce
WHERE dialysis_present = 1
UNION DISTINCT
-- SELECT
-- stay_id, charttime, dialysis_present, dialysis_active, dialysis_type
-- FROM oe
-- WHERE dialysis_present = 1
-- UNION DISTINCT
SELECT
stay_id
, starttime AS charttime
, dialysis_present
, dialysis_active
, dialysis_type
FROM mv_ranges
)
SELECT
stg0.stay_id
, charttime
, COALESCE(mv.dialysis_present, stg0.dialysis_present) AS dialysis_present
, COALESCE(mv.dialysis_active, stg0.dialysis_active) AS dialysis_active
, COALESCE(mv.dialysis_type, stg0.dialysis_type) AS dialysis_type
FROM stg0
LEFT JOIN mv_ranges mv
ON stg0.stay_id = mv.stay_id
AND stg0.charttime >= mv.starttime
AND stg0.charttime <= mv.endtime
;step-040 生成ventilation表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS ventilation; CREATE TABLE ventilation AS
-- Classify oxygen devices and ventilator modes into six clinical categories.
-- Categories include..
-- Invasive oxygen delivery types:
-- Tracheostomy (with or without positive pressure ventilation)
-- InvasiveVent (positive pressure ventilation via endotracheal tube,
-- could be oro/nasotracheal or tracheostomy)
-- Non invasive oxygen delivery types (ref doi:10.1001/jama.2020.9524):
-- NonInvasiveVent (non-invasive positive pressure ventilation)
-- HFNC (high flow nasal oxygen / cannula)
-- SupplementalOxygen (all other non-rebreather,
-- facemask, face tent, nasal prongs...)
-- No oxygen device:
-- None
-- When conflicting settings occur (rare), the priority is:
-- trach > mech vent > NIV > high flow > o2
-- Some useful cases for debugging:
-- stay_id = 30019660 has a tracheostomy placed in the ICU
-- stay_id = 30000117 has explicit documentation of extubation
-- first we collect all times which have relevant documentation
WITH tm AS (
SELECT stay_id, charttime
FROM mimiciv_derived.ventilator_setting
UNION DISTINCT
SELECT stay_id, charttime
FROM mimiciv_derived.oxygen_delivery
)
, vs AS (
SELECT tm.stay_id, tm.charttime
-- source data columns, here for debug
, o2_delivery_device_1
, COALESCE(ventilator_mode, ventilator_mode_hamilton) AS vent_mode
-- case statement determining the type of intervention
-- done in order of priority: trach > mech vent > NIV > high flow > o2
, CASE
-- tracheostomy
WHEN o2_delivery_device_1 IN
(
'Tracheostomy tube'
-- 1135 observations for T-Piece
-- could be either InvasiveVent or Tracheostomy, so omit
-- 'T-piece',
, 'Trach mask ' -- 16435 observations
)
THEN 'Tracheostomy'
-- mechanical / invasive ventilation
WHEN o2_delivery_device_1 IN
(
'Endotracheal tube'
)
OR ventilator_mode IN
(
'(S) CMV'
, 'APRV'
, 'APRV/Biphasic+ApnPress'
, 'APRV/Biphasic+ApnVol'
, 'APV (cmv)'
, 'Ambient'
, 'Apnea Ventilation'
, 'CMV'
, 'CMV/ASSIST'
, 'CMV/ASSIST/AutoFlow'
, 'CMV/AutoFlow'
, 'CPAP/PPS'
, 'CPAP/PSV'
, 'CPAP/PSV+Apn TCPL'
, 'CPAP/PSV+ApnPres'
, 'CPAP/PSV+ApnVol'
, 'MMV'
, 'MMV/AutoFlow'
, 'MMV/PSV'
, 'MMV/PSV/AutoFlow'
, 'P-CMV'
, 'PCV+'
, 'PCV+/PSV'
, 'PCV+Assist'
, 'PRES/AC'
, 'PRVC/AC'
, 'PRVC/SIMV'
, 'PSV/SBT'
, 'SIMV'
, 'SIMV/AutoFlow'
, 'SIMV/PRES'
, 'SIMV/PSV'
, 'SIMV/PSV/AutoFlow'
, 'SIMV/VOL'
, 'SYNCHRON MASTER'
, 'SYNCHRON SLAVE'
, 'VOL/AC'
)
OR ventilator_mode_hamilton IN
(
'APRV'
, 'APV (cmv)'
, 'Ambient'
, '(S) CMV'
, 'P-CMV'
, 'SIMV'
, 'APV (simv)'
, 'P-SIMV'
, 'VS'
, 'ASV'
)
THEN 'InvasiveVent'
-- NIV
WHEN o2_delivery_device_1 IN
(
'Bipap mask ' -- 8997 observations
, 'CPAP mask ' -- 5568 observations
)
OR ventilator_mode_hamilton IN
(
'DuoPaP'
, 'NIV'
, 'NIV-ST'
)
THEN 'NonInvasiveVent'
-- high flow nasal cannula
WHEN o2_delivery_device_1 IN
(
'High flow nasal cannula' -- 925 observations
)
THEN 'HFNC'
-- non rebreather
WHEN o2_delivery_device_1 IN
(
'Non-rebreather' -- 5182 observations
, 'Face tent' -- 24601 observations
, 'Aerosol-cool' -- 24560 observations
, 'Venti mask ' -- 1947 observations
, 'Medium conc mask ' -- 1888 observations
, 'Ultrasonic neb' -- 9 observations
, 'Vapomist' -- 3 observations
, 'Oxymizer' -- 1301 observations
, 'High flow neb' -- 10785 observations
, 'Nasal cannula'
)
THEN 'SupplementalOxygen'
WHEN o2_delivery_device_1 IN
(
'None'
)
THEN 'None'
-- not categorized: other
ELSE NULL END AS ventilation_status
FROM tm
LEFT JOIN mimiciv_derived.ventilator_setting vs
ON tm.stay_id = vs.stay_id
AND tm.charttime = vs.charttime
LEFT JOIN mimiciv_derived.oxygen_delivery od
ON tm.stay_id = od.stay_id
AND tm.charttime = od.charttime
)
, vd0 AS (
SELECT
stay_id, charttime
-- source data columns, here for debug
-- , o2_delivery_device_1
-- , vent_mode
-- carry over the previous charttime which had the same state
, LAG(
charttime, 1
) OVER (
PARTITION BY stay_id, ventilation_status ORDER BY charttime
) AS charttime_lag
-- bring back the next charttime, regardless of the state
-- this will be used as the end time for state transitions
, LEAD(charttime, 1) OVER w AS charttime_lead
, ventilation_status
, LAG(ventilation_status, 1) OVER w AS ventilation_status_lag
FROM vs
WHERE ventilation_status IS NOT NULL
WINDOW w AS (PARTITION BY stay_id ORDER BY charttime)
)
, vd1 AS (
SELECT
stay_id
, charttime
, charttime_lag
, charttime_lead
, ventilation_status
-- source data columns, here for debug
-- , o2_delivery_device_1
-- , vent_mode
-- calculate the time since the last event
, DATETIME_DIFF(charttime, charttime_lag, 'MINUTE') / 60 AS ventduration
-- now we determine if the current ventilation status is "new",
-- or continuing the previous event
, CASE
-- if lag is null, this is the first event for the patient
WHEN ventilation_status_lag IS NULL THEN 1
-- a 14 hour gap always initiates a new event
WHEN DATETIME_DIFF(charttime, charttime_lag, 'HOUR') >= 14 THEN 1
-- not a new event if identical to the last row
WHEN ventilation_status_lag != ventilation_status THEN 1
ELSE 0
END AS new_ventilation_event
FROM vd0
)
, vd2 AS (
SELECT vd1.stay_id, vd1.charttime
, vd1.charttime_lead, vd1.ventilation_status
, ventduration, new_ventilation_event
-- create a cumulative sum of the instances of new ventilation
-- this results in a monotonically increasing integer assigned
-- to each instance of ventilation
, SUM(new_ventilation_event) OVER
(
PARTITION BY stay_id
ORDER BY charttime
) AS vent_seq
FROM vd1
)
-- create the durations for each ventilation instance
SELECT
stay_id
, MIN(charttime) AS starttime
-- for the end time of the ventilation event, the time of the *next* setting
-- i.e. if we go NIV -> O2, the end time of NIV is the first row
-- with a documented O2 device
-- ... unless it's been over 14 hours,
-- in which case it's the last row with a documented NIV.
, MAX(
CASE
WHEN charttime_lead IS NULL
OR DATETIME_DIFF(charttime_lead, charttime, 'HOUR') >= 14
THEN charttime
ELSE charttime_lead
END
) AS endtime
-- all rows with the same vent_num will have the same ventilation_status
-- for efficiency, we use an aggregate here,
-- but we could equally well group by this column
, MAX(ventilation_status) AS ventilation_status
FROM vd2
GROUP BY stay_id, vent_seq
HAVING MIN(charttime) != MAX(charttime)
;step-041 生成first_day_bg表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_bg; CREATE TABLE first_day_bg AS
-- Highest/lowest blood gas values for all blood specimens,
-- including venous/arterial/mixed
SELECT
ie.subject_id
, ie.stay_id
, MIN(lactate) AS lactate_min, MAX(lactate) AS lactate_max
, MIN(ph) AS ph_min, MAX(ph) AS ph_max
, MIN(so2) AS so2_min, MAX(so2) AS so2_max
, MIN(po2) AS po2_min, MAX(po2) AS po2_max
, MIN(pco2) AS pco2_min, MAX(pco2) AS pco2_max
, MIN(aado2) AS aado2_min, MAX(aado2) AS aado2_max
, MIN(aado2_calc) AS aado2_calc_min, MAX(aado2_calc) AS aado2_calc_max
, MIN(pao2fio2ratio) AS pao2fio2ratio_min
, MAX(pao2fio2ratio) AS pao2fio2ratio_max
, MIN(baseexcess) AS baseexcess_min, MAX(baseexcess) AS baseexcess_max
, MIN(bicarbonate) AS bicarbonate_min, MAX(bicarbonate) AS bicarbonate_max
, MIN(totalco2) AS totalco2_min, MAX(totalco2) AS totalco2_max
, MIN(hematocrit) AS hematocrit_min, MAX(hematocrit) AS hematocrit_max
, MIN(hemoglobin) AS hemoglobin_min, MAX(hemoglobin) AS hemoglobin_max
, MIN(carboxyhemoglobin) AS carboxyhemoglobin_min
, MAX(carboxyhemoglobin) AS carboxyhemoglobin_max
, MIN(methemoglobin) AS methemoglobin_min
, MAX(methemoglobin) AS methemoglobin_max
, MIN(temperature) AS temperature_min, MAX(temperature) AS temperature_max
, MIN(chloride) AS chloride_min, MAX(chloride) AS chloride_max
, MIN(calcium) AS calcium_min, MAX(calcium) AS calcium_max
, MIN(glucose) AS glucose_min, MAX(glucose) AS glucose_max
, MIN(potassium) AS potassium_min, MAX(potassium) AS potassium_max
, MIN(sodium) AS sodium_min, MAX(sodium) AS sodium_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.bg bg
ON ie.subject_id = bg.subject_id
AND bg.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND bg.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id
;
step-042 生成first_day_bg_art表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_bg_art; CREATE TABLE first_day_bg_art AS
-- Highest/lowest blood gas values for arterial blood specimens
SELECT
ie.subject_id
, ie.stay_id
, MIN(lactate) AS lactate_min, MAX(lactate) AS lactate_max
, MIN(ph) AS ph_min, MAX(ph) AS ph_max
, MIN(so2) AS so2_min, MAX(so2) AS so2_max
, MIN(po2) AS po2_min, MAX(po2) AS po2_max
, MIN(pco2) AS pco2_min, MAX(pco2) AS pco2_max
, MIN(aado2) AS aado2_min, MAX(aado2) AS aado2_max
, MIN(aado2_calc) AS aado2_calc_min, MAX(aado2_calc) AS aado2_calc_max
, MIN(pao2fio2ratio) AS pao2fio2ratio_min
, MAX(pao2fio2ratio) AS pao2fio2ratio_max
, MIN(baseexcess) AS baseexcess_min, MAX(baseexcess) AS baseexcess_max
, MIN(bicarbonate) AS bicarbonate_min, MAX(bicarbonate) AS bicarbonate_max
, MIN(totalco2) AS totalco2_min, MAX(totalco2) AS totalco2_max
, MIN(hematocrit) AS hematocrit_min, MAX(hematocrit) AS hematocrit_max
, MIN(hemoglobin) AS hemoglobin_min, MAX(hemoglobin) AS hemoglobin_max
, MIN(carboxyhemoglobin) AS carboxyhemoglobin_min
, MAX(carboxyhemoglobin) AS carboxyhemoglobin_max
, MIN(methemoglobin) AS methemoglobin_min
, MAX(methemoglobin) AS methemoglobin_max
, MIN(temperature) AS temperature_min, MAX(temperature) AS temperature_max
, MIN(chloride) AS chloride_min, MAX(chloride) AS chloride_max
, MIN(calcium) AS calcium_min, MAX(calcium) AS calcium_max
, MIN(glucose) AS glucose_min, MAX(glucose) AS glucose_max
, MIN(potassium) AS potassium_min, MAX(potassium) AS potassium_max
, MIN(sodium) AS sodium_min, MAX(sodium) AS sodium_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.bg bg
ON ie.subject_id = bg.subject_id
AND bg.specimen = 'ART.'
AND bg.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND bg.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id
;
step-043 生成first_day_gcs表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_gcs; CREATE TABLE first_day_gcs AS
-- Glasgow Coma Scale, a measure of neurological function.
-- Ranges from 3 (worst, comatose) to 15 (best, normal function).
-- Note:
-- The GCS for sedated patients is defaulted to 15 in this code.
-- This follows common practice for scoring patients with severity
-- of illness scores.
--
-- e.g., from the SAPS II publication:
-- For sedated patients, the Glasgow Coma Score before sedation was used.
-- This was ascertained either from interviewing the physician who ordered
-- the sedation, or by reviewing the patient's medical record.
WITH gcs_final AS (
SELECT
ie.subject_id, ie.stay_id
, g.gcs
, g.gcs_motor
, g.gcs_verbal
, g.gcs_eyes
, g.gcs_unable
-- This sorts the data by GCS
-- rn = 1 is the the lowest total GCS value
, ROW_NUMBER() OVER
(
PARTITION BY g.stay_id
ORDER BY g.gcs
) AS gcs_seq
FROM mimiciv_icu.icustays ie
-- Only get data for the first 24 hours
LEFT JOIN mimiciv_derived.gcs g
ON ie.stay_id = g.stay_id
AND g.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND g.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
SELECT
ie.subject_id
, ie.stay_id
-- The minimum GCS is determined by the above row partition
-- we only join if gcs_seq = 1
, gcs AS gcs_min
, gcs_motor
, gcs_verbal
, gcs_eyes
, gcs_unable
FROM mimiciv_icu.icustays ie
LEFT JOIN gcs_final gs
ON ie.stay_id = gs.stay_id
AND gs.gcs_seq = 1
;
step-044 生成first_day_height表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_height; CREATE TABLE first_day_height AS
-- This query extracts heights for adult ICU patients.
-- It uses all information from the patient's first ICU day.
-- This is done for consistency with other queries.
-- Height is unlikely to change throughout a patient's stay.
SELECT
ie.subject_id
, ie.stay_id
, ROUND(CAST(AVG(height) AS NUMERIC), 2) AS height
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.height ht
ON ie.stay_id = ht.stay_id
AND ht.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND ht.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id;
step-045 生成first_day_lab表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_lab; CREATE TABLE first_day_lab AS
WITH cbc AS (
SELECT
ie.stay_id
, MIN(hematocrit) AS hematocrit_min
, MAX(hematocrit) AS hematocrit_max
, MIN(hemoglobin) AS hemoglobin_min
, MAX(hemoglobin) AS hemoglobin_max
, MIN(platelet) AS platelets_min
, MAX(platelet) AS platelets_max
, MIN(wbc) AS wbc_min
, MAX(wbc) AS wbc_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.complete_blood_count le
ON le.subject_id = ie.subject_id
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
, chem AS (
SELECT
ie.stay_id
, MIN(albumin) AS albumin_min, MAX(albumin) AS albumin_max
, MIN(globulin) AS globulin_min, MAX(globulin) AS globulin_max
, MIN(total_protein) AS total_protein_min
, MAX(total_protein) AS total_protein_max
, MIN(aniongap) AS aniongap_min, MAX(aniongap) AS aniongap_max
, MIN(bicarbonate) AS bicarbonate_min
, MAX(bicarbonate) AS bicarbonate_max
, MIN(bun) AS bun_min, MAX(bun) AS bun_max
, MIN(calcium) AS calcium_min, MAX(calcium) AS calcium_max
, MIN(chloride) AS chloride_min, MAX(chloride) AS chloride_max
, MIN(creatinine) AS creatinine_min, MAX(creatinine) AS creatinine_max
, MIN(glucose) AS glucose_min, MAX(glucose) AS glucose_max
, MIN(sodium) AS sodium_min, MAX(sodium) AS sodium_max
, MIN(potassium) AS potassium_min, MAX(potassium) AS potassium_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.chemistry le
ON le.subject_id = ie.subject_id
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
, diff AS (
SELECT
ie.stay_id
, MIN(basophils_abs) AS abs_basophils_min
, MAX(basophils_abs) AS abs_basophils_max
, MIN(eosinophils_abs) AS abs_eosinophils_min
, MAX(eosinophils_abs) AS abs_eosinophils_max
, MIN(lymphocytes_abs) AS abs_lymphocytes_min
, MAX(lymphocytes_abs) AS abs_lymphocytes_max
, MIN(monocytes_abs) AS abs_monocytes_min
, MAX(monocytes_abs) AS abs_monocytes_max
, MIN(neutrophils_abs) AS abs_neutrophils_min
, MAX(neutrophils_abs) AS abs_neutrophils_max
, MIN(atypical_lymphocytes) AS atyps_min
, MAX(atypical_lymphocytes) AS atyps_max
, MIN(bands) AS bands_min, MAX(bands) AS bands_max
, MIN(immature_granulocytes) AS imm_granulocytes_min
, MAX(immature_granulocytes) AS imm_granulocytes_max
, MIN(metamyelocytes) AS metas_min, MAX(metamyelocytes) AS metas_max
, MIN(nrbc) AS nrbc_min, MAX(nrbc) AS nrbc_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.blood_differential le
ON le.subject_id = ie.subject_id
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
, coag AS (
SELECT
ie.stay_id
, MIN(d_dimer) AS d_dimer_min, MAX(d_dimer) AS d_dimer_max
, MIN(fibrinogen) AS fibrinogen_min, MAX(fibrinogen) AS fibrinogen_max
, MIN(thrombin) AS thrombin_min, MAX(thrombin) AS thrombin_max
, MIN(inr) AS inr_min, MAX(inr) AS inr_max
, MIN(pt) AS pt_min, MAX(pt) AS pt_max
, MIN(ptt) AS ptt_min, MAX(ptt) AS ptt_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.coagulation le
ON le.subject_id = ie.subject_id
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
, enz AS (
SELECT
ie.stay_id
, MIN(alt) AS alt_min, MAX(alt) AS alt_max
, MIN(alp) AS alp_min, MAX(alp) AS alp_max
, MIN(ast) AS ast_min, MAX(ast) AS ast_max
, MIN(amylase) AS amylase_min, MAX(amylase) AS amylase_max
, MIN(bilirubin_total) AS bilirubin_total_min
, MAX(bilirubin_total) AS bilirubin_total_max
, MIN(bilirubin_direct) AS bilirubin_direct_min
, MAX(bilirubin_direct) AS bilirubin_direct_max
, MIN(bilirubin_indirect) AS bilirubin_indirect_min
, MAX(bilirubin_indirect) AS bilirubin_indirect_max
, MIN(ck_cpk) AS ck_cpk_min, MAX(ck_cpk) AS ck_cpk_max
, MIN(ck_mb) AS ck_mb_min, MAX(ck_mb) AS ck_mb_max
, MIN(ggt) AS ggt_min, MAX(ggt) AS ggt_max
, MIN(ld_ldh) AS ld_ldh_min, MAX(ld_ldh) AS ld_ldh_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.enzyme le
ON le.subject_id = ie.subject_id
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
SELECT
ie.subject_id
, ie.stay_id
-- complete blood count
, hematocrit_min, hematocrit_max
, hemoglobin_min, hemoglobin_max
, platelets_min, platelets_max
, wbc_min, wbc_max
-- chemistry
, albumin_min, albumin_max
, globulin_min, globulin_max
, total_protein_min, total_protein_max
, aniongap_min, aniongap_max
, bicarbonate_min, bicarbonate_max
, bun_min, bun_max
, calcium_min, calcium_max
, chloride_min, chloride_max
, creatinine_min, creatinine_max
, glucose_min, glucose_max
, sodium_min, sodium_max
, potassium_min, potassium_max
-- blood differential
, abs_basophils_min, abs_basophils_max
, abs_eosinophils_min, abs_eosinophils_max
, abs_lymphocytes_min, abs_lymphocytes_max
, abs_monocytes_min, abs_monocytes_max
, abs_neutrophils_min, abs_neutrophils_max
, atyps_min, atyps_max
, bands_min, bands_max
, imm_granulocytes_min, imm_granulocytes_max
, metas_min, metas_max
, nrbc_min, nrbc_max
-- coagulation
, d_dimer_min, d_dimer_max
, fibrinogen_min, fibrinogen_max
, thrombin_min, thrombin_max
, inr_min, inr_max
, pt_min, pt_max
, ptt_min, ptt_max
-- enzymes and bilirubin
, alt_min, alt_max
, alp_min, alp_max
, ast_min, ast_max
, amylase_min, amylase_max
, bilirubin_total_min, bilirubin_total_max
, bilirubin_direct_min, bilirubin_direct_max
, bilirubin_indirect_min, bilirubin_indirect_max
, ck_cpk_min, ck_cpk_max
, ck_mb_min, ck_mb_max
, ggt_min, ggt_max
, ld_ldh_min, ld_ldh_max
FROM mimiciv_icu.icustays ie
LEFT JOIN cbc
ON ie.stay_id = cbc.stay_id
LEFT JOIN chem
ON ie.stay_id = chem.stay_id
LEFT JOIN diff
ON ie.stay_id = diff.stay_id
LEFT JOIN coag
ON ie.stay_id = coag.stay_id
LEFT JOIN enz
ON ie.stay_id = enz.stay_id
;step-046 生成first_day_rrt表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_rrt; CREATE TABLE first_day_rrt AS
-- flag indicating if patients received dialysis during
-- the first day of their ICU stay
SELECT
ie.subject_id
, ie.stay_id
, MAX(dialysis_present) AS dialysis_present
, MAX(dialysis_active) AS dialysis_active
, STRING_AGG(DISTINCT dialysis_type, ', ') AS dialysis_type
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.rrt rrt
ON ie.stay_id = rrt.stay_id
AND rrt.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND rrt.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id
step-047 生成first_day_urine_output表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_urine_output; CREATE TABLE first_day_urine_output AS
-- Total urine output over the first 24 hours in the ICU
SELECT
-- patient identifiers
ie.subject_id
, ie.stay_id
, SUM(urineoutput) AS urineoutput
FROM mimiciv_icu.icustays ie
-- Join to the outputevents table to get urine output
LEFT JOIN mimiciv_derived.urine_output uo
ON ie.stay_id = uo.stay_id
-- ensure the data occurs during the first day
AND uo.charttime >= ie.intime
AND uo.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_idstep-048 生成first_day_vitalsign表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_vitalsign; CREATE TABLE first_day_vitalsign AS
-- This query pivots vital signs and aggregates them
-- for the first 24 hours of a patient's stay.
SELECT
ie.subject_id
, ie.stay_id
, MIN(heart_rate) AS heart_rate_min
, MAX(heart_rate) AS heart_rate_max
, AVG(heart_rate) AS heart_rate_mean
, MIN(sbp) AS sbp_min
, MAX(sbp) AS sbp_max
, AVG(sbp) AS sbp_mean
, MIN(dbp) AS dbp_min
, MAX(dbp) AS dbp_max
, AVG(dbp) AS dbp_mean
, MIN(mbp) AS mbp_min
, MAX(mbp) AS mbp_max
, AVG(mbp) AS mbp_mean
, MIN(resp_rate) AS resp_rate_min
, MAX(resp_rate) AS resp_rate_max
, AVG(resp_rate) AS resp_rate_mean
, MIN(temperature) AS temperature_min
, MAX(temperature) AS temperature_max
, AVG(temperature) AS temperature_mean
, MIN(spo2) AS spo2_min
, MAX(spo2) AS spo2_max
, AVG(spo2) AS spo2_mean
, MIN(glucose) AS glucose_min
, MAX(glucose) AS glucose_max
, AVG(glucose) AS glucose_mean
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.vitalsign ce
ON ie.stay_id = ce.stay_id
AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id;step-049 生成first_day_weight表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_weight; CREATE TABLE first_day_weight AS
-- This query extracts weights for adult ICU patients on their first ICU day.
-- It does *not* use any information after the first ICU day, as weight is
-- sometimes used to monitor fluid balance.
-- The MIMIC-III version used echodata but this isn't available in MIMIC-IV.
SELECT
ie.subject_id
, ie.stay_id
, AVG(
CASE WHEN weight_type = 'admit' THEN ce.weight ELSE NULL END
) AS weight_admit
, AVG(ce.weight) AS weight
, MIN(ce.weight) AS weight_min
, MAX(ce.weight) AS weight_max
FROM mimiciv_icu.icustays ie
-- admission weight
LEFT JOIN mimiciv_derived.weight_durations ce
ON ie.stay_id = ce.stay_id
-- we filter to weights documented during or before the 1st day
AND ce.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.subject_id, ie.stay_id
;step-050 生成kdigo_creatinine表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS kdigo_creatinine; CREATE TABLE kdigo_creatinine AS
-- Extract all creatinine values from labevents around patient's ICU stay
WITH cr AS (
SELECT
ie.hadm_id
, ie.stay_id
, le.charttime
, AVG(le.valuenum) AS creat
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_hosp.labevents le
ON ie.subject_id = le.subject_id
AND le.itemid = 50912
AND le.valuenum IS NOT NULL
AND le.valuenum <= 150
AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '7' DAY)
AND le.charttime <= ie.outtime
GROUP BY ie.hadm_id, ie.stay_id, le.charttime
)
, cr48 AS (
-- add in the lowest value in the previous 48 hours
SELECT
cr.stay_id
, cr.charttime
, MIN(cr48.creat) AS creat_low_past_48hr
FROM cr
-- add in all creatinine values in the last 48 hours
LEFT JOIN cr cr48
ON cr.stay_id = cr48.stay_id
AND cr48.charttime < cr.charttime
AND cr48.charttime >= DATETIME_SUB(cr.charttime, INTERVAL '48' HOUR)
GROUP BY cr.stay_id, cr.charttime
)
, cr7 AS (
-- add in the lowest value in the previous 7 days
SELECT
cr.stay_id
, cr.charttime
, MIN(cr7.creat) AS creat_low_past_7day
FROM cr
-- add in all creatinine values in the last 7 days
LEFT JOIN cr cr7
ON cr.stay_id = cr7.stay_id
AND cr7.charttime < cr.charttime
AND cr7.charttime >= DATETIME_SUB(cr.charttime, INTERVAL '7' DAY)
GROUP BY cr.stay_id, cr.charttime
)
SELECT
cr.hadm_id
, cr.stay_id
, cr.charttime
, cr.creat
, cr48.creat_low_past_48hr
, cr7.creat_low_past_7day
FROM cr
LEFT JOIN cr48
ON cr.stay_id = cr48.stay_id
AND cr.charttime = cr48.charttime
LEFT JOIN cr7
ON cr.stay_id = cr7.stay_id
AND cr.charttime = cr7.charttime
;step-051 生成meld表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS meld; CREATE TABLE meld AS
-- Model for end-stage liver disease (MELD)
-- This model is used to determine prognosis and receipt of
-- liver transplantation.
-- Reference:
-- Kamath PS, Wiesner RH, Malinchoc M, Kremers W, Therneau TM,
-- Kosberg CL, D'Amico G, Dickson ER, Kim WR.
-- A model to predict survival in patients with end-stage liver disease.
-- Hepatology. 2001 Feb;33(2):464-70.
-- Updated January 2016 to include serum sodium, see:
-- https://optn.transplant.hrsa.gov/news/meld-serum-sodium-policy-changes/
-- Here is the relevant portion of the policy note:
-- 9.1.D MELD Score
-- Candidates who are at least 12 years old receive an initial MELD(i) score
-- equal to:
-- 0.957 x ln(creatinine mg/dL)
-- + 0.378 x ln(bilirubin mg/dL)
-- + 1.120 x ln(INR)
-- + 0.643
-- Laboratory values less than 1.0 will be set to 1.0 when calculating a
-- candidate’s MELD score.
-- The following candidates will receive a creatinine value of 4.0 mg/dL:
-- - Candidates with a creatinine value greater than 4.0 mg/dL
-- - Candidates who received two or more dialysis treatments within
-- the prior week
-- - Candidates who received 24 hours of continuous veno-venous hemodialysis
-- (CVVHD) within the prior week
-- The maximum MELD score is 40. The MELD score derived from this calculation
-- will be rounded to the tenth decimal place and then multiplied by 10.
-- For candidates with an initial MELD score greater than 11, The MELD score
-- is then recalculated as follows:
-- MELD = MELD(i) + 1.32*(137-Na) – [0.033*MELD(i)*(137-Na)]
-- Sodium values less than 125 mmol/L will be set to 125, and values greater
-- than 137 mmol/L will be set to 137.
-- TODO needed in this code:
-- 1. identify 2x dialysis in the past week, or 24 hours of CVVH
-- at the moment it just checks for any dialysis on the first day
-- 2. identify cholestatic or alcoholic liver disease
-- 0.957 x ln(creatinine mg/dL)
-- + 0.378 x ln(bilirubin mg/dL)
-- + 1.120 x ln(INR)
-- + 0.643 x etiology
-- (0 if cholestatic or alcoholic, 1 otherwise)
-- 3. adjust the serum sodium using the corresponding glucose measurement
-- Measured sodium + 0.024 * (Serum glucose - 100) (Hiller, 1999)
WITH cohort AS (
SELECT
ie.subject_id
, ie.hadm_id
, ie.stay_id
, ie.intime
, ie.outtime
, labs.creatinine_max
, labs.bilirubin_total_max
, labs.inr_max
, labs.sodium_min
, r.dialysis_present AS rrt
FROM mimiciv_icu.icustays ie
-- join to custom tables to get more data....
LEFT JOIN mimiciv_derived.first_day_lab labs
ON ie.stay_id = labs.stay_id
LEFT JOIN mimiciv_derived.first_day_rrt r
ON ie.stay_id = r.stay_id
)
, score AS (
SELECT
subject_id
, hadm_id
, stay_id
, rrt
, creatinine_max
, bilirubin_total_max
, inr_max
, sodium_min
-- TODO: Corrected Sodium
, CASE
WHEN sodium_min IS NULL
THEN 0.0
WHEN sodium_min > 137
THEN 0.0
WHEN sodium_min < 125
THEN 12.0 -- 137 - 125 = 12
ELSE 137.0 - sodium_min
END AS sodium_score
-- if hemodialysis, value for Creatinine is automatically set to 4.0
, CASE
WHEN rrt = 1 OR creatinine_max > 4.0
THEN (0.957 * LN(4))
-- if creatinine < 1, score is 1
WHEN creatinine_max < 1
THEN (0.957 * LN(1))
ELSE 0.957 * COALESCE(LN(creatinine_max), LN(1))
END AS creatinine_score
, CASE
-- if value < 1, score is 1
WHEN bilirubin_total_max < 1
THEN 0.378 * LN(1)
ELSE 0.378 * COALESCE(LN(bilirubin_total_max), LN(1))
END AS bilirubin_score
, CASE
WHEN inr_max < 1
THEN (1.120 * LN(1) + 0.643)
ELSE (1.120 * COALESCE(LN(inr_max), LN(1)) + 0.643)
END AS inr_score
FROM cohort
)
, score2 AS (
SELECT
subject_id
, hadm_id
, stay_id
, rrt
, creatinine_max
, bilirubin_total_max
, inr_max
, sodium_min
, creatinine_score
, sodium_score
, bilirubin_score
, inr_score
, CASE
WHEN (creatinine_score + bilirubin_score + inr_score) > 4
THEN 40.0
ELSE
ROUND(
CAST(
creatinine_score
+ bilirubin_score
+ inr_score
AS NUMERIC)
, 1
) * 10
END AS meld_initial
FROM score
)
SELECT
subject_id
, hadm_id
, stay_id
-- MELD Score without sodium change
, meld_initial
-- MELD Score (2016) = MELD*10 + 1.32*(137-Na) – [0.033*MELD*10*(137-Na)]
, CASE
WHEN meld_initial > 11
THEN
meld_initial + 1.32 * sodium_score
- 0.033 * meld_initial * sodium_score
ELSE
meld_initial
END AS meld
-- original variables
, rrt
, creatinine_max
, bilirubin_total_max
, inr_max
, sodium_min
FROM score2
;step-052 生成apsiii表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS apsiii; CREATE TABLE apsiii AS
-- ------------------------------------------------------------------
-- Title: Acute Physiology Score III (APS III)
-- This query extracts the acute physiology score III.
-- This score is a measure of patient severity of illness.
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------
-- Reference for APS III:
-- Knaus WA, Wagner DP, Draper EA, Zimmerman JE, Bergner M,
-- Bastos PG, Sirio CA, Murphy DJ, Lotring T, Damiano A.
-- The APACHE III prognostic system. Risk prediction of hospital
-- mortality for critically ill hospitalized adults. Chest Journal.
-- 1991 Dec 1;100(6):1619-36.
-- Reference for the equation for calibrating APS III:
-- Johnson, A. E. W. (2015). Mortality prediction and acuity assessment
-- in critical care. University of Oxford, Oxford, UK.
-- Variables used in APS III:
-- GCS
-- VITALS: Heart rate, mean blood pressure, temperature, respiration rate
-- FLAGS: ventilation/cpap, chronic dialysis
-- IO: urine output
-- LABS: pao2, A-aDO2, hematocrit, WBC, creatinine
-- , blood urea nitrogen, sodium, albumin, bilirubin, glucose, pH, pCO2
-- Note:
-- The score is calculated for *all* ICU patients, with the assumption that
-- the user will subselect appropriate stay_ids.
-- List of TODO:
-- The site of temperature is not incorporated. Axillary measurements
-- should be increased by 1 degree.
WITH pa AS (
SELECT ie.stay_id, bg.charttime
, po2 AS pao2
, ROW_NUMBER() OVER (PARTITION BY ie.stay_id ORDER BY bg.po2 DESC) AS rn
FROM mimiciv_derived.bg bg
INNER JOIN mimiciv_icu.icustays ie
ON bg.hadm_id = ie.hadm_id
AND bg.charttime >= ie.intime AND bg.charttime < ie.outtime
LEFT JOIN mimiciv_derived.ventilation vd
ON ie.stay_id = vd.stay_id
AND bg.charttime >= vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
WHERE vd.stay_id IS NULL -- patient is *not* ventilated
-- and fio2 < 50, or if no fio2, assume room air
AND COALESCE(fio2, fio2_chartevents, 21) < 50
AND bg.po2 IS NOT NULL
AND bg.specimen = 'ART.'
)
, aa AS (
-- join blood gas to ventilation durations to determine if patient was vent
-- also join to cpap table for the same purpose
SELECT ie.stay_id, bg.charttime
, bg.aado2
, ROW_NUMBER() OVER (
PARTITION BY ie.stay_id ORDER BY bg.aado2 DESC
) AS rn
-- row number indicating the highest AaDO2
FROM mimiciv_derived.bg bg
INNER JOIN mimiciv_icu.icustays ie
ON bg.hadm_id = ie.hadm_id
AND bg.charttime >= ie.intime AND bg.charttime < ie.outtime
INNER JOIN mimiciv_derived.ventilation vd
ON ie.stay_id = vd.stay_id
AND bg.charttime >= vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
WHERE vd.stay_id IS NOT NULL -- patient is ventilated
AND COALESCE(fio2, fio2_chartevents) >= 50
AND bg.aado2 IS NOT NULL
AND bg.specimen = 'ART.'
)
-- because ph/pco2 rules are an interaction *within* a blood gas,
-- we calculate them here
-- the worse score is then taken for the final calculation
, acidbase AS (
SELECT ie.stay_id
, ph, pco2 AS paco2
, CASE
WHEN ph IS NULL OR pco2 IS NULL THEN null
WHEN ph < 7.20 THEN
CASE
WHEN pco2 < 50 THEN 12
ELSE 4
END
WHEN ph < 7.30 THEN
CASE
WHEN pco2 < 30 THEN 9
WHEN pco2 < 40 THEN 6
WHEN pco2 < 50 THEN 3
ELSE 2
END
WHEN ph < 7.35 THEN
CASE
WHEN pco2 < 30 THEN 9
WHEN pco2 < 45 THEN 0
ELSE 1
END
WHEN ph < 7.45 THEN
CASE
WHEN pco2 < 30 THEN 5
WHEN pco2 < 45 THEN 0
ELSE 1
END
WHEN ph < 7.50 THEN
CASE
WHEN pco2 < 30 THEN 5
WHEN pco2 < 35 THEN 0
WHEN pco2 < 45 THEN 2
ELSE 12
END
WHEN ph < 7.60 THEN
CASE
WHEN pco2 < 40 THEN 3
ELSE 12
END
ELSE -- ph >= 7.60
CASE
WHEN pco2 < 25 THEN 0
WHEN pco2 < 40 THEN 3
ELSE 12
END
END AS acidbase_score
FROM mimiciv_derived.bg bg
INNER JOIN mimiciv_icu.icustays ie
ON bg.hadm_id = ie.hadm_id
AND bg.charttime >= ie.intime AND bg.charttime < ie.outtime
WHERE ph IS NOT NULL AND pco2 IS NOT NULL
AND bg.specimen = 'ART.'
)
, acidbase_max AS (
SELECT stay_id, acidbase_score, ph, paco2
-- create integer which indexes maximum value of score with 1
, ROW_NUMBER() OVER (
PARTITION BY stay_id ORDER BY acidbase_score DESC
) AS acidbase_rn
FROM acidbase
)
-- define acute renal failure (ARF) as:
-- creatinine >=1.5 mg/dl
-- and urine output <410 cc/day
-- and no chronic dialysis
, arf AS (
SELECT ie.stay_id
, CASE
WHEN labs.creatinine_max >= 1.5
AND uo.urineoutput < 410
-- acute renal failure is only coded if the patient
-- is not on chronic dialysis
-- we use ICD-9 coding of ESRD as a proxy for chronic dialysis
AND icd.ckd = 0
THEN 1
ELSE 0 END AS arf
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.first_day_urine_output uo
ON ie.stay_id = uo.stay_id
LEFT JOIN mimiciv_derived.first_day_lab labs
ON ie.stay_id = labs.stay_id
LEFT JOIN
(
SELECT hadm_id
, MAX(CASE
-- severe kidney failure requiring use of dialysis
WHEN
icd_version = 9 AND SUBSTR(
icd_code, 1, 4
) IN ('5854', '5855', '5856') THEN 1
WHEN
icd_version = 10 AND SUBSTR(
icd_code, 1, 4
) IN ('N184', 'N185', 'N186') THEN 1
-- we do not include 5859 as that is sometimes coded
-- for acute-on-chronic ARF
ELSE 0 END)
AS ckd
FROM mimiciv_hosp.diagnoses_icd
GROUP BY hadm_id
) icd
ON ie.hadm_id = icd.hadm_id
)
-- first day mechanical ventilation
, vent AS (
SELECT ie.stay_id
, MAX(
CASE WHEN v.stay_id IS NOT NULL THEN 1 ELSE 0 END
) AS vent
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.ventilation v
ON ie.stay_id = v.stay_id
AND v.ventilation_status = 'InvasiveVent'
AND (
(
v.starttime >= ie.intime
AND v.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
OR (
v.endtime >= ie.intime
AND v.endtime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
OR (
v.starttime <= ie.intime
AND v.endtime >= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
)
GROUP BY ie.stay_id
)
, cohort AS (
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
, ie.intime
, ie.outtime
, vital.heart_rate_min
, vital.heart_rate_max
, vital.mbp_min
, vital.mbp_max
, vital.temperature_min
, vital.temperature_max
, vital.resp_rate_min
, vital.resp_rate_max
, pa.pao2
, aa.aado2
, ab.ph
, ab.paco2
, ab.acidbase_score
, labs.hematocrit_min
, labs.hematocrit_max
, labs.wbc_min
, labs.wbc_max
, labs.creatinine_min
, labs.creatinine_max
, labs.bun_min
, labs.bun_max
, labs.sodium_min
, labs.sodium_max
, labs.albumin_min
, labs.albumin_max
, labs.bilirubin_total_min AS bilirubin_min
, labs.bilirubin_total_max AS bilirubin_max
, CASE
WHEN labs.glucose_max IS NULL AND vital.glucose_max IS NULL
THEN null
WHEN labs.glucose_max IS NULL
OR vital.glucose_max > labs.glucose_max
THEN vital.glucose_max
WHEN vital.glucose_max IS NULL
OR labs.glucose_max > vital.glucose_max
THEN labs.glucose_max
ELSE labs.glucose_max -- if equal, just pick labs
END AS glucose_max
, CASE
WHEN labs.glucose_min IS NULL
AND vital.glucose_min IS NULL
THEN null
WHEN labs.glucose_min IS NULL
OR vital.glucose_min < labs.glucose_min
THEN vital.glucose_min
WHEN vital.glucose_min IS NULL
OR labs.glucose_min < vital.glucose_min
THEN labs.glucose_min
ELSE labs.glucose_min -- if equal, just pick labs
END AS glucose_min
-- , labs.bicarbonate_min
-- , labs.bicarbonate_max
, vent.vent
, uo.urineoutput
-- gcs and its components
, gcs.gcs_min AS mingcs
, gcs.gcs_motor, gcs.gcs_verbal, gcs.gcs_eyes, gcs.gcs_unable
-- acute renal failure
, arf.arf AS arf
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_hosp.admissions adm
ON ie.hadm_id = adm.hadm_id
INNER JOIN mimiciv_hosp.patients pat
ON ie.subject_id = pat.subject_id
-- join to above views - the row number filters to 1 row per stay_id
LEFT JOIN pa
ON ie.stay_id = pa.stay_id
AND pa.rn = 1
LEFT JOIN aa
ON ie.stay_id = aa.stay_id
AND aa.rn = 1
LEFT JOIN acidbase_max ab
ON ie.stay_id = ab.stay_id
AND ab.acidbase_rn = 1
LEFT JOIN arf
ON ie.stay_id = arf.stay_id
-- join to custom tables to get more data....
LEFT JOIN vent
ON ie.stay_id = vent.stay_id
LEFT JOIN mimiciv_derived.first_day_gcs gcs
ON ie.stay_id = gcs.stay_id
LEFT JOIN mimiciv_derived.first_day_vitalsign vital
ON ie.stay_id = vital.stay_id
LEFT JOIN mimiciv_derived.first_day_urine_output uo
ON ie.stay_id = uo.stay_id
LEFT JOIN mimiciv_derived.first_day_lab labs
ON ie.stay_id = labs.stay_id
)
-- First, we calculate the score for the minimum values
, score_min AS (
SELECT cohort.subject_id, cohort.hadm_id, cohort.stay_id
, CASE
WHEN heart_rate_min IS NULL THEN null
WHEN heart_rate_min < 40 THEN 8
WHEN heart_rate_min < 50 THEN 5
WHEN heart_rate_min < 100 THEN 0
WHEN heart_rate_min < 110 THEN 1
WHEN heart_rate_min < 120 THEN 5
WHEN heart_rate_min < 140 THEN 7
WHEN heart_rate_min < 155 THEN 13
WHEN heart_rate_min >= 155 THEN 17
END AS hr_score
, CASE
WHEN mbp_min IS NULL THEN null
WHEN mbp_min < 40 THEN 23
WHEN mbp_min < 60 THEN 15
WHEN mbp_min < 70 THEN 7
WHEN mbp_min < 80 THEN 6
WHEN mbp_min < 100 THEN 0
WHEN mbp_min < 120 THEN 4
WHEN mbp_min < 130 THEN 7
WHEN mbp_min < 140 THEN 9
WHEN mbp_min >= 140 THEN 10
END AS mbp_score
-- TODO: add 1 degree to axillary measurements
, CASE
WHEN temperature_min IS NULL THEN null
WHEN temperature_min < 33.0 THEN 20
WHEN temperature_min < 33.5 THEN 16
WHEN temperature_min < 34.0 THEN 13
WHEN temperature_min < 35.0 THEN 8
WHEN temperature_min < 36.0 THEN 2
WHEN temperature_min < 40.0 THEN 0
WHEN temperature_min >= 40.0 THEN 4
END AS temp_score
, CASE
WHEN resp_rate_min IS NULL THEN null
-- special case for ventilated patients
WHEN vent = 1 AND resp_rate_min < 14 THEN 0
WHEN resp_rate_min < 6 THEN 17
WHEN resp_rate_min < 12 THEN 8
WHEN resp_rate_min < 14 THEN 7
WHEN resp_rate_min < 25 THEN 0
WHEN resp_rate_min < 35 THEN 6
WHEN resp_rate_min < 40 THEN 9
WHEN resp_rate_min < 50 THEN 11
WHEN resp_rate_min >= 50 THEN 18
END AS resp_rate_score
, CASE
WHEN hematocrit_min IS NULL THEN null
WHEN hematocrit_min < 41.0 THEN 3
WHEN hematocrit_min < 50.0 THEN 0
WHEN hematocrit_min >= 50.0 THEN 3
END AS hematocrit_score
, CASE
WHEN wbc_min IS NULL THEN null
WHEN wbc_min < 1.0 THEN 19
WHEN wbc_min < 3.0 THEN 5
WHEN wbc_min < 20.0 THEN 0
WHEN wbc_min < 25.0 THEN 1
WHEN wbc_min >= 25.0 THEN 5
END AS wbc_score
, CASE
WHEN creatinine_min IS NULL THEN null
WHEN arf = 1 AND creatinine_min < 1.5 THEN 0
WHEN arf = 1 AND creatinine_min >= 1.5 THEN 10
WHEN creatinine_min < 0.5 THEN 3
WHEN creatinine_min < 1.5 THEN 0
WHEN creatinine_min < 1.95 THEN 4
WHEN creatinine_min >= 1.95 THEN 7
END AS creatinine_score
, CASE
WHEN bun_min IS NULL THEN null
WHEN bun_min < 17.0 THEN 0
WHEN bun_min < 20.0 THEN 2
WHEN bun_min < 40.0 THEN 7
WHEN bun_min < 80.0 THEN 11
WHEN bun_min >= 80.0 THEN 12
END AS bun_score
, CASE
WHEN sodium_min IS NULL THEN null
WHEN sodium_min < 120 THEN 3
WHEN sodium_min < 135 THEN 2
WHEN sodium_min < 155 THEN 0
WHEN sodium_min >= 155 THEN 4
END AS sodium_score
, CASE
WHEN albumin_min IS NULL THEN null
WHEN albumin_min < 2.0 THEN 11
WHEN albumin_min < 2.5 THEN 6
WHEN albumin_min < 4.5 THEN 0
WHEN albumin_min >= 4.5 THEN 4
END AS albumin_score
, CASE
WHEN bilirubin_min IS NULL THEN null
WHEN bilirubin_min < 2.0 THEN 0
WHEN bilirubin_min < 3.0 THEN 5
WHEN bilirubin_min < 5.0 THEN 6
WHEN bilirubin_min < 8.0 THEN 8
WHEN bilirubin_min >= 8.0 THEN 16
END AS bilirubin_score
, CASE
WHEN glucose_min IS NULL THEN null
WHEN glucose_min < 40 THEN 8
WHEN glucose_min < 60 THEN 9
WHEN glucose_min < 200 THEN 0
WHEN glucose_min < 350 THEN 3
WHEN glucose_min >= 350 THEN 5
END AS glucose_score
FROM cohort
)
, score_max AS (
SELECT cohort.subject_id, cohort.hadm_id, cohort.stay_id
, CASE
WHEN heart_rate_max IS NULL THEN null
WHEN heart_rate_max < 40 THEN 8
WHEN heart_rate_max < 50 THEN 5
WHEN heart_rate_max < 100 THEN 0
WHEN heart_rate_max < 110 THEN 1
WHEN heart_rate_max < 120 THEN 5
WHEN heart_rate_max < 140 THEN 7
WHEN heart_rate_max < 155 THEN 13
WHEN heart_rate_max >= 155 THEN 17
END AS hr_score
, CASE
WHEN mbp_max IS NULL THEN null
WHEN mbp_max < 40 THEN 23
WHEN mbp_max < 60 THEN 15
WHEN mbp_max < 70 THEN 7
WHEN mbp_max < 80 THEN 6
WHEN mbp_max < 100 THEN 0
WHEN mbp_max < 120 THEN 4
WHEN mbp_max < 130 THEN 7
WHEN mbp_max < 140 THEN 9
WHEN mbp_max >= 140 THEN 10
END AS mbp_score
-- TODO: add 1 degree to axillary measurements
, CASE
WHEN temperature_max IS NULL THEN null
WHEN temperature_max < 33.0 THEN 20
WHEN temperature_max < 33.5 THEN 16
WHEN temperature_max < 34.0 THEN 13
WHEN temperature_max < 35.0 THEN 8
WHEN temperature_max < 36.0 THEN 2
WHEN temperature_max < 40.0 THEN 0
WHEN temperature_max >= 40.0 THEN 4
END AS temp_score
, CASE
WHEN resp_rate_max IS NULL THEN null
-- special case for ventilated patients
WHEN vent = 1 AND resp_rate_max < 14 THEN 0
WHEN resp_rate_max < 6 THEN 17
WHEN resp_rate_max < 12 THEN 8
WHEN resp_rate_max < 14 THEN 7
WHEN resp_rate_max < 25 THEN 0
WHEN resp_rate_max < 35 THEN 6
WHEN resp_rate_max < 40 THEN 9
WHEN resp_rate_max < 50 THEN 11
WHEN resp_rate_max >= 50 THEN 18
END AS resp_rate_score
, CASE
WHEN hematocrit_max IS NULL THEN null
WHEN hematocrit_max < 41.0 THEN 3
WHEN hematocrit_max < 50.0 THEN 0
WHEN hematocrit_max >= 50.0 THEN 3
END AS hematocrit_score
, CASE
WHEN wbc_max IS NULL THEN null
WHEN wbc_max < 1.0 THEN 19
WHEN wbc_max < 3.0 THEN 5
WHEN wbc_max < 20.0 THEN 0
WHEN wbc_max < 25.0 THEN 1
WHEN wbc_max >= 25.0 THEN 5
END AS wbc_score
, CASE
WHEN creatinine_max IS NULL THEN null
WHEN arf = 1 AND creatinine_max < 1.5 THEN 0
WHEN arf = 1 AND creatinine_max >= 1.5 THEN 10
WHEN creatinine_max < 0.5 THEN 3
WHEN creatinine_max < 1.5 THEN 0
WHEN creatinine_max < 1.95 THEN 4
WHEN creatinine_max >= 1.95 THEN 7
END AS creatinine_score
, CASE
WHEN bun_max IS NULL THEN null
WHEN bun_max < 17.0 THEN 0
WHEN bun_max < 20.0 THEN 2
WHEN bun_max < 40.0 THEN 7
WHEN bun_max < 80.0 THEN 11
WHEN bun_max >= 80.0 THEN 12
END AS bun_score
, CASE
WHEN sodium_max IS NULL THEN null
WHEN sodium_max < 120 THEN 3
WHEN sodium_max < 135 THEN 2
WHEN sodium_max < 155 THEN 0
WHEN sodium_max >= 155 THEN 4
END AS sodium_score
, CASE
WHEN albumin_max IS NULL THEN null
WHEN albumin_max < 2.0 THEN 11
WHEN albumin_max < 2.5 THEN 6
WHEN albumin_max < 4.5 THEN 0
WHEN albumin_max >= 4.5 THEN 4
END AS albumin_score
, CASE
WHEN bilirubin_max IS NULL THEN null
WHEN bilirubin_max < 2.0 THEN 0
WHEN bilirubin_max < 3.0 THEN 5
WHEN bilirubin_max < 5.0 THEN 6
WHEN bilirubin_max < 8.0 THEN 8
WHEN bilirubin_max >= 8.0 THEN 16
END AS bilirubin_score
, CASE
WHEN glucose_max IS NULL THEN null
WHEN glucose_max < 40 THEN 8
WHEN glucose_max < 60 THEN 9
WHEN glucose_max < 200 THEN 0
WHEN glucose_max < 350 THEN 3
WHEN glucose_max >= 350 THEN 5
END AS glucose_score
FROM cohort
)
-- Combine together the scores for min/max, using the following rules:
-- 1) select the value furthest from a predefined normal value
-- 2) if both equidistant, choose the one which gives a worse score
-- 3) calculate score for acid-base abnormalities as it requires interactions
-- sometimes the code is a bit redundant, i.e. we know the max would always
-- be furthest from 0
, scorecomp AS (
SELECT co.*
-- The rules for APS III require the definition of a "worst" value
-- This value is defined as whatever value is furthest from a
-- predefined normal e.g., for heart rate, worst is defined
-- as furthest from 75
, CASE
WHEN heart_rate_max IS NULL THEN null
WHEN ABS(heart_rate_max - 75) > ABS(heart_rate_min - 75)
THEN smax.hr_score
WHEN ABS(heart_rate_max - 75) < ABS(heart_rate_min - 75)
THEN smin.hr_score
WHEN ABS(heart_rate_max - 75) = ABS(heart_rate_min - 75)
AND smax.hr_score >= smin.hr_score
THEN smax.hr_score
WHEN ABS(heart_rate_max - 75) = ABS(heart_rate_min - 75)
AND smax.hr_score < smin.hr_score
THEN smin.hr_score
END AS hr_score
, CASE
WHEN mbp_max IS NULL THEN null
WHEN ABS(mbp_max - 90) > ABS(mbp_min - 90)
THEN smax.mbp_score
WHEN ABS(mbp_max - 90) < ABS(mbp_min - 90)
THEN smin.mbp_score
-- values are equidistant - pick the larger score
WHEN ABS(mbp_max - 90) = ABS(mbp_min - 90)
AND smax.mbp_score >= smin.mbp_score
THEN smax.mbp_score
WHEN ABS(mbp_max - 90) = ABS(mbp_min - 90)
AND smax.mbp_score < smin.mbp_score
THEN smin.mbp_score
END AS mbp_score
, CASE
WHEN temperature_max IS NULL THEN null
WHEN ABS(temperature_max - 38) > ABS(temperature_min - 38)
THEN smax.temp_score
WHEN ABS(temperature_max - 38) < ABS(temperature_min - 38)
THEN smin.temp_score
-- values are equidistant - pick the larger score
WHEN ABS(temperature_max - 38) = ABS(temperature_min - 38)
AND smax.temp_score >= smin.temp_score
THEN smax.temp_score
WHEN ABS(temperature_max - 38) = ABS(temperature_min - 38)
AND smax.temp_score < smin.temp_score
THEN smin.temp_score
END AS temp_score
, CASE
WHEN resp_rate_max IS NULL THEN null
WHEN ABS(resp_rate_max - 19) > ABS(resp_rate_min - 19)
THEN smax.resp_rate_score
WHEN ABS(resp_rate_max - 19) < ABS(resp_rate_min - 19)
THEN smin.resp_rate_score
-- values are equidistant - pick the larger score
WHEN ABS(resp_rate_max - 19) = ABS(resp_rate_max - 19)
AND smax.resp_rate_score >= smin.resp_rate_score
THEN smax.resp_rate_score
WHEN ABS(resp_rate_max - 19) = ABS(resp_rate_max - 19)
AND smax.resp_rate_score < smin.resp_rate_score
THEN smin.resp_rate_score
END AS resp_rate_score
, CASE
WHEN hematocrit_max IS NULL THEN null
WHEN ABS(hematocrit_max - 45.5) > ABS(hematocrit_min - 45.5)
THEN smax.hematocrit_score
WHEN ABS(hematocrit_max - 45.5) < ABS(hematocrit_min - 45.5)
THEN smin.hematocrit_score
-- values are equidistant - pick the larger score
WHEN ABS(hematocrit_max - 45.5) = ABS(hematocrit_max - 45.5)
AND smax.hematocrit_score >= smin.hematocrit_score
THEN smax.hematocrit_score
WHEN ABS(hematocrit_max - 45.5) = ABS(hematocrit_max - 45.5)
AND smax.hematocrit_score < smin.hematocrit_score
THEN smin.hematocrit_score
END AS hematocrit_score
, CASE
WHEN wbc_max IS NULL THEN null
WHEN ABS(wbc_max - 11.5) > ABS(wbc_min - 11.5)
THEN smax.wbc_score
WHEN ABS(wbc_max - 11.5) < ABS(wbc_min - 11.5)
THEN smin.wbc_score
-- values are equidistant - pick the larger score
WHEN ABS(wbc_max - 11.5) = ABS(wbc_max - 11.5)
AND smax.wbc_score >= smin.wbc_score
THEN smax.wbc_score
WHEN ABS(wbc_max - 11.5) = ABS(wbc_max - 11.5)
AND smax.wbc_score < smin.wbc_score
THEN smin.wbc_score
END AS wbc_score
-- For some labs, "furthest from normal" doesn't make sense
-- e.g. creatinine w/ ARF, the minimum could be 0.3,
-- and the max 1.6 while the minimum of 0.3 is
-- "further from 1", seems like the max should
-- be scored
, CASE
WHEN creatinine_max IS NULL THEN null
-- if they have arf then use the max to score
WHEN arf = 1 THEN smax.creatinine_score
-- otherwise furthest from 1
WHEN ABS(creatinine_max - 1) > ABS(creatinine_min - 1)
THEN smax.creatinine_score
WHEN ABS(creatinine_max - 1) < ABS(creatinine_min - 1)
THEN smin.creatinine_score
-- values are equidistant
WHEN smax.creatinine_score >= smin.creatinine_score
THEN smax.creatinine_score
WHEN smax.creatinine_score < smin.creatinine_score
THEN smin.creatinine_score
END AS creatinine_score
-- the rule for BUN is the furthest from 0.. equivalent to the max value
, CASE
WHEN bun_max IS NULL THEN null
ELSE smax.bun_score
END AS bun_score
, CASE
WHEN sodium_max IS NULL THEN null
WHEN ABS(sodium_max - 145.5) > ABS(sodium_min - 145.5)
THEN smax.sodium_score
WHEN ABS(sodium_max - 145.5) < ABS(sodium_min - 145.5)
THEN smin.sodium_score
-- values are equidistant - pick the larger score
WHEN ABS(sodium_max - 145.5) = ABS(sodium_max - 145.5)
AND smax.sodium_score >= smin.sodium_score
THEN smax.sodium_score
WHEN ABS(sodium_max - 145.5) = ABS(sodium_max - 145.5)
AND smax.sodium_score < smin.sodium_score
THEN smin.sodium_score
END AS sodium_score
, CASE
WHEN albumin_max IS NULL THEN null
WHEN ABS(albumin_max - 3.5) > ABS(albumin_min - 3.5)
THEN smax.albumin_score
WHEN ABS(albumin_max - 3.5) < ABS(albumin_min - 3.5)
THEN smin.albumin_score
-- values are equidistant - pick the larger score
WHEN ABS(albumin_max - 3.5) = ABS(albumin_max - 3.5)
AND smax.albumin_score >= smin.albumin_score
THEN smax.albumin_score
WHEN ABS(albumin_max - 3.5) = ABS(albumin_max - 3.5)
AND smax.albumin_score < smin.albumin_score
THEN smin.albumin_score
END AS albumin_score
, CASE
WHEN bilirubin_max IS NULL THEN null
ELSE smax.bilirubin_score
END AS bilirubin_score
, CASE
WHEN glucose_max IS NULL THEN null
WHEN ABS(glucose_max - 130) > ABS(glucose_min - 130)
THEN smax.glucose_score
WHEN ABS(glucose_max - 130) < ABS(glucose_min - 130)
THEN smin.glucose_score
-- values are equidistant - pick the larger score
WHEN ABS(glucose_max - 130) = ABS(glucose_max - 130)
AND smax.glucose_score >= smin.glucose_score
THEN smax.glucose_score
WHEN ABS(glucose_max - 130) = ABS(glucose_max - 130)
AND smax.glucose_score < smin.glucose_score
THEN smin.glucose_score
END AS glucose_score
-- Below are interactions/special cases where only 1 value is important
, CASE
WHEN urineoutput IS NULL THEN null
WHEN urineoutput < 400 THEN 15
WHEN urineoutput < 600 THEN 8
WHEN urineoutput < 900 THEN 7
WHEN urineoutput < 1500 THEN 5
WHEN urineoutput < 2000 THEN 4
WHEN urineoutput < 4000 THEN 0
WHEN urineoutput >= 4000 THEN 1
END AS uo_score
, CASE
WHEN gcs_unable = 1
-- here they are intubated, so their verbal score
-- is inappropriate
-- normally you are supposed to use "clinical judgement"
-- we don't have that, so we just assume normal
-- (as was done in the original study)
THEN 0
WHEN gcs_eyes = 1
THEN CASE
WHEN gcs_verbal = 1 AND gcs_motor IN (1, 2)
THEN 48
WHEN gcs_verbal = 1 AND gcs_motor IN (3, 4)
THEN 33
WHEN gcs_verbal = 1 AND gcs_motor IN (5, 6)
THEN 16
WHEN gcs_verbal IN (2, 3) AND gcs_motor IN (1, 2)
THEN 29
WHEN gcs_verbal IN (2, 3) AND gcs_motor IN (3, 4)
THEN 24
WHEN gcs_verbal IN (2, 3) AND gcs_motor >= 5
-- highly unlikely clinical combination
THEN null
WHEN gcs_verbal >= 4
THEN null
END
WHEN gcs_eyes > 1
THEN CASE
WHEN gcs_verbal = 1 AND gcs_motor IN (1, 2)
THEN 29
WHEN gcs_verbal = 1 AND gcs_motor IN (3, 4)
THEN 24
WHEN gcs_verbal = 1 AND gcs_motor IN (5, 6)
THEN 15
WHEN gcs_verbal IN (2, 3) AND gcs_motor IN (1, 2)
THEN 29
WHEN gcs_verbal IN (2, 3) AND gcs_motor IN (3, 4)
THEN 24
WHEN gcs_verbal IN (2, 3) AND gcs_motor = 5
THEN 13
WHEN gcs_verbal IN (2, 3) AND gcs_motor = 6
THEN 10
WHEN gcs_verbal = 4 AND gcs_motor IN (1, 2, 3, 4)
THEN 13
WHEN gcs_verbal = 4 AND gcs_motor = 5
THEN 8
WHEN gcs_verbal = 4 AND gcs_motor = 6
THEN 3
WHEN gcs_verbal = 5 AND gcs_motor IN (1, 2, 3, 4, 5)
THEN 3
WHEN gcs_verbal = 5 AND gcs_motor = 6
THEN 0
END
ELSE null
END AS gcs_score
, CASE
WHEN pao2 IS NULL AND aado2 IS NULL
THEN null
WHEN pao2 IS NOT NULL THEN
CASE
WHEN pao2 < 50 THEN 15
WHEN pao2 < 70 THEN 5
WHEN pao2 < 80 THEN 2
ELSE 0 END
WHEN aado2 IS NOT NULL THEN
CASE
WHEN aado2 < 100 THEN 0
WHEN aado2 < 250 THEN 7
WHEN aado2 < 350 THEN 9
WHEN aado2 < 500 THEN 11
WHEN aado2 >= 500 THEN 14
ELSE 0 END
END AS pao2_aado2_score
FROM cohort co
LEFT JOIN score_min smin
ON co.stay_id = smin.stay_id
LEFT JOIN score_max smax
ON co.stay_id = smax.stay_id
)
-- tabulate the APS III using the scores from the worst values
, score AS (
SELECT s.*
-- coalesce statements impute normal score of zero
-- if data element is missing
, COALESCE(hr_score, 0)
+ COALESCE(mbp_score, 0)
+ COALESCE(temp_score, 0)
+ COALESCE(resp_rate_score, 0)
+ COALESCE(pao2_aado2_score, 0)
+ COALESCE(hematocrit_score, 0)
+ COALESCE(wbc_score, 0)
+ COALESCE(creatinine_score, 0)
+ COALESCE(uo_score, 0)
+ COALESCE(bun_score, 0)
+ COALESCE(sodium_score, 0)
+ COALESCE(albumin_score, 0)
+ COALESCE(bilirubin_score, 0)
+ COALESCE(glucose_score, 0)
+ COALESCE(acidbase_score, 0)
+ COALESCE(gcs_score, 0)
AS apsiii
FROM scorecomp s
)
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
, apsiii
-- Calculate probability of hospital mortality using
-- equation from Johnson 2014.
, 1 / (1 + EXP(- (-4.4360 + 0.04726 * (apsiii)))) AS apsiii_prob
, hr_score
, mbp_score
, temp_score
, resp_rate_score
, pao2_aado2_score
, hematocrit_score
, wbc_score
, creatinine_score
, uo_score
, bun_score
, sodium_score
, albumin_score
, bilirubin_score
, glucose_score
, acidbase_score
, gcs_score
FROM mimiciv_icu.icustays ie
LEFT JOIN score s
ON ie.stay_id = s.stay_id
;step-053 生成lods表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS lods; CREATE TABLE lods AS
-- ------------------------------------------------------------------
-- Title: Logistic Organ Dysfunction Score (LODS)
-- This query extracts the logistic organ dysfunction system.
-- This score is a measure of organ failure in a patient.
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------
-- Reference for LODS:
-- Le Gall, J. R., Klar, J., Lemeshow, S., Saulnier, F., Alberti, C.,
-- Artigas, A., & Teres, D.
-- The Logistic Organ Dysfunction system: a new way to assess organ
-- dysfunction in the intensive care unit. JAMA 276.10 (1996): 802-810.
-- Variables used in LODS:
-- GCS
-- VITALS: Heart rate, systolic blood pressure
-- FLAGS: ventilation/cpap
-- IO: urine output
-- LABS: blood urea nitrogen, WBC, bilirubin, creatinine,
-- prothrombin time (PT), platelets
-- ABG: PaO2 with associated FiO2
-- Note:
-- The score is calculated for *all* ICU patients, with the assumption
-- that the user will subselect appropriate stay_ids.
-- extract CPAP from the "Oxygen Delivery Device" fields
WITH cpap AS (
SELECT ie.stay_id
, MIN(DATETIME_SUB(charttime, INTERVAL '1' HOUR)) AS starttime
, MAX(DATETIME_ADD(charttime, INTERVAL '4' HOUR)) AS endtime
, MAX(CASE
WHEN LOWER(ce.value) LIKE '%cpap%' THEN 1
WHEN LOWER(ce.value) LIKE '%bipap mask%' THEN 1
ELSE 0 END) AS cpap
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_icu.chartevents ce
ON ie.stay_id = ce.stay_id
AND ce.charttime >= ie.intime
AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
WHERE itemid = 226732
AND (
LOWER(ce.value) LIKE '%cpap%' OR LOWER(ce.value) LIKE '%bipap mask%'
)
GROUP BY ie.stay_id
)
, pafi1 AS (
-- join blood gas to ventilation durations to determine if patient was vent
-- also join to cpap table for the same purpose
SELECT ie.stay_id, bg.charttime
, pao2fio2ratio
, CASE WHEN vd.stay_id IS NOT NULL THEN 1 ELSE 0 END AS vent
, CASE WHEN cp.stay_id IS NOT NULL THEN 1 ELSE 0 END AS cpap
FROM mimiciv_derived.bg bg
INNER JOIN mimiciv_icu.icustays ie
ON bg.hadm_id = ie.hadm_id
AND bg.charttime >= ie.intime AND bg.charttime < ie.outtime
LEFT JOIN mimiciv_derived.ventilation vd
ON ie.stay_id = vd.stay_id
AND bg.charttime >= vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
LEFT JOIN cpap cp
ON ie.stay_id = cp.stay_id
AND bg.charttime >= cp.starttime
AND bg.charttime <= cp.endtime
)
, pafi2 AS (
-- get the minimum PaO2/FiO2 ratio *only for ventilated/cpap patients*
SELECT stay_id
, MIN(pao2fio2ratio) AS pao2fio2_vent_min
FROM pafi1
WHERE vent = 1 OR cpap = 1
GROUP BY stay_id
)
, cohort AS (
SELECT ie.subject_id
, ie.hadm_id
, ie.stay_id
, ie.intime
, ie.outtime
, gcs.gcs_min
, vital.heart_rate_max
, vital.heart_rate_min
, vital.sbp_max
, vital.sbp_min
-- this value is non-null iff the patient is on vent/cpap
, pf.pao2fio2_vent_min
, labs.bun_max
, labs.bun_min
, labs.wbc_max
, labs.wbc_min
, labs.bilirubin_total_max AS bilirubin_max
, labs.creatinine_max
, labs.pt_min
, labs.pt_max
, labs.platelets_min AS platelet_min
, uo.urineoutput
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_hosp.admissions adm
ON ie.hadm_id = adm.hadm_id
INNER JOIN mimiciv_hosp.patients pat
ON ie.subject_id = pat.subject_id
-- join to above view to get pao2/fio2 ratio
LEFT JOIN pafi2 pf
ON ie.stay_id = pf.stay_id
-- join to custom tables to get more data....
LEFT JOIN mimiciv_derived.first_day_gcs gcs
ON ie.stay_id = gcs.stay_id
LEFT JOIN mimiciv_derived.first_day_vitalsign vital
ON ie.stay_id = vital.stay_id
LEFT JOIN mimiciv_derived.first_day_urine_output uo
ON ie.stay_id = uo.stay_id
LEFT JOIN mimiciv_derived.first_day_lab labs
ON ie.stay_id = labs.stay_id
)
, scorecomp AS (
SELECT
cohort.*
-- Below code calculates the component scores needed for SAPS
-- neurologic
, CASE
WHEN gcs_min IS NULL THEN null
WHEN gcs_min < 3 THEN null -- erroneous value/on trach
WHEN gcs_min <= 5 THEN 5
WHEN gcs_min <= 8 THEN 3
WHEN gcs_min <= 13 THEN 1
ELSE 0
END AS neurologic
-- cardiovascular
, CASE
WHEN heart_rate_max IS NULL
AND sbp_min IS NULL THEN null
WHEN heart_rate_min < 30 THEN 5
WHEN sbp_min < 40 THEN 5
WHEN sbp_min < 70 THEN 3
WHEN sbp_max >= 270 THEN 3
WHEN heart_rate_max >= 140 THEN 1
WHEN sbp_max >= 240 THEN 1
WHEN sbp_min < 90 THEN 1
ELSE 0
END AS cardiovascular
-- renal
, CASE
WHEN bun_max IS NULL
OR urineoutput IS NULL
OR creatinine_max IS NULL
THEN null
WHEN urineoutput < 500.0 THEN 5
WHEN bun_max >= 56.0 THEN 5
WHEN creatinine_max >= 1.60 THEN 3
WHEN urineoutput < 750.0 THEN 3
WHEN bun_max >= 28.0 THEN 3
WHEN urineoutput >= 10000.0 THEN 3
WHEN creatinine_max >= 1.20 THEN 1
WHEN bun_max >= 17.0 THEN 1
WHEN bun_max >= 7.50 THEN 1
ELSE 0
END AS renal
-- pulmonary
, CASE
WHEN pao2fio2_vent_min IS NULL THEN 0
WHEN pao2fio2_vent_min >= 150 THEN 1
WHEN pao2fio2_vent_min < 150 THEN 3
ELSE null
END AS pulmonary
-- hematologic
, CASE
WHEN wbc_max IS NULL
AND platelet_min IS NULL
THEN null
WHEN wbc_min < 1.0 THEN 3
WHEN wbc_min < 2.5 THEN 1
WHEN platelet_min < 50.0 THEN 1
WHEN wbc_max >= 50.0 THEN 1
ELSE 0
END AS hematologic
-- hepatic
-- We have defined the "standard" PT as 12 seconds.
-- This is an assumption and subsequent analyses may be
-- affected by this assumption.
, CASE
WHEN pt_max IS NULL
AND bilirubin_max IS NULL
THEN null
WHEN bilirubin_max >= 2.0 THEN 1
WHEN pt_max > (12 + 3) THEN 1
WHEN pt_min < (12 * 0.25) THEN 1
ELSE 0
END AS hepatic
FROM cohort
)
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
-- coalesce statements impute normal score of zero if NULL
, COALESCE(neurologic, 0)
+ COALESCE(cardiovascular, 0)
+ COALESCE(renal, 0)
+ COALESCE(pulmonary, 0)
+ COALESCE(hematologic, 0)
+ COALESCE(hepatic, 0)
AS lods
, neurologic
, cardiovascular
, renal
, pulmonary
, hematologic
, hepatic
FROM mimiciv_icu.icustays ie
LEFT JOIN scorecomp s
ON ie.stay_id = s.stay_id
;step-054 生成oasis表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS oasis; CREATE TABLE oasis AS
-- ------------------------------------------------------------------
-- Title: Oxford Acute Severity of Illness Score (oasis)
-- This query extracts the Oxford acute severity of illness score.
-- This score is a measure of severity of illness for patients in the ICU.
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------
-- Reference for OASIS:
-- Johnson, Alistair EW, Andrew A. Kramer, and Gari D. Clifford.
-- A new severity of illness scale using a subset of acute physiology
-- and chronic health evaluation data elements shows comparable
-- predictive accuracy*.
-- Critical care medicine 41, no. 7 (2013): 1711-1718.
-- Variables used in OASIS:
-- Heart rate, GCS, MAP, Temperature, Respiratory rate, Ventilation status
-- (from chartevents)
-- Urine output (from outputevents)
-- Elective surgery (from admissions and services)
-- Pre-ICU in-hospital length of stay (from admissions and icustays)
-- Age (from patients)
-- Regarding missing values:
-- The ventilation flag is always 0/1. It cannot be missing,
-- since VENT=0 if no data is found for vent settings.
-- Note:
-- The score is calculated for *all* ICU patients, with the assumption
-- that the user will subselect appropriate stay_ids.
WITH surgflag AS (
SELECT ie.stay_id
, MAX(CASE
WHEN LOWER(curr_service) LIKE '%surg%' THEN 1
WHEN curr_service = 'ORTHO' THEN 1
ELSE 0 END) AS surgical
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_hosp.services se
ON ie.hadm_id = se.hadm_id
AND se.transfertime < DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
GROUP BY ie.stay_id
)
-- first day ventilation
, vent AS (
SELECT ie.stay_id
, MAX(
CASE WHEN v.stay_id IS NOT NULL THEN 1 ELSE 0 END
) AS vent
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.ventilation v
ON ie.stay_id = v.stay_id
AND v.ventilation_status = 'InvasiveVent'
AND (
(
v.starttime >= ie.intime
AND v.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
OR (
v.endtime >= ie.intime
AND v.endtime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
OR (
v.starttime <= ie.intime
AND v.endtime >= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
)
GROUP BY ie.stay_id
)
, cohort AS (
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
, ie.intime
, ie.outtime
, adm.deathtime
, DATETIME_DIFF(ie.intime, adm.admittime, 'MINUTE') AS preiculos
, ag.age
, gcs.gcs_min
, vital.heart_rate_max
, vital.heart_rate_min
, vital.mbp_max
, vital.mbp_min
, vital.resp_rate_max
, vital.resp_rate_min
, vital.temperature_max
, vital.temperature_min
, vent.vent AS mechvent
, uo.urineoutput
, CASE
WHEN adm.admission_type = 'ELECTIVE' AND sf.surgical = 1
THEN 1
WHEN adm.admission_type IS NULL OR sf.surgical IS NULL
THEN null
ELSE 0
END AS electivesurgery
-- mortality flags
, CASE
WHEN adm.deathtime BETWEEN ie.intime AND ie.outtime
THEN 1
-- sometimes there are typographical errors in the death date
WHEN adm.deathtime <= ie.intime
THEN 1
WHEN adm.dischtime <= ie.outtime
AND adm.discharge_location = 'DEAD/EXPIRED'
THEN 1
ELSE 0 END
AS icustay_expire_flag
, adm.hospital_expire_flag
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_hosp.admissions adm
ON ie.hadm_id = adm.hadm_id
INNER JOIN mimiciv_hosp.patients pat
ON ie.subject_id = pat.subject_id
LEFT JOIN mimiciv_derived.age ag
ON ie.hadm_id = ag.hadm_id
LEFT JOIN surgflag sf
ON ie.stay_id = sf.stay_id
-- join to custom tables to get more data....
LEFT JOIN mimiciv_derived.first_day_gcs gcs
ON ie.stay_id = gcs.stay_id
LEFT JOIN mimiciv_derived.first_day_vitalsign vital
ON ie.stay_id = vital.stay_id
LEFT JOIN mimiciv_derived.first_day_urine_output uo
ON ie.stay_id = uo.stay_id
LEFT JOIN vent
ON ie.stay_id = vent.stay_id
)
, scorecomp AS (
SELECT co.subject_id, co.hadm_id, co.stay_id
, co.icustay_expire_flag
, co.hospital_expire_flag
-- Below code calculates the component scores needed for oasis
, CASE WHEN preiculos IS NULL THEN null
WHEN preiculos < 10.2 THEN 5
WHEN preiculos < 297 THEN 3
WHEN preiculos < 1440 THEN 0
WHEN preiculos < 18708 THEN 1
ELSE 2 END AS preiculos_score
, CASE WHEN age IS NULL THEN null
WHEN age < 24 THEN 0
WHEN age <= 53 THEN 3
WHEN age <= 77 THEN 6
WHEN age <= 89 THEN 9
WHEN age >= 90 THEN 7
ELSE 0 END AS age_score
, CASE WHEN gcs_min IS NULL THEN null
WHEN gcs_min <= 7 THEN 10
WHEN gcs_min < 14 THEN 4
WHEN gcs_min = 14 THEN 3
ELSE 0 END AS gcs_score
, CASE WHEN heart_rate_max IS NULL THEN null
WHEN heart_rate_max > 125 THEN 6
WHEN heart_rate_min < 33 THEN 4
WHEN heart_rate_max >= 107 AND heart_rate_max <= 125 THEN 3
WHEN heart_rate_max >= 89 AND heart_rate_max <= 106 THEN 1
ELSE 0 END AS heart_rate_score
, CASE WHEN mbp_min IS NULL THEN null
WHEN mbp_min < 20.65 THEN 4
WHEN mbp_min < 51 THEN 3
WHEN mbp_max > 143.44 THEN 3
WHEN mbp_min >= 51 AND mbp_min < 61.33 THEN 2
ELSE 0 END AS mbp_score
, CASE WHEN resp_rate_min IS NULL THEN null
WHEN resp_rate_min < 6 THEN 10
WHEN resp_rate_max > 44 THEN 9
WHEN resp_rate_max > 30 THEN 6
WHEN resp_rate_max > 22 THEN 1
WHEN resp_rate_min < 13 THEN 1 ELSE 0
END AS resp_rate_score
, CASE WHEN temperature_max IS NULL THEN null
WHEN temperature_max > 39.88 THEN 6
WHEN
temperature_min >= 33.22 AND temperature_min <= 35.93 THEN 4
WHEN
temperature_max >= 33.22 AND temperature_max <= 35.93 THEN 4
WHEN temperature_min < 33.22 THEN 3
WHEN temperature_min > 35.93 AND temperature_min <= 36.39 THEN 2
WHEN
temperature_max >= 36.89 AND temperature_max <= 39.88 THEN 2
ELSE 0 END AS temp_score
, CASE WHEN urineoutput IS NULL THEN null
WHEN urineoutput < 671.09 THEN 10
WHEN urineoutput > 6896.80 THEN 8
WHEN urineoutput >= 671.09
AND urineoutput <= 1426.99 THEN 5
WHEN urineoutput >= 1427.00
AND urineoutput <= 2544.14 THEN 1
ELSE 0 END AS urineoutput_score
, CASE WHEN mechvent IS NULL THEN null
WHEN mechvent = 1 THEN 9
ELSE 0 END AS mechvent_score
, CASE WHEN electivesurgery IS NULL THEN null
WHEN electivesurgery = 1 THEN 0
ELSE 6 END AS electivesurgery_score
-- The below code gives the component associated with each score
-- This is not needed to calculate oasis, but provided for
-- user convenience. If both the min/max are in the normal range
-- (score of 0), then the average value is stored.
, preiculos
, age
, gcs_min AS gcs
, CASE WHEN heart_rate_max IS NULL THEN null
WHEN heart_rate_max > 125 THEN heart_rate_max
WHEN heart_rate_min < 33 THEN heart_rate_min
WHEN heart_rate_max >= 107
AND heart_rate_max <= 125
THEN heart_rate_max
WHEN heart_rate_max >= 89
AND heart_rate_max <= 106
THEN heart_rate_max
ELSE (heart_rate_min + heart_rate_max) / 2 END AS heartrate
, CASE WHEN mbp_min IS NULL THEN null
WHEN mbp_min < 20.65 THEN mbp_min
WHEN mbp_min < 51 THEN mbp_min
WHEN mbp_max > 143.44 THEN mbp_max
WHEN mbp_min >= 51 AND mbp_min < 61.33 THEN mbp_min
ELSE (mbp_min + mbp_max) / 2 END AS meanbp
, CASE WHEN resp_rate_min IS NULL THEN null
WHEN resp_rate_min < 6 THEN resp_rate_min
WHEN resp_rate_max > 44 THEN resp_rate_max
WHEN resp_rate_max > 30 THEN resp_rate_max
WHEN resp_rate_max > 22 THEN resp_rate_max
WHEN resp_rate_min < 13 THEN resp_rate_min
ELSE (resp_rate_min + resp_rate_max) / 2 END AS resprate
, CASE WHEN temperature_max IS NULL THEN null
WHEN temperature_max > 39.88 THEN temperature_max
WHEN temperature_min >= 33.22
AND temperature_min <= 35.93
THEN temperature_min
WHEN temperature_max >= 33.22
AND temperature_max <= 35.93
THEN temperature_max
WHEN temperature_min < 33.22
THEN temperature_min
WHEN temperature_min > 35.93
AND temperature_min <= 36.39
THEN temperature_min
WHEN temperature_max >= 36.89
AND temperature_max <= 39.88
THEN temperature_max
ELSE (temperature_min + temperature_max) / 2 END AS temp
, urineoutput
, mechvent
, electivesurgery
FROM cohort co
)
, score AS (
SELECT s.*
, COALESCE(age_score, 0)
+ COALESCE(preiculos_score, 0)
+ COALESCE(gcs_score, 0)
+ COALESCE(heart_rate_score, 0)
+ COALESCE(mbp_score, 0)
+ COALESCE(resp_rate_score, 0)
+ COALESCE(temp_score, 0)
+ COALESCE(urineoutput_score, 0)
+ COALESCE(mechvent_score, 0)
+ COALESCE(electivesurgery_score, 0)
AS oasis
FROM scorecomp s
)
SELECT
subject_id, hadm_id, stay_id
, oasis
-- Calculate the probability of in-hospital mortality
, 1 / (1 + EXP(- (-6.1746 + 0.1275 * (oasis)))) AS oasis_prob
, age, age_score
, preiculos, preiculos_score
, gcs, gcs_score
, heartrate, heart_rate_score
, meanbp, mbp_score
, resprate, resp_rate_score
, temp, temp_score
, urineoutput, urineoutput_score
, mechvent, mechvent_score
, electivesurgery, electivesurgery_score
FROM score
;step-055 生成sapsii表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS sapsii; CREATE TABLE sapsii AS
-- ------------------------------------------------------------------
-- Title: Simplified Acute Physiology Score II (SAPS II)
-- This query extracts the simplified acute physiology score II.
-- This score is a measure of patient severity of illness.
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------
-- Reference for SAPS II:
-- Le Gall, Jean-Roger, Stanley Lemeshow, and Fabienne Saulnier.
-- "A new simplified acute physiology score (SAPS II) based on
-- a European/North American multicenter study."
-- JAMA 270, no. 24 (1993): 2957-2963.
-- Variables used in SAPS II:
-- Age, GCS
-- VITALS: Heart rate, systolic blood pressure, temperature
-- FLAGS: ventilation/cpap
-- IO: urine output
-- LABS: PaO2/FiO2 ratio, blood urea nitrogen, WBC,
-- potassium, sodium, HCO3
WITH co AS (
SELECT
subject_id
, hadm_id
, stay_id
, intime AS starttime
, DATETIME_ADD(intime, INTERVAL '24' HOUR) AS endtime
FROM mimiciv_icu.icustays
)
, cpap AS (
SELECT
co.subject_id
, co.stay_id
, GREATEST(
MIN(DATETIME_SUB(charttime, INTERVAL '1' HOUR)), co.starttime
) AS starttime
, LEAST(
MAX(DATETIME_ADD(charttime, INTERVAL '4' HOUR)), co.endtime
) AS endtime
, MAX(
CASE
WHEN
REGEXP_CONTAINS(LOWER(ce.value), '(cpap mask|bipap)') THEN 1
ELSE 0
END
) AS cpap
FROM co
INNER JOIN mimiciv_icu.chartevents ce
ON co.stay_id = ce.stay_id
AND ce.charttime > co.starttime
AND ce.charttime <= co.endtime
WHERE ce.itemid = 226732
AND REGEXP_CONTAINS(LOWER(ce.value), '(cpap mask|bipap)')
GROUP BY co.subject_id, co.stay_id, co.starttime, co.endtime
)
-- extract a flag for surgical service
-- this combined with "elective" from admissions table
-- defines elective/non-elective surgery
, surgflag AS (
SELECT adm.hadm_id
, CASE
WHEN LOWER(curr_service) LIKE '%surg%' THEN 1 ELSE 0
END AS surgical
, ROW_NUMBER() OVER
(
PARTITION BY adm.hadm_id
ORDER BY transfertime
) AS serviceorder
FROM mimiciv_hosp.admissions adm
LEFT JOIN mimiciv_hosp.services se
ON adm.hadm_id = se.hadm_id
)
-- icd-9 diagnostic codes are our best source for comorbidity information
-- unfortunately, they are technically a-causal
-- however, this shouldn't matter too much for the SAPS II comorbidities
, comorb AS (
SELECT hadm_id
-- these are slightly different than elixhauser comorbidities,
-- but based on them they include some non-comorbid ICD-9 codes
-- (e.g. 20302, relapse of multiple myeloma)
, MAX(CASE
WHEN
icd_version = 9 AND SUBSTR(
icd_code, 1, 3
) BETWEEN '042' AND '044'
THEN 1
WHEN
icd_version = 10 AND SUBSTR(
icd_code, 1, 3
) BETWEEN 'B20' AND 'B22' THEN 1
WHEN icd_version = 10 AND SUBSTR(icd_code, 1, 3) = 'B24' THEN 1
ELSE 0 END) AS aids /* HIV and AIDS */
, MAX(
CASE WHEN icd_version = 9 THEN
CASE
-- lymphoma
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20000' AND '20238' THEN 1
-- leukemia
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20240' AND '20248' THEN 1
-- lymphoma
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20250' AND '20302' THEN 1
-- leukemia
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20310' AND '20312' THEN 1
-- lymphoma
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20302' AND '20382' THEN 1
-- chronic leukemia
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20400' AND '20522' THEN 1
-- other myeloid leukemia
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20580' AND '20702' THEN 1
-- other myeloid leukemia
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20720' AND '20892' THEN 1
-- lymphoma
WHEN SUBSTR(icd_code, 1, 4) IN ('2386', '2733') THEN 1
ELSE 0 END
WHEN
icd_version = 10 AND SUBSTR(
icd_code, 1, 3
) BETWEEN 'C81' AND 'C96' THEN 1
ELSE 0 END) AS hem
, MAX(CASE
WHEN icd_version = 9 THEN
CASE
WHEN SUBSTR(icd_code, 1, 4) BETWEEN '1960' AND '1991' THEN 1
WHEN
SUBSTR(
icd_code, 1, 5
) BETWEEN '20970' AND '20975' THEN 1
WHEN SUBSTR(icd_code, 1, 5) IN ('20979', '78951') THEN 1
ELSE 0 END
WHEN
icd_version = 10 AND SUBSTR(
icd_code, 1, 3
) BETWEEN 'C77' AND 'C79' THEN 1
WHEN icd_version = 10 AND SUBSTR(icd_code, 1, 4) = 'C800' THEN 1
ELSE 0 END) AS mets /* Metastatic cancer */
FROM mimiciv_hosp.diagnoses_icd
GROUP BY hadm_id
)
, pafi1 AS (
-- join blood gas to ventilation durations to determine if patient was vent
-- also join to cpap table for the same purpose
SELECT
co.stay_id
, bg.charttime
, pao2fio2ratio AS pao2fio2
, CASE WHEN vd.stay_id IS NOT NULL THEN 1 ELSE 0 END AS vent
, CASE WHEN cp.subject_id IS NOT NULL THEN 1 ELSE 0 END AS cpap
FROM co
LEFT JOIN mimiciv_derived.bg bg
ON co.subject_id = bg.subject_id
AND bg.specimen = 'ART.'
AND bg.charttime > co.starttime
AND bg.charttime <= co.endtime
LEFT JOIN mimiciv_derived.ventilation vd
ON co.stay_id = vd.stay_id
AND bg.charttime > vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
LEFT JOIN cpap cp
ON bg.subject_id = cp.subject_id
AND bg.charttime > cp.starttime
AND bg.charttime <= cp.endtime
)
, pafi2 AS (
-- get the minimum PaO2/FiO2 ratio *only for ventilated/cpap patients*
SELECT stay_id
, MIN(pao2fio2) AS pao2fio2_vent_min
FROM pafi1
WHERE vent = 1 OR cpap = 1
GROUP BY stay_id
)
, gcs AS (
SELECT co.stay_id
, MIN(gcs.gcs) AS mingcs
FROM co
LEFT JOIN mimiciv_derived.gcs gcs
ON co.stay_id = gcs.stay_id
AND co.starttime < gcs.charttime
AND gcs.charttime <= co.endtime
GROUP BY co.stay_id
)
, vital AS (
SELECT
co.stay_id
, MIN(vital.heart_rate) AS heartrate_min
, MAX(vital.heart_rate) AS heartrate_max
, MIN(vital.sbp) AS sysbp_min
, MAX(vital.sbp) AS sysbp_max
, MIN(vital.temperature) AS tempc_min
, MAX(vital.temperature) AS tempc_max
FROM co
LEFT JOIN mimiciv_derived.vitalsign vital
ON co.subject_id = vital.subject_id
AND co.starttime < vital.charttime
AND co.endtime >= vital.charttime
GROUP BY co.stay_id
)
, uo AS (
SELECT
co.stay_id
, SUM(uo.urineoutput) AS urineoutput
FROM co
LEFT JOIN mimiciv_derived.urine_output uo
ON co.stay_id = uo.stay_id
AND co.starttime < uo.charttime
AND co.endtime >= uo.charttime
GROUP BY co.stay_id
)
, labs AS (
SELECT
co.stay_id
, MIN(labs.bun) AS bun_min
, MAX(labs.bun) AS bun_max
, MIN(labs.potassium) AS potassium_min
, MAX(labs.potassium) AS potassium_max
, MIN(labs.sodium) AS sodium_min
, MAX(labs.sodium) AS sodium_max
, MIN(labs.bicarbonate) AS bicarbonate_min
, MAX(labs.bicarbonate) AS bicarbonate_max
FROM co
LEFT JOIN mimiciv_derived.chemistry labs
ON co.subject_id = labs.subject_id
AND co.starttime < labs.charttime
AND co.endtime >= labs.charttime
GROUP BY co.stay_id
)
, cbc AS (
SELECT
co.stay_id
, MIN(cbc.wbc) AS wbc_min
, MAX(cbc.wbc) AS wbc_max
FROM co
LEFT JOIN mimiciv_derived.complete_blood_count cbc
ON co.subject_id = cbc.subject_id
AND co.starttime < cbc.charttime
AND co.endtime >= cbc.charttime
GROUP BY co.stay_id
)
, enz AS (
SELECT
co.stay_id
, MIN(enz.bilirubin_total) AS bilirubin_min
, MAX(enz.bilirubin_total) AS bilirubin_max
FROM co
LEFT JOIN mimiciv_derived.enzyme enz
ON co.subject_id = enz.subject_id
AND co.starttime < enz.charttime
AND co.endtime >= enz.charttime
GROUP BY co.stay_id
)
, cohort AS (
SELECT
ie.subject_id, ie.hadm_id, ie.stay_id
, ie.intime
, ie.outtime
, va.age
, co.starttime
, co.endtime
, vital.heartrate_max
, vital.heartrate_min
, vital.sysbp_max
, vital.sysbp_min
, vital.tempc_max
, vital.tempc_min
-- this value is non-null iff the patient is on vent/cpap
, pf.pao2fio2_vent_min
, uo.urineoutput
, labs.bun_min
, labs.bun_max
, cbc.wbc_min
, cbc.wbc_max
, labs.potassium_min
, labs.potassium_max
, labs.sodium_min
, labs.sodium_max
, labs.bicarbonate_min
, labs.bicarbonate_max
, enz.bilirubin_min
, enz.bilirubin_max
, gcs.mingcs
, comorb.aids
, comorb.hem
, comorb.mets
, CASE
WHEN adm.admission_type = 'ELECTIVE' AND sf.surgical = 1
THEN 'ScheduledSurgical'
WHEN adm.admission_type != 'ELECTIVE' AND sf.surgical = 1
THEN 'UnscheduledSurgical'
ELSE 'Medical'
END AS admissiontype
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_hosp.admissions adm
ON ie.hadm_id = adm.hadm_id
LEFT JOIN mimiciv_derived.age va
ON ie.hadm_id = va.hadm_id
INNER JOIN co
ON ie.stay_id = co.stay_id
-- join to above views
LEFT JOIN pafi2 pf
ON ie.stay_id = pf.stay_id
LEFT JOIN surgflag sf
ON adm.hadm_id = sf.hadm_id AND sf.serviceorder = 1
LEFT JOIN comorb
ON ie.hadm_id = comorb.hadm_id
-- join to custom tables to get more data....
LEFT JOIN gcs gcs
ON ie.stay_id = gcs.stay_id
LEFT JOIN vital
ON ie.stay_id = vital.stay_id
LEFT JOIN uo
ON ie.stay_id = uo.stay_id
LEFT JOIN labs
ON ie.stay_id = labs.stay_id
LEFT JOIN cbc
ON ie.stay_id = cbc.stay_id
LEFT JOIN enz
ON ie.stay_id = enz.stay_id
)
, scorecomp AS (
SELECT
cohort.*
-- Below code calculates the component scores needed for SAPS
, CASE
WHEN age IS NULL THEN null
WHEN age < 40 THEN 0
WHEN age < 60 THEN 7
WHEN age < 70 THEN 12
WHEN age < 75 THEN 15
WHEN age < 80 THEN 16
WHEN age >= 80 THEN 18
END AS age_score
, CASE
WHEN heartrate_max IS NULL THEN null
WHEN heartrate_min < 40 THEN 11
WHEN heartrate_max >= 160 THEN 7
WHEN heartrate_max >= 120 THEN 4
WHEN heartrate_min < 70 THEN 2
WHEN heartrate_max >= 70 AND heartrate_max < 120
AND heartrate_min >= 70 AND heartrate_min < 120
THEN 0
END AS hr_score
, CASE
WHEN sysbp_min IS NULL THEN null
WHEN sysbp_min < 70 THEN 13
WHEN sysbp_min < 100 THEN 5
WHEN sysbp_max >= 200 THEN 2
WHEN sysbp_max >= 100 AND sysbp_max < 200
AND sysbp_min >= 100 AND sysbp_min < 200
THEN 0
END AS sysbp_score
, CASE
WHEN tempc_max IS NULL THEN null
WHEN tempc_max >= 39.0 THEN 3
WHEN tempc_min < 39.0 THEN 0
END AS temp_score
, CASE
WHEN pao2fio2_vent_min IS NULL THEN null
WHEN pao2fio2_vent_min < 100 THEN 11
WHEN pao2fio2_vent_min < 200 THEN 9
WHEN pao2fio2_vent_min >= 200 THEN 6
END AS pao2fio2_score
, CASE
WHEN urineoutput IS NULL THEN null
WHEN urineoutput < 500.0 THEN 11
WHEN urineoutput < 1000.0 THEN 4
WHEN urineoutput >= 1000.0 THEN 0
END AS uo_score
, CASE
WHEN bun_max IS NULL THEN null
WHEN bun_max < 28.0 THEN 0
WHEN bun_max < 84.0 THEN 6
WHEN bun_max >= 84.0 THEN 10
END AS bun_score
, CASE
WHEN wbc_max IS NULL THEN null
WHEN wbc_min < 1.0 THEN 12
WHEN wbc_max >= 20.0 THEN 3
WHEN wbc_max >= 1.0 AND wbc_max < 20.0
AND wbc_min >= 1.0 AND wbc_min < 20.0
THEN 0
END AS wbc_score
, CASE
WHEN potassium_max IS NULL THEN null
WHEN potassium_min < 3.0 THEN 3
WHEN potassium_max >= 5.0 THEN 3
WHEN potassium_max >= 3.0 AND potassium_max < 5.0
AND potassium_min >= 3.0 AND potassium_min < 5.0
THEN 0
END AS potassium_score
, CASE
WHEN sodium_max IS NULL THEN null
WHEN sodium_min < 125 THEN 5
WHEN sodium_max >= 145 THEN 1
WHEN sodium_max >= 125 AND sodium_max < 145
AND sodium_min >= 125 AND sodium_min < 145
THEN 0
END AS sodium_score
, CASE
WHEN bicarbonate_max IS NULL THEN null
WHEN bicarbonate_min < 15.0 THEN 5
WHEN bicarbonate_min < 20.0 THEN 3
WHEN bicarbonate_max >= 20.0
AND bicarbonate_min >= 20.0
THEN 0
END AS bicarbonate_score
, CASE
WHEN bilirubin_max IS NULL THEN null
WHEN bilirubin_max < 4.0 THEN 0
WHEN bilirubin_max < 6.0 THEN 4
WHEN bilirubin_max >= 6.0 THEN 9
END AS bilirubin_score
, CASE
WHEN mingcs IS NULL THEN null
WHEN mingcs < 3 THEN null -- erroneous value/on trach
WHEN mingcs < 6 THEN 26
WHEN mingcs < 9 THEN 13
WHEN mingcs < 11 THEN 7
WHEN mingcs < 14 THEN 5
WHEN mingcs >= 14
AND mingcs <= 15
THEN 0
END AS gcs_score
, CASE
WHEN aids = 1 THEN 17
WHEN hem = 1 THEN 10
WHEN mets = 1 THEN 9
ELSE 0
END AS comorbidity_score
, CASE
WHEN admissiontype = 'ScheduledSurgical' THEN 0
WHEN admissiontype = 'Medical' THEN 6
WHEN admissiontype = 'UnscheduledSurgical' THEN 8
ELSE null
END AS admissiontype_score
FROM cohort
)
-- Calculate SAPS II here, later we will calculate probability
, score AS (
SELECT s.*
-- coalesce statements impute normal score
-- of zero if data element is missing
, COALESCE(age_score, 0)
+ COALESCE(hr_score, 0)
+ COALESCE(sysbp_score, 0)
+ COALESCE(temp_score, 0)
+ COALESCE(pao2fio2_score, 0)
+ COALESCE(uo_score, 0)
+ COALESCE(bun_score, 0)
+ COALESCE(wbc_score, 0)
+ COALESCE(potassium_score, 0)
+ COALESCE(sodium_score, 0)
+ COALESCE(bicarbonate_score, 0)
+ COALESCE(bilirubin_score, 0)
+ COALESCE(gcs_score, 0)
+ COALESCE(comorbidity_score, 0)
+ COALESCE(admissiontype_score, 0)
AS sapsii
FROM scorecomp s
)
SELECT s.subject_id, s.hadm_id, s.stay_id
, s.starttime
, s.endtime
, sapsii
, 1 / (
1 + EXP(- (-7.7631 + 0.0737 * (sapsii) + 0.9971 * (LN(sapsii + 1))))
) AS sapsii_prob
, age_score
, hr_score
, sysbp_score
, temp_score
, pao2fio2_score
, uo_score
, bun_score
, wbc_score
, potassium_score
, sodium_score
, bicarbonate_score
, bilirubin_score
, gcs_score
, comorbidity_score
, admissiontype_score
FROM score s
;step-056 生成sirs表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS sirs; CREATE TABLE sirs AS
-- ------------------------------------------------------------------
-- Title: Systemic inflammatory response syndrome (SIRS) criteria
-- This query extracts the Systemic inflammatory response syndrome
-- (SIRS) criteria. The criteria quantify the level of inflammatory
-- response of the body. The score is calculated on the first day
-- of each ICU patients' stay.
-- ------------------------------------------------------------------
-- Reference for SIRS:
-- American College of Chest Physicians/Society of Critical Care
-- Medicine Consensus Conference: definitions for sepsis and organ
-- failure and guidelines for the use of innovative therapies in
-- sepsis". Crit. Care Med. 20 (6): 864–74. 1992.
-- doi:10.1097/00003246-199206000-00025. PMID 1597042.
-- Variables used in SIRS:
-- Body temperature (min and max)
-- Heart rate (max)
-- Respiratory rate (max)
-- PaCO2 (min)
-- White blood cell count (min and max)
-- the presence of greater than 10% immature neutrophils (band forms)
-- Note:
-- The score is calculated for *all* ICU patients, with the assumption
-- that the user will subselect appropriate stay_ids.
-- Aggregate the components for the score
WITH scorecomp AS (
SELECT ie.stay_id
, v.temperature_min
, v.temperature_max
, v.heart_rate_max
, v.resp_rate_max
, bg.pco2_min AS paco2_min
, l.wbc_min
, l.wbc_max
, l.bands_max
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.first_day_bg_art bg
ON ie.stay_id = bg.stay_id
LEFT JOIN mimiciv_derived.first_day_vitalsign v
ON ie.stay_id = v.stay_id
LEFT JOIN mimiciv_derived.first_day_lab l
ON ie.stay_id = l.stay_id
)
, 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 stay_id
, CASE
WHEN temperature_min < 36.0 THEN 1
WHEN temperature_max > 38.0 THEN 1
WHEN temperature_min IS NULL THEN null
ELSE 0
END AS temp_score
, CASE
WHEN heart_rate_max > 90.0 THEN 1
WHEN heart_rate_max IS NULL THEN null
ELSE 0
END AS heart_rate_score
, CASE
WHEN resp_rate_max > 20.0 THEN 1
WHEN paco2_min < 32.0 THEN 1
WHEN COALESCE(resp_rate_max, paco2_min) IS NULL THEN null
ELSE 0
END AS resp_score
, CASE
WHEN wbc_min < 4.0 THEN 1
WHEN wbc_max > 12.0 THEN 1
WHEN bands_max > 10 THEN 1-- > 10% immature neurophils (band forms)
WHEN COALESCE(wbc_min, bands_max) IS NULL THEN null
ELSE 0
END AS wbc_score
FROM scorecomp
)
SELECT
ie.subject_id, ie.hadm_id, ie.stay_id
-- Combine all the scores to get SOFA
-- Impute 0 if the score is missing
, COALESCE(temp_score, 0)
+ COALESCE(heart_rate_score, 0)
+ COALESCE(resp_score, 0)
+ COALESCE(wbc_score, 0)
AS sirs
, temp_score, heart_rate_score, resp_score, wbc_score
FROM mimiciv_icu.icustays ie
LEFT JOIN scorecalc s
ON ie.stay_id = s.stay_id
;step-057 生成sofa表
sql计算时内存不够用，花了4351元从8G升级至16G，记得一起报销。
耗时11 分 47 秒 成功返回查询。
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS sofa; CREATE TABLE 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,
-- as the data window is limited.
-- ------------------------------------------------------------------
-- 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 (chartevents)
-- Creatinine, Bilirubin, FiO2, PaO2, Platelets (labevents)
-- Dopamine, Dobutamine, Epinephrine, Norepinephrine (inputevents)
-- Urine output (outputevents)
-- use icustay_hourly to get a row for every hour the patient was in the ICU
-- all of our joins to data will use these times
-- to extract data pertinent to only that hour
WITH co AS (
SELECT ih.stay_id, ie.hadm_id
, hr
-- start/endtime can be used to filter to values within this hour
, DATETIME_SUB(ih.endtime, INTERVAL '1' HOUR) AS starttime
, ih.endtime
FROM mimiciv_derived.icustay_hourly ih
INNER JOIN mimiciv_icu.icustays ie
ON ih.stay_id = ie.stay_id
)
, pafi AS (
-- join blood gas to ventilation durations to determine if patient was vent
SELECT ie.stay_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.stay_id IS NULL THEN pao2fio2ratio ELSE null
END AS pao2fio2ratio_novent
, CASE
WHEN vd.stay_id IS NOT NULL THEN pao2fio2ratio ELSE null
END AS pao2fio2ratio_vent
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.bg bg
ON ie.subject_id = bg.subject_id
LEFT JOIN mimiciv_derived.ventilation vd
ON ie.stay_id = vd.stay_id
AND bg.charttime >= vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
WHERE specimen = 'ART.'
)
, vs AS (
SELECT co.stay_id, co.hr
-- vitals
, MIN(vs.mbp) AS meanbp_min
FROM co
LEFT JOIN mimiciv_derived.vitalsign vs
ON co.stay_id = vs.stay_id
AND co.starttime < vs.charttime
AND co.endtime >= vs.charttime
GROUP BY co.stay_id, co.hr
)
, gcs AS (
SELECT co.stay_id, co.hr
-- gcs
, MIN(gcs.gcs) AS gcs_min
FROM co
LEFT JOIN mimiciv_derived.gcs gcs
ON co.stay_id = gcs.stay_id
AND co.starttime < gcs.charttime
AND co.endtime >= gcs.charttime
GROUP BY co.stay_id, co.hr
)
, bili AS (
SELECT co.stay_id, co.hr
, MAX(enz.bilirubin_total) AS bilirubin_max
FROM co
LEFT JOIN mimiciv_derived.enzyme enz
ON co.hadm_id = enz.hadm_id
AND co.starttime < enz.charttime
AND co.endtime >= enz.charttime
GROUP BY co.stay_id, co.hr
)
, cr AS (
SELECT co.stay_id, co.hr
, MAX(chem.creatinine) AS creatinine_max
FROM co
LEFT JOIN mimiciv_derived.chemistry chem
ON co.hadm_id = chem.hadm_id
AND co.starttime < chem.charttime
AND co.endtime >= chem.charttime
GROUP BY co.stay_id, co.hr
)
, plt AS (
SELECT co.stay_id, co.hr
, MIN(cbc.platelet) AS platelet_min
FROM co
LEFT JOIN mimiciv_derived.complete_blood_count cbc
ON co.hadm_id = cbc.hadm_id
AND co.starttime < cbc.charttime
AND co.endtime >= cbc.charttime
GROUP BY co.stay_id, co.hr
)
, pf AS (
SELECT co.stay_id, co.hr
, MIN(pafi.pao2fio2ratio_novent) AS pao2fio2ratio_novent
, MIN(pafi.pao2fio2ratio_vent) AS pao2fio2ratio_vent
FROM co
-- bring in blood gases that occurred during this hour
LEFT JOIN pafi
ON co.stay_id = pafi.stay_id
AND co.starttime < pafi.charttime
AND co.endtime >= pafi.charttime
GROUP BY co.stay_id, co.hr
)
-- sum uo separately to prevent duplicating values
, uo AS (
SELECT co.stay_id, co.hr
-- uo
, MAX(
CASE WHEN uo.uo_tm_24hr >= 22 AND uo.uo_tm_24hr <= 30
THEN uo.urineoutput_24hr / uo.uo_tm_24hr * 24
END) AS uo_24hr
FROM co
LEFT JOIN mimiciv_derived.urine_output_rate uo
ON co.stay_id = uo.stay_id
AND co.starttime < uo.charttime
AND co.endtime >= uo.charttime
GROUP BY co.stay_id, co.hr
)
-- collapse vasopressors into 1 row per hour
-- also ensures only 1 row per chart time
, vaso AS (
SELECT
co.stay_id
, co.hr
, MAX(epi.vaso_rate) AS rate_epinephrine
, MAX(nor.vaso_rate) AS rate_norepinephrine
, MAX(dop.vaso_rate) AS rate_dopamine
, MAX(dob.vaso_rate) AS rate_dobutamine
FROM co
LEFT JOIN mimiciv_derived.epinephrine epi
ON co.stay_id = epi.stay_id
AND co.endtime > epi.starttime
AND co.endtime <= epi.endtime
LEFT JOIN mimiciv_derived.norepinephrine nor
ON co.stay_id = nor.stay_id
AND co.endtime > nor.starttime
AND co.endtime <= nor.endtime
LEFT JOIN mimiciv_derived.dopamine dop
ON co.stay_id = dop.stay_id
AND co.endtime > dop.starttime
AND co.endtime <= dop.endtime
LEFT JOIN mimiciv_derived.dobutamine dob
ON co.stay_id = dob.stay_id
AND co.endtime > dob.starttime
AND co.endtime <= dob.endtime
WHERE epi.stay_id IS NOT NULL
OR nor.stay_id IS NOT NULL
OR dop.stay_id IS NOT NULL
OR dob.stay_id IS NOT NULL
GROUP BY co.stay_id, co.hr
)
, scorecomp AS (
SELECT
co.stay_id
, co.hr
, co.starttime, co.endtime
, pf.pao2fio2ratio_novent
, pf.pao2fio2ratio_vent
, vaso.rate_epinephrine
, vaso.rate_norepinephrine
, vaso.rate_dopamine
, vaso.rate_dobutamine
, vs.meanbp_min
, gcs.gcs_min
-- uo
, uo.uo_24hr
-- labs
, bili.bilirubin_max
, cr.creatinine_max
, plt.platelet_min
FROM co
LEFT JOIN vs
ON co.stay_id = vs.stay_id
AND co.hr = vs.hr
LEFT JOIN gcs
ON co.stay_id = gcs.stay_id
AND co.hr = gcs.hr
LEFT JOIN bili
ON co.stay_id = bili.stay_id
AND co.hr = bili.hr
LEFT JOIN cr
ON co.stay_id = cr.stay_id
AND co.hr = cr.hr
LEFT JOIN plt
ON co.stay_id = plt.stay_id
AND co.hr = plt.hr
LEFT JOIN pf
ON co.stay_id = pf.stay_id
AND co.hr = pf.hr
LEFT JOIN uo
ON co.stay_id = uo.stay_id
AND co.hr = uo.hr
LEFT JOIN vaso
ON co.stay_id = vaso.stay_id
AND co.hr = vaso.hr
)
, 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
, CASE
WHEN pao2fio2ratio_vent < 100 THEN 4
WHEN pao2fio2ratio_vent < 200 THEN 3
WHEN pao2fio2ratio_novent < 300 THEN 2
WHEN pao2fio2ratio_vent < 300 THEN 2
WHEN pao2fio2ratio_novent < 400 THEN 1
WHEN pao2fio2ratio_vent < 400 THEN 1
WHEN
COALESCE(
pao2fio2ratio_vent, pao2fio2ratio_novent
) IS NULL THEN null
ELSE 0
END AS respiration
-- Coagulation
, 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 coagulation
-- Liver
, CASE
-- Bilirubin checks in mg/dL
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 liver
-- Cardiovascular
, 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.1
OR 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 cardiovascular
-- Neurological failure (GCS)
, 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 cns
-- Renal failure - high creatinine or low urine output
, CASE
WHEN (creatinine_max >= 5.0) THEN 4
WHEN uo_24hr < 200 THEN 4
WHEN (creatinine_max >= 3.5 AND creatinine_max < 5.0) THEN 3
WHEN uo_24hr < 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(uo_24hr, creatinine_max) IS NULL THEN null
ELSE 0
END AS renal
FROM scorecomp
)
, 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
, COALESCE(
MAX(respiration) OVER w
, 0) AS respiration_24hours
, COALESCE(
MAX(coagulation) OVER w
, 0) AS coagulation_24hours
, COALESCE(
MAX(liver) OVER w
, 0) AS liver_24hours
, COALESCE(
MAX(cardiovascular) OVER w
, 0) AS cardiovascular_24hours
, COALESCE(
MAX(cns) OVER w
, 0) AS cns_24hours
, COALESCE(
MAX(renal) OVER w
, 0) AS renal_24hours
-- sum together data for final SOFA
, COALESCE(
MAX(respiration) OVER w
, 0)
+ COALESCE(
MAX(coagulation) OVER w
, 0)
+ COALESCE(
MAX(liver) OVER w
, 0)
+ COALESCE(
MAX(cardiovascular) OVER w
, 0)
+ COALESCE(
MAX(cns) OVER w
, 0)
+ COALESCE(
MAX(renal) OVER w
, 0)
AS sofa_24hours
FROM scorecalc s
WINDOW w AS
(
PARTITION BY stay_id
ORDER BY hr
ROWS BETWEEN 23 PRECEDING AND 0 FOLLOWING
)
)
SELECT * FROM score_final
WHERE hr >= 0;step-058 生成suspicion_of_infection表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS suspicion_of_infection; CREATE TABLE suspicion_of_infection AS
-- note this duplicates prescriptions
-- each ICU stay in the same hospitalization will get a copy of
-- all prescriptions for that hospitalization
WITH ab_tbl AS (
SELECT
abx.subject_id, abx.hadm_id, abx.stay_id
, abx.antibiotic
, abx.starttime AS antibiotic_time
-- date is used to match microbiology cultures with only date available
, DATE_TRUNC('DAY', abx.starttime) AS antibiotic_date
, abx.stoptime AS antibiotic_stoptime
-- create a unique identifier for each patient antibiotic
, ROW_NUMBER() OVER
(
PARTITION BY subject_id
ORDER BY starttime, stoptime, antibiotic
) AS ab_id
FROM mimiciv_derived.antibiotic abx
)
, me AS (
SELECT micro_specimen_id
-- the following columns are identical for all rows
-- of the same micro_specimen_id
-- these aggregates simply collapse duplicates down to 1 row
, MAX(subject_id) AS subject_id
, MAX(hadm_id) AS hadm_id
, CAST(MAX(chartdate) AS DATE) AS chartdate
, MAX(charttime) AS charttime
, MAX(spec_type_desc) AS spec_type_desc
-- identify negative cultures as NULL organism
-- or a specific itemid saying "NEGATIVE"
, MAX(
CASE WHEN org_name IS NOT NULL
AND org_itemid != 90856
AND org_name != ''
THEN 1 ELSE 0
END) AS positiveculture
FROM mimiciv_hosp.microbiologyevents
GROUP BY micro_specimen_id
)
-- culture followed by an antibiotic
, me_then_ab AS (
SELECT
ab_tbl.subject_id
, ab_tbl.hadm_id
, ab_tbl.stay_id
, ab_tbl.ab_id
, me72.micro_specimen_id
, COALESCE(me72.charttime, DATETIME(me72.chartdate)) AS last72_charttime
, me72.positiveculture AS last72_positiveculture
, me72.spec_type_desc AS last72_specimen
-- we will use this partition to select the earliest culture
-- before this abx
-- this ensures each antibiotic is only matched to a single culture
-- and consequently we have 1 row per antibiotic
, ROW_NUMBER() OVER
(
PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id
ORDER BY me72.chartdate, me72.charttime NULLS LAST
) AS micro_seq
FROM ab_tbl
-- abx taken after culture, but no more than 72 hours after
LEFT JOIN me me72
ON ab_tbl.subject_id = me72.subject_id
AND
(
(
-- if charttime is available, use it
me72.charttime IS NOT NULL
AND ab_tbl.antibiotic_time > me72.charttime
AND ab_tbl.antibiotic_time <= DATETIME_ADD(
me72.charttime, INTERVAL '72' HOUR
)
)
OR
(
-- if charttime is not available, use chartdate
me72.charttime IS NULL
AND antibiotic_date >= me72.chartdate
AND antibiotic_date <= DATE_ADD(
me72.chartdate, INTERVAL '3' DAY
)
)
)
)
, ab_then_me AS (
SELECT
ab_tbl.subject_id
, ab_tbl.hadm_id
, ab_tbl.stay_id
, ab_tbl.ab_id
, me24.micro_specimen_id
, COALESCE(me24.charttime, DATETIME(me24.chartdate)) AS next24_charttime
, me24.positiveculture AS next24_positiveculture
, me24.spec_type_desc AS next24_specimen
-- we will use this partition to select the earliest culture
-- before this abx
-- this ensures each antibiotic is only matched to a single culture
-- and consequently we have 1 row per antibiotic
, ROW_NUMBER() OVER
(
PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id
ORDER BY me24.chartdate, me24.charttime NULLS LAST
) AS micro_seq
FROM ab_tbl
-- culture in subsequent 24 hours
LEFT JOIN me me24
ON ab_tbl.subject_id = me24.subject_id
AND
(
(
-- if charttime is available, use it
me24.charttime IS NOT NULL
AND ab_tbl.antibiotic_time >= DATETIME_SUB(me24.charttime, INTERVAL '24' HOUR) -- noqa: L016
AND ab_tbl.antibiotic_time < me24.charttime
)
OR
(
-- if charttime is not available, use chartdate
me24.charttime IS NULL
AND ab_tbl.antibiotic_date >= DATE_SUB(me24.chartdate, INTERVAL '1' DAY) -- noqa: L016
AND ab_tbl.antibiotic_date <= me24.chartdate
)
)
)
SELECT
ab_tbl.subject_id
, ab_tbl.stay_id
, ab_tbl.hadm_id
, ab_tbl.ab_id
, ab_tbl.antibiotic
, ab_tbl.antibiotic_time
, CASE
WHEN last72_specimen IS NULL AND next24_specimen IS NULL
THEN 0
ELSE 1
END AS suspected_infection
-- time of suspected infection:
-- (1) the culture time (if before antibiotic)
-- (2) or the antibiotic time (if before culture)
, CASE
WHEN last72_specimen IS NULL AND next24_specimen IS NULL
THEN NULL
ELSE COALESCE(last72_charttime, antibiotic_time)
END AS suspected_infection_time
, COALESCE(last72_charttime, next24_charttime) AS culture_time
-- the specimen that was cultured
, COALESCE(last72_specimen, next24_specimen) AS specimen
-- whether the cultured specimen ended up being positive or not
, COALESCE(
last72_positiveculture, next24_positiveculture
) AS positive_culture
FROM ab_tbl
LEFT JOIN ab_then_me ab2me
ON ab_tbl.subject_id = ab2me.subject_id
AND ab_tbl.ab_id = ab2me.ab_id
AND ab2me.micro_seq = 1
LEFT JOIN me_then_ab me2ab
ON ab_tbl.subject_id = me2ab.subject_id
AND ab_tbl.ab_id = me2ab.ab_id
AND me2ab.micro_seq = 1
;step-059 生成kdigo_stages表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS kdigo_stages; CREATE TABLE kdigo_stages AS
-- This query checks if the patient had AKI according to KDIGO.
-- AKI is calculated every time a creatinine or urine output measurement occurs.
-- Baseline creatinine is defined as the lowest creatinine in the past 7 days.
-- get creatinine stages
WITH cr_stg AS (
SELECT
cr.stay_id
, cr.charttime
, cr.creat_low_past_7day
, cr.creat_low_past_48hr
, cr.creat
, CASE
-- 3x baseline
WHEN cr.creat >= (cr.creat_low_past_7day * 3.0) THEN 3
-- *OR* cr >= 4.0 with associated increase
WHEN cr.creat >= 4
-- For patients reaching Stage 3 by SCr >4.0 mg/dl
-- require that the patient first achieve ...
-- an acute increase >= 0.3 within 48 hr
-- *or* an increase of >= 1.5 times baseline
AND (
cr.creat_low_past_48hr <= 3.7 OR cr.creat >= (
1.5 * cr.creat_low_past_7day
)
)
THEN 3
-- TODO: initiation of RRT
WHEN cr.creat >= (cr.creat_low_past_7day * 2.0) THEN 2
WHEN cr.creat >= (cr.creat_low_past_48hr + 0.3) THEN 1
WHEN cr.creat >= (cr.creat_low_past_7day * 1.5) THEN 1
ELSE 0 END AS aki_stage_creat
FROM mimiciv_derived.kdigo_creatinine cr
)
-- stages for UO / creat
, uo_stg AS (
SELECT
uo.stay_id
, uo.charttime
, uo.weight
, uo.uo_rt_6hr
, uo.uo_rt_12hr
, uo.uo_rt_24hr
-- AKI stages according to urine output
, CASE
WHEN uo.uo_rt_6hr IS NULL THEN NULL
-- require patient to be in ICU for at least 6 hours to stage UO
WHEN uo.charttime <= DATETIME_ADD(ie.intime, INTERVAL '6' HOUR)
THEN 0
-- require the UO rate to be calculated over the
-- duration specified in KDIGO
-- Stage 3: <0.3 ml/kg/h for >=24 hours
WHEN uo.uo_tm_24hr >= 24 AND uo.uo_rt_24hr < 0.3 THEN 3
-- *or* anuria for >= 12 hours
WHEN uo.uo_tm_12hr >= 12 AND uo.uo_rt_12hr = 0 THEN 3
-- Stage 2: <0.5 ml/kg/h for >= 12 hours
WHEN uo.uo_tm_12hr >= 12 AND uo.uo_rt_12hr < 0.5 THEN 2
-- Stage 1: <0.5 ml/kg/h for 6–12 hours
WHEN uo.uo_tm_6hr >= 6 AND uo.uo_rt_6hr < 0.5 THEN 1
ELSE 0 END AS aki_stage_uo
FROM mimiciv_derived.kdigo_uo uo
INNER JOIN mimiciv_icu.icustays ie
ON uo.stay_id = ie.stay_id
)
-- get CRRT data
, crrt_stg AS (
SELECT
stay_id
, charttime
, CASE
WHEN charttime IS NOT NULL THEN 3
ELSE NULL END AS aki_stage_crrt
FROM mimiciv_derived.crrt
WHERE crrt_mode IS NOT NULL
)
-- get all charttimes documented
, tm_stg AS (
SELECT
stay_id, charttime
FROM cr_stg
UNION DISTINCT
SELECT
stay_id, charttime
FROM uo_stg
UNION DISTINCT
SELECT
stay_id, charttime
FROM crrt_stg
)
SELECT
ie.subject_id
, ie.hadm_id
, ie.stay_id
, tm.charttime
, cr.creat_low_past_7day
, cr.creat_low_past_48hr
, cr.creat
, cr.aki_stage_creat
, uo.uo_rt_6hr
, uo.uo_rt_12hr
, uo.uo_rt_24hr
, uo.aki_stage_uo
, crrt.aki_stage_crrt
-- Classify AKI using both creatinine/urine output criteria
, GREATEST(
COALESCE(cr.aki_stage_creat, 0)
, COALESCE(uo.aki_stage_uo, 0)
, COALESCE(crrt.aki_stage_crrt, 0)
) AS aki_stage
-- We intend to combine together the scores from creatinine/UO by left
-- joining from the above temporary table which has all possible charttime.
-- This will guarantee we include all creatinine/UO measurements.
-- However, we have times where UO is measured, but not creatinine.
-- Thus we end up with NULLs for the creatinine column(s). Calculating
-- the highest stage across the columns will often only consider one stage.
-- For example, consider the following rows:
-- stay_id=123, time=10:00, cr_low_7day=4.0, uo_rt_6hr=NULL -> stage 3
-- stay_id=123, time=10:30, cr_low_7day=NULL, uo_rt_6hr=0.3 -> stage 1
-- This results in the stage alternating from low/high across rows.
-- To overcome this, we create a new column which carries forward the
-- highest KDIGO stage from the last 6 hours. In most cases, this smooths
-- out any discontinuity.
, MAX(
GREATEST(
COALESCE(cr.aki_stage_creat, 0)
, COALESCE(uo.aki_stage_uo, 0)
, COALESCE(crrt.aki_stage_crrt, 0)
)
) OVER
(
PARTITION BY ie.subject_id
ORDER BY DATETIME_DIFF(tm.charttime, ie.intime, 'SECOND')
ROWS BETWEEN 21600 PRECEDING AND CURRENT ROW
) AS aki_stage_smoothed
FROM mimiciv_icu.icustays ie
-- get all possible charttimes as listed in tm_stg
LEFT JOIN tm_stg tm
ON ie.stay_id = tm.stay_id
LEFT JOIN cr_stg cr
ON ie.stay_id = cr.stay_id
AND tm.charttime = cr.charttime
LEFT JOIN uo_stg uo
ON ie.stay_id = uo.stay_id
AND tm.charttime = uo.charttime
LEFT JOIN crrt_stg crrt
ON ie.stay_id = crrt.stay_id
AND tm.charttime = crrt.charttime
;
step-060 生成first_day_sofa表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS first_day_sofa; CREATE TABLE first_day_sofa AS
-- ------------------------------------------------------------------
-- Title: Sequential Organ Failure Assessment (SOFA)
-- This query extracts the sequential organ failure assessment
-- (formerly: sepsis-related organ failure assessment).
-- This score is a measure of organ failure for patients in the ICU.
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------
-- 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 CHARTEVENTS)
-- Creatinine, Bilirubin, FiO2, PaO2, Platelets (sourced from LABEVENTS)
-- Dopamine, Dobutamine, Epinephrine, Norepinephrine (sourced from INPUTEVENTS)
-- Urine output (sourced from OUTPUTEVENTS)
-- The following views required to run this query:
-- 1) first_day_urine_output
-- 2) first_day_vitalsign
-- 3) first_day_gcs
-- 4) first_day_lab
-- 5) first_day_bg_art
-- 6) ventdurations
-- extract drug rates from derived vasopressor tables
WITH vaso_stg AS (
SELECT ie.stay_id, 'norepinephrine' AS treatment, vaso_rate AS rate
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.norepinephrine mv
ON ie.stay_id = mv.stay_id
AND mv.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND mv.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
UNION ALL
SELECT ie.stay_id, 'epinephrine' AS treatment, vaso_rate AS rate
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.epinephrine mv
ON ie.stay_id = mv.stay_id
AND mv.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND mv.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
UNION ALL
SELECT ie.stay_id, 'dobutamine' AS treatment, vaso_rate AS rate
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.dobutamine mv
ON ie.stay_id = mv.stay_id
AND mv.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND mv.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
UNION ALL
SELECT ie.stay_id, 'dopamine' AS treatment, vaso_rate AS rate
FROM mimiciv_icu.icustays ie
INNER JOIN mimiciv_derived.dopamine mv
ON ie.stay_id = mv.stay_id
AND mv.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND mv.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
)
, vaso_mv AS (
SELECT
ie.stay_id
, MAX(
CASE WHEN treatment = 'norepinephrine' THEN rate ELSE NULL END
) AS rate_norepinephrine
, MAX(
CASE WHEN treatment = 'epinephrine' THEN rate ELSE NULL END
) AS rate_epinephrine
, MAX(
CASE WHEN treatment = 'dopamine' THEN rate ELSE NULL END
) AS rate_dopamine
, MAX(
CASE WHEN treatment = 'dobutamine' THEN rate ELSE NULL END
) AS rate_dobutamine
FROM mimiciv_icu.icustays ie
LEFT JOIN vaso_stg v
ON ie.stay_id = v.stay_id
GROUP BY ie.stay_id
)
, pafi1 AS (
-- join blood gas to ventilation durations to determine if patient was vent
SELECT ie.stay_id, bg.charttime
, bg.pao2fio2ratio
, CASE WHEN vd.stay_id IS NOT NULL THEN 1 ELSE 0 END AS isvent
FROM mimiciv_icu.icustays ie
LEFT JOIN mimiciv_derived.bg bg
ON ie.subject_id = bg.subject_id
AND bg.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)
AND bg.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
LEFT JOIN mimiciv_derived.ventilation vd
ON ie.stay_id = vd.stay_id
AND bg.charttime >= vd.starttime
AND bg.charttime <= vd.endtime
AND vd.ventilation_status = 'InvasiveVent'
)
, pafi2 AS (
-- 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.
SELECT stay_id
, MIN(
CASE WHEN isvent = 0 THEN pao2fio2ratio ELSE NULL END
) AS pao2fio2_novent_min
, MIN(
CASE WHEN isvent = 1 THEN pao2fio2ratio ELSE NULL END
) AS pao2fio2_vent_min
FROM pafi1
GROUP BY stay_id
)
-- Aggregate the components for the score
, scorecomp AS (
SELECT ie.stay_id
, v.mbp_min
, mv.rate_norepinephrine
, mv.rate_epinephrine
, mv.rate_dopamine
, mv.rate_dobutamine
, l.creatinine_max
, l.bilirubin_total_max AS bilirubin_max
, l.platelets_min AS platelet_min
, pf.pao2fio2_novent_min
, pf.pao2fio2_vent_min
, uo.urineoutput
, gcs.gcs_min
FROM mimiciv_icu.icustays ie
LEFT JOIN vaso_mv mv
ON ie.stay_id = mv.stay_id
LEFT JOIN pafi2 pf
ON ie.stay_id = pf.stay_id
LEFT JOIN mimiciv_derived.first_day_vitalsign v
ON ie.stay_id = v.stay_id
LEFT JOIN mimiciv_derived.first_day_lab l
ON ie.stay_id = l.stay_id
LEFT JOIN mimiciv_derived.first_day_urine_output uo
ON ie.stay_id = uo.stay_id
LEFT JOIN mimiciv_derived.first_day_gcs gcs
ON ie.stay_id = gcs.stay_id
)
, 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 stay_id
-- Respiration
, CASE
WHEN pao2fio2_vent_min < 100 THEN 4
WHEN pao2fio2_vent_min < 200 THEN 3
WHEN pao2fio2_novent_min < 300 THEN 2
WHEN pao2fio2_novent_min < 400 THEN 1
WHEN
COALESCE(
pao2fio2_vent_min, pao2fio2_novent_min
) IS NULL THEN NULL
ELSE 0
END AS respiration
-- Coagulation
, 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 coagulation
-- Liver
, CASE
-- Bilirubin checks in mg/dL
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 liver
-- Cardiovascular
, 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.1
OR rate_norepinephrine <= 0.1
THEN 3
WHEN rate_dopamine > 0 OR rate_dobutamine > 0 THEN 2
WHEN mbp_min < 70 THEN 1
WHEN
COALESCE(
mbp_min
, rate_dopamine
, rate_dobutamine
, rate_epinephrine
, rate_norepinephrine
) IS NULL THEN NULL
ELSE 0
END AS cardiovascular
-- Neurological failure (GCS)
, 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 cns
-- Renal failure - high creatinine or low urine output
, CASE
WHEN (creatinine_max >= 5.0) THEN 4
WHEN urineoutput < 200 THEN 4
WHEN (creatinine_max >= 3.5 AND creatinine_max < 5.0) THEN 3
WHEN urineoutput < 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(urineoutput, creatinine_max) IS NULL THEN NULL
ELSE 0 END
AS renal
FROM scorecomp
)
SELECT ie.subject_id, ie.hadm_id, ie.stay_id
-- Combine all the scores to get SOFA
-- Impute 0 if the score is missing
, COALESCE(respiration, 0)
+ COALESCE(coagulation, 0)
+ COALESCE(liver, 0)
+ COALESCE(cardiovascular, 0)
+ COALESCE(cns, 0)
+ COALESCE(renal, 0)
AS sofa
, respiration
, coagulation
, liver
, cardiovascular
, cns
, renal
FROM mimiciv_icu.icustays ie
LEFT JOIN scorecalc s
ON ie.stay_id = s.stay_id
;step-061 生成first_day_weight表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS sepsis3; CREATE TABLE sepsis3 AS
-- Creates a table with "onset" time of Sepsis-3 in the ICU.
-- That is, the earliest time at which a patient had SOFA >= 2
-- and suspicion of infection.
-- As many variables used in SOFA are only collected in the ICU,
-- this query can only define sepsis-3 onset within the ICU.
-- extract rows with SOFA >= 2
-- implicitly this assumes baseline SOFA was 0 before ICU admission.
WITH sofa AS (
SELECT stay_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 mimiciv_derived.sofa
WHERE sofa_24hours >= 2
)
, s1 AS (
SELECT
soi.subject_id
, soi.stay_id
-- suspicion columns
, soi.ab_id
, soi.antibiotic
, soi.antibiotic_time
, soi.culture_time
, soi.suspected_infection
, soi.suspected_infection_time
, soi.specimen
, soi.positive_culture
-- sofa columns
, starttime, endtime
, respiration, coagulation, liver, cardiovascular, cns, renal
, sofa_score
-- All rows have an associated suspicion of infection event
-- Therefore, Sepsis-3 is defined as SOFA >= 2.
-- Implicitly, the baseline SOFA score is assumed to be zero,
-- as we do not know if the patient has preexisting
-- (acute or chronic) organ dysfunction before the onset
-- of infection.
, sofa_score >= 2 AND suspected_infection = 1 AS sepsis3
-- subselect to the earliest suspicion/antibiotic/SOFA row
, ROW_NUMBER() OVER
(
PARTITION BY soi.stay_id
ORDER BY
suspected_infection_time, antibiotic_time, culture_time, endtime
) AS rn_sus
FROM mimiciv_derived.suspicion_of_infection AS soi
INNER JOIN sofa
ON soi.stay_id = sofa.stay_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
)
-- only include in-ICU rows
WHERE soi.stay_id IS NOT NULL
)
SELECT
subject_id, stay_id
-- note: there may be more than one antibiotic given at this time
, antibiotic_time
-- culture times may be dates, rather than times
, culture_time
, suspected_infection_time
-- endtime is latest time at which the SOFA score is valid
, endtime AS sofa_time
, sofa_score
, respiration, coagulation, liver, cardiovascular, cns, renal
, sepsis3
FROM s1
WHERE rn_sus = 1step-061 生成sepsis3表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS sepsis3; CREATE TABLE sepsis3 AS
-- Creates a table with "onset" time of Sepsis-3 in the ICU.
-- That is, the earliest time at which a patient had SOFA >= 2
-- and suspicion of infection.
-- As many variables used in SOFA are only collected in the ICU,
-- this query can only define sepsis-3 onset within the ICU.
-- extract rows with SOFA >= 2
-- implicitly this assumes baseline SOFA was 0 before ICU admission.
WITH sofa AS (
SELECT stay_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 mimiciv_derived.sofa
WHERE sofa_24hours >= 2
)
, s1 AS (
SELECT
soi.subject_id
, soi.stay_id
-- suspicion columns
, soi.ab_id
, soi.antibiotic
, soi.antibiotic_time
, soi.culture_time
, soi.suspected_infection
, soi.suspected_infection_time
, soi.specimen
, soi.positive_culture
-- sofa columns
, starttime, endtime
, respiration, coagulation, liver, cardiovascular, cns, renal
, sofa_score
-- All rows have an associated suspicion of infection event
-- Therefore, Sepsis-3 is defined as SOFA >= 2.
-- Implicitly, the baseline SOFA score is assumed to be zero,
-- as we do not know if the patient has preexisting
-- (acute or chronic) organ dysfunction before the onset
-- of infection.
, sofa_score >= 2 AND suspected_infection = 1 AS sepsis3
-- subselect to the earliest suspicion/antibiotic/SOFA row
, ROW_NUMBER() OVER
(
PARTITION BY soi.stay_id
ORDER BY
suspected_infection_time, antibiotic_time, culture_time, endtime
) AS rn_sus
FROM mimiciv_derived.suspicion_of_infection AS soi
INNER JOIN sofa
ON soi.stay_id = sofa.stay_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
)
-- only include in-ICU rows
WHERE soi.stay_id IS NOT NULL
)
SELECT
subject_id, stay_id
-- note: there may be more than one antibiotic given at this time
, antibiotic_time
-- culture times may be dates, rather than times
, culture_time
, suspected_infection_time
-- endtime is latest time at which the SOFA score is valid
, endtime AS sofa_time
, sofa_score
, respiration, coagulation, liver, cardiovascular, cns, renal
, sepsis3
FROM s1
WHERE rn_sus = 1step-062 生成vasoactive_agent表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS vasoactive_agent; CREATE TABLE vasoactive_agent AS
-- This query creates a single table with ongoing doses of vasoactive agents.
-- TBD: rarely angiotensin II, methylene blue, and
-- isoprenaline/isoproterenol are used. These are not in the query currently
-- as they are not documented in MetaVision. However, they could
-- be documented in other hospital wide systems.
-- collect all vasopressor administration times
-- create a single table with these as start/stop times
WITH tm AS (
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.dobutamine
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.dopamine
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.epinephrine
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.norepinephrine
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.phenylephrine
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.vasopressin
UNION DISTINCT
SELECT
stay_id, starttime AS vasotime
FROM mimiciv_derived.milrinone
UNION DISTINCT
-- combine end times from the same tables
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.dobutamine
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.dopamine
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.epinephrine
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.norepinephrine
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.phenylephrine
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.vasopressin
UNION DISTINCT
SELECT
stay_id, endtime AS vasotime
FROM mimiciv_derived.milrinone
)
-- create starttime/endtime from all possible times collected
, tm_lag AS (
SELECT stay_id
, vasotime AS starttime
-- note: the last row for each partition (stay_id) will have
-- a NULL endtime. we can drop this row later, as we know that no
-- vasopressor will start at this time (otherwise, we would have
-- a later end time, which would mean it's not the last row!)
, LEAD(
vasotime, 1
) OVER (PARTITION BY stay_id ORDER BY vasotime) AS endtime
FROM tm
)
-- left join to raw data tables to combine doses
SELECT t.stay_id, t.starttime, t.endtime
-- inopressors/vasopressors
, dop.vaso_rate AS dopamine -- mcg/kg/min
, epi.vaso_rate AS epinephrine -- mcg/kg/min
, nor.vaso_rate AS norepinephrine -- mcg/kg/min
, phe.vaso_rate AS phenylephrine -- mcg/kg/min
, vas.vaso_rate AS vasopressin -- units/hour
-- inodialators
, dob.vaso_rate AS dobutamine -- mcg/kg/min
, mil.vaso_rate AS milrinone -- mcg/kg/min
-- isoproterenol is used in CCU/CVICU but not in metavision
-- other drugs not included here but (rarely) used in the BIDMC:
-- angiotensin II, methylene blue
FROM tm_lag t
LEFT JOIN mimiciv_derived.dobutamine dob
ON t.stay_id = dob.stay_id
AND t.starttime >= dob.starttime
AND t.endtime <= dob.endtime
LEFT JOIN mimiciv_derived.dopamine dop
ON t.stay_id = dop.stay_id
AND t.starttime >= dop.starttime
AND t.endtime <= dop.endtime
LEFT JOIN mimiciv_derived.epinephrine epi
ON t.stay_id = epi.stay_id
AND t.starttime >= epi.starttime
AND t.endtime <= epi.endtime
LEFT JOIN mimiciv_derived.norepinephrine nor
ON t.stay_id = nor.stay_id
AND t.starttime >= nor.starttime
AND t.endtime <= nor.endtime
LEFT JOIN mimiciv_derived.phenylephrine phe
ON t.stay_id = phe.stay_id
AND t.starttime >= phe.starttime
AND t.endtime <= phe.endtime
LEFT JOIN mimiciv_derived.vasopressin vas
ON t.stay_id = vas.stay_id
AND t.starttime >= vas.starttime
AND t.endtime <= vas.endtime
LEFT JOIN mimiciv_derived.milrinone mil
ON t.stay_id = mil.stay_id
AND t.starttime >= mil.starttime
AND t.endtime <= mil.endtime
-- remove the final row for each stay_id
-- it will not have any infusions associated with it
WHERE t.endtime IS NOT NULL;step-063 生成norepinephrine_equivalent_dose表
SET search_path TO mimiciv_derived, mimiciv_hosp, mimiciv_icu, mimiciv_ed;
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS norepinephrine_equivalent_dose; CREATE TABLE norepinephrine_equivalent_dose AS
-- This query calculates norepinephrine equivalent dose for vasopressors.
-- Based on "Vasopressor dose equivalence: A scoping review and
-- suggested formula" by Goradia et al. 2020.
-- The relevant table makes the following equivalences:
-- Norepinephrine - 1:1 - comparison dose of 0.1 ug/kg/min
-- Epinephrine - 1:1 [0.7, 1.4] - 0.1 ug/kg/min
-- Dopamine - 1:100 [75.2, 144.4] - 10 ug/kg/min
-- Metaraminol - 1:8 [8.3] - 0.8 ug/kg/min
-- Phenylephrine - 1:10 [1.1, 16.3] - 1 ug/kg/min
-- Vasopressin - 1:0.4 [0.3, 0.4] - 0.04 units/min
-- Angiotensin II - 1:0.1 [0.07, 0.13] - 0.01 ug/kg/min
SELECT stay_id, starttime, endtime
-- calculate the dose
-- all sources are in mcg/kg/min,
-- except vasopressin which is in units/hour
, ROUND(CAST(
COALESCE(norepinephrine, 0)
+ COALESCE(epinephrine, 0)
+ COALESCE(phenylephrine / 10, 0)
+ COALESCE(dopamine / 100, 0)
-- + metaraminol/8 -- metaraminol not used in BIDMC
+ COALESCE(vasopressin * 2.5 / 60, 0)
-- angiotensin_ii*10 -- angiotensin ii rarely used, though
-- it could be included due to norepinephrine sparing effects
AS NUMERIC), 4) AS norepinephrine_equivalent_dose
FROM mimiciv_derived.vasoactive_agent
WHERE norepinephrine IS NOT NULL
OR epinephrine IS NOT NULL
OR phenylephrine IS NOT NULL
OR dopamine IS NOT NULL
OR vasopressin IS NOT NULL;