sql - 找出每天自行车的第一次行程时长和最后一次行程时长

Question

找出自行车每天的第一次旅行持续时间和最后一次旅行持续时间。

Table
trip_id bike-id trip_date   trip_starttime    trip_duration
1          1    2018-12-01  12:00:00.0000000    10
2          2    2018-12-01  14:00:00.0000000    25
3          1    2018-12-01  14:30:00.0000000    5
4          3    2018-12-02  05:00:00.0000000    12
5          3    2018-12-02  19:00:00.0000000    37
6          1    2018-12-02  20:30:00.0000000    20

Expected Result

trip_date   bike-id first_trip_duration last_trip_duration
2018-12-01    1            10               5
2018-12-01    2            25              25
2018-12-02    1            20              20
2018-12-02    3            12              37

我用下面的代码试过了，

select A.trip_date,A.[bike-id],A.trip_duration AS Minduration,B.trip_duration AS MaxDUrtaion from 

(SELECT T1.trip_date,T1.[bike-id],T1.trip_duration FROM TRIP T1
INNER JOIN (
select trip_date,[bike-id] , min(trip_starttime) AS Mindate 
from Trip  group by trip_date,[bike-id] ) T2
oN T1.[bike-id]=T2.[bike-id] AND T1.trip_date=T2.trip_date AND t1.trip_starttime=t2.Mindate ) as A
inner join 

(SELECT T1.trip_date,T1.[bike-id],T1.trip_duration FROM TRIP T1
INNER JOIN (
select trip_date,[bike-id] , MAX(trip_starttime) AS Maxdate 
from Trip  group by trip_date,[bike-id] ) T2
oN T1.[bike-id]=T2.[bike-id] AND T1.trip_date=T2.trip_date AND t1.trip_starttime=t2.Maxdate )  as B

ON A.[bike-id]=B.[bike-id] AND A.trip_date=B.trip_date

order by A.trip_date,A.[bike-id]

我也想知道一些其他的逻辑，请帮忙。

标签： sql

Answer 1

select trip_date,bike_id
      ,first_value(trip_duration) over(partition by trip_date,bike_id order by trip_starttime) as first_trip_duration
      ,first_value(trip_duration) over(partition by trip_date,bike_id order by trip_starttime desc) as last_trip_duration
from trip;