视图安装好之后,我们利用blood_differential、chemistry、enzyme等视图,配合常用的查询语句,便可以提取患者在每次入院时,整个住院过程中的实验室或者生命体征指标。但是如果我们所要提取的指标不在视图中,我们应该如何提取呢?
实验室测量指标都存在于mimic_hosp.labevents表中,表中需要注意的是specimen_id字段。specimen_id是每次收取的血液或者尿液样本等的唯一标识。(ps: 为了减少误差,对于同一个样本往往会测量多次,官方视图中是以多次测量中的最大值作为该样本的指标测量值。)
所以我们应该以specimen_id进行分组,再取每个分组中valuenum的最大值,因此,我们可以看到实验室指标的视图中,会出现大量的group by(分组)和max(取最大值)等函数。
下面我们用一个案例来进行说明:
如何提取患者血液样本中的lymphocytes
1. 首先通过lymphocytes来查询对应的itemid
这时我们要用到入门语句中的字符串匹配
select * from d_labitems where label ~* 'lymphocytes'
查询结果如下,我们根据需要,选择血液样本中的lymphocytes,对应的itemid为51244和51245
2. 首先我们根据每个患者(subject_id),在每次入院(hadm_id)时同一时间收集的样本(charttime,specimen_id)进行分组,然后取每个分组中多次测量的最大值。
用SQL表示上述意思就是:使用group by,对subject_id,hadm_id,charttime,specimen_id进行分组,用max取每组中的最大值。
select subject_id,hadm_id,charttime,specimen_id,
max(valuenum) valuenum from
mimic_hosp.labevents
where itemid in (51244,51245)
group by subject_id,hadm_id,charttime,specimen_id
注意:51244,51245的单位是相同的,因此我们可以直接max(valuenum)取最大值,如果需要提取的指标对应的多个itemid单位不同时,提取方法见后文。所以建议大家提取前先确认itemid对应的单位是否一致,是否需要换算!
这里可以看到小编在group by后写了多个字段,这是由于SQL语法规定:select中除聚合函数(max、avg、min等)中的字段,都必须在group by中出现。
换句话说就是如果我们要select subject_id,hadm_id,charttime和specimen_id的话,就需要group by subject_id,hadm_id,charttime和specimen_id
用如下简单的示意图来说明,当我们group by subject_id,hadm_id,charttime,specimen_id时,只有这四个字段都相同的才会聚合,若specimen_id不相同时,就会被分到另外一组
上述SQL查询结果如下,我们可以看到查出了相同specimen_id下的最大值,也就是患者每次测量的lymphocytes值
3. 当所提取指标对应的多个itemid的单位不同时,我们应该怎么提取?
比如Absolute Lymphocyte Count对应的itemid为51133和52769(用字符串匹配查询)
我们可以通过以下代码查看itemid所对应的单位
-- 只需要修改itemid即可
select itemid, valueuom from labevents where itemid in (51133,52769) and valueuom is not null GROUP BY itemid, valueuom;
可以看到itemid为52769时,对应的单位是个/ul,换算成K/ul时需要除以1000
然后我们使用case when语句,当itemid为52769时值除以1000,当itemid为51133时用原值
-- 对照上文中提取lymphocytes的SQL
-- 我们只是在max里加了一个case when语句
select subject_id,hadm_id,charttime,specimen_id,
max(
CASE
WHEN (itemid = 51133) THEN valuenum
WHEN (itemid = 52769) THEN (valuenum / (1000.0))
END) AS absolute_lymphocytes_count from
mimic_hosp.labevents
where itemid in (51133,52769)
group by subject_id,hadm_id,charttime,specimen_id
查询结果如下
更多关于临床公共数据库挖掘相关知识可同时关注以下公众号