array_agg_探索强大SQL模式：ARRAY_AGG，STRUCT和UNNEST

array_agg

by Lak Lakshmanan

由Lak Lakshmanan

探索强大SQL模式：ARRAY_AGG，STRUCT和UNNEST (Exploring a powerful SQL pattern: ARRAY_AGG, STRUCT and UNNEST)

It can be extremely cost-effective (both in terms of storage and in terms of query time) to use nested fields rather than flatten out all your data. Nested, repeated fields are very powerful, but the SQL required to query them looks a bit unfamiliar. So, it’s worth spending a little time with STRUCT, UNNEST and ARRAY_AGG. Using these three in combination also makes some kinds of queries much, much easier to write.

使用嵌套字段而不是拼合所有数据可能会极具成本效益(在存储和查询时间方面)。 嵌套的重复字段非常强大，但是查询它们所需SQL看起来有点陌生。 因此，值得在STRUCT，UNNEST和ARRAY_AGG上花一些时间。 结合使用这三个选项还可以使某些查询变得非常容易编写。

任务 (Task)

Let’s take a BigQuery table of tropical cyclones. Here’s a preview of the table:

让我们来一个热带气旋的BigQuery表 。 这是表格的预览：

The task is to find the maximum usa_sshs (better known as “category”) reached by each North American hurricane (basin=NA) of the 2010 season and the time at which the category was first reached. I want to be able to say something like “Hurricane Danielle re