获取 BigQuery 中最新行的属性? [英] Get the attributes of the most recent row in BigQuery?

问题描述

我在 BigQuery 工作.我有一个表 t1，其中包含地址、邮政编码、价格和日期字段.我想按地址和邮政编码对其进行分组，找到每个地址的最新行的价格.

I'm working in BigQuery. I have a table t1 which has address, postcode, price and date fields. I want to group this by address and postcode, an find the price of the most recent row for each address.

如何在 BigQuery 中执行此操作?我知道如何获取地址、邮政编码和最近的日期:

How can I do this in BigQuery? I know how to get the address, postcode and most recent date:

SELECT
  ADDRESS, POSTCODE, MAX(DATE)
FROM
  [mytable]
GROUP BY
  ADDRESS,
  POSTCODE

但我不知道如何获得与这些字段匹配的这些行的价格.这是我最好的猜测，它确实会产生结果 - 这是否正确?

But I don't know how to get the price of these rows matching these fields. This is my best guess, which does produce results - will this be correct?

SELECT 
  t1.address, t1.postcode, t1.date, t2.price
FROM [mytable] t2
JOIN 
(SELECT
  ADDRESS, POSTCODE, MAX(DATE) AS date
FROM
  [mytable]
GROUP BY
  ADDRESS,
  POSTCODE) t1
ON t1.address=t2.address 
   AND t1.postcode=t2.postcode 
   AND t1.date=t2.date

在我看来它应该可以工作，但是一些 类似的问题有更复杂的解决方案.

This seems to me like it should work, but some of the similar questions have solutions that are much more complex.

推荐答案

只需使用row_number():

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY ADDRESS, POSTCODE
                                ORDER BY DATE DESC
                               ) as seqnum
      FROM [mytable] t
     ) t
WHERE seqnum = 1;

这不是聚合查询.您想过滤行以获取最新值.

This is not an aggregation query. You want to filter the rows to get the most recent value.

这篇关于获取 BigQuery 中最新行的属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！