Aggregate function with window function filtered by time

Question

I have a table with data about buses while making their routes. There are columns for:

bus trip id (different each time a bus starts the route from the first stop)
bus stop id
datetime column that indicates the moment that the bus leaves each bus stop
integer that indicates how many passengers entered the bus in that stop

There is no information about how many passengers get off the bus on each stop, so I have to make an estimation supposing that once they get on the bus, they stay on it for 30 minutes. The trip lasts about 70 minutes from the first to the last stop.

I am trying to aggregate results on each stop using

SUM(iPassengersIn) OVER (
    PARTITION BY tripDate, tripId
    ORDER BY busStopOrder
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) total_passengers

The problem is that I can add passengers since the beginning of the trip, but not since "30 minutes ago" on each stop. How could I limit the aggregation to "the last 30 minutes" on each row in order to estimate the occupation between stops?

This is a subset of my data:

trip_date   trip_id  bus_stop_order  minutes_since_trip_start  passengers_in  trip_total_passengers
2020-06-08  374910    0               0                        0               0
2020-06-08  374910    1               3                        0               0
2020-06-08  374910    2               5                        1               1
2020-06-08  374910    3               8                        0               1
2020-06-08  374910    4               9                        0               1
2020-06-08  374910    5              12                        0               1
2020-06-08  374910    6              13                        0               1
2020-06-08  374910    7              13                        0               1
2020-06-08  374910    8              15                        0               1
2020-06-08  374910    9              16                        0               1
2020-06-08  374910   10              16                        0               1
2020-06-08  374910   11              17                        0               1
2020-06-08  374910   12              18                        2               3
2020-06-08  374910   13              20                        0               3
2020-06-08  374910   14              22                        0               3
2020-06-08  374910   15              24                        0               3
2020-06-08  374910   16              25                        0               3
2020-06-08  374910   17              28                        2               5
2020-06-08  374910   18              30                        1               6
2020-06-08  374910   19              31                        0               6
2020-06-08  374910   20              33                        0               6
2020-06-08  374910   21              41                        3               9
2020-06-08  374910   22              44                        3              12
2020-06-08  374910   23              45                        4              16
2020-06-08  374910   24              48                        2              18
2020-06-08  374910   25              48                        2              20
2020-06-08  374910   26              50                        0              20
2020-06-08  374910   27              51                        0              20
2020-06-08  374910   28              51                        0              20
2020-06-08  374910   29              53                        0              20
2020-06-08  374910   30              55                        0              20
2020-06-08  374910   31              58                        0              20

For the row with bus_stop_order 21 (41 minutes into the bus trip), where 3 passengers enter the bus, I have to sum only the passengers that entered the bus between minute 11 and 41. Thus, the passenger that entered the bus in the 2nd bus stop (5 minutes into the trip) should be excluded.

That should be applied for every row.

user_11683 · Answer

The only thing I can think of is:

select
  trip_date,
  trip_id,
  minutes_since_trip_start,
  v.total_passengers
from
  @t t1
  outer apply (
    select sum(passengers_in)
    from @t t2
    where
      t1.trip_date = t2.trip_date
      and t1.trip_id = t2.trip_id
      and t2.bus_stop_order <= t1.bus_stop_order
      and t2.minutes_since_trip_start >= t1.minutes_since_trip_start - 30
  ) v(total_passengers)
order by
  trip_date,
  trip_id,
  minutes_since_trip_start
;

Aggregate function with window function filtered by time

1 Answers