google-bigquery - 在 Google BigQuery 中按索引添加向量

Question

我有一个这样的数据集：

id | house_id | room_data
-------------------------------------------
1  | abc      | [1, 1, 1, 1, 1, 1, 1]
2  | abc      | [1, 1, 0, 0, 0, 0, 0]
3  | abc      | [0, 1, 2, 1, 0, 1, 0]
4  | abc      | [1, 1, 1, 0, 1, 1, 1]
5  | def      | [1, 1, 1, 1, 0, 0, 0]
6  | def      | NULL
7  | def      | [1, 1, 1, 1, 0, 0, 0, 2, 1]
8  | def      | [1, 0, 0, 0, 1, 1, 1, 1, 0]

我想选择与给定关联的向量house_id并创建一个新的聚合向量，该向量将每个对应的向量通过其相加index

house_id带有of的查询的示例结果abc理想情况下如下所示：

house_id | aggregate_room_data
--------------------------------
abc      | [3, 4, 4, 2, 2, 3, 2]

house_id以及带有of的查询的示例结果def：

house_id | aggregate_room_data
--------------------------------------
def      | [3, 2, 2, 2, 1, 1, 1, 3, 1]

我相信我需要一些,和也许的组合ARRAY_AGG，但我是一个傻瓜，只是无法正确使用语法，即使我认为我在这里圈出了答案。CROSS JOIN UNNESTSUMWITH OFFSET

提前感谢您的帮助

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select house_id, 
  array_agg(data order by offset) as aggregate_room_data
from (
  select house_id, offset, sum(data) data
  from `project.dataset.table`, 
  unnest(room_data) data with offset 
  where not room_data is null
  group by house_id, offset
)
group by house_id

如果适用于您的问题的样本数据 - 输出是