SQL UNNEST需要别名吗？

Question

我正在学习SQL，并试图理解UNNEST。在具有以下模式的FireBase事件表上：

event_params    RECORD  REPEATED    
event_params. key   STRING  NULLABLE    
event_params. value RECORD  NULLABLE    
event_params.value. string_value    STRING  NULLABLE    
event_params.value. int_value   INTEGER NULLABLE    

这样做是可行的：

SELECT params.key, params.value.string_value, params.value.int_value
FROM `...events_20181021`, 
   UNNEST(event_params) as params

当我跑得更远

SELECT *
FROM `...events_20181021`, 
   UNNEST(event_params)

我看到像event_params.key, event_params.value.string_value, event_params.value.int_value这样的列(在BigQuery中)。但当我尝试

SELECT event_params.key, event_params.value.string_value, event_params.value.int_value
FROM `...events_20181021`, 
   UNNEST(event_params)

那就失败了。为什么？

编辑：参见https://stackoverflow.com/a/51563922/1908650以获得一个不带别名的UNNEST示例。

Answer 1

我会尽力解释的

将在CTE下面使用我的示例

WITH `table` AS (
  SELECT 1 id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('a', ('1',1)),('b', ('2',2)),('c', ('3',3))] params UNION ALL
  SELECT 2, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('x', ('666', 666)),('y', ('777',777))]
)

示例1-简单选择*

#standardSQL
SELECT * 
FROM `table`   

这给出了明显的预期产出。

Row id  params.key  params.value.string_value   params.value.int_value   
1   1   a           1                           1    
        b           2                           2    
        c           3                           3    
2   2   x           666                         666  
        y           777                         777  

例2- UNNEST w/o别名

#standardSQL
SELECT * 
FROM `table`, UNNEST(params)   

结果将是

Row id  params.key  params.value.string_value   params.value.int_value  key value.string_value  value.int_value  
1   1   a           1                           1                       a   1                   1    
        b           2                           2                
        c           3                           3                
2   1   a           1                           1                       b   2                   2    
        b           2                           2                
        c           3                           3                
3   1   a           1                           1                       c   3                   3    
        b           2                           2                
        c           3                           3                
4   2   x           666                         666                     x   666                 666  
        y           777                         777              
5   2   x           666                         666                     y   777                 777  
        y           777                         777              

现在您可以看到两个额外的列，名为key和value (值为STRUCT，分别有两个字段，分别为string_value和int_value)，它们位于由UNNEST的STRUCT数组获得的各个STRUCT字段名称之后。

重要:以params开头的列实际上是重复记录的一部分，不能直接访问，而那些由UNNEST‘in产生的字段可以直接引用(除非其中一些字段本身是数组，在这种情况下需要第二个UNNEST)

因此，长话短说--我们可以直接将它们称为key和value --例如

示例3-引用“继承”字段名

 #standardSQL
SELECT id, key, value 
FROM `table`, UNNEST(params)  

Row id  key value.string_value  value.int_value  
1   1   a   1                   1    
2   1   b   2                   2    
3   1   c   3                   3    
4   2   x   666                 666  
5   2   y   777                 777  

示例4-带有别名的UNNEST

显然，您可以为UNNEST提供别名，以避免潜在的歧义--如果有另一个名为“`key”的字段，那么您希望能够解决这个问题。

#standardSQL
SELECT id AS key, param.key AS param_key, value
FROM `table`, UNNEST(params) param     

结果如

Row key param_key   value.string_value  value.int_value  
1   1   a           1                   1    
2   1   b           2                   2    
3   1   c           3                   3    
4   2   x           666                 666  
5   2   y           777                 777  

希望，以上将帮助你与UNNEST :O友好)

您可以在FROM条款的文档中阅读更多关于FROM条款的内容，然后向下滚动直到UNNEST部分。

示例5-在需要别名的情况下

如果需要使用UNNEST简单数组，如下面的CTE所示，引用扁平元素的唯一方法是使用别名

WITH `project.dataset.table` AS (
  SELECT 1 id, [1,2,3] params UNION ALL
  SELECT 2, [666,777]
)

例如

#standardSQL
SELECT id, param
FROM `project.dataset.table`, UNNEST(params) param
WHERE NOT param IN (2,777)

有结果

Row id  param    
1   1   1    
2   1   3    
3   2   666