¿Cómo transformar una tabla de BigQuery en una lista de secuencias de filas donde las secuencias se agregan mediante una ventana deslizante en el tiempo?

Aleksandar Djuric

Tengo una tabla muy grande donde cada fila representa una abstracción llamada Viaje. Los viajes constan de columnas numéricas como la identificación del vehículo, la identificación del viaje, la hora de inicio, la hora de finalización, la distancia recorrida, la duración de la conducción, etc.

Quiero transformar esta tabla en una lista de secuencias de viajes donde los viajes se agrupan en secuencias por identificación del vehículo y también por una ventana deslizante en el tiempo. Esencialmente, cada grupo / secuencia consiste en viajes desde la misma identificación del vehículo donde los viajes caen dentro de una ventana de, digamos, 5 días. Naturalmente, el grupo / secuencia será de duración variable (preferiblemente con un tamaño máximo donde se podrían ignorar los viajes adicionales). Sin embargo, la ventana no se superpone, por lo que un viaje no puede estar en dos grupos / secuencias diferentes. Finalmente, las secuencias están ordenadas por StartTime.

Ejemplo: (ventana = 5 días)

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

Variante de una pregunta anterior

Mikhail Berlyant

A continuación se muestra para SQL estándar de BigQuery

#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

Puede probar, jugar con los anteriores utilizando datos ficticios como en el ejemplo a continuación

#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

con resultado

Este artículo se recopila de Internet, indique la fuente cuando se vuelva a imprimir.

En caso de infracción, por favor [email protected] Eliminar

Editado en2021-07-29

Déjame decir algunas palabras

0Comentarios

Iniciar sesiónRevisión de participación posterior

Anterior:Almacene valores de múltiples matrices en una matriz en PHP

TOP Lista

Artículos