Open JackieMium opened 6 years ago
又忘了贴代码......
(贴代码的时候终于耐心地一个个把套壳 query
去掉了,上一篇等心情好再说)
# Step 4: -----------------------------------------------------------------
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;
# step 4.1 ----------------------------------------------------------------
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;
# Step 4.2: create a binary flag for new events ---------------------------
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;
# Step 4.3: create an integer to mark the last row of an event ------------
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;
# Step 4.4: aggregate to merge together contiguous start/end times --------
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;
# remove icustay_id = '246866' to query for all patients
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;
# conclusion --------------------------------------------------------------
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;
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;
书接上回。 上回说到,我们首先自己搜索
d_items
表格找到对应的item_id
,然后运用专业知识对这些item_id
进行筛选和归类,最后得到真正能够定义 CRRT 时间的那些item_id
。下面,我们就要通过最后得到的这些
item_id
来制定规则,看看到底 CRRT 的时间是如何对应到这些数据上的。(上一篇放到 GitHub 上之后发现由于代码基本上都是 SQL,只是通过懒人函数
query()
套壳到 R 了,所以粘贴时按照 R 来高亮。导致高亮这么重要的功能基本上算是废了。所以这一篇决定还是直接贴 SQL 代码好了,要用的时候要么放到 psql 终端要么再套query()
的壳就行了。这样有个问题就是和上一篇其实代码上不统一,加之这一篇我也是边写边改,写到后面才决定整体干脆用 SQL 代码又回来改掉的,所以代码风格可能也不一致,写在这里算了,有空再改。反正长长的 Todo list 也不差再多一个了...)To do:
Step 4: definition of concept using rules
我们再回头想想这个笔记本最开始的目的。我们是想得到每个病人 CRRT 的时间段,就是说对于每个
icustay_id
我们都要得到:starttime
endtime
因为在一个病人住院期间,CRRT 有可能会有中断,因此对于一个
icustay_id
来说可能不止有一个starttime
和endtime
,而且多个时段之间应该都不重叠。回想一下,CHARTEENTS 就是存储各种事件的时刻(
charttime
)用的,而且所有东西都会记录为一个单独的时间点(即哪天几点几分做了什么)。因此对于 CHARTEVENTS 来说,我们现在的主要任务就是把一系列的charttime
时间点转成一对一对的starttime
和endtime
(即从哪天几点几分到哪天几点几分这样的时间段)。乍一看这个直接连续一个一个小地查看参数记录然后把它们组合起来就行了。第一个出现的charttime
就作为starttime
,最后一个就是endtime
。但是,事实上这些数据不仅仅存储在 CHARTEVENTS 里, INPUTEVENTS_MV 和 PROCEDUREEVENTS_MV 也有。所以为了提高准确性,在 CHARTEVENTS 的基础上,我们必须得把这两个表的数据也考虑进来。对于 INPUTEVENTS_MV 这个还不是很复杂,因为 INPUTEVENTS 里对每个观测记录也有一个charttime
,所有我们只需要处理之前把这张表格和 CHARTEVENTS 组合起来就行了(可能用 SQL 的UNION
语句吧)。但是 PROCEDUREEVENTS_MV 就略微复杂点了,因为这个表格本来就有
starttime
和endtime
这两列。所以我们怎么做呢,首先把 CHARTEVENTS 和 INPUTEVENTS_MV 的数据提取合并完,之后再和 PROCEDUREEVENTS_MV 合并。任务明确了我们就可以开始了。我们需要做这些:
这个记事本本来作为示例,为了代码运行效率我们把查询限制为一个
icustay_id
,我们这里使用icustay_id = 246866
(从前面可以看到这其实就是第一个icustay_id
)。所以每次代码WHERE
里最后都要记得加上AND icustay_id = 246866
。 原文这里还定义了函数用来在输出里去掉icustay_id
和年月。Aggregating INPUTEVENTS_MV
我们先来看 INPUTEVENTS_MV。这个表的每条记录都有一个
starttime
和endtime
。注意我们查询时要加上一个statusdescription = 'Rewritten'
,因为这些都是没有没有实际执行而被重写的医嘱(用作审计用途,但是并没有说明用的药物是什么)。得到:
(事实我没有改代码把年月去掉,我得到的结果和这个格式有一点点差别,我懒得再去改了直接用了原文。但我得到的结果和这个内容上没有差别)
正常情况下
linkorderid
会把同属于一个医嘱的项目但是可能输液速度发生改动的多行相关联,但是效果不是很好。8-10 行和 16-18 行连到一起了(即它们应该是相连续的,只是输液速度可能变了或者没变),但是我们发现很多应该是同一个的也没有连起来。我们想要的就是连续性事件都合并起来简化得到时间段,看来我们得检查数据然后把一行的starttime
与上一行的endtime
这样的行合并起来。大概步骤是:1
,下一行在时间上与上一行直接连续则标记为0
PARTITION
)starttime
和endtime
我们一步一步来看代码怎么写。
Step 4.1: create a binary flag for new events
先看这段代码(这是不完整示例,不可运行):
这段代码就可以把一个病人的 KCl 使用情况从 INPUTEVENTS_MV 中提取出来(因为仅仅是示例所以才加了针对一个病人的限制条件,把这个限制条件去掉就能查询所有人了)。
关键的代码是:
这段代码的作用是生成一个布尔值,在当前行的
starttime
和上一行的endtime
不等时为1
。即,这个布尔值 flag 标记了新的“event”。看看实际运行的效果:上面的例子里为了清楚地展示这一查询的工作原理,我们特异添加了
endtime_lag
这一列。可以看到第一行的endtime_lag
是null
,所以new\_event_flag = 1
。而下一行endtime_lag != starttime
,所以new\_event_flag
又是1
。再然后,第 2 行(最左边标记为2
,下同)的endtime_lag == starttime
,所以new\_event_flag = 0
(时间上连续,所以不是新事件,记0
)。这一连续事件一直持续到第 9 行再一次出现endtime_lag != starttime
(即一个新的事件,0
变为1
)。可以看到第 8 行甚至告诉我们原因:因为用药”Paused“(暂停)了。这就是我们上面提到的,一个事件的最后一行可能回提供有用的信息的意思。Step 4.2: create a binary flag for new events
在 SQL 里,要把行通过分组聚合起来要用 partition。要用 partition,那我们得借助这些分组的某种唯一键值(一般是整数)。一旦有了这个键值就能用 SQL 标准的聚合操作,比如
MAX()
、MIN()
等等这些(不指定特定列的情况下,SQL 的窗口函数运行的原则与这些函数相同)。这样来说,我们下一步就是要用上面得到的
new_event_flag
在我们想要合并的行分组上再得到一个整数键值了。因为我们想要的是把新事件合并起来,那可以通过在new_event_flag
上累加,当有新的事件时(new_event_flag = 1
)这个值就会加 1,这样同属一个事件的行的这个和会一样,知道下一事件这个值就会再加 1。这样就很巧妙地为每一个事件分配了一个唯一的整数键值了。代码大概是:看看实际效果:
得到:
上面的例子(希望是)清楚地展示了如何通过对 KCl 用药情况上用窗函数 partition 对
new_event_flag
的累加得到一个新的列time_partition
。Step 4.3: create an integer to mark the last row of an event
从前面我们知道,每个事件的最后一个
statusdescription
可能会提供关于事件为何停止的有用信息,所以我们想到应该为每个事件的最后一行加上一个 flag,及statusdescription
为本事件最后一个时标记为1
。得到:
Step 4.4: aggregate to merge together contiguous start/end times
现在我们在
time_partition
(一个time_partition
对应一个事件)的基础上对starttime
和endtime
进行聚合(即在每个事件的基础上聚合):starttime
,因此用MIN(starttime)
endtime
,因此用MAX(endtime)
statusdescription
,所以我们在仅有最后一行不是null
的列上聚合最后一步不是很直观,我们来看代码:
聚合函数会忽略
null
值,所以我们新建的这一列仅在lastrow = 1
是不为null
,因此保证了聚合函数最终只会返回lastrow = 1
的行。而这个聚合函数其实用MIN()
和MAX()
都可以,因为这个聚合操作最终只会在一个值上起作用(因为每个事件里lastrow = 1
只有一个)。综合一下,我们最终的查询长这样:
得到:
结果看起来没什么问题。所以现在就可以去掉
icustay_id = '246866'
这个限制条件查询所有病人数据了(注意这时候是用 R 了,作用是把这个查询所有 INPUTEVENTS_MV 病人的完整的查询语句暂时记下来,后面要用到所有病人数据的时候直接套壳query()
就能迅速拿到数据而不用再把长长的查询语句再复制粘贴一遍了):Conclusion
现在我们对于合并 INPUTEVENTS_MV 里的连续事件有了一个很好的方法。但注意,一般情况下没有必要这么做,因为
linkorderid
其实就是为了帮我们把同一时间连接起来的。举个例子,我们看一看 ICU 里一个非常常用的镇静药物丙泊酚:可以得到:
可以看到
linkorderid
也可以很好地把连续的时间组合到了一起,而不需要我们上面辛辛苦苦这么多步骤。它同时也区分了不同的用药,上述第 6 行可以看到有一个 “1 分钟” 的用药。这其实是 MetaVision 系统的表格(结尾带有_mv
的)标记瞬间事件的方法——具体到用药上来说,这是使用了丸剂(相对于静滴来说,口服丸剂是瞬间完成的)。(2018-10-11 更新:这里其实是推注用药,英语用 Bolus 表示一次性推注,用以和静脉滴注相区别)用这个数据我们可以像之前那样在每个事件 partition 进行聚合,但是现在我们根本就不需要创建 partition 了,因为这其实就是
linkorderid
。得到:
丸剂那一行没有
rate
(用药速度),这很正常,丸剂只有剂量没有用药速度。又这么长了,奇怪。再分一篇吧。Peace。