Logística

Caso práctico de logística y libro de recetas SQL

Habilitar Consulta IoT antes de utilizar los datos para construir análisis completos. Si aún no lo tiene, contáctenos para obtener detalles de activación - [e-mail]

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 entregas. La integración de 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 sólidas capacidades de ingesta 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 ingesta de telemática proporcionan una visibilidad integral de estas operaciones. Los datos GPS en tiempo real, los diagnósticos de datos de sensores, la geocercas y el análisis de sensores permiten a los operadores logísticos digitalizar los flujos de trabajo, automatizar los controles y tomar decisiones informadas.

Fase del ciclo de vida	Objetivos	Casos de uso / recetas cubiertos
Gestión de rutas	Optimizar la planificación de rutas de los vehículos, garantizar una asignación eficiente y reducir los retrasos	Conteo de viajes por día Conteo de kilometraje por vehículo y por día (últimos 7 días)
Monitoreo de 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	Realizar el seguimiento de la utilización de la flota, garantizar 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 del tiempo de inactividad del vehículo Seguimiento de activos sin movimiento
Seguridad y protección de la ruta	Detectar uso indebido, actividad no autorizada e infracciones de seguridad	Detección de desvíos de ruta - Paradas no autorizadas (últimas 24 horas) Detección de uso fuera de horario
Gestión del cumplimiento	Supervisar el comportamiento del conductor, hacer cumplir las 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 posentrega	Evaluar la eficiencia operativa y el rendimiento histórico	Informe de registro de eventos del vehículo Conteo de kilometraje por vehículo y por día (últimos 7 días) Conteo de viajes por día Seguimiento de activos sin movimiento

Seguimiento de activos sin movimiento

Este caso identifica los activos (por ejemplo, vehículos o remolques) que no han cambiado su GPS compare the coordenadas mínimas y máximas durante el período. Si ambos valores se mantienen dentro de un rango muy estrecho (un umbral de tolerancia, por ejemplo, ±0.01 grados), marcamos el activo como inmóvil. 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 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 del tiempo de inactividad del vehículo

Este caso se centra en analizar cuánto tiempo los vehículos no están operativos debido al mantenimiento, averías o inactividad. Las métricas de inactividad son cruciales para las operaciones logísticas, ya que permiten supervisar el estado de la flota, reducir el tiempo ocioso y mejorar la utilización general y la eficiencia de la programación.

El núcleo del análisis del 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 período de inactividad. Al filtrar por tareas de servicio completadas, podemos calcular la duración exacta de cada período en que el 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 desglosarlo entre mantenimiento planificado y no planificado mediante el indicador is_unplanned. Para que los resultados sean más accionables, se une con la tabla vehicles para incluir las etiquetas del vehículo, los números de registro y la 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 desvío de ruta

Este caso identifica situaciones en las que los vehículos se desvían de sus rutas asignadas o previstas, especialmente zonas geocercadas o corredores de entrega. El seguimiento de estas desviaciones ayuda a garantizar el cumplimiento de la ruta, reducir los retrasos, detectar comportamientos de conducción de riesgo 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 las zonas geográficas predefinidas de la tabla zones en raw_business_data. Estas zonas representan rutas asignadas o segmentos de ruta. Mediante comparaciones geométricas con ST_DWithin, determinamos si un punto está dentro o fuera del área de la ruta con buffer.

La consulta une cada posición GPS con cada zona de ruta conocida mediante un CROSS JOIN, luego aplica ST_DWithin() para verificar si el vehículo estaba dentro del corredor permitido. Aislamos las filas en las que el vehículo estaba fuera de todas las rutas geocercadas y las marcamos como desviaciones. La salida final enumera estas desviaciones, incluyendo el dispositivo, la marca de tiempo, la etiqueta del vehículo y la distancia del punto respecto al 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 estuvieron activos los motores de cada vehículo de forma diaria, lo que permite a los administradores de flotas supervisar la utilización, identificar el uso excesivo o insuficiente, y correlacionar la actividad con las asignaciones de conductores. Cuando se vincula con los conductores, también respalda la validación de horas laboradas y el análisis del rendimiento.

