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

问题描述

我在 Big Query 中有一个表，其中包含唯一 ID、时间戳和距离，我想通过具有最新时间戳的 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.

例如桌子看起来像

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

PostgreSQL 中的工作查询看起来像这样，但 bigquery 中没有distinct ON"?

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

推荐答案

这个怎么样?

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屋！