BigQuery GROUP_CONCAT and ORDER BY

I am currently using BigQuery and GROUP_CONCAT which works perfectly fine. However, when I try to add a ORDER BY clause to the GROUP_CONCAT statement like I would do in SQL, I receive an error.

So e.g., something like

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

The same happens if I try to specify the separator.

Any ideas on how to approach this?

Answer

Since BigQuery doesn’t support ORDER BY clause inside GROUP_CONCAT function, this functionality can be achieved by use of analytic window functions. And in BigQuery separator for GROUP_CONCAT is simply a second parameter for the function. Below example illustrates this:

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

Will produce the following:

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

NOTE on Window specification: The query uses “rows between unbounded preceding and unbounded following” window specification, to make sure that all rows within a partition participate in GROUP_CONCAT aggregation. Per SQL Standard default window specification is “rows between unbounded preceding and current row” which is good for things like running sum, but won’t work correctly in this problem.

Performance note: Even though it looks wasteful to recompute aggregation function multiple times, the BigQuery optimizer does recognize that since window is not changing result will be the same, so it only computes aggregation once per partition.