BigQuery:将多个字段聚合到数组中 [英] BigQuery: Aggregate multiple fields into array

问题描述

我有一些数据，对于每个 ID，我想将两个或多个字段聚合到一个数组中，并且我希望它们按顺序匹配.

I have some data where for each ID I want to aggregate two or more fields into an array, and I want them to match in terms of order.

例如，如果我有以下数据:

So for example if I have the following data:

我想把它变成这样:

或者，这样的事情也可以:

Alternatively, something like this would also be fine:

所以首先，如果我要使用这样的查询，它会做我想要的还是不保证两个字段以相同的顺序通过(即 Value_1 和 Value_2 中的相应值可能不匹配)?

So firstly, if I were to use a query like this, would it do what I want or does it not guarantee that the two fields pull through in the same order (i.e. that the corresponding values in Value_1 and Value_2 may not match)?

SELECT
  ID,
  ARRAY_AGG (
    Value_1
  ) AS Value_1,
  ARRAY_AGG (
    Value_2
  ) AS Value_2

FROM
  table

GROUP BY
  ID

如果没有，我该怎么做?

If not, how can I go about doing this?

推荐答案

如果要将值配对在一起，请使用 ARRAY_AGG 和 STRUCT.例如，

Use ARRAY_AGG with STRUCT if you want to pair the values together. For example,

SELECT
  ID,
  ARRAY_AGG (
    STRUCT(Value_1, Value_2)
  ) AS Values
FROM
  table
GROUP BY
  ID;

这篇关于BigQuery:将多个字段聚合到数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！