BigQuery:选择最近一组具有数组类型字段的行

Question

我有一个包含3列的表: String、Datetime、ARRAY()。

Name  |       LastLogin       | FavoriteNumbers
Paul  | "2019-03-03T06:29:35" | (1, 3, 6, 8)
Paul  | "2019-03-03T02:29:35" | (1, 3, 6, 8)
Paul  | "2019-03-01T01:29:35" | (1, 3, 6, 8)
Anna  | "2019-03-03T02:29:35" | (1, 2, 3, 4)
Anna  | "2019-03-03T01:29:35" | (1, 2, 3, 4)
Maya  | "2019-03-02T10:29:35" | (9, 11, 13, 8)

这就是我想要的结果：

Paul  | "2019-03-03T06:29:35" | (1, 3, 6, 8)
Anna  | "2019-03-03T02:29:35" | (1, 2, 3, 4)
Maya  | "2019-03-02T10:29:35" | (9, 11, 13, 8)

我尝试使用GROUP BY和ARRAY_AGG来获取每个名称的最新时间戳，但由于GROUP BY不能在数组类型字段上使用，所以无法工作。

我怎样才能得到我想要的结果？使用标准SQL。

Answer 1

使用ARRAY_AGG聚合到一个结构中，然后提取字段：

SELECT
  Name,
  ARRAY_AGG(
    STRUCT(LastLogin, FavoriteNumbers)
    ORDER BY LastLogin DESC LIMIT 1
  )[OFFSET(0)].*
FROM dataset.table
GROUP BY Name