Requêtes courantes

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

Veuillez noter : la base de données contenant une très grande quantité d’informations, veuillez vous assurer d’effectuer des requêtes de test sur un nombre limité de valeurs récupérées.

Requêtes de base

Récupérer des informations de base sur les 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érez 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 обратно au 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 mis à l’échelle par 10^7 afin d’améliorer l’efficacité du stockage dans TimescaleDB. Lors des requêtes, divisez par 10000000 pour revenir au format décimal standard.

Fusion des données métier et télématiques

Rapport d’activité des véhicules

Cette requête génère un résumé quotidien de l’activité en fusionnant 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 des conducteurs et historique des localisations

Suivez 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 de 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 les géozones

Identifiez 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 se trouve à l’intérieur de la 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 de 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 les techniques d’optimisation suivantes :

1. Utilisez un filtrage basé sur le temps: Incluez toujours un filtre temporel sur les device_time ou record_added_at colonnes afin de limiter les données analysées. Bonne pratique:

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

À éviter (analyse l’intégralité 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 les (device_id, device_time) paires. Structurez vos requêtes pour utiliser ces index lorsque cela est possible.

2. Utilisez les jointures avec parcimonie: Joignez les tables uniquement lorsque cela est nécessaire et essayez de filtrer les données avant de joindre de grandes tables.

3. Conversion d’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-les uniquement dans le SELECT final, et non dans les clauses WHERE.

4. Limitez les ensembles de résultats: Utilisez toujours LIMIT pour les requêtes exploratoires afin d’éviter de renvoyer 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 les blocages de 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 élaborer des requêtes plus complexes pour répondre à vos besoins métier spécifiques.