我有一个很大的表，其中每一行代表一个称为Trip的抽象。行程由数字列组成，例如车辆ID，行程ID，开始时间，停止时间，行驶距离，行驶时间等。
我想将此表转换为Trip序列的列表，其中Trips通过车辆ID以及时间上的滑动窗口分为序列。从本质上讲，每个组/序列都包含来自同一车辆ID的行程，其中行程在例如5天的时间范围内。当然，组/序列的长度是可变的（最好是最大大小，可以忽略额外的行程）。但是，该窗口不重叠，因此行程不能位于两个不同的组/序列中。最后，这些序列按StartTime排序。
示例： （窗口= 5天）
[
**Oct31 - Nov4**
(Vehicle1,[Trip7,Trip8,Trip9,Trip10]),(Vehicle2,[Trip3,Trip4,Trip5])
**Oct26 - Oct30**
(Vehicle1,[Trip1,Trip2,Trip3,Trip5,Trip6]),Trip2]),]
上一个question的变体形式
以下是用于BigQuery标准SQL
#standardSQL
WITH windows AS (
SELECT start_day,DATE_ADD(start_day,INTERVAL 4 DAY) end_day
FROM UNNEST(GENERATE_DATE_ARRAY('2019-10-01',CURRENT_DATE(),INTERVAL 5 DAY)) start_day
)
SELECT start_day,end_day,trip.vehicle_id,ARRAY_AGG(trip ORDER BY trip.start_time) trips
FROM `project.dataset.table` trip
JOIN windows ON start_time BETWEEN start_day AND end_day
GROUP BY start_day,vehicle_id
您可以像下面的示例一样使用虚拟数据进行测试，玩游戏
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Vehicle1' vehicle_id,'Trip1' trip_id,DATE '2019-10-29' start_time,DATE '2019-10-30' stop_time UNION ALL
SELECT 'Vehicle1','Trip2','2019-10-30','2019-10-31' UNION ALL
SELECT 'Vehicle1','Trip3','2019-10-31','2019-11-01' UNION ALL
SELECT 'Vehicle1','Trip4','2019-11-01','2019-11-02' UNION ALL
SELECT 'Vehicle1','Trip5','2019-11-02','2019-11-03' UNION ALL
SELECT 'Vehicle1','Trip6','2019-11-03','2019-12-04' UNION ALL
SELECT 'Vehicle1','Trip7','2019-11-04','2019-12-05' UNION ALL
SELECT 'Vehicle2','Trip1','2019-10-29','2019-10-30' UNION ALL
SELECT 'Vehicle2','2019-10-31' UNION ALL
SELECT 'Vehicle2','2019-11-01' UNION ALL
SELECT 'Vehicle2','2019-11-02'
),windows AS (
SELECT start_day,vehicle_id
-- ORDER BY start_day,vehicle_id
有结果
