# 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.

{% hint style="danger" %}
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.
{% endhint %}

## Consultas básicas

### Recuperar informações básicas de objetos

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

```sql
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:

```sql
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;
```

{% hint style="info" %}
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.
{% endhint %}

## 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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;
```

{% hint style="info" %}
Esta consulta usa funções espaciais do PostGIS. Se encontrar erros, verifique se a extensão PostGIS está habilitada em seu banco de dados.
{% endhint %}

## 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**:

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

**Evite isto** (varre toda a tabela)

```sql
SELECT * FROM raw_telematics_data.tracking_data_core;
```

2. **Aproveite índices**: O banco de dados possui índices em `(device_id, device_time)` pares. Estruture suas consultas para usar esses índices quando possível.
3. **Use joins seletivamente**: Faça junções de tabelas somente quando necessário e procure filtrar os dados antes de unir tabelas grandes.
4. **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.
5. **Limitar conjuntos de resultados**: Sempre use LIMIT para consultas exploratórias para evitar retornar milhões de linhas.

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

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