每个唯一的ID使用最新的时间戳选择一个 [英] Select one per unique ID with newest timestamp

问题描述

我在Big Query中有一个带有唯一ID，时间戳和距离的表格，并且希望通过ID和最新的时间戳选择一条记录。

表格看起来像是：

  ID |时间戳|距离
 A | 100 | 2 
 A | 90 | 3 
 B | 110 | 5 
 D | 100 | 4 
 A | 80 | 2 
 B | 10 | 2 
  pre> 
 
 
查询应该返回如下内容： 
 
 
  A | 100 | 2 
 B | 110 | 5 
 D | 100 | 4 
  
 A PostgreSQL中的工作查询看起来像这样，但在bigquery中没有distinct ON？ 
 
 
  SELECT * FROM（
 SELECT DISTINCT ON（ID）
 id，timestamp，distance 
 FROM ranking 
 ORDER BY ID，timestamp DESC 
）AS latest_dtg 
 ORDER BY距离
  
 
 
解决方案
那么这个呢？
  SELECT a。* 
 FROM yourtable AS a 
 INNER JOIN（
 SELECT id，MAX（timestamp）as newesttimestamp 
 FROM yourtable 
 GROUP BY id 
）AS b 
 ON a.id = b.id AND a.timestamp = b.newesttimestamp 
 ORDER BY a.id 
  
 
I have a table in Big Query with unique IDs , timestamps and distances and would like to select one record by ID with the newest timestamp.


E.g. the table looks like
ID|timestamp|distance
A|100|2
A|90|3
B|110|5
D|100|4
A|80|2
B|10|2
The query should return something like:
A|100|2
B|110|5
D|100|4
A working query in PostgreSQL looks like this, but there is no "distinct ON" in bigquery?
SELECT * FROM (
SELECT DISTINCT ON (ID)
id, timestamp, distance
FROM ranking
ORDER BY ID, timestamp DESC
) AS latest_dtg
ORDER BY distance

解决方案
What about this one?
SELECT a.*
FROM yourtable AS a
INNER JOIN (
SELECT id, MAX(timestamp) AS newesttimestamp
FROM yourtable
GROUP BY id
) AS b 
ON a.id = b.id AND a.timestamp = b.newesttimestamp
ORDER BY a.id


                        
这篇关于每个唯一的ID使用最新的时间戳选择一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！