优雅高效的方法来编写bigquery中的最小值 [英] Elegant and efficient way to write minimum in bigquery

问题描述

我有一个如下所示的表结构

I have a table structure like as shown below

我想做的是找到每组物品的最小值.我必须不断寻找不同组itemid的最小值.尽管我的代码有效，但是请确保这不是一种优雅而有效的方法.

What I am trying to do is find the minimum for each group of items. I have to keep finding minimums for different group of itemids. Though my code works, am sure this isn't the elegant and efficient way to do it.

查找最小WHERE itemid IN(1,2)

select subject_id,icu_id,value as min_val_1 FROM
(SELECT c.subject_id,c.time_1,d.min_time,d.max_time,c.value,c.icu_id,
row_number() OVER (PARTITION BY c.subject_id ORDER BY c.value,c.time_1) AS rank
from table_1 d
left join table_2 c 
on c.subject_id = d.subject_id and (c.icu_id = d.icu_id_1 or c.icu_id = d.icu_id_2)
where c. itemid in 
(1,2)) SBP
where rank = 1
order by subject_id,charttime

在(3,4)中的itemid处查找最小值

select subject_id,icu_id,value as min_val_2 FROM
(SELECT c.subject_id,c.time_1,d.min_time,d.max_time,c.value,c.icu_id,
row_number() OVER (PARTITION BY c.subject_id ORDER BY c.value,c.time_1) AS rank
from table_1 d
left join table_2 c 
on c.subject_id = d.subject_id and (c.icu_id = d.icu_id_1 or c.icu_id = d.icu_id_2)
where c. itemid in 
(3,4)) SBP
where rank = 1
order by subject_id

如您所见，一切都一样.唯一的区别是itemid.是否有任何优雅的方式来合并/合并这两个?你能帮我吗?

As you can see everything is same. Only difference is the itemid. Is there any elegant way to join/merge these two? can you help me?

我希望我的输出像这样吗?

I expect my output to be like this?

推荐答案

以下内容适用于BigQuery Standard SQL，并且保留了完整的原始查询，而只是添加了缺少的空白(添加了注释，以便您可以看到一些更改/添加)

Below is for BigQuery Standard SQL and leaves your original query fully intact while just adding missing peaces (added comments so you can see those few changes/additions)

#standardSQL 
SELECT 
  subject_id,
  icu_id,
  MAX(IF(grp = 1, value, NULL)) AS min_val_1,   -- changed
  MAX(IF(grp = 2, value, NULL)) AS min_val_2    -- changed
FROM (
  SELECT 
    c.subject_id,
    c.time_1,
    d.min_time,
    d.max_time,
    c.value,c.icu_id,
    -- in below row - added element to PARTITION BY
    ROW_NUMBER() OVER (PARTITION BY c.subject_id, CASE WHEN c.itemid IN (1, 2) THEN 1 WHEN c.itemid IN (3, 4) THEN 2 END ORDER BY c.value, c.time_1) AS RANK,
    CASE WHEN c.itemid IN (1, 2) THEN 1 WHEN c.itemid IN (3, 4) THEN 2 END grp  -- added
  FROM table_1 d
  LEFT JOIN table_2 c 
  ON c.subject_id = d.subject_id AND (c.icu_id = d.icu_id_1 OR c.icu_id = d.icu_id_2)
  WHERE c.itemid IN (1, 2, 3, 4)  -- changed
) SBP
WHERE RANK = 1
GROUP BY subject_id, icu_id  -- added
ORDER BY subject_id

如果要应用于您的问题的样本数据-结果为

If to apply to sample data from your question - result is

Row subject_id  icu_id  min_val_1   min_val_2    
1   124         A1      10          19   
2   199         B2      21          21  

这篇关于优雅高效的方法来编写bigquery中的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！