google-bigquery - BigQuery - 如何根据数组元素对查询结果进行排序

Question

我运行以下查询：

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2)
     group by 1)

select * from x

我想首先按 agent_id 排序结果，然后按 price_range 级别排序。
问题是，如果我首先 unnest() 数组，我会得到交叉连接结果。

标签： google-bigquery

Answer 1

如果我在构造数组之前对子查询结果进行排序，那么我会得到我想要的。

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2
     order by 1,2) -- this additional line solved the issue
     group by 1)

select * from x