Logística
Estudo de caso de logística e livro de receitas SQL
Ative o DataHub antes de utilizar os dados para construir análises abrangentes. Se ainda não o possui, 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, inventário e execução de entregas. Integrar telemática aos processos logísticos permite que as empresas coletem dados em tempo real sobre veículos, motoristas, rotas e condições de carga, o que melhora significativamente a tomada de decisões e a eficiência operacional.
O Navixy DataHub, com suas robustas capacidades de ingestão de dados e análise de séries temporais, apoia a transformação digital das operações logísticas ao possibilitar uma visibilidade aprofundada em cada estágio do ciclo de vida. Suas robustas capacidades de ingestão telemática fornecem visibilidade abrangente dessas operações. Dados GPS em tempo real, diagnóstico de dados de sensores, geofencing e análise de sensores permitem que os operadores logísticos digitalizem fluxos de trabalho, automatizem controles e tomem decisões informadas.
Gerenciamento de rotas
Otimizar o roteamento de veículos, garantir despacho eficiente e reduzir atrasos
Contagem de viagens por dia Contagem de quilometragem por veículo por dia (Últimos 7 dias)
Monitoramento de carga
Garantir condições de transporte adequadas para mercadorias sensíveis
Eventos de violação de temperatura (e umidade) nos últimos 7 dias
Operação de veículo
Rastrear utilização da frota, garantir manutenção e reduzir tempo de inatividade
Resumo de horas do motor por veículo / motorista / dia (Últimos 7 dias) Análise de tempo de inatividade do veículo Rastreamento de ativo sem movimento
Segurança e integridade 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 comportamento do motorista, aplicar políticas e conformidade operacional
Resumo de horas do motor por veículo / motorista / dia (Últimos 7 dias) Detecção de uso fora do horário
Análise pós-entrega
Avaliar eficiência operacional e 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 viagens por dia Rastreamento de ativo sem movimento
Rastreamento de ativo sem movimento
Este caso identifica ativos (por exemplo, veículos ou reboques) que não alteraram seu GPS comparar o coordenadas mínima e máxima durante o período. Se ambos os valores estiverem dentro de uma faixa muito estreita (um limite de tolerância, por exemplo, ±0,01 graus), marcamos o ativo como sem movimento. A consulta também faz junção com as tabelas objects e vehicles em raw_business_data para recuperar rótulos de ativo significativos para a saída de 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 com dados muito esparsos
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álise de tempo de inatividade do veículo
Este caso foca em analisar por quanto tempo os veículos ficam inoperantes devido a manutenção, avarias ou inatividade. Métricas de tempo de inatividade são cruciais para operações logísticas para monitorar a saúde da frota, reduzir tempo ocioso e melhorar a utilização geral e a eficiência do agendamento.
O núcleo da análise de tempo de inatividade reside em aproveitar a tabela vehicle_service_tasks de raw_business_data, que registra tanto eventos de manutenção planejados quanto 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 esteve 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). Também permite a classificação por manutenção planejada vs não planejada usando o indicador is_unplanned. Para tornar os resultados mais acionáveis, ela faz junção com a tabela vehicles para incluir rótulos de veículos, números de registro e informações de 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;Detecção de desvio de rota
Este caso identifica instâncias em que veículos desviam de suas rotas atribuídas ou esperadas — particularmente zonas geofence ou corredores de entrega. Rastrear tais desvios ajuda a garantir conformidade de rota, reduzir atrasos, detectar comportamento de direção de risco e manter os SLAs de entrega.
Esta lógica compara as posições GPS reais do veículo de tracking_data_core (no schema raw_telematics_data) contra 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 com buffer da rota.
A consulta junta cada posição GPS com cada zona de rota conhecida usando um 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 geofence e as marcamos como desvios. A saída final lista esses desvios, incluindo o dispositivo, carimbo de data/hora, rótulo do veículo e quão distante o ponto estava do centro da zona mais próxima.
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;Resumo de horas do motor por veículo / motorista / dia (Últimos 7 dias)
Este caso mede por quanto tempo os motores estiveram ativos para cada veículo em base diária, permitindo que gestores de frota acompanhem a utilização, identifiquem uso excessivo ou subutilização, e correlacionem a atividade com as designações de motoristas. Quando vinculado a motoristas, também suporta validação de horas de trabalho e análise de desempenho.
A tabela states em raw_telematics_data registra indicadores de estado de motor em séries temporais, tipicamente com um state_name como 'ignition' e um valor de 1 (ligado) ou 0 (desligado). Para calcular horas do motor, encontramos todas as transições com carimbo de tempo para cada dispositivo e calculamos as durações em que o motor esteve ligado (1).
Para vincular a atividade do motor tanto a veículos quanto motoristas, usamos as tabelas objects, vehicles e driver_history de raw_business_data. Associamos cada registro de estado ao motorista atual naquele objeto (via histórico de atribuição de motoristas) e ao veículo correspondente. Em seguida, agrupamos os dados por dia, veículo e motorista, somando o tempo total de motor ativo (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 -- transições de LIGADO para DESLIGADO
),
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 violação de temperatura (e umidade) nos últimos 7 dias
Este caso identifica leituras de sensores — tais como temperatura ou umidade — que excedem limites críticos durante o transporte. Monitorar tais violações é vital para indústrias que transportam produtos perecíveis (por exemplo, alimentos, produtos farmacêuticos) para garantir conformidade com os requisitos da cadeia fria e evitar deterioração.
Esta consulta extrai dados de entrada de sensores da tabela inputs no schema 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 aqueles dos últimos 7 dias.
A lógica principal de filtragem é baseada em padrões de nome de sensor e uma comparação de seus valores numéricos contra limites (por exemplo, >25°C para temperatura, >80% para umidade). Como value é armazenado como texto, fazemos cast para numérico antes de aplicar as condições de limite. Para enriquecer os resultados, fazemos junção com a tabela objects para recuperar rótulos de veículo ou 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
FROM raw_telematics_data.inputs i
WHERE i.device_time >= now() - interval '1 hour'
),
calibration_data AS (
SELECT
sensor_id,
value AS cal_value,
volume AS cal_volume
FROM raw_business_data.sensor_calibration_data
),
sensor_meta AS (
SELECT
sd.device_id,
sd.input_label,
sd.sensor_id
FROM raw_business_data.sensor_description sd
),
joined_data AS (
SELECT
rsd.*,
sm.sensor_id
FROM recent_sensor_data rsd
LEFT JOIN sensor_meta sm
ON rsd.device_id = sm.device_id AND rsd.sensor_name = sm.input_label
),
calibrated_data AS (
SELECT
jd.device_id,
jd.device_time,
jd.sensor_name,
jd.value,
jd.sensor_id,
CASE
WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
CASE
WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
ELSE cd_low.cal_volume +
((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0))
* (cd_high.cal_volume - cd_low.cal_volume)
END
ELSE jd.value
END AS calibrated_value
FROM joined_data jd
LEFT JOIN LATERAL (
SELECT * FROM calibration_data
WHERE sensor_id = jd.sensor_id AND cal_value <= jd.value
ORDER BY cal_value DESC LIMIT 1
) cd_low ON TRUE
LEFT JOIN LATERAL (
SELECT * FROM calibration_data
WHERE sensor_id = jd.sensor_id AND cal_value >= jd.value
ORDER BY cal_value ASC LIMIT 1
) cd_high ON TRUE
),
violations AS (
SELECT
cd.device_id,
cd.device_time,
cd.sensor_name,
cd.calibrated_value,
CASE
WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
WHEN sd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
ELSE NULL
END AS violation_type
FROM calibrated_data cd
left join raw_business_data.sensor_description sd on sd.device_id = cd.device_id and sd.input_label = cd.sensor_name
WHERE sensor_type in ('temperature', 'humidity')
)
SELECT
v.vehicle_label,
o.object_label,
v.registration_number,
v.model,
vio.device_time,
vio.sensor_name,
vio.calibrated_value,
vio.violation_type
FROM violations vio
JOIN raw_business_data.objects o ON o.device_id = vio.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;
Paradas não autorizadas (Últimas 24 horas)
Este caso identifica paradas não autorizadas ou não planejadas realizadas por veículos nas últimas 24 horas. Auxilia na detecção de potenciais violações das rotas de entrega, pausas não autorizadas ou tempo ocioso que podem afetar a eficiência de combustível e o desempenho de SLA.
A consulta analisa pontos de localização com velocidade baixa ou zero usando a A consulta usa a tabela tracking_data_core de raw_telematics_data para extrair dados de localização em séries temporais 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 carimbos de início e fim da parada.
Para detectar paradas não autorizadas, ela filtra locais que estejam dentro de zonas geofence conhecidas (tabela zones) usando ST_DWithin do PostGIS. Apenas paradas fora de qualquer buffer de zona são relatadas. O resultado inclui ID do veículo, rótulo do objeto, registro, carimbos de data/hora, duração e coordenadas para cada parada.
WITH speed_data AS (
SELECT
td.device_id,
td.device_time,
td.speed / 100.0 AS speed_kph,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '1 day'
),
low_speed_points AS (
SELECT
*,
LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
FROM speed_data
),
stops_marked AS (
SELECT *,
CASE
WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1
ELSE 0
END AS stop_start,
CASE
WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1
ELSE 0
END AS stop_end
FROM low_speed_points
),
stop_segments AS (
SELECT
device_id,
device_time AS stop_start_time,
LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time,
lat,
lon
FROM stops_marked
WHERE stop_start = 1
),
unauthorized_stops AS (
SELECT
ss.*,
EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS stop_duration_min
FROM stop_segments ss
LEFT JOIN raw_business_data.zones z ON
ST_DWithin(
ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
z.radius
)
WHERE z.zone_id IS NULL -- não dentro de nenhuma zona conhecida
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;Detecção de uso fora do horário
Este caso identifica instâncias em que veículos são operados fora do horário comercial normal — definido aqui como segunda a sexta, 09:00–18:00. Tais detecções são essenciais para sinalizar uso não autorizado, identificar potenciais uso indevido do veículo, e melhorar segurança do ativo.
A lógica é construída sobre a tabela tracking_data_core de raw_telematics_data, que registra eventos GPS com carimbo de tempo por dispositivo. Derivamos o local dia da semana e hora de uso de cada entrada device_time e filtramos os registros fora da janela comercial definida (ou seja, antes das 9h, após as 18h, ou em qualquer horário nos fins de semana).
Para fornecer clareza, enriquecemos os dados GPS com metadados de objeto e veículo de raw_business_data (por exemplo, rótulo do veículo, registro, ID do objeto). Para resumos mais significativos, opcionalmente agregamos o uso para contar quantos eventos fora do horário ocorreram por veículo e quando ocorreram. 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=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 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 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;Contagem de viagens por dia
Este caso mede quantas viagens cada veículo completa diariamente e quão longe eles viajam, ajudando as equipes logísticas a avaliar uso do veículo, otimizar rotas e detectar anomalias como viagens incompletas ou uso não reportado.
Para definir uma viagem, usamos uma mudança no estado de movimento do veículo — ou seja, transição de parado para em movimento e de volta para parado. Usando os valores de velocidade da tabela tracking_data_core, a consulta segmenta os dados com base nessas transições. Uma viagem é identificada como um período contínuo de movimento onde a velocidade se mantém acima de um limite (por exemplo, >5 km/h).
Cada viagem inclui:
Um carimbo de data/hora e local de início (primeiro ponto em movimento)
Um carimbo de data/hora e local de término (último ponto em movimento antes de parar)
A distância de Haversine entre os locais de início e fim
Calculamos a contagem de viagens e a distância total por dia por veículo, opcionalmente enriquecida com rótulos de veículo da tabela vehicles.
WITH base_points AS (
SELECT
td.device_id,
td.device_time,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon,
td.speed / 100.0 AS speed_kph,
LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed,
LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '7 days'
),
trip_segments AS (
SELECT
*,
CASE
WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'start'
WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'end'
END AS trip_marker
FROM base_points
),
trip_points AS (
SELECT
device_id,
device_time AS trip_start_time,
lat AS start_lat,
lon AS start_lon,
LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS trip_end_time,
LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat,
LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon
FROM trip_segments
WHERE trip_marker = 'start'
),
trip_metrics AS (
SELECT
tp.device_id,
tp.trip_start_time,
tp.trip_end_time,
tp.start_lat,
tp.start_lon,
tp.end_lat,
tp.end_lon,
tp.trip_start_time::date AS trip_day,
-- Approximate distance using Haversine formula (in km)
111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2)) AS distance_km
FROM trip_points tp
WHERE tp.trip_end_time IS NOT NULL
)
SELECT
v.vehicle_label,
v.registration_number,
tm.device_id,
tm.trip_day,
COUNT(*) AS trip_count,
ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM trip_metrics tm
JOIN raw_business_data.objects o ON o.device_id = tm.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;Contagem de Quilometragem por Veículo por Dia (Últimos 7 Dias)
Este caso calcula a quilometragem diária (em quilômetros) para cada veículo nos últimos 7 dias. É fundamental para o acompanhamento da utilização do veículo, monitoramento da eficiência de combustível, planejamento de manutenção, e detecção de subutilização ou uso excessivo.
Extraímos todos os registros de GPS de tracking_data_core dos últimos 7 dias. Cada ponto de GPS possui um carimbo de data/hora, latitude e longitude. Para cada veículo e cada dia, nós:
Ordenamos os pontos de GPS cronologicamente por dispositivo.
Calculamos a distância entre pontos consecutivos usando a fórmula de Haversine.
Somamos as distâncias por dia por dispositivo para obter a quilometragem total.
Essa abordagem fornece alta precisão sem depender de sensores de odômetro externos. Opcionalmente, a consulta faz join com objects e vehicles para enriquecer os resultados com metadados do ativo.
WITH gps_points AS (
SELECT
td.device_id,
td.device_time,
td.device_time::date AS trip_day,
td.latitude / 1e7 AS lat,
td.longitude / 1e7 AS lon,
LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lat,
LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lon
FROM raw_telematics_data.tracking_data_core td
WHERE td.device_time >= now() - interval '7 days'
),
distances AS (
SELECT
device_id,
trip_day,
-- Haversine formula approximation in km
111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km
FROM gps_points
WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL
AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- exclude outliers
)
SELECT
v.vehicle_label,
v.registration_number,
o.object_label,
d.device_id,
d.trip_day,
ROUND(SUM(d.segment_distance_km)::numeric, 2) AS mileage_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;Relatório de Registro de Eventos do Veículo
Este caso fornece um relatório abrangente de todos os eventos relacionados ao veículo (por exemplo, ignição, porta aberta, frenagem brusca, etc.) em toda a frota. Inclui tipo de evento, carimbo de data/hora, e contexto do veículo, permitindo que as equipes de operação auditem comportamentos, rastreiem atividades anormais ou acionem alertas e análises.
A fonte primária é a tabela states do schema raw_telematics_data. Cada linha inclui: device_id (origem do evento), device_time (timestamp), state_name (rótulo do evento) e value (status ou medição).
Para criar um relatório utilizável:
Extraímos todos os registros dos últimos 7 dias.
Agrupamos por tipo de evento, veículo, e data para fornecer um contagem de quantas vezes cada evento ocorreu e quando ocorreu.
Enriquecer os resultados com metadados do veículo (vehicle_label, registration_number, object_label) via objects e vehicles.
Isso fornece uma linha do tempo diária de eventos em toda a frota - essencial para diagnóstico, 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,
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;Atualizado
Isto foi útil?