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 grande quantité d'informations, veillez à effectuer 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 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 position les plus récentes pour tous vos appareils :

SELECT
    t.device_id,
    o.object_label,
    -- Convert scaled integer coordinates back to decimal format
    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 pour une meilleure efficacité de stockage dans TimescaleDB. Lors des requêtes, divisez par 10000000 pour reconvertir 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 positions

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

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

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
        -- Calculate if point is within circular zone
        -- Convert coordinates from scaled integers to 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;

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 l'entrepôt de données cloud, considérez ces techniques d'optimisation :

1. Utilisez le filtrage basé sur le temps: Always include a time filter on the device_time or record_added_at columns to limit the data scanned. Bonne pratique:

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

À éviter (parcourt la table entière)

SELECT * FROM raw_telematics_data.tracking_data_core;

1. Exploitez les index: The database has indexes on (device_id, device_time) pairs. Structure your queries to use these indexes when possible.

2. Utilisez les jointures de manière sélective: Join tables only when necessary and try to filter data before joining large tables.

3. Conversion des entiers mis à l'échelle: Remember that coordinate data is stored as scaled integers. Convert only in the final SELECT, not in WHERE clauses.

4. Limitez les jeux de résultats: Always use LIMIT for exploratory queries to avoid returning millions of rows.

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

1. Utilisez les relations hiérarchiques: Structure complex queries following the entity hierarchy (dealer → client → user/device → object) for more efficient joins and filtering.

2. Gérez correctement les connexions: Close database connections when they're not in use, especially in BI tools or scheduled scripts, to avoid resource locking or timeout issues.

Prochaines étapes

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.