-
Notifications
You must be signed in to change notification settings - Fork 0
/
08-mpwr_abg_unpivot.sql
87 lines (87 loc) · 2.47 KB
/
08-mpwr_abg_unpivot.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
create temp table mpwr_abg_unpivot as
with abg AS (
select
abg.icustay_id
, RANK() OVER(PARTITION BY abg.icustay_id ORDER BY abg.charttime) AS abg_seq
, abg.charttime AS charttime
, MAX(CASE
WHEN abg.itemid IN (777, 225698) THEN abg.valuenum
ELSE NULL
END) AS arterial_co2
, MAX(CASE
WHEN abg.itemid IN (778, 220235) THEN abg.valuenum
ELSE NULL
END) AS arterial_paco2
, MAX(CASE
WHEN abg.itemid IN (779, 220224) THEN abg.valuenum
ELSE NULL
END) AS arterial_pao2
, MAX(CASE
WHEN abg.itemid IN (780, 223830) THEN abg.valuenum
ELSE NULL
END) AS arterial_ph
from mpwr_cohort ch
inner join mpwr_abg abg
on ch.icustay_id = abg.icustay_id
and abg.charttime between ch.starttime and ch.endtime
group by abg.icustay_id, abg.charttime
)
, spo2 AS
(
select
abg.icustay_id
, abg.abg_seq
, mpwr_o2.valuenum AS spo2
, datetime_diff(mpwr_o2.charttime, abg.charttime, MINUTE) AS spo2_offset
, RANK() OVER(
PARTITION BY abg.icustay_id, abg.charttime
ORDER BY datetime_diff(mpwr_o2.charttime, abg.charttime, MINUTE) DESC
) AS spo2_seq
from abg
inner join mpwr_o2
on abg.icustay_id = mpwr_o2.icustay_id
and mpwr_o2.charttime between datetime_sub(abg.charttime, INTERVAL 6 HOUR) and abg.charttime
and mpwr_o2.itemid IN (646, 220277)
)
, fio2 AS
(
select
abg.icustay_id
, abg.abg_seq
, CASE
WHEN mpwr_o2.itemid = 190 THEN CAST(mpwr_o2.valuenum*100 AS INT64)
ELSE mpwr_o2.valuenum
END AS fio2
, datetime_diff(mpwr_o2.charttime, abg.charttime, MINUTE) AS fio2_offset
, RANK() OVER(
PARTITION BY abg.icustay_id, abg.charttime
ORDER BY datetime_diff(mpwr_o2.charttime, abg.charttime, MINUTE) DESC
) AS fio2_seq
from abg
inner join mpwr_o2
on abg.icustay_id = mpwr_o2.icustay_id
and mpwr_o2.charttime between datetime_sub(abg.charttime, INTERVAL 6 HOUR) and abg.charttime
and mpwr_o2.itemid IN (190, 223835)
)
select
abg.icustay_id
, abg.charttime
, abg.arterial_co2
, abg.arterial_paco2
, abg.arterial_pao2
, abg.arterial_ph
, spo2.spo2_offset
, spo2.spo2
, fio2.fio2_offset
, fio2.fio2
from abg
left join spo2
on abg.icustay_id = spo2.icustay_id
and abg.abg_seq = spo2.abg_seq
and spo2.spo2_seq = 1
left join fio2
on abg.icustay_id = fio2.icustay_id
and abg.abg_seq = fio2.abg_seq
and fio2.fio2_seq = 1
order by abg.icustay_id, abg.charttime
;