google-bigquery - 我想要表中的最新日期和价格，该表在大查询中具有多个不同日期的价格。下面是我的查询

Question

我面临的问题是我不想按价格列分组。我只想按 id 分组，这样我就可以获得最新的日期和价格。但是大查询不允许我在不按价格分组的情况下运行此查询。

这是我的查询:

选择编号，最大(创建日期)作为最新日期，价格FROM 表名按编号分组

Answer 1

我有一个解决办法给你。它不是最干净的，但可以工作。

SELECT id, "created date" as "latest date", price 
FROM (SELECT id, 
FIRST_VALUE("created date") OVER (PARTITION BY id ORDER BY "created date" DESC) as "created date", 
FIRST_VALUE("price") OVER (PARTITION BY id ORDER BY "created date" DESC) as "price" 
FROM "table name")
GROUP BY id

我觉得这也行

SELECT UNIQUE id, 
FIRST_VALUE("created date") OVER (PARTITION BY id ORDER BY "created date" DESC) as "created date", 
FIRST_VALUE("price") OVER (PARTITION BY id ORDER BY "created date" DESC) as "price" 
FROM "table name"