跟着 mimic-code 探索 MIMIC 数据之 notebooks CRRT (二)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT linkorderid
  , orderid
  , CASE WHEN itemid = 227525 THEN 'Calcium' ELSE 'KCl' END AS label
  , starttime, endtime
  , rate, rateuom
  , statusdescription
FROM inputevents_mv
WHERE itemid IN
(
  --227525,-- Calcium Gluconate (CRRT)
  227536 -- KCl (CRRT)
)
AND statusdescription != 'Rewritten'
AND icustay_id = '246866'
ORDER BY starttime, endtime;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

WITH t1 AS
(
SELECT icustay_id
  , CASE WHEN itemid = 227525 then 'Calcium' else 'KCl' END AS label
  , starttime
  , endtime
  , CASE WHEN LAG(endtime) 
		  OVER (PARTITION BY icustay_id, itemid 
			  ORDER BY starttime, endtime) = starttime 
		THEN 0 ELSE 1 END
    AS new_event_flag
  , rate, rateuom
  , statusdescription
FROM inputevents_mv
WHERE itemid IN
(
  --227525,-- Calcium Gluconate (CRRT)
  227536 -- KCl (CRRT)
)
AND statusdescription != 'Rewritten'
AND icustay_id = '246866';

1
2
3
4

, CASE WHEN LAG(endtime) OVER
	(PARTITION BY icustay_id, itemid 
		ORDER BY starttime, endtime) = starttime
THEN 0 ELSE 1 END AS new_event_flag

 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

WITH t1 AS
(
  SELECT icustay_id
  , CASE WHEN 
      itemid = 227525 THEN 'Calcium' 
    ELSE 'KCl' END AS label
  , starttime, endtime
  , LAG(endtime) OVER 
      (PARTITION BY icustay_id, itemid ORDER BY starttime, endtime)
      AS endtime_lag
  , CASE WHEN LAG(endtime) OVER 
      (PARTITION BY icustay_id, itemid ORDER BY starttime, endtime) = starttime THEN 0
    ELSE 1 END AS new_event_flag
  , rate, rateuom
  , statusdescription
  FROM inputevents_mv
  WHERE itemid IN
	  (
  --227525,-- Calcium Gluconate (CRRT)
  227536 -- KCl (CRRT)
	  )
  AND statusdescription != 'Rewritten'
  AND icustay_id = '246866'
)
SELECT 
label
, starttime, endtime, endtime_lag
, new_event_flag
, rate, rateuom
, statusdescription
FROM t1;

1
2
3

SUM(new_event_flag) OVER 
    (PARTITION BY icustay_id, label 
    ORDER BY starttime, endtime) AS time_partition

 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

WITH t1 AS
  (
    SELECT icustay_id
    , CASE WHEN
        itemid = 227525
        THEN 'Calcium' ELSE 'KCl' END AS label
    , starttime, endtime
    , CASE WHEN
        LAG(endtime) OVER
          (PARTITION BY icustay_id, itemid
          ORDER BY starttime, endtime) = starttime
        THEN 0 ELSE 1 END AS new_event_flag
    , rate, rateuom, statusdescription
    FROM inputevents_mv
    WHERE itemid IN
      (
        --227525,-- Calcium Gluconate (CRRT)
        227536 -- KCl (CRRT)
      )
    AND statusdescription != 'Rewritten'
    AND icustay_id = '246866'
  )
  ,t2 AS
  (
    SELECT icustay_id
    , label, starttime, endtime, new_event_flag
    , SUM(new_event_flag) OVER
        (PARTITION BY icustay_id, label
        ORDER BY starttime, endtime) AS time_partition
    , rate, rateuom, statusdescription
    FROM t1
  )
SELECT
label
, starttime, endtime
, new_event_flag
, time_partition
, rate, rateuom, statusdescription
FROM t2
ORDER BY starttime, endtime;

 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

WITH t1 AS
  (
    SELECT icustay_id
    , CASE WHEN 
        itemid = 227525 THEN 'Calcium' 
      ELSE 'KCl' END AS label
    , starttime, endtime
    , CASE WHEN LAG(endtime) OVER 
        (PARTITION BY icustay_id, itemid
        ORDER BY starttime, endtime) = starttime
        THEN 0
      ELSE 1 END AS new_event_flag
    , rate, rateuom, statusdescription
    FROM inputevents_mv
    WHERE itemid IN
    (
      --227525,-- Calcium Gluconate (CRRT)
      227536 -- KCl (CRRT)
    )
    AND statusdescription != 'Rewritten'
    AND icustay_id = '246866'
  )
  , t2 AS
  (
    SELECT 
    icustay_id, label
    , starttime, endtime
    , SUM(new_event_flag) OVER 
        (PARTITION BY icustay_id, label 
	        ORDER BY starttime, endtime)
	    AS time_partition 
    , rate, rateuom, statusdescription
    FROM t1
  )
  , t3 AS
  (
    SELECT
    icustay_id, label
    , starttime, endtime, time_partition 
    , rate, rateuom, statusdescription
    , ROW_NUMBER() OVER 
        (PARTITION BY icustay_id, label, time_partition 
            ORDER BY starttime DESC, endtime DESC) 
        AS lastrow
    FROM t2
  )
SELECT 
label, starttime, endtime, time_partition
, rate, rateuom
, statusdescription, lastrow
FROM t3
ORDER BY starttime, endtime;

1

, MIN(CASE WHEN lastrow = 1 THEN statusdescription ELSE null END) AS statusdescription

 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

WITH t1 AS
  (
    SELECT icustay_id
    , CASE WHEN
        itemid = 227525
      THEN 'Calcium' ELSE 'KCl' END AS label
    , starttime, endtime
    , CASE WHEN
        LAG(endtime) OVER
          (PARTITION BY icustay_id, itemid 
	          ORDER BY starttime, endtime) = starttime
      THEN 0
      ELSE 1 END AS new_event_flag
    , rate, rateuom, statusdescription
    FROM inputevents_mv
    WHERE itemid IN
      (
      227525,-- Calcium Gluconate (CRRT)
      227536 -- KCl (CRRT)
      )
    AND statusdescription != 'Rewritten'
    AND icustay_id = '246866'
  )
  , t2 AS
  (
    SELECT
    icustay_id, label
    , starttime, endtime
    , SUM(new_event_flag) OVER
        (PARTITION BY icustay_id, label 
	        ORDER BY starttime, endtime)
	    AS time_partition
    , rate, rateuom, statusdescription
    FROM t1
  )
  , t3 AS
  (
    SELECT
    icustay_id, label
    , starttime, endtime, time_partition
    , rate, rateuom, statusdescription
    , ROW_NUMBER() OVER
        (PARTITION BY icustay_id, label, time_partition
          ORDER BY starttime DESC, endtime DESC)
	    AS lastrow
    FROM t2
  )
SELECT
label
--, time_partition
, MIN(starttime) AS starttime
, MAX(endtime) AS endtime
, MIN(rate) AS rate_min
, MAX(rate) AS rate_max
, MIN(rateuom) AS rateuom
, MIN(CASE WHEN
        lastrow = 1 THEN statusdescription
      ELSE null END)
  AS statusdescription
FROM t3
GROUP BY icustay_id, label, time_partition
ORDER BY starttime, endtime;

 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

WITH t1 AS
  (
    SELECT icustay_id
    , CASE WHEN
        itemid = 227525 THEN 'Calcium'
      ELSE 'KCl' END AS label
    , starttime, endtime
    , CASE WHEN LAS(endtime) OVER
        (PARTITION BY icustay_id, itemid
	        ORDER BY starttime, endtime) = starttime
      THEN 0
	  ELSE 1 END AS new_event_flag
    , rate, rateuom
    , statusdescription
    FROM inputevents_mv
    WHERE itemid IN
      (
      227525,-- Calcium Gluconate (CRRT)
      227536 -- KCl (CRRT)
      )
    AND statusdescription != 'Rewritten'
  )
  , t2 as
  (
    SELECT
    icustay_id, label
    , starttime, endtime
    , SUM(new_event_flag) OVER
        (PARTITION BY icustay_id, label ORDER BY starttime, endtime)
        AS time_partition
    , rate, rateuom, statusdescription
    FROM t1
  )
  , t3 as
  (
    SELECT
    icustay_id, label
    , starttime, endtime
    , time_partition
    , rate, rateuom, statusdescription
    , ROW_NUMBER() OVER
        (PARTITION BY icustay_id, label, time_partition
          ORDER BY starttime DESC, endtime DESC)
      AS lastrow
    FROM t2
  )
SELECT
icustay_id
, time_partition AS num
, MIN(starttime) AS starttime
, max(endtime) AS endtime
, label
--, MIN(rate) AS rate_min
--, max(rate) AS rate_max
--, MIN(rateuom) AS rateuom
--, MIN(CASE WHEN 
			lastrow = 1 THEN statusdescription
		ELSE null END)
	AS statusdescription
FROM t3
GROUP BY icustay_id, label, time_partition
ORDER BY starttime, endtime;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

WITH t1 AS
  (
    SELECT
      icustay_id, di.label
      , mv.linkorderid, mv.orderid
      , starttime, endtime
      , rate, rateuom
      , amount, amountuom
    FROM inputevents_mv mv
    INNER JOIN d_items di ON
      mv.itemid = di.itemid
    AND statusdescription != 'Rewritten'
    AND icustay_id = '246866'
    AND mv.itemid = 222168
  )
SELECT 
  label
  , linkorderid, orderid
  , starttime, endtime
  , rate, rateuom
  , amount, amountuom
FROM t1
ORDER BY starttime, endtime;

 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

WITH t1 AS
  (
    SELECT icustay_id
      , di.itemid, di.label
      , mv.linkorderid, mv.orderid
      , starttime, endtime
      , amount, amountuom
      , rate, rateuom
    FROM inputevents_mv mv
    INNER JOIN d_items di ON
      mv.itemid = di.itemid
    AND statusdescription != 'Rewritten'
    AND icustay_id = '246866'
    AND mv.itemid = 222168
  )
    SELECT icustay_id
      , label, linkorderid
      , MIN(starttime) AS starttime
      , max(endtime) AS endtime
      , MIN(rate) AS rate_min
      , MAX(rate) AS rate_max
      , MAX(rateuom) AS rateuom
      , MIN(amount) AS amount_min
      , MAX(amount) AS amount_max
      , MAX(amountuom) AS amountuom
    FROM t1
    GROUP BY icustay_id, itemid, label, linkorderid
    ORDER BY starttime, endtime;