google-bigquery - 在 bigquery 中聚合最大日期

Question

我在 BQ 中有以下三行数据：

date        instance_id   value
2020-01-01  2            'hello'
2020-01-04  2             NULL
2020-02-03  3            'new'

我将如何分组以按日期获取“最新”值？我尝试对以下内容进行连接：

SELECT * from `historical_prices` m1 join `historical_prices` m2 
ON (m1.instance_id=m2.instance_id and m1.date=max(m2.date))
WHERE date > '2020-01-01'

但出现以下错误：

[7:100] 处的 JOIN ON 子句中不允许使用聚合函数 MAX

获得上述内容的正确模式是什么？

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY date DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.historical_prices` t
WHERE date > '2020-01-01'
GROUP BY instance_id