检索每个组的第一条记录和最后一条记录 [英] Retrieving first and last records of each group

问题描述

两点之间的距离:

我有一组每辆车行驶的gps点.我正在尝试检索每次旅行的第一条记录和最后一条记录.

I have a set of gps points travelled by each vehicle. I am trying to retrieve the first and last records for each trip.

数据:

  VehicleId       TripId          Latitude            Longitude
    121             131             33.645              -84.424
    121             131             33.452              -84.409
    121             131             33.635              -84.424
    121             131             35.717              -85.121
    121             131             35.111              -85.111

在上面的数据集中，我需要将结果集作为每次旅行的起点和终点.

In the above dataset, I need the resultset to be the first and last points of each trip.

  VehicleId       TripId          StartLat            StartLong          EndLat          EndLong
    121             131             33.645              -84.424         35.111          -85.111

我尝试使用以下查询，但收到错误消息除非支持将其他表解除关联，否则不支持引用其他表的相关子查询， 例如将它们转换为有效的JOIN".任何帮助将不胜感激.

I tried with the below query but I get the error "Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN". Any help would be appreciated.

    SELECT
      a.VehicleId,
      a.Tripid,
      a.Latitude AS StartLat,
      a.Longitude AS StartLong,
      b.Latitude AS EndLat,
      b.Longitude AS EndLong,
      a.DateTime
    FROMQ
      `Vehicles` AS a
    JOIN
      `Vehicles` AS b
    ON
      a.VehicleId = b.VehicleId
      AND a.Tripid = b.Tripid
    WHERE
      a.DateTime IN (
      SELECT
        MIN(DateTime)
      FROM
        `Vehicles`
      WHERE
        VehicleId = a.VehicleId
        AND Tripid = a.Tripid)
      AND b.DateTime IN (
      SELECT
        MAX(DateTime)
      FROM
        `Vehicles`
      WHERE
        VehicleId = a.VehicleId
        AND Tripid = a.Tripid)

推荐答案

首先想到的是row_number():

select v.*
from (select v.*,
             row_number() over (partition by vehicleid, tripid order by datetime asc) as seqnum_asc,
             row_number() over (partition by vehicleid, tripid order by datetime desc) as seqnum_desc
      from vehicles v
     ) v
where seqnum_asc = 1 or seqnum_desc = 1;

如果您希望它们位于同一行:

If you want them on the same row:

select vehicleid, tripid,
       min(datetime) as start_datetime, max(datetime) as end_datetime,
       min(case when seqnum_asc = 1 then latitude end) as start_latitude,
       min(case when seqnum_asc = 1 then longitude end) as start_longitude,
       min(case when seqnum_desc = 1 then latitude end) as end_latitude,
       min(case when seqnum_desc = 1 then longitude end) as end_longitude
from (select v.*,
             row_number() over (partition by vehicleid, tripid order by datetime asc) as seqnum_asc,
             row_number() over (partition by vehicleid, tripid order by datetime desc) as seqnum_desc
      from vehicles v
     ) v
where seqnum_asc = 1 or seqnum_desc = 1
group by vehicleid, tripid;

这篇关于检索每个组的第一条记录和最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！