Logística

Modelos de consulta SQL para análises de logística e transporte, cobrindo otimização de Rota, monitoramento de carga, utilização da Frota e desempenho de entrega

Ativar IoT Query antes de utilizar os dados para criar análises abrangentes. Se você ainda não o tiver, entre em contato conosco para obter detalhes de ativação - [email protected]

A logística é um ecossistema complexo que envolve a coordenação de transporte, operações de armazém, estoque e execução de entregas. A integração da telemetria aos processos logísticos permite que as empresas coletem dados em tempo real sobre Veículos, Motoristas, rotas e condições da carga, o que melhora significativamente a tomada de decisões e a eficiência operacional.

Navixy IoT Query, com seus robustos recursos de ingestão de dados e análise de séries temporais, oferece suporte à transformação digital das operações logísticas, ao permitir ampla visibilidade em cada estágio do ciclo de vida. Seus robustos recursos de ingestão de telemetria fornecem visibilidade abrangente dessas operações. Dados de GPS em tempo real, dados de sensores, diagnósticos, geocercas e análises de sensores permitem que os operadores logísticos digitalizem fluxos de trabalho, automatizem controles e tomem decisões informadas.

Fase do ciclo de vida	Objetivos	Casos de uso / receitas cobertos
Gestão de Rota	Otimize a roteirização de veículos, garanta um despacho eficiente e reduza atrasos	Contagem de Trajetos por dia Contagem de Quilometragem por veículo por dia (Últimos 7 dias)
Monitoramento de carga	Garanta condições adequadas de transporte para mercadorias sensíveis	Eventos de violação de temperatura (e umidade) nos últimos 7 dias
Operação do veículo	Acompanhe a utilização da Frota, garanta a Manutenção e reduza o tempo de inatividade	Resumo de Horas de motor por veículo / Motorista / dia (Últimos 7 dias) Análise de inatividade do veículo Monitor de Ativo sem Movimento
Segurança e Segurança da Rota	Detectar uso indevido, atividade não autorizada e violações de Segurança	Detecção de desvio de Rota- Paradas não autorizadas (Últimas 24 horas) Detecção de uso fora do horário
Gestão de conformidade	Monitorar o comportamento do Motorista, aplicar políticas e garantir a conformidade operacional	Resumo das Horas de motor por Veículo / Motorista / Dia (Últimos 7 dias) Detecção de uso fora do horário
Análise pós-entrega	Avaliar a eficiência operacional e o desempenho histórico	Relatório de log de eventos do veículo Contagem de Quilometragem por Veículo por Dia (Últimos 7 dias) Contagens de Trajeto por Dia Monitor de Ativo Sem Movimento

Monitor de Ativo Sem Movimento

Este caso identifica ativos (por exemplo, Veículos ou reboques) que não mudaram seu GPS compare o coordenadas mínimas e máximas durante o período. Se ambos os valores caírem em uma faixa muito estreita (um limite de tolerância, por exemplo, ±0.01 graus), sinalizamos o Ativo como não em movimento. A consulta também faz join com as tabelas objects e Veículos em raw_business_data para recuperar rótulos relevantes de Ativo para a saída do 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'
    Grupo BY td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    FROM gps_bounds
    WHERE location_records > 10 -- excluir dispositivos com dados muito esparsos
	AND((max_lat - min_lat) <= 2000 -- ~10 metros
		OU
      	(max_lon - min_lon) <= 1000)  -- ~10 meters
    		)
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álise do tempo de inatividade do veículo

Este caso se concentra em analisar por quanto tempo os Veículos ficam fora de operação devido à Manutenção, a quebras ou à inatividade. As métricas de tempo de inatividade são cruciais para as operações de logística monitorarem a saúde da Frota, reduzirem o tempo ocioso e melhorarem a utilização geral e a eficiência de Agendamento.

O núcleo da análise de indisponibilidade depende de aproveitar a tabela vehicle_service_tasks a partir de raw_business_data, que registra tanto eventos de manutenção planejados e não planejados. Cada Tarefa contém um start_date e um end_date, representando o período de inatividade. Ao filtrar por tarefas de serviço concluídas, podemos calcular a duração exata em que cada veículo ficou fora de operação.

A consulta calcula o tempo total de inatividade por veículo somando as durações de todas as suas Tarefas de serviço (em horas). Ela também permite a discriminação entre Manutenção planejada e não planejada usando o sinalizador is_unplanned. Para tornar os resultados mais práticos, ela faz uma junção com a tabela de Veículos para incluir rótulos dos veículos, números de registro e informações do 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.Veículos 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;

Detecção de desvio de Rota

Este caso identifica instâncias em que Veículos se desviam de suas Rotas atribuídas ou esperadas — especialmente zonas geocercadas ou corredores de entrega. O Monitor de tais desvios ajuda a garantir a conformidade com a Rota, reduzir atrasos, detectar comportamento de Condução arriscada e manter os SLAs de entrega.

Esta lógica compara as posições GPS reais do veículo do tracking_data_core (no schema raw_telematics_data) com zonas geográficas predefinidas da tabela zones em raw_business_data. Essas zonas representam Rotas atribuídas ou segmentos de Rota. Usando comparações geométricas via ST_DWithin, determinamos se um ponto está dentro ou fora da área de rota com buffer.

