Google BigQuery,使用"unnest"功能时我丢失了空行 [英] Google BigQuery, I lost null row when using 'unnest' function
本文介绍了Google BigQuery,使用"unnest"功能时我丢失了空行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
#StandardSQL
WITH tableA AS (
SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
UNION ALL
SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)
SELECT * FROM tableA, UNNEST(L_id) AS unnest
执行此代码时,我希望得到如下结果.
When I executed this code, I expected the result such as that below.
RowNumber T-id L-id unnest
1 T001,T002,T003 1,5 1
2 T001,T002,T003 1,5 5
3 T004,T005 NULL NULL
但是我却得到了这个结果:
But I get this result instead:
RowNumber T-id L-id unnest
1 T001,T002,T003 1,5 1
2 T001,T002,T003 1,5 5
我输了第三排. 然后,我看到了Google的官方文档,其中指出以下内容:
I lost the third row. Then, I saw the official Google documentation, which states the following:
UNNEST treats NULL as follows.
・NULL and empty ARRAY generate zero rows.
・An ARRAY containing NULL generates a row containing a NULL value.
但是我不想丢失我的空行.
But I don't want to lose my null row.
如何保留空行?
请告诉我解决方案...
Please tell me the solution...
推荐答案
使用LEFT JOIN代替CROSS JOIN.这将为空数组返回一行包含null的行.您可能也对使用数组主题来自文档.
Instead of CROSS JOIN, use LEFT JOIN. This will return a row with nulls for an empty array. You may also be interested in the working with arrays topic from the documentation.
#StandardSQL
WITH tableA AS (
SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
UNION ALL
SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)
SELECT * FROM tableA
LEFT JOIN UNNEST(L_id) AS value;
这篇关于Google BigQuery,使用"unnest"功能时我丢失了空行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文