支持BigQuery SQL Issue中的UNION ALL函数

Question

如何使此查询在GCP大查询上运行而不会出错：

'引用其他表的关联子查询不受支持，除非它们可以去关联，例如通过将它们转换为有效的JOIN。'

---

你们知道我该如何解决这场冲突吗？

Select
x.subject_id as PatientId
,sum(x.BPS) as sbp
,sum(x.RR) as rr
,sum(x.sepsis) as sepsis
From (
Select
c.subject_id
,(
Select  (Case
WHEN cast(c2.value as numeric) <= 90 THEN 3
WHEN cast(c2.value as numeric) >= 91 AND cast(c2.value as numeric) <= 100 THEN 2
WHEN cast(c2.value as numeric) >= 101 AND cast(c2.value as numeric) < 110 THEN 1
WHEN cast(c2.value as numeric) >= 111 AND cast(c2.value as numeric) < 219 THEN 0
WHEN cast(c2.value as numeric) >= 220 THEN 3
else 0
End)
From    chartevents c2
Where   c2.subject_id = c.subject_id
and c2.itemid = c.itemid
limit 1
) as BPS
,0 as RR
,0 as sepsis
From
chartevents c
Where
c.itemid = 220050
Group by
c.subject_id
,c.itemid
Union all
Select
c.subject_id
,0 as BPS
,(Select  (Case
WHEN cast(c2.value as numeric) <= 8 THEN 3
WHEN cast(c2.value as numeric) >= 9 AND cast(c2.value as numeric) <= 11 THEN 1
WHEN cast(c2.value as numeric) >= 12 AND cast(c2.value as numeric) <= 20 THEN 0
WHEN cast(c2.value as numeric) >= 21 AND cast(c2.value as numeric) <= 24 THEN 2
WHEN cast(c2.value as numeric) >= 25 THEN 3
else 0
End)
From    chartevents c2
Where   c2.subject_id = c.subject_id
and c2.itemid = c.itemid
limit 1
) as RR
,0 as sepsis
From
chartevents c
Where
c.itemid = 220210
Group by
c.subject_id
,c.itemid
Union all
Select
c.subject_id
,0 as BPS
,0 as RR
,(case when exists (
Select  1
From    diagnoses_icd d
Where   d.subject_id = c.subject_id
and d.icd9_code like '%99591%'
)
then 1
else 0
end) as sepsis
From
chartevents c
Where
c.itemid = 228334
Group by
c.subject_id
,c.itemid
) x
Group by
x.subject_id
limit 5
;

所需输出：

|患者ID|sbp|rr|败血症||1|0|0|
|2|2|3|1|

Answer 1

这可能是因为chartevents c2子查询。尝试将它们转换为ARRAY_AGG构造。

这将产生错误：

SELECT
c.subject_id,
(
SELECT c2.value * 2
FROM chartevents c2
WHERE c2.subject_id = c.subject_id
AND c2.itemid = c.itemid
LIMIT 1
) as BPS
FROM
chartevents c

这将起作用：

SELECT
c.subject_id,
ARRAY_AGG(c2.value * 2 LIMIT 1)[OFFSET(0)] AS BPS
FROM chartevents AS c
JOIN chartevents AS c2
ON c2.subject_id = c.subject_id
AND c2.itemid = c.itemid