Consultas comuns

Ao conectar-se ao banco de dados, você poderá 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. Esses exemplos demonstram como acessar e analisar dados a partir do Dados brutos camada, que contém dados brutos de negócio e telemáticos com transformação mínima.

Por favor, note: Como o banco de dados contém uma grande quantidade de informações, certifique-se de fazer consultas de teste com base em um número limitado de valores recuperados.

Consultas básicas

Recuperar informações básicas de objetos

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 últimas localizações de dispositivos

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

SELECT
    t.device_id,
    o.object_label,
    -- Converter coordenadas inteiras escaladas de volta ao 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ócio e telemáticos

Relatório de atividade do veículo

Esta consulta gera um resumo diário de atividade unindo dados de negócio 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 motoristas 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 no device_time ou record_added_at colunas para limitar os dados verificados. 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 usar esses índices quando possível.

2. Use joins seletivamente: Faça junções de tabelas somente quando necessário e procure filtrar os dados antes de unir tabelas grandes.

3. Conversão de inteiros escalados: 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. Limitar 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 relacionamentos hierárquicos: Estruture consultas complexas seguindo a hierarquia de entidades (dealer → client → user/device → object) para junções e filtragens mais eficientes.

2. Gerencie conexões corretamente: 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.