Consultas comuns

Ao conectar-se ao banco de dados, será possível recuperar dados via consultas SQL. Esta seção fornece consultas SQL de exemplo para ajudá-lo a começar a trabalhar com o Private Telematics Lakehouse. Estes exemplos demonstram como acessar e analisar dados da camada Bronze, que contém dados brutos de negócios e telemática com transformação mínima.

Observação: Como o banco de dados contém uma grande quantidade de informação, certifique-se de realizar consultas de teste com base em um número limitado de valores recuperados.

Consultas básicas

Recuperar informações básicas do objeto

Esta consulta retorna informações sobre objetos (veículos/ativos) em seu sistema:

SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
FROM
    raw_business_data.objects o
    LEFT JOIN raw_business_data.devices d ON o.device_id = d.device_id
    LEFT JOIN raw_business_data.groups g ON o.group_id = g.group_id
WHERE
    o.is_deleted = false
ORDER BY
    o.object_label;

Obter localizações mais recentes dos dispositivos

Recupere os dados de localização mais recentes para todos os seus dispositivos:

SELECT
    t.device_id,
    o.object_label,
    -- Converter coordenadas inteiras escaladas de volta para o formato decimal
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed,
    t.device_time
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
    AND t.latitude != 0
    AND t.longitude != 0
ORDER BY
    t.device_id, t.device_time DESC;

Os valores de coordenadas são armazenados como inteiros escalados por 10^7 para melhorar a eficiência de armazenamento no TimescaleDB. Ao consultar, divida por 10000000 para converter de volta ao formato decimal padrão.

Unindo dados de negócios e telemáticos

Relatório de atividade do veículo

Esta consulta gera um resumo diário de atividade ao unir dados de negócios e telemáticos:

SELECT
    o.object_label AS vehicle,
    v.vehicle_type,
    DATE(t.device_time) AS date,
    COUNT(DISTINCT DATE_PART('hour', t.device_time)) AS active_hours,
    MAX(t.speed) AS max_speed,
    AVG(t.speed) AS avg_speed
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON o.object_id = v.object_id
WHERE
    t.device_time BETWEEN '2025-03-01' AND '2025-03-28'
GROUP BY
    o.object_label, v.vehicle_type, DATE(t.device_time)
ORDER BY
    o.object_label, DATE(t.device_time);

Atribuições de motorista e histórico de localização

Acompanhe quais colaboradores foram atribuídos a quais veículos e seu histórico de localização:

SELECT 
    o.object_label AS vehicle,
    new_row.changed_datetime AS assignment_time,
    e_new.first_name || ' ' || e_new.last_name AS new_driver_name,
    e_old.first_name || ' ' || e_old.last_name AS old_driver_name,
    new_row.address,
    new_row.latitude,
    new_row.longitude
FROM 
    raw_business_data.driver_history new_row
JOIN 
    raw_business_data.driver_history old_row
    ON new_row.changed_datetime = old_row.changed_datetime
    AND new_row.object_id = old_row.object_id
LEFT JOIN 
    raw_business_data.employees e_new ON new_row.new_employee_id = e_new.employee_id
LEFT JOIN 
    raw_business_data.employees e_old ON old_row.old_employee_id = e_old.employee_id
LEFT JOIN 
    raw_business_data.objects o ON new_row.object_id = o.object_id
ORDER BY 
    assignment_time;

Analisando dados de sensores

Monitoramento do nível de combustível

Esta consulta mostra como analisar dados do sensor de combustível:

SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
FROM
    raw_telematics_data.inputs i
    JOIN raw_business_data.objects o ON i.device_id = o.device_id
    JOIN raw_telematics_data.tracking_data_core t ON
        i.device_id = t.device_id AND
        i.device_time = t.device_time
WHERE
    i.sensor_name = 'fuel'
    AND t.device_time > (CURRENT_DATE - INTERVAL '7 days')
ORDER BY
    o.object_label, t.device_time;

Análise geoespacial

Veículos em geozonas

Identifique quais veículos entraram em geozonas específicas:

SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    JOIN raw_business_data.zones z ON
        -- Calcular se o ponto está dentro de uma zona circular
        -- Converter coordenadas de inteiros escalados para decimal
        (
            CASE
                WHEN z.zone_type = 'circle' THEN
                    ST_DWithin(
                        ST_MakePoint(t.longitude::float/10000000, t.latitude::float/10000000)::geography,
                        ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude)::geography,
                        z.radius
                    )
                ELSE false
            END
        )
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
ORDER BY
    z.zone_label, o.object_label, t.device_time;

Esta consulta usa funções espaciais do PostGIS. Se encontrar erros, verifique se a extensão PostGIS está habilitada em seu banco de dados.

Dicas de otimização de desempenho

Ao trabalhar com o Cloud Data Warehouse, considere estas técnicas de otimização:

1. Use filtragem baseada em tempo: Sempre inclua um filtro de tempo na device_time ou record_added_at colunas para limitar os dados escaneados. Boa prática:

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '7 days');

Evite isto (varre toda a tabela)

SELECT * FROM raw_telematics_data.tracking_data_core;

1. Aproveite índices: O banco de dados possui índices em (device_id, device_time) pares. Estruture suas consultas para utilizar esses índices sempre que possível.

2. Use joins seletivamente: Una tabelas somente quando necessário e tente filtrar os dados antes de unir tabelas grandes.

3. Conversão de inteiro escalado: Lembre-se de que os dados de coordenadas são armazenados como inteiros escalados. Converta apenas na cláusula SELECT final, não nas cláusulas WHERE.

4. Limite conjuntos de resultados: Sempre use LIMIT para consultas exploratórias para evitar retornar milhões de linhas.

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '1 day')
LIMIT 1000;

1. Utilize relações hierárquicas: Estruture consultas complexas seguindo a hierarquia de entidades (dealer → client → user/device → object) para joins e filtragens mais eficientes.

2. Gerencie conexões adequadamente: Feche conexões com o banco de dados quando não estiverem em uso, especialmente em ferramentas de BI ou scripts agendados, para evitar bloqueio de recursos ou problemas de timeout.

Próximos passos

Estes exemplos fornecem um ponto de partida para trabalhar com seus dados. À medida que você se familiarizar com o esquema, poderá desenvolver consultas mais complexas para atender às suas necessidades de negócio específicas.