Requêtes courantes

Lorsque vous vous connectez à 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 de la couche Bronze, qui contient des données métier et télématiques brutes avec une transformation minimale.

Veuillez noter : Étant donné que la base de données contient une quantité importante d'informations, veillez à exécuter des requêtes de test basées sur un nombre limité de valeurs récupérées.

Requêtes de base

Récupérer les informations de base des objets

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

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érer les données de localisation les plus récentes pour tous vos appareils :

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;

Les valeurs de coordonnées sont stockées sous forme d'entiers multipliés par 10^7 pour améliorer l'efficacité de stockage dans TimescaleDB. Lors des requêtes, divisez par 10000000 pour convertir à nouveau au format décimal standard.

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 :

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 de conducteurs et historique des localisations

Suivre quels employés ont été affectés à quels véhicules et leur historique de localisation :

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 :

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 des géozones

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

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 d'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;

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.

Conseils d'optimisation des performances

Lorsque vous travaillez avec le Cloud Data Warehouse, envisagez ces techniques d'optimisation :

1. Utilisez le filtrage temporel: Incluez toujours un filtre temporel sur le device_time ou record_added_at colonnes pour limiter les données scannées. Bonne pratique:

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

Éviter ceci (scanne l'ensemble de la table)

SELECT * FROM raw_telematics_data.tracking_data_core;

1. Exploitez 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.

2. Utilisez les jointures de manière sélective: Joignez les tables seulement lorsque c'est nécessaire et essayez de filtrer les données avant de joindre de grandes tables.

3. Conversion des entiers mis à l'échelle: N'oubliez pas que les données de coordonnées sont stockées comme des entiers mis à l'échelle. Convertissez uniquement dans le SELECT final, pas dans les clauses WHERE.

4. Limiter les jeux de résultats: Utilisez toujours LIMIT pour les requêtes exploratoires afin d'éviter de retourner des millions de lignes.

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

1. Utilisez 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 des filtrages plus efficaces.

2. Gérez 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, afin d'éviter des verrous de ressources ou des problèmes de timeout.

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