sql - 如何获取最新日期的行？

Question

我在 BigQuery 中有这张表

country, date, confirmed    
Afghanistan, 2020-03-16, 21
Afghanistan, 2020-03-17, 22
Afghanistan, 2020-03-18, 22
Albania, 2020-03-16, 23
Albania, 2020-03-17, 33
Albania, 2020-03-18, 38
...

我想显示每个国家/地区的所有行以及最新日期，但是这个命令不起作用 >

SELECT * FROM table where date=max(date) > 聚合函数 MAX 不允许出现在 WHERE 子句中。

输出应该是:

Afghanistan, 2020-03-18, 22
Albania, 2020-03-18, 38

有什么想法吗？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY day DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY country   

如果应用到您问题中的示例数据，如下例所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Afghanistan' country, DATE '2020-03-16' day, 21 confirmed UNION ALL
  SELECT 'Afghanistan', '2020-03-17', 22 UNION ALL
  SELECT 'Afghanistan', '2020-03-18', 22 UNION ALL
  SELECT 'Albania', '2020-03-16', 23 UNION ALL
  SELECT 'Albania', '2020-03-17', 33 UNION ALL
  SELECT 'Albania', '2020-03-18', 38 
)
SELECT AS VALUE ARRAY_AGG(t ORDER BY day DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY country

输出是

Row country         day         confirmed    
1   Afghanistan     2020-03-18  22   
2   Albania         2020-03-18  38