A consulta une cada posição de GPS com cada zona de Rota conhecida usando uma espacial CROSS JOIN, então aplica ST_DWithin() para verificar se o veículo estava dentro do corredor permitido. Isolamos as linhas em que o veículo estava fora de todas as rotas geocercadas e marcá-los como desvios. A saída final lista esses desvios, incluindo o dispositivo, o carimbo de data/hora, o rótulo do veículo e o quão longe o ponto estava do centro da zona mais próximo.

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'
),
avaliado AS (
    SELECT
        device_id,
        device_time,
        object_id,
        Objeto_label,
        zone_id,
        rótulo da zona,
        NOT ST_DWithin(gps_point, route_buffer, 0) AS is_deviation,
        ST_Distance(gps_point, Rota_buffer) AS deviation_distance_meters
    FROM positions
),
deviations_only AS (
    SELECT *
    FROM evaluated
    WHERE is_deviation = true
)
SELECT
    device_id,
    Objeto_label,
    rótulo da zona,
    device_time,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;

Resumo de Horas de motor por veículo / Motorista / dia (últimos 7 dias)

Este caso mede por quanto tempo os motores estiveram ativos para cada veículo em uma base diária, permitindo que os gerentes de frota acompanhem utilização, identificar uso excessivo ou insuficiente, e correlacionar a atividade com as atribuições do Motorista. Quando vinculada a Motoristas, também oferece suporte a validação de horas de trabalho e análise de desempenho.

A tabela states em raw_telematics_data registra indicadores de estado do motor em séries temporais, normalmente com um state_name como 'Ignição' e um valor de 1 (ligado) ou 0 (desligado). Para calcular Horas de motor, encontramos todas as transições com carimbo de data/hora para cada dispositivo e calculamos as durações em que o motor estava ligado (1).

Para vincular a atividade do motor a ambos veículos e motoristas, nós usamos as tabelas Objeto, Veículos e driver_history de raw_business_data. Associamos cada registro de estado ao Motorista atual nesse Objeto (via histórico de atribuição do Motorista) e ao veículo correspondente. Em seguida, agrupamos os dados por dia, veículo e Motorista, somando o tempo total ativo do motor (em 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  -- transitions from ON to OFF
),
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,
   Objeto_label,
   e.first_name || ' ' || e.last_name AS motorista_name,
   SUM(engine_hours) AS total_engine_hours
DE assigned_Motoristas 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 violação de temperatura (e umidade) nos últimos 7 dias

Este caso identifica leituras de sensores — como temperatura ou umidade — que excedem limites críticos durante o transporte. Monitorar essas violações é vital para indústrias que transportam produtos perecíveis (por exemplo, alimentos, produtos farmacêuticos) para garantir a conformidade com os requisitos da cadeia de frio e evitar a deterioração.

Esta consulta extrai dados de entrada do sensor da tabela inputs no esquema raw_telematics_data. Cada linha representa uma leitura de sensor (por exemplo, temperatura, umidade) registrada em um carimbo de data/hora específico por um dispositivo. Filtramos esses registros para incluir apenas os dos últimos 7 dias.

A lógica principal de filtragem se baseia em padrões de nomes de sensores e em uma comparação de seus valores numéricos em relação aos limites (por exemplo, >25°C para temperatura, >80% para umidade). Como value é armazenado como texto, fazemos o cast para numérico antes de aplicar as condições de limite. Para enriquecer os resultados, fazemos join com a tabela objects para recuperar rótulos de veículo ou de Ativo, o que melhora a interpretabilidade para gestores de Frota.

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.Veículos v
  ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

Paradas não autorizadas (Últimas 24 horas)

Este caso identifica paradas não autorizadas ou não planejadas feitas por Veículos nas últimas 24 horas. Isso ajuda a detectar possíveis violações de rotas de entrega, paradas não autorizadas ou tempo ocioso que podem afetar a eficiência de Combustível e o desempenho do SLA.

A consulta analisa pontos de localização com velocidade baixa ou nula utilizando a consulta, a consulta usa a tabela tracking_data_core do raw_telematics_data para extrair dados de localização em série temporal e velocidade. Uma parada é detectada quando a velocidade cai abaixo de 3 km/h por uma duração de mais de 2 minutos. Usando as funções LAG e LEAD, a consulta segmenta esses períodos de baixa velocidade para determinar os timestamps de início e fim das Paradas.

