Logística
Estudio de caso de logística y libro de recetas SQL
Active DataHub antes de utilizar los datos para crear análisis exhaustivos. Si aún no lo tiene, contáctenos para obtener detalles de activación - [email protected]
La logística es un ecosistema complejo que implica la coordinación del transporte, las operaciones de almacén, el inventario y la ejecución de las entregas. Integrar la telemática en los procesos logísticos permite a las empresas recopilar datos en tiempo real sobre vehículos, conductores, rutas y condiciones de la carga, lo que mejora significativamente la toma de decisiones y la eficiencia operativa.
Navixy DataHub, con sus sólidas capacidades de ingestión de datos y análisis de series temporales, respalda la transformación digital de las operaciones logísticas al permitir una visibilidad profunda en cada etapa del ciclo de vida. Sus robustas capacidades de ingestión telemática proporcionan una visibilidad integral de estas operaciones. Los datos GPS en tiempo real, el diagnóstico de datos de sensores, la geovallado y el análisis de sensores permiten a los operadores logísticos digitalizar flujos de trabajo, automatizar controles y tomar decisiones informadas.
Gestión de rutas
Optimizar el enrutamiento de vehículos, garantizar un despacho eficiente y reducir retrasos
Recuento de viajes por día Recuento de kilómetros por vehículo por día (Últimos 7 días)
Monitoreo de la carga
Garantizar condiciones de transporte adecuadas para mercancías sensibles
Eventos de violación de temperatura (y humedad) en los últimos 7 días
Operación del vehículo
Rastrear la utilización de la flota, asegurar el mantenimiento y reducir el tiempo de inactividad
Resumen de horas de motor por vehículo / conductor / día (Últimos 7 días) Análisis de tiempo de inactividad del vehículo Rastreo de activos sin movimiento
Seguridad y protección de la ruta
Detectar uso indebido, actividad no autorizada e infracciones de seguridad
Detección de desviación de ruta - Paradas no autorizadas (Últimas 24 horas) Detección de uso fuera de horario
Gestión de cumplimiento
Monitorear el comportamiento del conductor, aplicar políticas y cumplimiento operativo
Resumen de horas de motor por vehículo / conductor / día (Últimos 7 días) Detección de uso fuera de horario
Análisis post-entrega
Evaluar la eficiencia operativa y el rendimiento histórico
Informe de registro de eventos del vehículo Recuento de kilómetros por vehículo por día (Últimos 7 días) Recuento de viajes por día Rastreo de activos sin movimiento
Rastreo de activos sin movimiento
Este caso identifica activos (p. ej., vehículos o remolques) que no han cambiado su GPS comparar los coordenadas mínima y máxima durante el periodo. Si ambos valores caen dentro de un rango muy estrecho (un umbral de tolerancia, p. ej., ±0,01 grados), marcamos el activo como inmóvil. La consulta también une con las tablas objects y vehicles en raw_business_data para recuperar etiquetas significativas de los activos para la salida de resultados.
WITH gps_bounds AS (
SELECT
td.device_id,
MIN(td.latitude) AS min_lat,
MAX(td.latitude) AS max_lat,
MIN(td.longitude) AS min_lon,
MAX(td.longitude) AS max_lon,
COUNT(*) AS location_records
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '48 hours'
GROUP BY td.device_id
),
stationary_devices AS (
SELECT
device_id
FROM gps_bounds
WHERE location_records > 10 -- excluir dispositivos con datos muy escasos
AND((max_lat - min_lat) <= 2000 -- ~10 metros
OR
(max_lon - min_lon) <= 1000 -- ~10 metros
)
SELECT
v.vehicle_id,
v.vehicle_label,
o.object_id,
o.object_label,
sd.device_id,
gb.min_lat / 1e7 AS latitude,
gb.min_lon / 1e7 AS longitude,
gb.location_records
FROM stationary_devices sd
JOIN gps_bounds gb ON sd.device_id = gb.device_id
JOIN raw_business_data.objects o ON o.device_id = sd.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY gb.location_records DESC;Análisis de tiempo de inactividad del vehículo
Este caso se centra en analizar cuánto tiempo los vehículos están no operativos debido a mantenimiento, averías o inactividad. Las métricas de tiempo de inactividad son cruciales para las operaciones logísticas para monitorear la salud de la flota, reducir el tiempo ocioso y mejorar la utilización y la eficiencia de la programación.
El núcleo del análisis de tiempo de inactividad radica en aprovechar la tabla vehicle_service_tasks de raw_business_data, que registra tanto eventos de mantenimiento planificados y no planificados. Cada tarea contiene una start_date y una end_date, que representan el período de inactividad. Filtrando por tareas de servicio completadas, podemos calcular la duración exacta durante la cual cada vehículo estuvo fuera de servicio.
La consulta calcula el tiempo total de inactividad por vehículo sumando las duraciones de todas sus tareas de servicio (en horas). También permite desglosar por mantenimiento planificado frente a no planificado utilizando el indicador is_unplanned. Para hacer los resultados más accionables, se une con la tabla vehicles para incluir etiquetas de vehículo, números de matrícula e información del modelo.
WITH downtime_durations AS (
SELECT
vst.vehicle_id,
vst.is_unplanned,
vst.start_date,
vst.end_date,
EXTRACT(EPOCH FROM (vst.end_date - vst.start_date))/3600 AS downtime_hours
FROM raw_business_data.vehicle_service_tasks vst
WHERE vst.status = 'done'
AND vst.start_date IS NOT NULL
AND vst.end_date IS NOT NULL
)
SELECT
v.vehicle_id,
v.vehicle_label,
v.registration_number,
v.model,
COUNT(dd.*) AS total_service_events,
SUM(dd.downtime_hours) AS total_downtime_hours,
SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = TRUE) AS unplanned_downtime_hours,
SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = FALSE) AS planned_downtime_hours
FROM downtime_durations dd
JOIN raw_business_data.vehicles v ON v.vehicle_id = dd.vehicle_id
GROUP BY v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;Detección de desviación de ruta
Este caso identifica instancias donde los vehículos se desvían de sus rutas asignadas o esperadas — particularmente zonas geovalladas o corredores de entrega. Rastrear tales desviaciones ayuda a garantizar el cumplimiento de rutas, reducir retrasos, detectar conductas de conducción riesgosas y mantener los SLA de entrega.
Esta lógica compara las posiciones GPS reales del vehículo desde tracking_data_core (en el esquema raw_telematics_data) con zonas geográficas predefinidas desde la tabla zones en raw_business_data. Estas zonas representan rutas asignadas o segmentos de ruta. Usando comparaciones geométricas mediante ST_DWithin, determinamos si un punto está dentro o fuera del área tamponada de la ruta.
La consulta une cada posición GPS con cada zona de ruta conocida usando un CROSS JOIN, luego aplica ST_DWithin() para comprobar si el vehículo estaba dentro del corredor permitido. Aislamos las filas donde el vehículo estaba fuera de todas las rutas geovalladas y las marcamos como desviaciones. La salida final lista estas desviaciones, incluyendo el dispositivo, la marca de tiempo, la etiqueta del vehículo y qué tan lejos estaba el punto del centro de la zona más cercana.
WITH positions AS (
SELECT
td.device_id,
td.device_time,
o.object_id,
o.object_label,
z.zone_id,
z.zone_label,
ST_SetSRID(ST_MakePoint(td.longitude / 1e7, td.latitude / 1e7), 4326)::geography AS gps_point,
ST_Buffer(
ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
z.radius
) AS route_buffer
FROM raw_telematics_data.tracking_data_core td
JOIN raw_business_data.objects o ON td.device_id = o.device_id
CROSS JOIN raw_business_data.zones z
WHERE td.device_time >= now() - interval '2 days'
),
evaluated AS (
SELECT
device_id,
device_time,
object_id,
object_label,
zone_id,
zone_label,
NOT ST_DWithin(gps_point, route_buffer, 0) AS is_deviation,
ST_Distance(gps_point, route_buffer) AS deviation_distance_meters
FROM positions
),
deviations_only AS (
SELECT *
FROM evaluated
WHERE is_deviation = true
)
SELECT
device_id,
object_label,
zone_label,
device_time,
deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;Resumen de horas de motor por vehículo / conductor / día (Últimos 7 días)
Este caso mide cuánto tiempo los motores estuvieron activos para cada vehículo a nivel diario, lo que permite a los gestores de flota rastrear utilización, identificar sobreuso o subutilización, y correlacionar la actividad con las asignaciones de conductores. Cuando se vincula a los conductores, también respalda validación de horas laborales y análisis de rendimiento.
La tabla states en raw_telematics_data registra indicadores de estado del motor en series temporales, típicamente con un state_name como 'ignition' y un valor de 1 (encendido) o 0 (apagado). Para calcular las horas de motor, encontramos todas las transiciones con marca de tiempo para cada dispositivo y calculamos las duraciones cuando el motor estuvo encendido (1).
Para vincular la actividad del motor tanto a vehículos como conductores, usamos las tablas objects, vehicles y driver_history de raw_business_data. Asociamos cada registro de estado al conductor actual de ese objeto (a través del historial de asignación de conductores) y al vehículo correspondiente. Luego agrupamos los datos por día, vehículo y conductor, sumando el tiempo total activo del motor (en horas).
WITH inputs_core AS (
SELECT
i.device_id,
i.device_time,
i.event_id,
i.record_added_at,
i.sensor_name,
i.value
FROM raw_telematics_data.inputs i
WHERE i.device_time >= now() - interval '7 days'
),
clear_inputs AS (
SELECT
i.device_id,
i.device_time,
i.event_id,
i.record_added_at,
i.sensor_name,
i.value,
LAG(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_time,
LAG(i.value::int) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_status
FROM inputs_core i
LEFT JOIN raw_business_data.sensor_description sd
ON sd.input_label = i.sensor_name
AND sd.device_id = i.device_id
WHERE sd.sensor_type = 'engine'
AND i.value = '1'
),
engine_on_periods AS (
SELECT
device_id,
prev_time AS engine_on_time,
device_time AS engine_off_time,
device_time::date AS activity_day,
EXTRACT(EPOCH FROM (device_time - prev_time)) / 3600 AS engine_hours
FROM clear_inputs
WHERE value::int = 0 AND prev_status = 1 -- transiciones de ENCENDIDO a APAGADO
),
enriched_with_objects AS (
SELECT
eop.*,
o.object_id,
o.object_label,
v.vehicle_id,
v.vehicle_label,
v.registration_number
FROM engine_on_periods eop
JOIN raw_business_data.objects o ON o.device_id = eop.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
assigned_drivers AS (
SELECT
ewo.*,
dh.new_employee_id as employee_id
FROM enriched_with_objects ewo
LEFT JOIN LATERAL (
SELECT d.*
FROM raw_business_data.driver_history d
WHERE d.object_id = ewo.object_id
AND d.changed_datetime <= ewo.engine_on_time
ORDER BY d.changed_datetime DESC
LIMIT 1
) dh ON true
)
SELECT
activity_day,
vehicle_label,
registration_number,
object_label,
e.first_name || ' ' || e.last_name AS driver_name,
SUM(engine_hours) AS total_engine_hours
FROM assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
GROUP BY activity_day, vehicle_label, registration_number, object_label, driver_name
ORDER BY activity_day DESC, vehicle_label;
Eventos de violación de temperatura (y humedad) en los últimos 7 días
Este caso identifica lecturas de sensores — como temperatura o humedad — que exceden umbrales críticos durante el transporte. Monitorear tales violaciones es vital para industrias que transportan productos perecederos (p. ej., alimentos, productos farmacéuticos) para garantizar el cumplimiento de la cadena de frío y prevenir el deterioro.
Esta consulta extrae datos de entrada de sensores de la tabla inputs en el esquema raw_telematics_data. Cada fila representa una lectura de sensor (p. ej., temperatura, humedad) registrada en una marca de tiempo específica por un dispositivo. Filtramos estos registros para incluir solo aquellos de los últimos 7 días.
La lógica principal de filtrado se basa en patrones de nombres de sensores y una comparación de sus valores numéricos frente a umbrales (p. ej., >25°C para temperatura, >80% para humedad). Debido a que value se almacena como texto, lo convertimos a numérico antes de aplicar las condiciones de umbral. Para enriquecer los resultados, nos unimos con la tabla objects para recuperar etiquetas del vehículo o activo, lo que mejora la interpretabilidad para los gestores de flota.
WITH recent_sensor_data AS (
SELECT
i.device_id,
i.device_time,
i.sensor_name,
i.value::float
FROM raw_telematics_data.inputs i
WHERE i.device_time >= now() - interval '1 hour'
),
calibration_data AS (
SELECT
sensor_id,
value AS cal_value,
volume AS cal_volume
FROM raw_business_data.sensor_calibration_data
),
sensor_meta AS (
SELECT
sd.device_id,
sd.input_label,
sd.sensor_id
FROM raw_business_data.sensor_description sd
),
joined_data AS (
SELECT
rsd.*,
sm.sensor_id
FROM recent_sensor_data rsd
LEFT JOIN sensor_meta sm
ON rsd.device_id = sm.device_id AND rsd.sensor_name = sm.input_label
),
calibrated_data AS (
SELECT
jd.device_id,
jd.device_time,
jd.sensor_name,
jd.value,
jd.sensor_id,
CASE
WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
CASE
WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
ELSE cd_low.cal_volume +
((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0))
* (cd_high.cal_volume - cd_low.cal_volume)
END
ELSE jd.value
END AS calibrated_value
FROM joined_data jd
LEFT JOIN LATERAL (
SELECT * FROM calibration_data
WHERE sensor_id = jd.sensor_id AND cal_value <= jd.value
ORDER BY cal_value DESC LIMIT 1
) cd_low ON TRUE
LEFT JOIN LATERAL (
SELECT * FROM calibration_data
WHERE sensor_id = jd.sensor_id AND cal_value >= jd.value
ORDER BY cal_value ASC LIMIT 1
) cd_high ON TRUE
),
violations AS (
SELECT
cd.device_id,
cd.device_time,
cd.sensor_name,
cd.calibrated_value,
CASE
WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
WHEN sd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
ELSE NULL
END AS violation_type
FROM calibrated_data cd
left join raw_business_data.sensor_description sd on sd.device_id = cd.device_id and sd.input_label = cd.sensor_name
WHERE sensor_type in ('temperature', 'humidity')
)
SELECT
v.vehicle_label,
o.object_label,
v.registration_number,
v.model,
vio.device_time,
vio.sensor_name,
vio.calibrated_value,
vio.violation_type
FROM violations vio
JOIN raw_business_data.objects o ON o.device_id = vio.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;
Paradas no autorizadas (Últimas 24 horas)
Este caso identifica paradas no autorizadas o no planificadas realizadas por vehículos en las últimas 24 horas. Ayuda a detectar posibles infracciones de rutas de entrega, descansos no autorizados o tiempo de inactividad que pueden afectar la eficiencia del combustible y el rendimiento de los SLA.
La consulta analiza puntos de ubicación con velocidad baja o cero utilizando la La consulta utiliza la tabla tracking_data_core de raw_telematics_data para extraer datos de ubicación en series temporales y velocidad. Se detecta una parada cuando la velocidad cae por debajo de 3 km/h durante un período de más de 2 minutos. Usando las funciones LAG y LEAD, la consulta segmenta estos periodos de baja velocidad para determinar las marcas de inicio y fin de la parada.
Para detectar paradas no autorizadas, filtra ubicaciones que caen dentro de zonas geovalladas conocidas (tabla zones) usando ST_DWithin de PostGIS. Solo se reportan las paradas fuera de cualquier buffer de zona . El resultado incluye ID del vehículo, etiqueta del objeto, matrícula, marcas de tiempo, duración y coordenadas de cada parada.
WITH speed_data AS (
SELECT
td.device_id,
td.device_time,
td.speed / 100.0 AS speed_kph,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '1 day'
),
low_speed_points AS (
SELECT
*,
LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
FROM speed_data
),
stops_marked AS (
SELECT *,
CASE
WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1
ELSE 0
END AS stop_start,
CASE
WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1
ELSE 0
END AS stop_end
FROM low_speed_points
),
stop_segments AS (
SELECT
device_id,
device_time AS stop_start_time,
LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time,
lat,
lon
FROM stops_marked
WHERE stop_start = 1
),
unauthorized_stops AS (
SELECT
ss.*,
EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS stop_duration_min
FROM stop_segments ss
LEFT JOIN raw_business_data.zones z ON
ST_DWithin(
ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
z.radius
)
WHERE z.zone_id IS NULL -- no dentro de ninguna zona conocida
AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120 -- mínimo 2 minutos
),
with_metadata AS (
SELECT
us.*,
o.object_label,
v.vehicle_label,
v.registration_number
FROM unauthorized_stops us
JOIN raw_business_data.objects o ON o.device_id = us.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
)
SELECT
vehicle_label,
registration_number,
object_label,
stop_start_time,
stop_end_time,
ROUND(stop_duration_min, 1) AS stop_duration_minutes,
ROUND(lat, 6) AS latitude,
ROUND(lon, 6) AS longitude
FROM with_metadata
ORDER BY stop_start_time DESC;Detección de uso fuera de horario
Este caso identifica instancias donde los vehículos son operados fuera del horario laboral normal — definido aquí como de lunes a viernes, 09:00–18:00. Tales detecciones son esenciales para marcar uso no autorizado, identificar posibles uso indebido del vehículo, y mejorar la seguridad del activo.
La lógica se basa en la tabla tracking_data_core de raw_telematics_data, que registra eventos GPS con marca de tiempo por dispositivo. Derivamos el día de la semana y hora de uso de cada entrada device_time y filtramos los registros fuera de la ventana comercial definida (es decir, antes de las 9 AM, después de las 6 PM o en cualquier momento del fin de semana).
Para proporcionar claridad, enriquecemos los datos GPS con metadatos de objetos y vehículos de raw_business_data (p. ej., etiqueta del vehículo, matrícula, ID del objeto). Para resúmenes más significativos, opcionalmente agregamos el uso para contar cuántos eventos fuera de horario ocurrieron por vehículo y cuándo sucedieron. Esto puede ayudar a identificar patrones o reincidentes.
WITH gps_events AS (
SELECT
td.device_id,
td.device_time,
EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Domingo, 6=Sábado
EXTRACT(HOUR FROM td.device_time) AS hour_of_day,
td.latitude / 1e7 AS latitude,
td.longitude / 1e7 AS longitude
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '7 days'
),
off_hour_events AS (
SELECT
ge.*
FROM gps_events ge
WHERE
day_of_week IN (0, 6) -- Sábado o Domingo
OR hour_of_day < 9
OR hour_of_day >= 18
),
with_metadata AS (
SELECT
o.object_label,
v.vehicle_label,
v.registration_number,
e.first_name || ' ' || e.last_name AS driver_name,
o.device_id,
o.object_id,
o.create_datetime,
ge.device_time,
ge.latitude,
ge.longitude,
ge.day_of_week,
ge.hour_of_day
FROM off_hour_events ge
JOIN raw_business_data.objects o ON ge.device_id = o.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
LEFT JOIN raw_business_data.driver_history dh ON dh.object_id = o.object_id
AND dh.changed_datetime <= ge.device_time
LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id
)
SELECT
object_label,
vehicle_label,
registration_number,
driver_name,
device_time,
TO_CHAR(device_time, 'Day') AS weekday,
TO_CHAR(device_time, 'HH24:MI') AS time_of_event,
ROUND(latitude::numeric, 6) AS lat,
ROUND(longitude::numeric, 6) AS lon
FROM with_metadata
ORDER BY device_time DESC;Recuento de viajes por día
Este caso mide cuántos viajes completa cada vehículo diariamente y qué distancia recorren, ayudando a los equipos logísticos a evaluar uso del vehículo, optimizar rutas y detectar anomalías como viajes incompletos o uso no reportado.
Para definir un viaje, usamos un cambio en el estado de movimiento del vehículo — es decir, transición de detenido a en movimiento y de nuevo a detenido. Usando los valores de velocidad de la tabla tracking_data_core, la consulta segmenta los datos según estas transiciones. Un viaje se identifica como un periodo de movimiento continuo donde la velocidad se mantiene por encima de un umbral (p. ej., >5 km/h).
Cada viaje incluye:
Una marca de tiempo de inicio y ubicación (primer punto en movimiento)
Una marca de tiempo de fin y ubicación (último punto en movimiento antes de detenerse)
La distancia Haversine entre las ubicaciones de inicio y fin
Calculamos el recuento de viajes y la distancia total por día y por vehículo, opcionalmente enriquecidos con etiquetas de vehículo de la tabla vehicles.
WITH base_points AS (
SELECT
td.device_id,
td.device_time,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon,
td.speed / 100.0 AS speed_kph,
LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed,
LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '7 days'
),
trip_segments AS (
SELECT
*,
CASE
WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'start'
WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'end'
END AS trip_marker
FROM base_points
),
trip_points AS (
SELECT
device_id,
device_time AS trip_start_time,
lat AS start_lat,
lon AS start_lon,
LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS trip_end_time,
LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat,
LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon
FROM trip_segments
WHERE trip_marker = 'start'
),
trip_metrics AS (
SELECT
tp.device_id,
tp.trip_start_time,
tp.trip_end_time,
tp.start_lat,
tp.start_lon,
tp.end_lat,
tp.end_lon,
tp.trip_start_time::date AS trip_day,
-- Approximate distance using Haversine formula (in km)
111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2)) AS distance_km
FROM trip_points tp
WHERE tp.trip_end_time IS NOT NULL
)
SELECT
v.vehicle_label,
v.registration_number,
tm.device_id,
tm.trip_day,
COUNT(*) AS trip_count,
ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM trip_metrics tm
JOIN raw_business_data.objects o ON o.device_id = tm.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;Recuento de kilometraje por vehículo por día (últimos 7 días)
Este caso calcula el kilometraje diario (en kilómetros) para cada vehículo durante los últimos 7 días. Es fundamental para el seguimiento de la utilización del vehículo, la supervisión de la eficiencia de combustible, la planificación de mantenimiento, y la detección de subutilización o sobreutilización.
Extraemos todos los registros GPS de tracking_data_core de los últimos 7 días. Cada punto GPS tiene una marca temporal, latitud y longitud. Para cada vehículo y cada día, nosotros:
Ordenamos los puntos GPS cronológicamente por dispositivo.
Calculamos la distancia entre puntos consecutivos usando la fórmula de Haversine.
Sumamos las distancias por día por dispositivo para obtener el kilometraje total.
Este enfoque proporciona alta precisión sin depender de sensores externos del odómetro. Opcionalmente, la consulta se une con objects y vehicles para enriquecer los resultados con metadatos del activo.
WITH gps_points AS (
SELECT
td.device_id,
td.device_time,
td.device_time::date AS trip_day,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon,
LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lat,
LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lon
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '7 days'
),
distances AS (
SELECT
device_id,
trip_day,
-- Haversine formula approximation in km
111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km
FROM gps_points
WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL
AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- exclude outliers
)
SELECT
v.vehicle_label,
v.registration_number,
o.object_label,
d.device_id,
d.trip_day,
ROUND(SUM(d.segment_distance_km)::numeric, 2) AS mileage_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;Informe de registro de eventos del vehículo
Este caso proporciona un informe exhaustivo de todos los eventos relacionados con el vehículo (por ejemplo, encendido, puerta abierta, frenada brusca, etc.) en toda la flota. Incluye tipo de evento, marca temporal, y contexto del vehículo, lo que permite a los equipos de operaciones auditar el comportamiento, rastrear actividades anómalas o impulsar alertas y análisis.
La fuente principal es la tabla states del esquema raw_telematics_data. Cada fila incluye: device_id (origen del evento), device_time (marca temporal), state_name (etiqueta del evento) y value (estado o medición).
Para crear un informe usable:
Extraemos todos los registros de los últimos 7 días.
Los agrupamos por tipo de evento, vehículo, y fecha para proporcionar un recuento de cuántas veces ocurrió cada evento y cuándo ocurrió.
Enriquecer los resultados con metadatos del vehículo (vehicle_label, registration_number, object_label) mediante objects y vehicles.
Esto proporciona una línea de tiempo de eventos diaria en toda la flota - esencial para diagnósticos, análisis de comportamiento y mantenimiento proactivo.
WITH raw_events AS (
SELECT
s.device_id,
s.device_time,
s.device_time::date AS event_day,
s.state_name,
s.value
FROM raw_telematics_data.states s
WHERE s.device_time >= now() - interval '7 days'
),
with_vehicle_info AS (
SELECT
re.device_id,
re.event_day,
re.device_time,
re.state_name,
re.value,
v.vehicle_label,
v.registration_number,
o.object_label
FROM raw_events re
JOIN raw_business_data.objects o ON o.device_id = re.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
event_summary AS (
SELECT
event_day,
state_name,
vehicle_label,
registration_number,
object_label,
COUNT(*) AS event_count,
MIN(device_time) AS first_occurred,
MAX(device_time) AS last_occurred
FROM with_vehicle_info
GROUP BY event_day, state_name, vehicle_label, registration_number, object_label
)
SELECT
event_day,
vehicle_label,
registration_number,
object_label,
state_name AS event_type,
event_count,
first_occurred,
last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;Última actualización
¿Te fue útil?