BigQuery:使用ARRAY类型的栏位选取最新的资料列群组 [英] BigQuery: Select most recent of group of rows with ARRAY type field

问题描述

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

I have a table with 3 columns: 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)

这是我想要的结果:

This is the result I want:

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 可以不能在ARRAY类型字段上使用.

I tried to use GROUP BY with ARRAY_AGG to get the latest timestamp for each Name but it doesn't work because GROUP BY can't be used on an ARRAY type field.

如何获得所需的结果?使用标准SQL.

How can I get the result that I want? Using Standard SQL.

推荐答案

使用ARRAY_AGG聚合为一个结构，然后提取字段:

Aggregate into a struct with ARRAY_AGG, then extract the fields:

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

这篇关于BigQuery:使用ARRAY类型的栏位选取最新的资料列群组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！