> For the complete documentation index, see [llms.txt](https://navixy.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://navixy.com/docs/analytics/fr/example-queries/logistics.md). # Logistique Modèles de requêtes SQL pour les analyses logistiques et de transport, couvrant l'optimisation des itinéraires, le suivi du chargement, l'utilisation de la Flotte et les performances de livraison {% hint style="warning" %} Activer **IoT Query** avant d’utiliser les données pour élaborer des analyses complètes. Si vous ne l’avez pas encore, contactez-nous pour connaître les détails d’activation - {% endhint %} La logistique est un écosystème complexe impliquant la coordination du transport, des opérations d’entrepôt, des stocks et de l’exécution des livraisons. L’intégration de la télématique dans les processus logistiques permet aux entreprises de collecter des données en temps réel sur les Véhicules, les Conducteurs, les itinéraires et l’état des marchandises, ce qui améliore considérablement la prise de décision et l’efficacité opérationnelle. Navixy **IoT Query**, grâce à ses solides capacités d’ingestion de données et d’analyse des séries temporelles, accompagne la transformation numérique des opérations logistiques en offrant une visibilité approfondie sur chaque étape du cycle de vie. Ses solides capacités d’ingestion de télématique offrent une visibilité complète sur ces opérations. Les données GPS en temps réel, les diagnostics des Données du capteur, le géorepérage et les analyses des capteurs permettent aux opérateurs logistiques de numériser les workflows, d’automatiser les contrôles et de prendre des décisions éclairées. | Phase du cycle de vie | Objectifs | Cas d’utilisation / recettes couverts | | ---------------------------------------- | -------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Gestion de l'Itinéraire** | Optimisez l'Itinéraire des véhicules, assurez une répartition efficace et réduisez les retards | Nombre de Voyages par jour, nombre de kilometrage par véhicule par jour (7 derniers jours) | | **Surveillance du fret** | Assurez des conditions de transport appropriées pour les marchandises sensibles | Événements de violation de la température (et de l'humidité) au cours des 7 derniers jours | | **Exploitation des véhicules** | Suivi de l'utilisation de la Flotte, assurez l'Entretien et réduisez les temps d'arrêt | Résumé des Heures moteur par véhicule / Conducteur / jour (7 derniers jours) Analyse des temps d'arrêt des véhicules Suivi des Actifs sans Mouvement | | **Sécurité de l'Itinéraire et Sécurité** | Détecter les abus, les activités non autorisées et les violations de Sécurité | Détection des écarts d’Itinéraire - Détail des arrêts non autorisés (24 dernières heures) Détection de l’utilisation hors horaires | | **Gestion de la conformité** | Surveiller le comportement du Conducteur, faire respecter les politiques et la conformité opérationnelle | Résumé des Heures moteur par Véhicule / Conducteur / jour (7 derniers jours) Détection de l’utilisation hors horaires | | **Analyse post-livraison** | Évaluer l’efficacité opérationnelle et les performances historiques | Rapport du journal des événements du véhicule : compteur de kilometrage par véhicule et par jour (7 derniers jours), Nombre de Voyage par jour, Suivi d’Actif sans Mouvement | ## **Suivi d’Actif sans Mouvement** Ce cas identifie des actifs (par ex. des Véhicules ou des remorques) qui n'ont pas modifié leur GPS, comparez le **coordonnées minimales et maximales** pendant la période. Si les deux valeurs se situent dans une plage très étroite (un seuil de tolérance, par exemple ±0,01 degré), nous marquons l'Actif comme non mobile. La requête joint également les tables objects et Véhicules dans raw\_business\_data pour récupérer des libellés d'Actif pertinents pour la sortie des résultats. {% code expandable="true" %} ```sql WITH gps_bounds AS ( Sélectionner td.device_id, MIN(td.latitude) AS min_lat, MAX(td.latitude) AS max_lat, MIN(td.longitude) AS min_lon, MAX(td.longitude) AS max_lon, COUNT(*) AS location_records DE raw_telematics_data.Suivi_data_core td OÙ td.device_time >= now() - interval '48 hours' Groupe PAR td.id_appareil ), appareils_stationnaires AS ( Sélectionner ID de l'appareil DE limites_GPS OÙ location_records > 10 -- exclure les appareils avec des données très clairsemées ET((max_lat - min_lat) <= 2000 -- ~10 mètres OU (max_lon - min_lon) <= 1000) -- ~10 mètres ) Sélectionner v.vehicle_id, v.vehicle_label, o.traceur_id, o.traceur_label, sd.device_id, gb.min_lat / 1e7 AS latitude, gb.min_lon / 1e7 AS longitude, gb.location_records FROM stationary_devices sd JOIN gps_bounds gb ON sd.device_id = gb.device_id JOIN raw_business_data.objects o ON o.device_id = sd.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id ORDER BY gb.location_records DESC; ``` {% endcode %} ## **Analyse des temps d’immobilisation des véhicules** Ce cas se concentre sur l’analyse de la durée pendant laquelle les Véhicules sont hors service en raison de l’Entretien, de pannes ou de l’inactivité. Les indicateurs de temps d’arrêt sont essentiels pour les opérations logistiques afin de surveiller la santé de la Flotte, de réduire le temps d’inactivité et d’améliorer l’utilisation globale ainsi que l’efficacité de l’Ordonnancement. Le cœur de l’analyse des temps d’arrêt réside dans l’exploitation de la table vehicle\_service\_missions de raw\_business\_data, qui consigne à la fois **événements d'entretien planifiés et non planifiés**. Chaque Mission contient une start\_date et une end\_date, représentant la période d’indisponibilité. En filtrant par **missions de service terminées**, nous pouvons calculer la durée exacte pendant laquelle chaque véhicule était hors service. La requête calcule le temps d'arrêt total par véhicule en additionnant les durées de toutes ses Missions de service (en heures). Elle permet également de distinguer l'Entretien planifié de l'Entretien non planifié en utilisant le champ is\_unplanned. Pour rendre les résultats plus exploitables, elle joint la table des Véhicules afin d'inclure les libellés des véhicules, les numéros d'immatriculation et les informations sur le modèle. {% code expandable="true" %} ```sql WITH downtime_durations AS ( Sélectionner vst.vehicle_id, vst.is_unplanned, vst.start_date, vst.end_date, EXTRACT(EPOCH FROM (vst.end_date - vst.start_date))/3600 AS downtime_hours FROM raw_business_data.vehicle_service_Missions vst WHERE vst.status = 'done' AND vst.start_date IS NOT NULL AND vst.end_date n'est pas NULL ) Sélectionner v.vehicle_id, v.vehicle_label, v.numero_d'enregistrement, v.model, COUNT(dd.*) AS total_service_events, SUM(dd.downtime_hours) AS total_downtime_hours, SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = TRUE) AS heures_d_arret_non_planifie, SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = FALSE) AS temps_d'arrêt_planifié DE downtime_durations dd REJOINDRE raw_business_data.vehicles v SUR v.vehicle_id = dd.vehicle_id GROUPE PAR v.vehicle_id, v.vehicle_label, v.registration_number, v.model ORDER BY total_downtime_hours DESC; ``` {% endcode %} ## **Détection des déviations d'Itinéraire** Ce cas identifie les instances où les Véhicules s’écartent de leurs Itinéraires attribués ou attendus — en particulier des zones géorepérées ou des couloirs de livraison. Le Suivi de ces écarts aide à garantir la conformité à l’Itinéraire, à réduire les retards, à détecter un comportement d’Eco conduite à risque et à maintenir les SLA de livraison. Cette logique compare les positions GPS réelles du véhicule provenant de tracking\_data\_core (dans le schéma raw\_telematics\_data) à des zones géographiques prédéfinies issues de la table zones dans raw\_business\_data. Ces zones représentent des itinéraires attribués ou des segments d’itinéraire. En utilisant des comparaisons géométriques via ST\_DWithin, nous déterminons si un point se trouve à l’intérieur ou à l’extérieur de la zone d’itinéraire mise en tampon. La requête associe chaque position GPS à chaque zone d'itinéraire connue à l'aide d'une opération spatiale **JOIN CROISÉ**, puis applique ST\_DWithin() pour vérifier si le véhicule se trouvait dans le couloir autorisé. Nous isolons les lignes où le véhicule était **en dehors de tous les itinéraires géorepérés** et les signaler comme des écarts. Le résultat final répertorie ces écarts, y compris l’appareil, l’horodatage, le libellé du véhicule et la distance du point au centre de la zone le plus proche. {% code expandable="true" %} ```sql WITH positions AS ( Sélectionner td.device_id, td.device_time, o.traceur_id, o.traceur_label, z.zone_id, z.zone_label, ST_SetSRID(ST_MakePoint(td.longitude / 1e7, td.latitude / 1e7), 4326)::geography AS gps_point, ST_Buffer( ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography, z.radius ) AS route_buffer DE raw_telematics_data.Suivi_data_core td JOIN raw_business_data.objects o ON td.device_id = o.device_id CROSS JOIN raw_business_data.zones z WHERE td.device_time >= now() - interval '2 days' ), évalué AS ( Sélectionner device_id, device_time, Traceur_id, Traceur_label, zone_id, zone_label, NOT ST_DWithin(gps_point, Itinéraire_buffer, 0) AS is_deviation, ST_Distance(gps_point, Itinéraire_buffer) AS deviation_distance_meters FROM positions ), deviations_only AS ( SELECT * FROM evaluated WHERE is_deviation = true ) Sélectionner device_id, Traceur_label, zone_label, device_time, deviation_distance_meters FROM deviations_only ORDER BY device_time DESC; ``` {% endcode %} ## **Résumé des Heures moteur par véhicule / Conducteur / jour (7 derniers jours)** Ce cas mesure combien de temps les moteurs ont été actifs pour chaque véhicule au quotidien, permettant aux gestionnaires de flotte de suivre **utilisation**, identifier **surutilisation ou sous-utilisation**, et corrèle l’activité avec les attributions de Conducteur. Lorsqu’elle est liée aux Conducteurs, elle prend également en charge **validation des heures de travail** et **analyse des performances**. La table des états dans raw\_telematics\_data enregistre **des indicateurs d’état du moteur dans le temps**, généralement avec un state\_name comme « Contact » et une valeur de 1 (allumé) ou de 0 (éteint). Pour calculer les Heures moteur, nous trouvons toutes les transitions horodatées pour chaque appareil et calculons les durées pendant lesquelles le moteur était en marche (1). Lier l’activité du moteur aux deux **véhicules et conducteurs**, nous utilisons les tables objects, Vehicles, et driver\_history de raw\_business\_data. Nous associons chaque enregistrement d’état au Conducteur actuel sur cet Traceur (via l’historique d’affectation du conducteur) et au véhicule correspondant. Nous regroupons ensuite les données par jour, Véhicules et conducteur, en additionnant le temps total de moteur actif (en heures). {% code expandable="true" %} ```sql AVEC inputs_core AS ( Sélectionner i.device_id, i.device_time, i.event_id, i.record_added_at, i.sensor_name, i.value FROM raw_telematics_data.inputs i WHERE i.device_time >= now() - interval '7 days' ), clear_inputs AS ( Sélectionner i.device_id, i.device_time, i.event_id, i.record_added_at, i.sensor_name, i.value, LAG(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_time, LAG(i.value::int) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_status FROM inputs_core i LEFT JOIN raw_business_data.sensor_description sd ON sd.input_label = i.sensor_name AND sd.device_id = i.device_id WHERE sd.sensor_type = 'engine' AND i.value = '1' ), engine_on_periods AS ( Sélectionner device_id, prev_time AS engine_on_time, device_time AS engine_off_time, device_time::date AS activity_day, EXTRACT(EPOCH FROM (device_time - prev_time)) / 3600 AS engine_hours FROM clear_inputs WHERE value::int = 0 AND prev_status = 1 -- transitions de ON à OFF ), enriched_with_objects AS ( Sélectionner eop.*, o.traceur_id, o.traceur_label, v.vehicle_id, v.vehicle_label, v.numéro_d'enregistrement FROM engine_on_periods eop JOIN raw_business_data.objects o ON o.device_id = eop.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id ), Conducteurs_affectés AS ( Sélectionner ewo.*, dh.new_employee_id as employee_id FROM enriched_with_objects ewo LEFT JOIN LATERAL ( SELECT d.* FROM raw_business_data.Conducteur_history d WHERE d.Traceur_id = ewo.Traceur_id AND d.changed_datetime <= ewo.engine_on_time ORDER BY d.changed_datetime DESC LIMIT 1 ) dh ON true ) Sélectionner activity_day, vehicle_label, registration_number, Traceur_label, e.first_name || ' ' || e.last_name AS Conducteur_name, SUM(engine_hours) AS heures_moteur_totales À partir de conducteurs assignés ad LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id Groupe par activity_day, vehicle_label, registration_number, traceur_label, conducteur_name ORDER BY activity_day DESC, vehicle_label; ``` {% endcode %} ## **Événements de violation de la température (et de l'humidité) au cours des 7 derniers jours** Ce cas identifie des relevés de capteurs — tels que **température ou humidité** — qui dépassent les seuils critiques pendant le transport. La surveillance de telles violations est essentielle pour les secteurs qui transportent des denrées périssables (p. ex. aliments, produits pharmaceutiques) afin de garantir le respect des exigences de la chaîne du froid et d’éviter la détérioration. Cette requête extrait **données d'entrée du capteur** depuis la table inputs du schéma raw\_telematics\_data. Chaque ligne représente une mesure de capteur (par ex. température, humidité) enregistrée à un horodatage spécifique par un appareil. Nous filtrons ces enregistrements pour n’inclure que ceux des **7 derniers jours**. La logique de filtrage principale est basée sur **des motifs de nom de capteur** et une comparaison de leurs **valeurs numériques par rapport à des seuils** (par ex. >25°C pour la température, >80 % pour l’humidité). Comme value est stocké sous forme de texte, nous le convertissons en numérique avant d’appliquer les conditions de seuil. Pour enrichir les résultats, nous effectuons une jointure avec la table des objects afin de récupérer des libellés de véhicule ou d’Actif, ce qui améliore l’interprétabilité pour les gestionnaires de Flotte. {% code expandable="true" %} ```sql WITH recent_sensor_data AS ( Sélectionner i.device_id, i.device_time, i.sensor_name, i.value::float AS value FROM raw_telematics_data.inputs i WHERE i.device_time >= now() - interval '1 hour' ), sensor_meta AS ( Sélectionner sd.device_id, sd.input_label, sd.sensor_id, sd.sensor_type, sd.calibration_data FROM raw_business_data.sensor_description sd ), joined_data AS ( Sélectionner rsd.*, sm.sensor_id, sm.sensor_type, sm.calibration_data FROM recent_sensor_data rsd LEFT JOIN sensor_meta sm ON rsd.device_id = sm.device_id AND rsd.sensor_name = sm.input_label ), calibrated_data AS ( Sélectionner jd.device_id, jd.device_time, jd.sensor_name, jd.value, jd.sensor_id, jd.sensor_type, CASE WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN CASE WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume ELSE cd_low.cal_volume + ((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0)) * (cd_high.cal_volume - cd_low.cal_volume) END ELSE jd.value END AS calibrated_value FROM joined_data jd LEFT JOIN LATERAL ( Sélectionner (p->>'in')::float AS cal_value, (p->>'out')::float AS cal_volume FROM jsonb_array_elements(jd.calibration_data) AS p WHERE (p->>'in')::float <= jd.value ORDER BY (p->>'in')::float DESC LIMIT 1 ) cd_low ON TRUE LEFT JOIN LATERAL ( Sélectionner (p->>'in')::float AS cal_value, (p->>'out')::float AS cal_volume FROM jsonb_array_elements(jd.calibration_data) AS p WHERE (p->>'in')::float >= jd.value ORDER BY (p->>'in')::float ASC LIMIT 1 ) cd_high ON TRUE ), violations AS ( Sélectionner cd.device_id, cd.device_time, cd.sensor_name, cd.calibrated_value, CASE WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'Température élevée' WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Température basse' WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'Humidité élevée' ELSE NULL END AS violation_type FROM calibrated_data cd WHERE cd.sensor_type IN ('temperature', 'humidity') ) Sélectionner v.vehicle_label, o.traceur_label, v.numero_d'enregistrement, v.model, vio.device_time, vio.sensor_name, vio.calibrated_value, vio.violation_type FROM violations vio JOIN raw_business_data.objects o ON o.device_id = vio.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id ORDER BY vio.device_time DESC; ``` {% endcode %} ## **Détail des arrêts non autorisés (Dernières 24 heures)** Ce cas identifie **Détail des arrêts non autorisés ou non planifiés** effectués par des Véhicules au cours des dernières 24 heures. Il aide à détecter d'éventuelles violations des itinéraires de livraison, des pauses non autorisées ou du temps d'inactivité qui peuvent affecter l'efficacité du Carburant et les performances SLA. La requête analyse **des points de localisation avec une vitesse faible ou nulle** en utilisant la table Suivi\_data\_core de raw\_telematics\_data pour extraire des données de localisation et de vitesse de série temporelle. Un arrêt est détecté lorsque **la vitesse descend en dessous de 3 km/h** pendant une durée de **plus de 2 minutes**. À l'aide des fonctions LAG et LEAD, la requête segmente ces périodes de faible vitesse pour déterminer les horodatages de début et de fin d'arrêt. Pour détecter **des arrêts non autorisés**, elle exclut les emplacements qui se trouvent dans des **zones de géorepérage** (table des zones) à l'aide de ST\_DWithin de PostGIS. Seuls les arrêts **en dehors de toute zone tampon** sont signalés. Le résultat comprend l'identifiant du véhicule, le libellé du Traceur, l'immatriculation, les horodatages, la durée et les coordonnées de chaque arrêt. {% code expandable="true" %} ```sql WITH speed_data AS ( Sélectionner td.device_id, td.device_time, td.speed / 100.0 AS speed_kph, td.latitude / 1e7 AS lat, td.longitude / 1e7 AS lon DE raw_telematics_data.Suivi_data_core td WHERE td.device_time >= now() - interval '1 day' ), low_speed_points AS ( Sélectionner *, LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time, LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed FROM speed_data ), stops_marked AS ( SELECT *, CASE WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1 SINON 0 END AS stop_start, CASE QUAND speed_kph >= 3 ET prev_speed < 3 ALORS 1 SINON 0 FIN AS stop_end DE low_speed_points ), stop_segments AS ( Sélectionner device_id, device_time AS stop_start_time, LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time, lat, lon DE Détail des arrêts_marqués OÙ stop_start = 1 ), arrêts_non_autorisés AS ( Sélectionner ss.*, EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS duree_arret_min FROM stop_segments ss LEFT JOIN raw_business_data.zones z ON ST_DWithin( ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography, ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography, z.radius ) WHERE z.zone_id IS NULL -- pas dans une zone connue AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120 -- durée minimale de 2 minutes ), avec_métadonnées AS ( Sélectionner us.*, o.traceur_label, v.vehicle_label, v.numéro_d'enregistrement DE Détail des arrêts non autorisés États-Unis JOIN raw_business_data.objects o ON o.device_id = us.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id ) Sélectionner vehicle_label, registration_number, Traceur_label, heure de début d'arrêt, heure de fin d'arrêt, ROUND(stop_duration_min, 1) AS stop_duration_minutes, ROUND(lat, 6) AS latitude, ROUND(lon, 6) AS longitude FROM with_metadata ORDER BY stop_start_time DESC; ``` {% endcode %} ## **Détection d'utilisation hors heures ouvrées** Ce cas identifie les instances où des Véhicules sont exploités **en dehors des heures normales d'ouverture** — définies ici comme **du lundi au vendredi, de 09:00 à 18:00**. De telles détections sont essentielles pour signaler **utilisation non autorisée**, identifiant le potentiel **utilisation abusive du véhicule**, et en améliorant **Actif Sécurité**. La logique est construite sur la table Suivi\_data\_core à partir de raw\_telematics\_data, qui consigne des événements GPS horodatés par appareil. Nous dérivons local **jour de la semaine** et **heure d’utilisation** à partir de chaque entrée device\_time et filtrer les enregistrements **en dehors de la fenêtre commerciale définie** (c.-à-d., avant 9 h, après 18 h, ou à tout moment le week-end). Pour plus de clarté, nous enrichissons les données GPS avec les métadonnées du Traceur et du véhicule issues de raw\_business\_data (par ex., libellé du véhicule, immatriculation, ID du Traceur). Pour des résumés plus pertinents, nous agrégeons éventuellement l’utilisation pour compter **combien d’événements hors horaires** se sont produits par véhicule et à quel moment ils ont eu lieu. Cela peut aider à identifier des tendances ou des récidivistes. {% code expandable="true" %} ```sql WITH gps_events AS ( Sélectionner td.device_id, td.device_time, EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=dimanche, 6=samedi EXTRACT(HOUR FROM td.device_time) AS hour_of_day, td.latitude / 1e7 AS latitude, td.longitude / 1e7 AS longitude DE raw_telematics_data.Suivi_data_core td WHERE td.device_time >= now() - interval '7 days' ), off_hour_events AS ( Sélectionner ge.* FROM GPS_events ge WHERE day_of_week IN (0, 6) -- Samedi ou dimanche OR hour_of_day < 9 OR hour_of_day >= 18 ), avec_métadonnées AS ( Sélectionner o.traceur_label, v.vehicle_label, v.numero_d'enregistrement, e.first_name || ' ' || e.last_name AS Conducteur_name, o.device_id, o.traceur_id, o.create_datetime, ge.device_time, ge.latitude, ge.longitude, ge.day_of_week, ge.hour_of_day FROM off_hour_events ge JOIN raw_business_data.Traceurs o ON ge.device_id = o.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id LEFT JOIN raw_business_data.Conducteur_history dh ON dh.Traceur_id = o.Traceur_id AND dh.changed_datetime <= ge.device_time LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id ) Sélectionner Traceur_label, vehicle_label, registration_number, conducteur_name, device_time, TO_CHAR(device_time, 'Day') AS weekday, TO_CHAR(device_time, 'HH24:MI') AS time_of_event, ROUND(latitude::numeric, 6) AS lat, ROUND(longitude::numeric, 6) AS lon FROM with_metadata ORDER BY device_time DESC; ``` {% endcode %} ## **Nombre de Trajets par jour** Ce cas mesure **combien de Trajets** que chaque véhicule effectue quotidiennement et la distance qu'ils parcourent, aidant les équipes logistiques à évaluer **l'utilisation des véhicules**, optimiser les itinéraires et détecter des anomalies comme des trajets incomplets ou une utilisation non signalée. Pour définir un **Voyage**, nous utilisons un changement dans **état de mouvement du véhicule** — c.-à-d. transition de Arrêté à En mouvement puis de nouveau à Arrêté. En utilisant les valeurs de vitesse de la table tracking\_data\_core, la requête segmente les données en fonction de ces transitions. Un Voyage est identifié comme un **période de Mouvement continue** où la vitesse reste au-dessus d’un seuil (par ex. >5 km/h). Chaque Voyage comprend : * A **horodatage et emplacement de début** (premier point en mouvement) * Un **horodatage et emplacement de fin** (dernier point en mouvement avant l'arrêt) * Le **distance de Haversine** entre les emplacements de début et de fin Nous calculons le nombre de trajets et la distance totale par jour et par véhicule, éventuellement enrichis avec les libellés des véhicules provenant de la table des véhicules. {% code expandable="true" %} ```sql WITH base_points AS ( Sélectionner td.device_id, td.device_time, td.latitude / 1e7 AS lat, td.longitude / 1e7 AS lon, td.speed / 100.0 AS speed_kph, LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed, LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed DE raw_telematics_data.Suivi_data_core td WHERE td.device_time >= now() - interval '7 days' ), Voyage_segments AS ( Sélectionner *, CASE WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'début' WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'fin' END AS Voyage_marker FROM base_points ), Voyage_points AS ( Sélectionner device_id, device_time AS voyage_start_time, lat AS start_lat, lon AS start_lon, LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS Voyage_end_time, LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat, LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon DE segments de Voyage OÙ Voyage_marker = 'start' ), métriques_de_voyage AS ( Sélectionner tp.device_id, tp.Voyage_start_time, tp.Voyage_end_time, tp.start_lat, tp.start_lon, tp.end_lat, tp.end_lon, tp.Voyage_start_time::date AS Voyage_day, -- Distance approximative à l'aide de la formule de Haversine (en km) 111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2))::numeric AS distance_km FROM Voyage_points tp WHERE tp.Voyage_end_time IS NOT NULL ) Sélectionner v.vehicle_label, v.numero_d'enregistrement, tm.device_id, tm.voyage_day, COUNT(*) AS Voyage_count, ROUND(SUM(tm.distance_km), 2) AS total_distance_km DEPUIS voyage_metrics tm JOIN raw_business_data.objects o ON o.device_id = tm.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id Groupe par v.vehicle_label, v.registration_number, tm.device_id, tm.Voyage_day ORDER BY tm.Voyage_day DESC, v.vehicle_label; ``` {% endcode %} ## **Nombre de kilometrage par véhicule par jour (7 derniers jours)** Ce cas calcule le **kilometrage quotidien** (en kilomètres) pour chaque véhicule au cours des 7 derniers jours. Il est fondamental pour le Suivi **utilisation du véhicule**, surveillance **efficacité du Carburant**, planification **Entretien**, et détecter une sous-utilisation ou une surutilisation. Nous extrayons tous les enregistrements GPS depuis tracking\_data\_core pour les 7 derniers jours. Chaque point GPS possède un horodatage, une latitude et une longitude. Pour chaque véhicule et chaque jour, nous : 1. **Trier les points GPS chronologiquement** par appareil. 2. **Calculer la distance entre des points consécutifs** à l’aide de la formule de Haversine. 3. **Additionner les distances par jour et par appareil** pour obtenir le kilometrage total. Cette approche offre une grande précision sans dépendre de capteurs de Compteur kilométrique externes. Facultativement, la requête effectue une jointure avec les objets et les Véhicules afin d’enrichir les résultats avec les métadonnées des Actif. {% code expandable="true" %} ```sql WITH gps_points AS ( Sélectionner td.device_id, td.device_time, td.device_time::date AS jour_de_Voyage, td.latitude / 1e7 AS lat, td.longitude / 1e7 AS lon, LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS latitude_précédente, LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS longitude_précédente DE raw_telematics_data.Suivi_data_core td WHERE td.device_time >= now() - interval '7 days' ), distances AS ( Sélectionner device_id, jour_de_Voyage, -- Approximation de la formule de Haversine en km 111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km FROM gps_points WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- exclure les valeurs aberrantes ) Sélectionner v.vehicle_label, v.numero_d'enregistrement, o.traceur_label, d.device_id, d.jour_de_Voyage, ROUND(SUM(d.segment_distance_km)::numeric, 2) AS kilometrage_km DE distances d JOIN raw_business_data.Traceur o ON o.device_id = d.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id Groupe par v.vehicle_label, v.registration_number, o.Traceur_label, d.device_id, d.jour_du_Voyage Ordre par d.jour_du_Voyage DESC, v.vehicle_label; ``` {% endcode %} ## **Rapport du journal des événements du véhicule** Ce cas fournit un rapport complet de tous **les événements liés aux véhicules** (p. ex., Contact, porte ouverte, Freinage brutal, etc.) dans la Flotte. Il inclut **type d'événement**, **horodatage**, et **contexte du véhicule**, permettant aux équipes opérationnelles d’auditer le comportement, de suivre les activités anormales ou de générer des alertes et des analyses. La source principale est la table states du schéma raw\_telematics\_data. Chaque ligne comprend : device\_id (source de l’événement), device\_time (horodatage), state\_name (libellé de l’événement) et value (statut ou mesure). Pour créer un rapport utilisable : 1. Nous extrayons tous les enregistrements des 7 derniers jours. 2. Groupez-les par **type d'événement**, **Groupe**, et **date** pour fournir un **nombre de fois que chaque événement s'est produit** et **lorsque cela s’est produit**. 3. Enrichissez les résultats avec les métadonnées des Véhicules (vehicle\_label, registration\_number, Traceur\_label) via des objets et des Véhicules. Cela donne un **Heure des événements quotidiens** sur l'ensemble de la Flotte - essentiel pour les diagnostics, l'analyse comportementale et l'Entretien proactif. {% code expandable="true" %} ```sql WITH raw_events AS ( Sélectionner s.device_id, s.device_time, s.device_time::date AS jour_evenement, s.state_name, s.value FROM raw_telematics_data.states s WHERE s.device_time >= now() - interval '7 days' ), with_vehicle_info AS ( Sélectionner re.device_id, re.event_day, re.device_time, re.state_name, re.value, v.vehicle_label, v.numero_d'enregistrement, o.traceur_label DE raw_events re JOIN raw_business_data.objects o ON o.device_id = re.device_id LEFT JOIN raw_business_data.Véhicules v ON v.Traceur_id = o.Traceur_id ), résumé_événement AS ( Sélectionner jour_de_l'événement, nom de l'État, vehicle_label, registration_number, Traceur_label, COUNT(*) AS nombre_d'événements, MIN(device_time) AS premiere_occurrence, MAX(device_time) AS dernière_occurrence DE with_vehicle_info Groupe BY event_day, state_name, vehicle_label, registration_number, Traceur_label ) Sélectionner jour_de_l'événement, vehicle_label, registration_number, Traceur_label, state_name AS event_type, event_count, first_occurred, last_occurred FROM event_summary ORDER BY event_day DESC, vehicle_label, state_name; ``` {% endcode %} --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://navixy.com/docs/analytics/fr/example-queries/logistics.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.