多层次的数组聚合

埃文·凯丁

考虑以下相当标准的非规范化交易信息模型：

with transactions as(
  select 'T_10000' as trans_id, 'L_1000' as line_item_id, 'P_100' as part_id
  union all 
  select 'T_10000', 'L_1000', 'P_101'
  union all
  select 'T_10000', 'L_1001', 'P_103'
  union all 
  select 'T_10001', 'L_1002', 'P_104'
)

我想进一步对该表进行反规范化以消除所有重复值。BigQuery 中的数组似乎是一个很好的候选者。

下面是接近的，但仍然在第二列中返回重复的值。

select trans_id, array_agg(line_item_id), array_agg(part_id)
from transactions
group by 1

此外，下面的内容也很接近，但现在第一列中包含重复的值。

select trans_id, line_item_id, array_agg(part_id)
from transactions
group by 1, 2

有没有直接的方法来实现这一点？

米哈伊尔·贝里安特

下面是 BigQuery 标准 SQL

#standardSQL 
SELECT trans_id,
  ARRAY_AGG(STRUCT(line_item_id, parts)) items
FROM (
  SELECT trans_id, 
    line_item_id, 
    ARRAY_AGG(part_id) parts
  FROM transactions
  GROUP BY trans_id, line_item_id
)
GROUP BY trans_id

当应用于您问题中的样本数据时 - 结果是