sql - 如何将 BigQuery 表转换为行序列列表，其中序列由滑动窗口及时聚合？

Question

我有一个非常大的表，其中每一行代表一个称为 Trip 的抽象。行程由数字列组成，例如车辆 ID、行程 ID、开始时间、停止时间、行驶距离、行驶时间等。

我想将此表转换为行程序列列表，其中行程按车辆 ID 和时间滑动窗口分组为序列。从本质上讲，每个组/序列都包含来自同一车辆 ID 的行程，其中行程落在 5 天的窗口内。自然地，组/序列将具有可变长度（最好具有可以忽略额外行程的最大大小）。然而，该窗口是不重叠的，因此旅行不能在两个不同的组/序列中。最后，序列按 StartTime 排序。

示例：（窗口 = 5 天）

[
 **Oct31 - Nov4**
(Vehicle1, [Trip7, Trip8, Trip9, Trip10]),
(Vehicle2, [Trip3, Trip4, Trip5]) 
 **Oct26 - Oct30**
(Vehicle1, [Trip1, Trip2, Trip3, Trip4, Trip5, Trip6]),   
(Vehicle2, [Trip1, Trip2]),
]

上一个问题的变体

标签： sqlgoogle-bigquery

Answer 1

以下是 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, end_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', 'Trip2', '2019-10-30', '2019-10-31' UNION ALL
  SELECT 'Vehicle2', 'Trip3', '2019-10-31', '2019-11-01' UNION ALL
  SELECT 'Vehicle2', 'Trip4', '2019-11-01', '2019-11-02'  
), 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, end_day, vehicle_id
-- ORDER BY start_day, end_day, vehicle_id

结果