旧版SQL的GROUP_CONCAT_UNQUOTED的Bigquery标准Sql等效项 [英] Bigquery Standard Sql equivalent for GROUP_CONCAT_UNQUOTED from Legacy Sql

问题描述

我目前正在使用Bigquery通过"GROUP_CONCAT_UNQUOTED"动态生成查询.用于将多个字符串连接为一个的函数.

I am currently using Bigquery for dynamic generation of query using "GROUP_CONCAT_UNQUOTED" function for concatenation of multiple strings to one.

示例:

SELECT 'SELECT ID, AID, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(KEY = "' + KEY + '", VALUE, NULL)) as [' + KEY + ']'
   ) 
   + ' FROM [project:dataset.tbl] GROUP BY 1,2 ORDER BY 1,2'
FROM (
  SELECT KEY 
  FROM [project:dataset.tbl]
  GROUP BY KEY
  ORDER BY KEY
) 

以上查询会产生另一个查询，如下所示:

Above query produces yet another query like below:

SELECT 
    ID, 
    AID, 
    MAX(IF(KEY = "key1", VALUE, NULL)) as [key1],
    MAX(IF(KEY = "key2", VALUE, NULL)) as [key2],
    MAX(IF(KEY = "key3", VALUE, NULL)) as [key3] 
FROM [project:dataset.tbl]
GROUP BY 1,2 
ORDER BY 1,2

我想使用标准SQL而不是传统SQL进行同样的操作.

I would like to do the same using Standard SQL instead of Legacy SQL.

Google文档提到了 STRING_AGG()，但我没有得到想要的输出

Google docs mentions STRING_AGG() as an alternative but I am not getting the desired output

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT '''
SELECT ID, AID, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(key = '" || key || "', value, NULL)) AS " || key)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1,2
ORDER BY 1,2
'''

您还可以使用立即执行一口气就能执行刚刚构建的查询

Also yo can use EXECUTE IMMEDIATE to actually execute just built query in one shot

EXECUTE IMMEDIATE '''
SELECT ID, AID, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(key = '" || key || "', value, NULL)) AS " || key)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1,2
ORDER BY 1,2
'''

这篇关于旧版SQL的GROUP_CONCAT_UNQUOTED的Bigquery标准Sql等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！