sql - 获取 BigQuery 中每个 ID 的最新行的可扩展解决方案

Question

我有一个很大的 table ，上面有一个字段 ID另一个字段为 collection_time .我想为每个 ID 选择最新的记录。不幸的组合(ID, collection_time)时间在我的数据中并不是唯一的。我只想要最大 collection time 的记录之一.我尝试了两种解决方案，但没有一种对我有用:

第一:使用查询

SELECT *  FROM 
(SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY collection_time) as rn 
FROM mytable)  where rn=1

这导致 Resources exceeded错误，我猜是因为 ORDER BY在查询中。

第二
在表和最新时间​​之间使用连接:

(SELECT tab1.* 
FROM mytable AS tab1
INNER JOIN EACH 
(SELECT ID, MAX(collection_time) AS second_time 
FROM mytable GROUP EACH BY ID) AS tab2
ON tab1.ID=tab2.ID AND tab1.collection_time=tab2.second_time) 

这个解决方案对我不起作用，因为 (ID, collection_time)在一起不是唯一的，所以在 JOIN结果每个 ID 会有多行.

我想知道是否有解决 resourcesExceeded 错误的方法，或者是否有适用于我的情况的不同查询？

Answer 1

快速和肮脏的选项 - 将您的两个查询合并为一个 - 首先使用最新的 collection_time 获取所有记录(使用您的第二个查询)，然后使用您的第一个查询删除它们:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY tab1.ID) AS rn 
  FROM (
    SELECT tab1.* 
    FROM mytable AS tab1
    INNER JOIN (
      SELECT ID, MAX(collection_time) AS second_time 
      FROM mytable GROUP BY ID
    ) AS tab2
    ON tab1.ID=tab2.ID AND tab1.collection_time=tab2.second_time
  )
)
WHERE rn = 1  

并使用标准 SQL(由 S.Mohsen sh 提出)

WITH myTable AS (
  SELECT 1 AS ID, 1 AS collection_time
),
tab1 AS (
  SELECT ID,
  MAX(collection_time) AS second_time 
  FROM myTable GROUP BY ID
),
tab2 AS (
  SELECT * FROM myTable
),
joint AS (
  SELECT tab2.* 
  FROM tab2 INNER JOIN tab1
  ON tab2.ID=tab1.ID AND tab2.collection_time=tab1.second_time 
)
SELECT * EXCEPT(rn) 
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID) AS rn 
  FROM joint
)
WHERE rn=1