BigQuery GROUP_CONCAT和ORDER BY

Question

我目前正在使用BigQuery和GROUP_CONCAT，它们工作得很好。但是，当我尝试像在SQL中那样将ORDER
BY子句添加到GROUP_CONCAT语句时，会收到错误消息。

因此，例如

SELECT a, GROUP_CONCAT(b ORDER BY c) FROM test GROUP BY a

如果我尝试指定分隔符，也会发生相同的情况。

关于如何解决这个问题的任何想法？

Answer 1

由于BigQuery不支持GROUP_CONCAT函数内的ORDER
BY子句，因此可以通过使用分析窗口函数来实现此功能。在BigQuery中，GROUP_CONCAT的分隔符只是该函数的第二个参数。下面的示例说明了这一点：

select key, first(grouped_value) concat_value from (
select 
  key, 
  group_concat(value, ':') over 
    (partition by key
     order by value asc
     rows between unbounded preceding and unbounded following) 
  grouped_value 
from (
select key, value from
(select 1 as key, 'b' as value),
(select 1 as key, 'c' as value),
(select 1 as key, 'a' as value),
(select 2 as key, 'y' as value),
(select 2 as key, 'x' as value))) group by key

将产生以下内容：

Row key concat_value     
1   1   a:b:c    
2   2   x:y

关于窗口规范的注释：查询使用“无界前行和无界后行之间的行”窗口规范，以确保分区内的所有行都参与GROUP_CONCAT聚合。根据SQL标准，默认窗口规范是“在无限制的前一行和当前行之间行”，这对于运行总和等操作很有用，但在此问题中将无法正常工作。

性能说明：尽管多次重新计算聚合函数看起来很浪费，但BigQuery优化器确实认识到由于窗口不变的结果将是相同的，因此每个分区只计算一次聚合。