Logística

Estudio de caso de logística y Libro de recetas SQL

Habilitar Consulta IoT antes de utilizar los datos para construir análisis integrales. Si aún no la 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 Consulta IoT, con sus capacidades robustas 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 sólidas capacidades de ingestión telemática proporcionan una visibilidad integral de estas operaciones. Los datos GPS en tiempo real, los diagnósticos de datos de sensores, la geovallas y el análisis de sensores permiten a los operadores logísticos digitalizar flujos de trabajo, automatizar controles y tomar decisiones informadas.

Fase del ciclo de vida	Objetivos	Casos de uso cubiertos / Recetas
Gestión de rutas	Optimizar el enrutamiento de vehículos, asegurar un despacho eficiente y reducir retrasos	Recuento de viajes por día Conteo de kilometraje por vehículo por día (Últimos 7 días)
Monitoreo de carga	Asegurar 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 Seguimiento de activos sin movimiento
Seguridad y protección de la ruta	Detectar uso indebido, actividad no autorizada y violaciones 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, hacer cumplir políticas y el 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 posterior a la entrega	Evaluar la eficiencia operativa y el rendimiento histórico	Informe del registro de eventos del vehículo Conteo de kilometraje por vehículo por día (Últimos 7 días) Recuento de viajes por día Seguimiento de activos sin movimiento

Seguimiento de activos sin movimiento

Este caso identifica activos (p. ej., vehículos o remolques) que no han cambiado su GPS compare el coordenadas mínima y máxima durante el período. Si ambos valores caen dentro de un rango muy estrecho (un umbral de tolerancia, p. ej., ±0,01 grados), marcamos el activo como sin movimiento. La consulta también se une con las tablas objects y vehicles en raw_business_data para recuperar etiquetas significativas de los activos para la salida del resultado.

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 dispersos
	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 monitorizar 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 reside 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 periodo de tiempo de inactividad. Al filtrar por tareas de servicio completadas, podemos calcular la duración exacta que cada vehículo estuvo fuera de operación.

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 usando la marca is_unplanned. Para hacer los resultados más accionables, se une con la tabla vehicles para incluir etiquetas del 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 con geovallas o corredores de entrega. Rastrear tales desviaciones ayuda a asegurar el cumplimiento de la ruta, reducir retrasos, detectar conductas de conducción de riesgo y mantener los SLA de entrega.

Esta lógica compara las posiciones GPS reales del vehículo de tracking_data_core (en el esquema raw_telematics_data) frente a zonas geográficas predefinidas de la tabla zones en raw_business_data. Estas zonas representan rutas asignadas o segmentos de ruta. Usando comparaciones geométricas a través de ST_DWithin, determinamos si un punto está dentro o fuera del área con buffer 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 temporal, la etiqueta del vehículo y qué tan lejos estaba el punto del centro de la zona más cercano.

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 estuvieron los motores activos para cada vehículo en base diaria, lo que permite a los gestores de flota rastrear utilización, identificar sobreuso o subuso, y correlacionar la actividad con las asignaciones de conductores. Cuando se vincula a conductores, también respalda la validación de horas de trabajo 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 temporal para cada dispositivo y calculamos las duraciones donde 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 con el conductor actual en ese objeto (vía el historial de asignación de conductores) y con el vehículo correspondiente. Luego agrupamos los datos por día, vehículo y conductor, sumando el tiempo total de motor activo (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. Monitorizar tales violaciones es vital para las industrias que transportan productos perecederos (p. ej., alimentos, productos farmacéuticos) para asegurar el cumplimiento de la cadena de frío y prevenir el deterioro.

Esta consulta extrae datos de entradas 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 temporal 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 de vehículos o activos, 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 AS value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id,
       sd.sensor_type,
       sd.calibration_data
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       sm.sensor_id,
       sm.sensor_type,
       sm.calibration_data
   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,
       jd.sensor_type,
       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
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float <= jd.value
       ORDER BY (p->>'in')::float DESC
       LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float >= jd.value
       ORDER BY (p->>'in')::float ASC
       LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'Alta temperatura'
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Baja temperatura'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'Alta humedad'
           ELSE NULL
       END AS violation_type
   FROM calibrated_data cd
   WHERE cd.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 violaciones de rutas de entrega, descansos no autorizados o tiempo ocioso que puede afectar la eficiencia del combustible y el cumplimiento de SLA.

La consulta analiza puntos de ubicación con velocidad baja o cero usando 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 periodo de más de 2 minutos. Usando las funciones LAG y LEAD, la consulta segmenta estos períodos de baja velocidad para determinar las marcas temporales de inicio y fin de la parada.

Para detectar paradas no autorizadas, filtra las ubicaciones que caen dentro de zonas conocidas con geovallas (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 temporales, duración y coordenadas para 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 está 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 comercial normal — definido aquí como de lunes a viernes, 09:00–18:00. Tales detecciones son esenciales para señalar uso no autorizado, identificar un posible 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 temporal por dispositivo. Derivamos la 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:00, después de las 18:00, o en cualquier momento durante los fines 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 de 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 infractores 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 el 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, la 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 en base a 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:

• A marca de tiempo y ubicación de inicio (primer punto en movimiento)

• Un marca de tiempo y ubicación de fin (último punto en movimiento antes de detenerse)

• El Distancia de Haversine entre las ubicaciones de inicio y fin

Calculamos el recuento de viajes y la distancia total por día por vehículo, opcionalmente enriquecidos con etiquetas de vehículo desde 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,
        -- Distancia aproximada usando la fórmula de Haversine (en km)
        111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2))::numeric 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 del uso del vehículo, la supervisión de la eficiencia del combustible, la planificación de mantenimiento, y la detección de subuso o sobreuso.

Extraemos todos los registros GPS de tracking_data_core para los últimos 7 días. Cada punto GPS tiene una marca de tiempo, latitud y longitud. Para cada vehículo y cada día, nosotros:

1. Ordenamos los puntos GPS cronológicamente por dispositivo.

2. Calculamos la distancia entre puntos consecutivos usando la fórmula de Haversine.

3. Sumamos las distancias por día por dispositivo para obtener el kilometraje total.

Este enfoque proporciona alta precisión sin depender de sensores de odómetro externos. 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,
        -- Aproximación de la fórmula de Haversine en 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 -- excluir valores atípicos
)
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 completo de todos los eventos relacionados con el vehículo (p. ej., encendido, puerta abierta, frenado brusco, etc.) en toda la flota. Incluye tipo de evento, marca de tiempo, y contexto del vehículo, permitiendo a los equipos de operaciones auditar el comportamiento, rastrear actividad anómala 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 de tiempo), state_name (etiqueta del evento) y value (estado o medición).

Para crear un informe utilizable:

1. Extraemos todos los registros de los últimos 7 días.

2. 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ó.

3. Enriquecer los resultados con metadatos del vehículo (vehicle_label, registration_number, object_label) vía objects y vehicles.

Esto ofrece una línea de tiempo de eventos diaria en toda la flota - esencial para diagnóstico, 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;