> For the complete documentation index, see [llms.txt](https://navixy.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://navixy.com/docs/analytics/es/example-queries/logistics.md).

# Logística

Plantillas de consultas SQL para análisis de logística y transporte, que abarcan optimización de rutas, monitoreo de carga, utilización de la flota y desempeño de las entregas

{% hint style="warning" %}
Activar **IoT Query** Antes de utilizar los datos para crear analíticas integrales. Si todavía no lo tiene, contáctenos para obtener detalles de activación - <iotquery@navixy.com>
{% endhint %}

La logística es un ecosistema complejo que involucra la coordinación del transporte, las operaciones de almacén, el inventario y la ejecución de entregas. Integrar la telemática en los procesos logísticos permite a las empresas recopilar datos en tiempo real sobre Gestión de vehículos, Conductores, rutas y condiciones de la carga, lo que mejora significativamente la toma de decisiones y la eficiencia operativa.

Navixy **IoT Query**, 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 del sensor, la geocerca y las analíticas 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**                | Optimice la asignación de rutas de los vehículos, garantice un despacho eficiente y reduzca los retrasos   | Cantidad de recorridos por día Kilometraje por vehículo por día (últimos 7 días)                                                                                                |
| **Monitoreo de carga**              | Asegure condiciones de transporte adecuadas para mercancías sensibles                                      | Eventos de incumplimiento de temperatura (y humedad) en los últimos 7 días                                                                                                      |
| **Operación del vehículo**          | Haga seguimiento a la utilización de la flota, asegure el mantenimiento y reduzca 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 rutas** | 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 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 posterior a la entrega** | Evaluar la eficiencia operativa y el desempeño histórico                                                   | Reporte de registro de eventos del vehículo Recuento de kilometraje por vehículo por día (últimos 7 días) Recuentos de recorridos por día Seguimiento de activos sin movimiento |

## **Seguimiento de activos sin movimiento** <a href="#asset-tracking-without-movement" id="asset-tracking-without-movement"></a>

Este caso identifica activos (p. ej., vehículos o remolques) que no han cambiado su GPS compare el **coordenadas mínimas y máximas** durante el período. Si ambos valores caen 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 Gestión de vehículos en raw\_business\_data para recuperar etiquetas significativas del Activo para la salida de resultados.

{% code expandable="true" %}

```sql
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'
    AGRUPAR POR 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
		O
      	(max_lon - min_lon) <= 1000)  -- ~10 metros
    		)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    o.objeto_id,
    o.object_label,
    sd.device_id,
    gb.min_lat / 1e7 AS latitude,
    gb.min_lon / 1e7 AS longitud,
    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;
```

{% endcode %}

## **Análisis del tiempo de inactividad del vehículo** <a href="#vehicle-downtime-analysis" id="vehicle-downtime-analysis"></a>

Este caso se centra en analizar durante cuánto tiempo la Gestión de vehículos permanece fuera de servicio 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 general y la eficiencia de Programación.

El núcleo del análisis de tiempo de inactividad se basa en aprovechar la tabla vehicle\_service\_tasks de raw\_business\_data, que registra ambos **eventos de Mantenimiento planificados y no planificados**. Cada tarea contiene un start\_date y un end\_date, que representan el período de inactividad. Al filtrar por **Tareas de servicio completadas**, podemos calcular la duración exacta que 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 desglosarlo entre Mantenimiento planificado y no planificado mediante el indicador is\_unplanned. Para hacer los resultados más accionables, lo vincula con la tabla de Gestión de vehículos para incluir etiquetas del vehículo, números de registro e información del modelo.

{% code expandable="true" %}

```sql
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_tareas 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
UNIR raw_business_data.Gestión de vehículos v EN v.vehicle_id = dd.vehicle_id
Grupo por v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;
```

{% endcode %}

## **Detección de desviación de la Ruta** <a href="#route-deviation-detection" id="route-deviation-detection"></a>

Este caso identifica instancias en las que la Gestión de vehículos se desvía de sus rutas asignadas o esperadas — en particular, zonas geocercadas o corredores de entrega. El Seguimiento de estas desviaciones ayuda a garantizar el cumplimiento de la Ruta, reducir retrasos, detectar comportamiento de Manejo riesgoso y mantener los SLA de entrega.

Esta lógica compara las posiciones GPS reales del vehículo provenientes de tracking\_data\_core (en el esquema raw\_telematics\_data) con 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 mediante ST\_DWithin, determinamos si un punto está dentro o fuera del área de Ruta en búfer.

La consulta une cada posición GPS con cada zona de Ruta conocida usando un criterio espacial **CROSS JOIN**, luego aplica ST\_DWithin() para verificar si el vehículo se encontraba dentro del corredor permitido. Aislamos las filas en las que el vehículo estaba **fuera de todas las rutas geocercadas** y marcarlos como desviaciones. El resultado final enumera estas desviaciones, incluyendo el dispositivo, la marca de tiempo, la etiqueta del vehículo y a qué distancia estaba el punto del centro de la zona más cercana.

{% code expandable="true" %}

```sql
WITH positions AS (
    SELECT
        td.device_id,
        td.device_time,
        o.objeto_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 ruta_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'
),
evaluado COMO (
    SELECT
        device_id,
        hora del dispositivo,
        objeto_id,
        etiqueta_del_objeto,
        id de la zona,
        etiqueta de zona,
        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,
    etiqueta_del_objeto,
    etiqueta de zona,
    hora del dispositivo,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;
```

{% endcode %}

## **Resumen de Horas de motor por vehículo / Conductor / día (Últimos 7 días)** <a href="#engine-hours-summary-per-vehicle-driver-day-last-7-days" id="engine-hours-summary-per-vehicle-driver-day-last-7-days"></a>

Este caso mide cuánto tiempo estuvieron activos los motores de cada vehículo en forma diaria, lo que permite a los administradores de flota dar seguimiento **utilización**, identifique **sobreuso o subuso**, y correlacione la actividad con las asignaciones de conductor. Cuando está vinculado a conductores, también admite **validación de horas de trabajo** y **análisis de rendimiento**.

La tabla de estados en raw\_telematics\_data registra **indicadores de estado del motor de series temporales**, generalmente con un state\_name como 'Ignición' y un value 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 en las que el motor estaba encendido (1).

Vincular la actividad del motor a ambos **Gestión de vehículos y Conductores**, usamos las tablas de Objeto, Gestión de vehículos y driver\_history de raw\_business\_data. Asociamos cada registro de estado al Conductor actual en ese Objeto (mediante el historial de asignación del conductor) y al vehículo correspondiente. Luego, hacemos un Grupo de los datos por día, vehículo y Conductor, sumando el tiempo total activo del motor (en horas).

{% code expandable="true" %}

```sql
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  -- transitions from ON to OFF
),
enriched_with_objects AS (
   SELECT
       eop.*,
       o.objeto_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.conductor_history d
       DONDE d.object_id = ewo.object_id
         AND d.changed_datetime <= ewo.engine_on_time
       ORDER BY d.changed_datetime DESC
       LIMIT 1
   ) dh activado verdadero
)
SELECT
   día de actividad,
   etiqueta del vehículo,
   número de matrícula,
   etiqueta_del_objeto,
   e.first_name || ' ' || e.last_name AS nombre_del_conductor,
   SUM(engine_hours) AS total_engine_hours
DESDE assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
Grupo por activity_day, vehicle_label, registration_number, Objeto_label, Conductor_name
ORDER BY activity_day DESC, vehicle_label;

```

{% endcode %}

## **Eventos de incumplimiento de temperatura (y humedad) en los últimos 7 días** <a href="#temperature-and-humidity-violation-events-in-the-last-7-days" id="temperature-and-humidity-violation-events-in-the-last-7-days"></a>

Este caso identifica lecturas de sensores — como **temperatura o humedad** — que superan umbrales críticos durante el transporte. Monitorear estas infracciones es vital para las industrias que transportan bienes perecederos (p. ej., 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 del sensor** 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 con los umbrales** (p. ej., >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, hacemos una unión con la tabla objects para recuperar etiquetas de vehículo o Activo, lo que mejora la interpretabilidad para los administradores de flota.

{% code expandable="true" %}

```sql
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.Gestión de vehículos v
  ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

```

{% endcode %}

## **Paradas no autorizadas (últimas 24 horas)** <a href="#unauthorized-stops-last-24-hours" id="unauthorized-stops-last-24-hours"></a>

Este caso identifica **Paradas no autorizadas o no planificadas** realizado por Gestión de vehículos en las últimas 24 horas. Ayuda a detectar posibles infracciones de las rutas de entrega, pausas no autorizadas o tiempo en ralentí que puede afectar la eficiencia del Combustible y el rendimiento del SLA.

La consulta analiza **puntos de ubicación con velocidad baja o nula** usando el The la consulta usa la tabla tracking\_data\_core de raw\_telematics\_data para extraer datos de ubicación en series de tiempo y velocidad. Se detecta una parada cuando **la velocidad cae por debajo de 3 km/h** por una duración 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 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 de zonas) usando ST\_DWithin de PostGIS. Solo las paradas** fuera de cualquier zona de amortiguamiento **fuera de cualquier zona de amortiguamiento** se reportan. El resultado incluye ID del vehículo, etiqueta del Objeto, matrícula, marcas de tiempo, duración y coordenadas de cada parada.

{% code expandable="true" %}

```sql
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
),
Paradas_marcadas 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
    DE Paradas_marcadas
    WHERE stop_start = 1
),
paradas_no_autorizadas 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
    DESDE Paradas no autorizadas nosotros
    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
    etiqueta del vehículo,
    número de matrícula,
    etiqueta_del_objeto,
    stop_start_time,
    hora_de_fin_de_parada,
    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;
```

{% endcode %}

## **Detección de uso fuera del horario laboral** <a href="#off-hour-usage-detection" id="off-hour-usage-detection"></a>

Este caso identifica instancias en las que se opera la Gestión de vehículos **fuera del horario laboral normal** — definido aquí como **lunes a viernes, 09:00–18:00**. Tales detecciones son esenciales para señalar **uso no autorizado**, identificando potencial **uso indebido del vehículo**, y mejorando **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 local **día de la semana** y **hora de uso** de cada entrada de device\_time y filtre los registros **fuera del horario de negocio definido** (es decir, antes de las 9 AM, después de las 6 PM o en cualquier momento durante los fines de semana).

Para dar claridad, enriquecemos los datos de GPS con metadatos de objeto y vehículo de raw\_business\_data (por ejemplo, etiqueta del vehículo, matrícula, ID de objeto). Para obtener resúmenes más significativos, opcionalmente agregamos el uso para contar **cuántos eventos fuera del horario ocurrieron** por vehículo y cuándo ocurrieron. Esto puede ayudar a identificar patrones o infractores reincidentes.

{% code expandable="true" %}

```sql
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 nombre_del_conductor,
        o.device_id,
        o.objeto_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
    etiqueta_del_objeto,
    etiqueta del vehículo,
    número de matrícula,
    nombre_del_conductor,
    hora del dispositivo,
    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;
```

{% endcode %}

## **Conteos de recorridos por día** <a href="#trip-counts-per-day" id="trip-counts-per-day"></a>

Este caso mide **cuántos viajes** cuánto completa cada vehículo al día y qué distancia recorre, lo que ayuda a los equipos de logística a evaluar **uso del vehículo**, optimice rutas y detecte anomalías como Viajes incompletos o uso no reportado.

Para definir un **Recorrido**, usamos un cambio en **estado de movimiento del vehículo** — es decir, transición de Detenido a En movimiento y de regreso a Detenido. Usando los valores de velocidad de la tabla tracking\_data\_core, la consulta segmenta los datos con base en estas transiciones. Un Recorrido se identifica como un **período continuo de movimiento** donde la velocidad se mantiene por encima de un umbral (p. ej., >5 km/h).

Cada Recorrido 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 conteo de Recorrido y la distancia total por día por vehículo, opcionalmente enriquecidos con etiquetas de vehículo de la tabla de Gestión de vehículos.

{% code expandable="true" %}

```sql
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'
),
segmentos_de_recorrido 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'
        FINALIZA como Recorrido_marker
    FROM base_points
),
recorrido_points AS (
    SELECT
        device_id,
        device_time AS hora_de_inicio_del_recorrido,
        lat AS start_lat,
        lon AS start_lon,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS hora_fin_recorrido,
        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
    DESDE segmentos_de_recorrido
    WHERE Recorrido_marker = 'start'
),
recorrido_metrics AS (
    SELECT
        tp.device_id,
        tp.trip_start_time,
        tp.Recorrido_end_time,
        tp.start_lat,
        tp.start_lon,
        tp.end_lat,
        tp.end_lon,
        tp.trip_start_time::date AS trip_day,
        -- Distancia aproximada mediante 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 puntos_de_recorrido tp
    WHERE tp.trip_end_time IS NOT NULL
)
SELECT
    v.vehicle_label,
    v.registration_number,
    tm.device_id,
    tm.recorrido_day,
    COUNT(*) AS recorrido_count,
    ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM Recorrido_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
Grupo BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;
```

{% endcode %}

## **Conteo de kilometraje por vehículo por día (Últimos 7 días)** <a href="#mileage-count-per-vehicle-per-day-last-7-days" id="mileage-count-per-vehicle-per-day-last-7-days"></a>

Este caso calcula el **kilometraje diario** (en kilómetros) para cada vehículo durante los últimos 7 días. Es fundamental para el seguimiento **utilización del vehículo**, monitoreo **eficiencia de combustible**, planificación **Mantenimiento**, y detectar el uso insuficiente o excesivo.

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

1. **Ordenar los puntos GPS cronológicamente** por dispositivo.
2. **Calcular la distancia entre puntos consecutivos** usando la fórmula de Haversine.
3. **Sumar las distancias por día y por dispositivo** para obtener el kilometraje total.

Este enfoque proporciona alta precisión sin depender de sensores externos de Odómetro. Opcionalmente, la consulta se une con objetos y Gestión de vehículos para enriquecer los resultados con metadatos del Activo.

{% code expandable="true" %}

```sql
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'
),
distancias AS (
    SELECT
        device_id,
        día_del_recorrido,
        -- 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.Recorrido_day,
    ROUND(SUM(d.segment_distance_km)::numeric, 2) AS Kilometraje_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
ORDENAR POR d.trip_day DESC, v.vehicle_label;
```

{% endcode %}

## **Reporte de registro de eventos del vehículo** <a href="#vehicle-event-log-report" id="vehicle-event-log-report"></a>

Este caso proporciona un reporte completo de todo **eventos relacionados con vehículos** (p. ej., ignición, 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 generar alertas y analítica.

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 reporte útil:

1. Extraemos todos los registros de los últimos 7 días.
2. Agrúpelos por **tipo de evento**, **vehículo**, y **fecha** para proporcionar un **conteo de cuántas veces ocurrió cada evento** y **cuando ocurrió**.
3. Enriquezca los resultados con metadatos del vehículo (vehicle\_label, registration\_number, object\_label) a través de objetos y Gestión de vehículos.

Esto proporciona un **Tiempo de eventos diarios** en toda la Flota - esencial para el diagnóstico, el análisis del comportamiento y el Mantenimiento proactivo.

{% code expandable="true" %}

```sql
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.etiqueta_del_objeto
    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
        día del evento,
        state_name,
        etiqueta del vehículo,
        número de matrícula,
        etiqueta_del_objeto,
        COUNT(*) AS event_count,
        MIN(device_time) AS first_occurred,
        MAX(device_time) AS last_occurred
    DESDE with_vehicle_info
    Grupo por event_day, state_name, vehicle_label, registration_number, Objeto_label
)
SELECT
    día del evento,
    etiqueta del vehículo,
    número de matrícula,
    etiqueta_del_objeto,
    state_name AS event_type,
    conteo de eventos,
    primera_ocurrencia,
    última ocurrencia
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;
```

{% endcode %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://navixy.com/docs/analytics/es/example-queries/logistics.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.