每个ID返回5个最近的旅行 [英] Returning 5 Most Recent Trips Per ID

问题描述

我有一张表，上面列出了旅行次数和station_id，我想返回每个ID最近进行的5次旅行(该表的示例图片如下)

I have a table with the number of trips taken and a station_id, and I want to return the 5 most recent trips made per ID (sample image of the table is below)

我在下面进行的查询汇总了车站ID和最近的行程，但是我很难返回5个最近的行程

The query I made below aggregates the station id's and the most recent trip, but I am having a difficult time returning the 5 most recent

SELECT start_station_id, MAX(start_time) 
FROM `bpd.shop.trips` 
group by start_station_id, start_time

提示: https://imgur.com/Ebh9FeZ

任何帮助将不胜感激，谢谢！

Any help would be much appreciated, thanks!

推荐答案

您可以使用 row_number():

SELECT t.*
FROM (SELECT t.*,
              ROW_NUMBER() OVER (PARTITION BY start_station_id ORDER BY start_time DESC) as seqnum
      FROM `bpd.shop.trips` t
     ) t
WHERE seqnum <= 5;

这篇关于每个ID返回5个最近的旅行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！