# Requêtes courantes

Lorsque vous vous connecterez à la base de données, vous pourrez récupérer des données via des requêtes SQL. Cette section fournit des requêtes SQL d'exemple pour vous aider à commencer à travailler avec le Private Telematics Lakehouse. Ces exemples montrent comment accéder et analyser les données provenant de la **Données brutes** couche, qui contient des données métier et télématiques brutes avec une transformation minimale.

{% hint style="danger" %}
Veuillez noter : Étant donné que la base de données contient une quantité importante d'informations, assurez-vous d'effectuer des requêtes de test basées sur un nombre limité de valeurs récupérées.
{% endhint %}

## Requêtes de base

### Récupérer les informations de base sur les objets

Cette requête renvoie des informations sur les objets (véhicules/actifs) de votre système :

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

### Obtenir les dernières positions des appareils

Récupérez les données de localisation les plus récentes pour tous vos appareils :

```sql
SELECT
    t.device_id,
    o.object_label,
    -- Convertir les coordonnées entières mises à l'échelle en format décimal
    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" %}
Les valeurs de coordonnées sont stockées sous forme d'entiers mis à l'échelle par 10^7 pour améliorer l'efficacité de stockage dans TimescaleDB. Lors des requêtes, divisez par 10000000 pour revenir au format décimal standard.
{% endhint %}

## Jointure des données métier et télématiques

### Rapport d'activité des véhicules

Cette requête génère un résumé d'activité quotidien en joignant les données métier et télématiques :

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

### Affectations des conducteurs et historique des positions

Suivez quels employés étaient affectés à quels véhicules et leur historique de localisation :

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

## Analyse des données des capteurs

### Suivi du niveau de carburant

Cette requête montre comment analyser les données du capteur de carburant :

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

## Analyse géospatiale

### Véhicules dans les géozones

Identifiez quels véhicules sont entrés dans des géozones spécifiques :

```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
        -- Calculer si le point est à l'intérieur d'une zone circulaire
        -- Convertir les coordonnées des entiers mis à l'échelle en décimal
        (
            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" %}
Cette requête utilise les fonctions spatiales PostGIS. Si vous rencontrez des erreurs, vérifiez que l'extension PostGIS est activée dans votre base de données.
{% endhint %}

## Conseils d'optimisation des performances

Lorsque vous travaillez avec le Cloud Data Warehouse, considérez ces techniques d'optimisation :

1. **Utiliser le filtrage par temps**: Inclure systématiquement un filtre temporel sur le `device_time` ou `record_added_at` colonnes pour limiter les données scannées.\
   **Bonne pratique**:

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

**À éviter** (scanne toute la table)

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

2. **Exploiter les index**: La base de données possède des index sur `(device_id, device_time)` paires. Structurez vos requêtes pour utiliser ces index lorsque cela est possible.
3. **Utiliser les jointures de manière sélective**: Joindre les tables uniquement lorsque c'est nécessaire et essayez de filtrer les données avant de joindre de grandes tables.
4. **Conversion des entiers mis à l'échelle**: N'oubliez pas que les données de coordonnées sont stockées sous forme d'entiers mis à l'échelle. Convertissez uniquement dans le SELECT final, pas dans les clauses WHERE.
5. **Limiter les jeux de résultats**: Utilisez toujours LIMIT pour les requêtes exploratoires afin d'éviter de renvoyer des millions de lignes.

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

6. **Utiliser les relations hiérarchiques**: Structurez les requêtes complexes en suivant la hiérarchie des entités (**dealer → client → user/device → object**) pour des jointures et filtrages plus efficaces.
7. **Gérer correctement les connexions**: Fermez les connexions à la base de données lorsqu'elles ne sont pas utilisées, en particulier dans les outils BI ou les scripts planifiés, pour éviter le blocage des ressources ou les problèmes de délai d'attente.

## Étapes suivantes

Ces exemples fournissent un point de départ pour travailler avec vos données. À mesure que vous vous familiariserez avec le schéma, vous pourrez développer des requêtes plus complexes pour répondre à vos besoins métier spécifiques.