La tabla states en raw_telematics_data registra indicadores de estado del motor en series temporales, normalmente 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 de cada dispositivo y calculamos las duraciones en las que el motor estuvo encendido (1).

Para vincular la actividad del motor tanto con vehículos como con conductores, utilizamos las tablas objects, vehicles y driver_history de raw_business_data. Asociamos cada registro de estado con el conductor actual de ese objeto (mediante 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 superan umbrales críticos durante el transporte. Supervisar estas violaciones es vital para las industrias que transportan bienes perecederos (por ejemplo, alimentos, productos farmacéuticos) para garantizar el cumplimiento de los requisitos de la cadena de frío y evitar 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 (por ejemplo, temperatura, humedad) registrada en una marca de tiempo específica por un dispositivo. Filtramos estos registros para incluir solo los de los últimos 7 días.

La lógica principal de filtrado se basa en patrones del nombre del sensor y una comparación de sus valores numéricos contra umbrales (por ejemplo, >25 °C para temperatura, >80 % para humedad). Como 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 las etiquetas del vehículo o del activo, lo que mejora la interpretabilidad para los administradores de flotas.

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 'High Temperature'
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           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 infracciones de las rutas de entrega, descansos no autorizados o tiempos ociosos que pueden afectar la eficiencia del combustible y el rendimiento de los SLA.

La consulta analiza puntos de ubicación con velocidad baja o nula using the The query uses the tracking_data_core table from raw_telematics_data to extract time-series location data and speed. A stop is detected when la velocidad cae por debajo de 3 km/h durante un período de más de 2 minutos. Utilizando las funciones LAG y LEAD, la consulta segmenta estos períodos de baja velocidad para determinar las marcas de tiempo de inicio y fin de la parada.

Para detectar paradas no autorizadas, filtra las ubicaciones que caen dentro de zonas geocercadas conocidas (tabla zones) utilizando ST_DWithin de PostGIS. Solo se informan las paradas fuera de cualquier buffer de zona . El resultado incluye el ID del vehículo, la etiqueta del objeto, la matrícula, las marcas de tiempo, la duración y las 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 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 situaciones en las que los vehículos se operan fuera del horario laboral normal — definido aquí como de lunes a viernes, de 09:00 a 18:00. Estas detecciones son esenciales para señalar el uso no autorizado, identificar posibles abusos del vehículo, y mejorar la seguridad de los activos.

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 local 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 el fin de semana).

Para aportar claridad, enriquecemos los datos GPS con metadatos de objetos y vehículos de raw_business_data (por ejemplo, etiqueta del vehículo, matrícula, ID del objeto). Para obtener resúmenes más significativos, podemos agregar opcionalmente el uso para contar cuántos eventos fuera de horario se produjeron por vehículo y cuándo ocurrieron. 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;

Conteo de viajes por día

Este caso mide cuántos viajes completa cada vehículo diariamente y cuánta distancia recorren, lo que ayuda a los equipos de logística a evaluar el uso del vehículo, optimizar rutas y detectar anomalías como viajes incompletos o uso no reportado.

Para definir un viaje, utilizamos 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 función de estas transiciones. Se identifica un viaje como un período continuo de movimiento en el que la velocidad se mantiene por encima de un umbral (por ejemplo, >5 km/h).

Cada viaje incluye:

• Un 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)

• La distancia de Haversine entre las ubicaciones de inicio y fin

Calculamos el número de viajes y la distancia total por día y por vehículo, opcionalmente enriquecidos con etiquetas de vehículo de la tabla de vehículos.

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;

Conteo de kilometraje por vehículo por día (últimos 7 días)

Este caso calcula el kilometraje diario (en kilómetros) de 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 de combustible, la planificación del 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 de tiempo, latitud y longitud. Para cada vehículo y cada día:

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 y 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,
        -- 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 vehículos (por ejemplo, encendido, puerta abierta, frenado brusco, etc.) en toda la flota. Incluye tipo de evento, marca de tiempo, y contexto del vehículo, lo que permite a los equipos de operaciones auditar el comportamiento, rastrear actividad anómala o impulsar alertas y analíticas.

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. Agruparlos por tipo de evento, vehículo, y fecha para proporcionar un conteo 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) a través de objects y vehicles.

Esto proporciona una cronología diaria de eventos 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;