Para detectar Paradas não autorizadas, ele filtra locais que estão dentro de zonas conhecidas geocercadas (zones table) usando ST_DWithin do PostGIS. Apenas Paradas fora de qualquer buffer da zona são informados. O resultado inclui ID do veículo, rótulo do Objeto, registro, carimbos de data/hora, duração e 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
),
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 não 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  -- not inside any known zone
      AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- minimum 2 minutes
),
with_metadata AS (
    SELECT
        us.*,
        o.object_label,
        v.vehicle_label,
        v.registration_number
    DE unauthorized_stops EUA
    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,
    Objeto_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;

Detecção de uso fora do horário comercial

Este caso identifica instâncias em que Veículos são operados fora do horário comercial normal — definido aqui como segunda-feira a sexta-feira, 09:00–18:00. Essas detecções são essenciais para sinalizar uso não autorizado, identificando potencial uso indevido do veículo, e melhorando segurança do ativo.

A lógica é construída na tabela tracking_data_core do raw_telematics_data, que registra eventos GPS com carimbo de data e hora por dispositivo. Derivamos local dia da semana e hora de uso de cada entrada device_time e filtre os registros fora da janela comercial definida (isto é, antes de 9 AM, depois de 6 PM ou a qualquer momento nos fins de semana).

Para fornecer clareza, enriquecemos os dados de GPS com metadados de objeto e veículo de raw_business_data (por exemplo, rótulo do veículo, placa, ID do objeto). Para resumos mais significativos, agregamos opcionalmente o uso para contar quantos eventos fora do horário comercial ocorreram por veículo e quando aconteceram. Isso pode ajudar a identificar padrões ou reincidentes.

WITH gps_events AS (
    SELECT
        td.device_id,
        td.device_time,
        EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Sunday, 6=Saturday
        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 ou 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 motorista_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.motorista_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
    Objeto_label,
    vehicle_label,
    registration_number,
    nome do motorista,
    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;

Contagem de Trajetos por dia

Este caso mede quantas Viagens quanto cada veículo percorre diariamente e a distância que eles percorrem, ajudando as equipes de logística a avaliar uso do veículo, otimize rotas e detecte anomalias como Viagens incompletas ou uso não reportado.

Para definir uma Trajeto, usamos uma alteração em estado de Movimento do veículo — ou seja, transição de Parado para Em movimento e de volta a Parado. Usando os valores de velocidade da tabela tracking_data_core, a consulta segmenta os dados com base nessas transições. Um Trajeto é identificado como um período contínuo de Movimento onde a velocidade permanece acima de um limite (por exemplo, >5 km/h).

Cada Trajeto inclui:

• A timestamp e local de início (primeiro ponto em movimento)

• Um timestamp e local de término (último ponto em movimento antes de parar)

• O distância de Haversine entre os locais de início e término

Calculamos a contagem de trajetos e a distância total por dia por veículo, opcionalmente enriquecida com rótulos de veículo da tabela Veí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 Trajeto_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'
),
Trajeto_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,
        -- Distância aproximada usando a fórmula de Haversine (em 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 pontos_de_trajeto 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;

Contagem de Quilometragem por veículo por dia (Últimos 7 dias)

Este caso calcula o quilometragem diária (em quilômetros) para cada veículo nos últimos 7 dias. É fundamental para Monitor utilização do veículo, monitoramento eficiência de Combustível, planejamento manutenção, e detectar subutilização ou superutilização.

Nós extraímos todos os registros de GPS de tracking_data_core dos últimos 7 dias. Cada ponto de GPS tem um carimbo de data e hora, latitude e longitude. Para cada veículo e cada dia, nós:

1. Ordene os pontos GPS cronologicamente por dispositivo.

2. Calcular a distância entre pontos consecutivos usando a fórmula de Haversine.

3. Some as distâncias por dia por dispositivo para obter a Quilometragem total.

Esta abordagem oferece alta precisão sem depender de sensores externos de Odômetro. Opcionalmente, a consulta faz junção com objetos e Veículos para enriquecer os resultados com metadados de Ativo.

WITH gps_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.device_time::date AS trajeto_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,
        trajeto_dia,
        -- Aproximação da fórmula de Haversine em 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 outliers
)
SELECT
    v.vehicle_label,
    v.registration_number,
    o.object_label,
    d.device_id,
    d.trip_day,
    ROUND(SUM(d.segment_distance_km)::numeric, 2) AS Quilometragem_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;

Relatório de log de eventos do veículo

Este caso fornece um relatório abrangente de todos os eventos relacionados a veículos (por exemplo, Ignição, porta aberta, Frenagem brusca etc.) em toda a Frota. Inclui tipo de evento, carimbo de data e hora, e contexto do veículo, permitindo que as equipes de operações auditem o comportamento, acompanhem atividades anormais ou acionem alertas e análises.

A fonte principal é a tabela states do esquema raw_telematics_data. Cada linha inclui: device_id (origem do evento), device_time (carimbo de data/hora), state_name (rótulo do evento) e value (status ou medição).

Para criar um relatório utilizável:

1. Extraímos todos os registros dos últimos 7 dias.

2. Agrupe-os por tipo de evento, veículo, e data para fornecer um contagem de quantas vezes cada evento ocorreu e quando ocorreu.

3. Enriqueça os resultados com metadados do veículo (vehicle_label, registration_number, object_label) via Objeto e Veículos.

Isso fornece um Tempo diário de eventos em toda a Frota - essencial para diagnósticos, análise comportamental e Manutenção proativa.

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,
        Objeto_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, Objeto_label
)
SELECT
    event_day,
    vehicle_label,
    registration_number,
    Objeto_label,
    state_name AS event_type,
    event_count,
    first_occurred,
    last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;