Écriture de requêtes SQL

Écrivez des requêtes PostgreSQL optimisées pour les visualisations de Dashboard Studio. Apprenez les modèles d'accès aux données, la sélection de couche et les bonnes pratiques de performance

Dashboard Studio utilise SQL pour récupérer des données à partir de schémas IoT Query. Vous écrivez du SQL dans deux contextes : les éditeurs de panneaux, où les requêtes alimentent les visualisations, et l'éditeur SQL autonome pour l'exploration des données. Cette page explique comment écrire un SQL efficace pour ces deux contextes, en mettant l'accent sur les exigences de visualisation, car elles ont des contraintes structurelles spécifiques.

Où SQL est utilisé

Dashboard Studio propose deux environnements SQL pour des usages différents. Comprendre quand utiliser chacun vous aide à travailler plus efficacement.

Requêtes de visualisation alimentent les panneaux individuels dans les Rapports. Vous écrivez ces requêtes dans l'éditeur de panneau Requête SQL onglet. Chaque panneau exécute une seule requête qui doit renvoyer des données dans une structure spécifique correspondant au type de visualisation. Ces requêtes s'exécutent lorsque les Rapports se chargent ou se rafraîchissent, donc les performances sont importantes pour l'expérience utilisateur. Le SQL de visualisation ne peut pas modifier les données ; toutes les requêtes s'exécutent en opérations SELECT en lecture seule sur des schémas IoT Query.

Rapports utilisent la même approche SQL de visualisation que les panneaux du tableau de bord. Un rapport exécute une seule requête qui alimente simultanément trois vues : le tableau de données, le graphique et la carte de localisation. La requête doit renvoyer toutes les colonnes nécessaires pour les trois composants, donc incluez ensemble les colonnes de coordonnées, de temps et de métriques dans un seul SELECT.

Éditeur SQL prend en charge l’exploration et l’exportation des données. Accédez à l’éditeur SQL depuis la barre latérale gauche, dans Outils. Écrivez n’importe quelle instruction SELECT pour examiner la structure des données, valider des hypothèses ou exporter les résultats au format CSV. L’éditeur SQL affiche des tableaux de résultats complets avec tri des colonnes et fournit des métriques d’exécution. Utilisez-le pour tester la logique avant d’ajouter du SQL aux panneaux de visualisation, ou pour des extractions de données ponctuelles qui n’ont pas besoin de visualisation.

La différence clé: le SQL de visualisation doit correspondre exactement aux structures de colonnes, tandis que les instructions de l’éditeur SQL peuvent renvoyer n’importe quel format de résultat. Testez d’abord la logique complexe dans l’éditeur SQL, puis adaptez-la aux visualisations.

Comment écrire du SQL pour les visualisations

Le SQL de visualisation doit renvoyer un nombre précis de colonnes et de types de données. Dashboard Studio ne peut pas afficher un graphique en barres à partir de trois colonnes ni une tuile de statistique à partir de données textuelles. Consultez la section Exigences du jeu de données dans l’onglet Requête SQL pour voir exactement ce que votre visualisation choisie attend avant d’écrire l’instruction. Le tableau ci-dessous contient les types de visualisation pris en charge :

Visualisation	Exigence de la requête	Exemple
Tuile de statistique	Valeur numérique unique	SELECT COUNT(*) FROM schema.table
Diagramme à barres	Deux colonnes : catégorie, valeur	SELECT column1, COUNT(*) FROM schema.table Groupe BY column1
Diagramme circulaire	Deux colonnes : libellé, valeur	SÉLECTIONNER la catégorie, SOMME(value) DE schema.table GROUPE PAR catégorie
Table	N'importe quelles colonnes	SELECT column1, column2, column3 FROM schema.table
Texte	Aucune requête requise	Markdown, HTML ou texte brut
Cartes	Colonnes de latitude et de longitude	SELECT latitude, longitude FROM schema.table

Tuiles de statistiques

Les tuiles de statistiques affichent des valeurs numériques uniques. Les instructions doivent renvoyer exactement une ligne avec une seule colonne numérique :

Nombre total de Trajets du mois en cours

SELECT COUNT(*) as valeur
DE processed_common_data.trajets
OÙ Voyage_start_time >= DATE_TRUNC('month', DATE_ACTUELLE);

Distance totale parcourue (km)

SELECT ROUND(SUM(distance_du_Voyage_en_mètres) / 1000.0, 1) AS valeur
DE processed_common_data.trajets
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '7 days';

Le nom de la colonne n’a pas d’importance, seul compte le fait que le résultat soit une valeur numérique unique. Dashboard Studio affiche cette valeur avec la mise en forme que vous configurez dans les paramètres de visualisation.

Graphiques à barres

Les diagrammes à barres nécessitent exactement deux colonnes : catégorie (texte ou date) et valeur (numérique). La première colonne devient l’axe X, la deuxième devient la hauteur des barres :

Trajets par Traceur

AVEC device_owner en tant que (
  SELECT DISTINCT ON (o.device_id) o.device_id, o.object_label
  FROM raw_business_data.objects o
  WHERE o.is_deleted IS NOT TRUE
  TRIER PAR o.device_id, o.Traceur_id
)
Sélectionner 
  d.traceur_label comme catégorie,
  COUNT(*) as value
DEPUIS processed_common_data.Trajets t
LEFT JOIN device_owner d ON d.device_id = t.device_id
WHERE t.Voyage_start_time >= DATE_TRUNC('month', CURRENT_DATE)
Groupe par d.traceur_label
ORDER BY valeur DESC;

Groupez par une colonne de texte. raw_business_data.Véhicules.type_de_véhicule contient un code entier plutôt qu'un nom, donc le groupement par celui-ci étiquette les barres (Groupe) 1, 2, 3.

Le device_Propriétaire Le bloc en haut n'est pas facultatif chaque fois que vous joignez des libellés de Traceur à des Trajets ou des événements. Voir Comment joindre des libellés de Traceur.

Nombre quotidien de Trajets

Sélectionner 
  DATE_TRUNC('day', Voyage_start_time)::date as catégorie,
  COUNT(*) as value
DE processed_common_data.trajets
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '30 days'
Groupe BY DATE_TRUNC('day', voyage_start_time)
ORDER BY category;

Utilisez ORDER BY pour contrôler l'ordre des barres. Triez par valeur pour des comparaisons classées ou par catégorie pour des progressions de séries temporelles.

Diagrammes en secteurs

Les diagrammes en secteurs nécessitent exactement deux colonnes : libellé (texte) et valeur (numérique). La première colonne devient les libellés des parts, la seconde détermine la taille des parts :

Trajets par zone de départ

Sélectionner 
  start_zone comme libellé,
  COUNT(*) as value
DE processed_common_data.trajets
WHERE voyage_start_time >= DATE_TRUNC('month', CURRENT_DATE)
  ET start_zone N'EST PAS NULL
Groupe par start_zone
Trier par value décroissant
Limiter à 10;

Ajoutez des clauses LIMIT pour les catégories avec de nombreuses valeurs. Les diagrammes circulaires avec 20 slices ou plus deviennent illisibles ; limitez-vous aux 10 à 15 principales catégories.

Tables

Les tables acceptent n'importe quel nombre de colonnes avec n'importe quel type de données. Sélectionnez les colonnes que vous souhaitez afficher :

Détails récents du Voyage

Sélectionner 
  device_id,
  Voyage_start_time,
  Voyage_end_time,
  ROUND(Voyage_distance_meters / 1000.0, 1) as distance_km,
  ROUND(Voyage_duration_seconds / 60.0) as duration_minutes,
  vitesse_maximale
DE processed_common_data.trajets
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY voyage_start_time DESC
LIMIT 100;

Les noms de colonne deviennent des en-têtes de tableau. Utilisez des alias avec des espaces pour des en-têtes lisibles : ROUND(Voyage_distance_meters / 1000.0, 1) as "Distance (km)".

Panneaux de texte

Les panneaux de texte affichent du contenu Markdown, HTML ou texte brut. Ils n’exécutent pas de requête SQL.

Dans l’ Contenu onglet, sélectionnez une option sous Mode de contenu:

• Markdown (par défaut) prend en charge le formatage comme les titres et les liens.

• HTML affiche le balisage brut.

• Texte brut affiche le contenu exactement comme vous l’avez saisi et n’interprète aucun balisage.

Utilisez des panneaux de texte pour les en-têtes de section, les instructions ou le contexte, à côté de vos visualisations de données.

Cartes

Les panneaux de carte affichent un marqueur par ligne. Les requêtes doivent renvoyer une colonne de latitude et une colonne de longitude :

Dernières positions des véhicules

AVEC device_owner en tant que (
  SELECT DISTINCT ON (o.device_id) o.device_id, o.object_label
  FROM raw_business_data.objects o
  WHERE o.is_deleted IS NOT TRUE
  TRIER PAR o.device_id, o.Traceur_id
)
SELECT DISTINCT ON (t.device_id)
  d.Traceur_label,
  t.latitude / 1e7 AS latitude,
  t.longitude / 1e7 AS longitude
FROM raw_telematics_data.Suivi_data_core t
LEFT JOIN device_owner d ON d.device_id = t.device_id
WHERE t.device_time >= NOW() - INTERVAL '24 hours'
  AND t.latitude <> 0 AND t.longitude <> 0
ORDER BY t.device_id, t.device_time DESC;

DISTINCT ON avec la correspondance ORDER BY conserve une ligne par appareil, la plus récente. Sans cela, la requête trace chaque point historique jamais envoyé par l’appareil.

Dashboard Studio détecte automatiquement les colonnes de coordonnées lorsqu’elles utilisent des noms courants tels que latitude, lat, ou gps_lat pour la latitude et longitude, lon, ou lng pour la longitude. Si vos colonnes utilisent des noms différents, sélectionnez-les manuellement dans les paramètres de visualisation.

Les coordonnées doivent être en degrés décimaux. Lorsqu'un tableau les stocke sous forme d'entiers mis à l'échelle, divisez par 1e7 comme indiqué ci-dessus. Toutes les autres colonnes renvoyées par l'instruction apparaissent dans la fenêtre contextuelle du marqueur.

Les requêtes de Rapports suivent les mêmes règles structurelles que les requêtes de visualisation dans les panneaux du tableau de bord. Comme une seule instruction alimente ensemble le tableau de données, le graphique et la carte de localisation, vous devrez peut-être combiner des colonnes qui seraient écrites comme des requêtes de panneau séparées dans un tableau de bord. Par exemple, une requête de panneau de graphique à barres renvoyant deux colonnes n'est pas suffisante pour un rapport qui a également besoin de coordonnées GPS pour la carte de localisation. Incluez toutes les colonnes requises pour chaque composant dans une seule instruction. La logique de filtrage et de JOIN de base reste la même que dans les requêtes de panneau ; seule la clause SELECT doit être plus large.

Comment écrire du SQL pour les Rapports

Un rapport exécute une requête SQL qui alimente simultanément trois composants : le tableau de données, le graphique et la carte de localisation. Contrairement aux panneaux du tableau de bord, où chaque panneau a sa propre requête ciblée, une requête de rapport doit renvoyer toutes les colonnes nécessaires à l'ensemble des composants dans une seule instruction SELECT.

Exigences relatives aux colonnes par composant

Chaque composant de rapport a des exigences de colonnes spécifiques. Votre requête doit satisfaire tous les composants que vous avez activés.

Composant	Colonnes requises	Remarques
Tableau de données	N'importe quelles colonnes	Toutes les colonnes renvoyées apparaissent comme colonnes du tableau
Graphique	Au moins une colonne de temps ou de catégorie, au moins une colonne numérique	Les colonnes d’axe sont sélectionnées dans les paramètres du graphique
Carte de localisation	Latitude et longitude en degrés décimaux	Dashboard Studio détecte automatiquement les colonnes de coordonnées

Comme la table de données accepte n’importe quelles colonnes, elle n’impose aucune contrainte supplémentaire. Le graphique et la carte de localisation déterminent la plupart des décisions structurelles.

Combiner des composants dans une seule requête

Une requête qui ne renvoie que les colonnes nécessaires à un graphique (deux colonnes : catégorie et valeur) ne peut pas non plus alimenter une carte de localisation. Vous devez inclure ensemble toutes les colonnes requises.

L’exemple suivant renvoie des colonnes pour les trois composants : une colonne temporelle et une colonne numérique pour le graphique, des colonnes de coordonnées pour la carte de localisation, et des attributs supplémentaires qui apparaissent dans la table de données.

AVEC device_owner en tant que (
  SELECT DISTINCT ON (o.device_id) o.device_id, o.object_label
  FROM raw_business_data.objects o
  WHERE o.is_deleted IS NOT TRUE
  TRIER PAR o.device_id, o.Traceur_id
)
Sélectionner
    t.device_id,
    d.Traceur_label,
    t.device_time,
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed::float / 100 AS speed
FROM raw_telematics_data.Suivi_data_core t
LEFT JOIN device_owner d ON d.device_id = t.device_id
WHERE t.device_time >= NOW() - INTERVAL '24 hours'
ORDER BY t.device_time DESC
LIMIT 1000

Dans cette requête, device_time et speed servent le graphique, latitude et longitude servent la carte de localisation, et toutes les colonnes apparaissent dans le tableau de données.

Les tables télématiques brutes stockent les coordonnées et la vitesse sous forme d’entiers mis à l’échelle. Les coordonnées sont divisées par 10 000 000 (10⁷) pour être converties en degrés décimaux, et la vitesse est divisée par 100 (10²) pour être convertie en km/h. Appliquez ces conversions dans toute requête qui lit à partir de raw_telematics_data tables.

Adapter les requêtes des panneaux du tableau de bord pour les Rapports

Toute requête de panneau provenant d’un tableau de bord constitue un point de départ valable pour un rapport. L’ajustement nécessaire dépend des composants que vous souhaitez activer.

Si la requête du panneau est déjà une visualisation sous forme de tableau renvoyant plusieurs colonnes, elle peut déjà inclure tout ce qui est nécessaire. Ajoutez des colonnes de coordonnées si la carte de localisation est requise.

Si la requête du panneau est un graphique en barres ou une requête de tuile de statistiques renvoyant des résultats agrégés, elle manque probablement du niveau de détail par ligne nécessaire pour le tableau de données et la carte de localisation. Dans ce cas, supprimez l’agrégation et travaillez plutôt à partir des tables sous-jacentes de la couche Données brutes ou de la couche Transformation.

Recueil de recettes SQL contient des exemples de requêtes prêts à l’emploi pour des analyses courantes de flotte. Les recettes du livre peuvent être adaptées pour des rapports en ajoutant des colonnes de coordonnées lorsque la carte de localisation est nécessaire. La logique WHERE et JOIN de base se transpose directement ; ajustez uniquement la clause SELECT pour couvrir tous les composants requis.

Comment utiliser les variables globales

Les variables globales fournissent des valeurs réutilisables dans plusieurs instructions SQL. Définissez les variables dans Paramètres > Configuration > Variables globales, puis faites-y référence en utilisant ${variable_name} syntaxe.

Définissez des variables pour des valeurs qui changent périodiquement mais restent cohérentes sur plusieurs panneaux : plages de dates d'analyse, filtres de type de véhicule ou valeurs de seuil. Lorsque ces valeurs changent, mettez à jour la définition de la variable une seule fois au lieu de modifier des instructions SQL individuelles.

Utilisation des variables de plage de dates

Sélectionner 
  DATE_TRUNC('day', Voyage_start_time)::date as catégorie,
  COUNT(*) as value
DE processed_common_data.trajets
WHERE Voyage_start_time >= '${analysis_start_date}'::date
  AND Voyage_start_time < '${analysis_end_date}'::date
Groupe BY DATE_TRUNC('day', voyage_start_time)
ORDER BY category;

Les variables stockent des valeurs textuelles. Convertissez-les en types appropriés en SQL : '${variable_name}'::date pour les dates, '${variable_name}'::integer pour les nombres.

Pour les paramètres spécifiques à l'instruction qui changent fréquemment, vous pouvez utiliser des blocs de paramètres CTE au début :

WITH params AS (
  Sélectionner 
    300 as min_idle_seconds,
    10 as max_idle_speed_kmh,
    '${analysis_start_date}'::date as date_from,
    '${analysis_end_date}'::date as date_to
)

Sélectionner 
  e.device_id,
  COUNT(*) as idle_count,
  ROUND(SUM(e.duration_sec) / 60.0) as total_minutes_inactives
DE processed_common_data.événements_de_conducteur_basés_sur_des_règles e
CROSS JOIN params p
WHERE e.event_type = 'Au ralenti_soft'
  AND e.device_time >= p.date_from
  AND e.device_time < p.date_to
  AND e.speed_kmh <= p.max_idle_speed_kmh
  AND e.duration_sec >= p.min_idle_seconds
GROUPE PAR e.device_id
ORDER BY total_idle_minutes DESC;

Ce schéma combine des variables globales (plages de dates) avec des paramètres spécifiques à l'instruction (seuils), en gardant toutes les valeurs ajustables en haut pour faciliter l'Entretien.

Comment joindre des libellés de Traceur

raw_business_data.objects.device_id n’est pas unique. Un appareil peut contenir plusieurs enregistrements de Traceur, car un appareil réaffecté d’un Traceur à un autre laisse derrière lui les lignes précédentes. Les tables de faits telles que processed_common_data.Trajets touche activée ID de l'appareil seul, donc une simple jointure à objets multiplie chaque ligne de faits par le nombre d’enregistrements Traceur correspondants. Les comptes et les sommes sont alors trop élevés, sans aucune erreur pour vous l’indiquer.

Filtrage sur est_supprimé n’est pas suffisant à lui seul, car plus d’un enregistrement peut passer ce filtre. Choisissez d’abord une ligne par appareil, puis faites la jointure dessus :

La jointure Traceur-étiquette

AVEC device_owner en tant que (
  SÉLECTION DISTINCTE SUR (o.device_id) o.device_id, o.Traceur_id, o.Traceur_label
  FROM raw_business_data.objects o
  WHERE o.is_deleted IS NOT TRUE
  TRIER PAR o.device_id, o.Traceur_id
)
SELECT d.traceur_label, COUNT(*) AS Trajets
DEPUIS processed_common_data.Trajets t
LEFT JOIN device_owner d ON d.device_id = t.device_id
OÙ t.Voyage_start_time >= CURRENT_DATE - INTERVAL '30 jours'
GROUPE PAR d.traceur_label;

Utilisez Jointure gauche plutôt qu'une jointure interne, afin qu'un appareil sans enregistrement d'objet survivant apparaisse encore plutôt que de disparaître du résultat.

données_communes_traitées.événements_du_conducteur_basés_sur_des_règles est l’exception. Il porte déjà id_objet et étiquette_objet, et ses coordonnées sont en degrés, donc il n’a besoin ni de cette jointure ni de la /1e7 conversion.

Comment accéder aux schémas IoT Query

IoT Query organise les données en couches : Données brutes, Transformation et Insight. Les couches Données brutes et Transformation contiennent chacune deux schémas PostgreSQL, et vous faites référence à une table par le nom de son schéma plutôt que par la couche. Choisir la bonne couche permet de gagner du temps et de garder le SQL clair. Pour obtenir tous les détails sur les schémas, consultez le Vue d'ensemble du schéma IoT Query.

Couche de Données brutes contient ce que les appareils et la plateforme Navixy ont enregistré, dans deux schémas. raw_telematics_data contient les données de Suivi, de saisie et d'état : raw_telematics_data.Suivi_data_core stocke chaque position GPS avec des horodatages, des coordonnées et des relevés de capteurs. raw_business_data contient des entités commerciales telles que raw_business_data.objects, raw_business_data.Véhicules, et raw_business_data.zones. Utilisez la couche Données brutes pour l'analyse au niveau des points, pour les valeurs brutes des capteurs et pour les étiquettes et attributs que vous joignez aux données traitées.

Couche de transformation contient les entités traitées dans deux schémas. données_communes_traitées contient les transformations que Navixy maintient, qui sont disponibles sans configuration : Trajets, données_des_capteurs_par_heures, événements_de_conducteur_basés_sur_règles, et événements_de_changement_d'entrée. données_personnalisées_traitées contient les transformations que vous créez vous-même dans Transformation Builder. Utilisez la couche Transformation pour la plupart des besoins de visualisation, car elle fournit des structures prêtes pour l’analyse. Voir Transformations courantes pour les colonnes de chaque table.

Couche d’analyse offre des métriques préagrégées et des modèles dimensionnels pour des analyses complexes. Utilisez-la pour des statistiques à l’échelle de la Flotte ou une analyse multidimensionnelle qui nécessiterait autrement des jointures complexes avec les tables de la couche de transformation.

Les noms de Couches Bronze, Silver et Gold décrivent l’architecture en médaillon suivie par les Couches. Ce ne sont pas des noms de schéma, et silver.Trajets n’est pas une table que vous pouvez interroger. Utilisez les noms de schéma ci-dessus.

Référencez les tables en utilisant schema.table format : processed_common_data.Trajets, pas seulement Trajets. Incluez des filtres de plage de dates dans les clauses WHERE pour limiter les données analysées :

Toujours filtrer par plages de temps

SELECT device_id, COUNT(*) as voyage_count
DE processed_common_data.trajets
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '30 days'
Groupe BY device_id;

La plupart des instructions SQL filtrent par appareil, plage horaire, ou les deux. Ajoutez ces filtres tôt dans les clauses WHERE afin de réduire le volume de données traité.

Unités de mesure dans les résultats de la requête

IoT Query stocke chaque mesure dans une unité fixe, et Dashboard Studio affiche tout ce que la requête renvoie. Il ne convertit pas les valeurs dans le système de mesure défini dans les Paramètres du compte Navixy, comme le fait l’application Dashboards prédéfinie. Deux personnes ayant des Paramètres du compte différents voient les mêmes chiffres sur le même panneau.

L'unité de chaque colonne est documentée dans le Vue d'ensemble du schéma IoT Query, et de nombreuses colonnes le nomment directement. Voyage_distance_mètres contient des mètres, vitesse_moyenne et vitesse_maximale contient des km/h, et altitude_de_départ et altitude_de_fin contient des mètres au-dessus du niveau de la mer. Vérifiez la colonne avant de nommer un panneau.

Convertissez dans la requête lorsque vos lecteurs travaillent dans d'autres unités, et nommez l'unité dans l'alias de colonne afin que le panneau se libelle correctement :

Retourner la distance en miles plutôt qu'en mètres

SELECT device_id,
       ROUND(SUM(distance_du_Voyage_en_mètres) / 1609.344, 1) comme "Distance (mi)"
DE processed_common_data.trajets
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '7 days'
Groupe BY device_id;

Divisez les mètres par 1 609,344 pour les miles, les km/h par 1,609344 pour les mph, et les mètres par 0,3048 pour les pieds.

Comment utiliser l’éditeur SQL

Accédez à l’éditeur SQL depuis la barre latérale gauche, sous Outils. Utilisez-le pour trois objectifs principaux : tester la logique avant de l’ajouter aux panneaux, explorer les schémas de données pour comprendre les colonnes disponibles, et exporter les données qui n’ont pas besoin de visualisation.

L’éditeur SQL prend en charge plusieurs onglets pour différentes instructions. Rédigez du SQL dans les onglets, exécutez-le avec le bouton « Exécuter la requête » et consultez les résultats dans le tableau ci-dessous. Les résultats affichent des métriques d’exécution (temps d’exécution, lignes renvoyées) et prennent en charge le tri des colonnes pour une analyse rapide des données.

Exportez les résultats au format CSV à l’aide du bouton « Export CSV ». Cela fonctionne pour les rapports ad hoc ou les extractions de données destinées à une analyse externe. L’éditeur SQL n’a aucune limite sur le nombre de lignes de résultats, contrairement au SQL de visualisation, qui doit renvoyer des jeux de données ciblés.

Testez la visualisation SQL dans l’éditeur SQL avant de l’ajouter aux panneaux. Rédigez l’instruction, vérifiez qu’elle renvoie les colonnes et types de données attendus, puis copiez-la dans l’onglet Requête SQL de l’éditeur de panneau. Ce flux de travail permet de détecter les problèmes structurels avant de configurer les paramètres de visualisation.

Schéma d’exploration pour les nouvelles données :

-- 1. Examiner la structure du tableau
SELECT * FROM processed_common_data.Trajets LIMIT 10;

-- 2. Vérifier la couverture de la plage de dates
Sélectionner 
  MIN(voyage_start_time) as plus_ancien,
  MAX(voyage_start_time) as plus_recent,
  COUNT(*) as total_trajets
FROM processed_common_data.trajets;

-- 3. Tester la logique de filtrage
Sélectionner 
  device_id,
  Voyage_start_time,
  Voyage_distance_mètres
DE processed_common_data.trajets
WHERE voyage_start_time >= '2024-01-01'
  AND device_id = 12345
ORDER BY voyage_start_time;

-- 4. Adapter pour la visualisation (2 colonnes pour un graphique à barres)
Sélectionner 
  DATE_TRUNC('day', voyage_start_time)::date as day,
  COUNT(*) as trajets
DE processed_common_data.trajets
WHERE voyage_start_time >= '2024-01-01'
  AND device_id = 12345
Groupe BY DATE_TRUNC('day', voyage_start_time)
ORDER BY day;

Modèles SQL courants

La plupart des requêtes SQL de visualisation suivent des modèles similaires. Copiez ces structures et adaptez les filtres, les colonnes et les agrégations à vos besoins spécifiques.

Comptages de séries temporelles pour le suivi des tendances

Sélectionner 
  DATE_TRUNC('hour', voyage_start_time) as time_bucket,
  COUNT(*) as event_count
DE processed_common_data.trajets
WHERE voyage_start_time >= CURRENT_DATE - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', trip_start_time)
ORDER BY time_bucket;

Classements par catégorie pour comparer des groupes

Sélectionner 
  colonne de catégorie,
  COUNT(*) as count
DE schema.table
OÙ filter_conditions
Groupe BY category_column
Trier par count décroissant
LIMIT 15;

Calculs métriques pour les statistiques agrégées

Sélectionner 
  ROUND(SUM(Voyage_distance_meters) / 1000.0, 1) as total_distance_km,
  ROUND(AVG(Voyage_duration_seconds) / 60.0) as avg_duration_minutes,
  COUNT(*) as Voyage_count
DE processed_common_data.trajets
WHERE Voyage_start_time >= DATE_TRUNC('week', CURRENT_DATE);

Résumés filtrés avec plusieurs conditions

Sélectionner 
  device_id,
  COUNT(*) as Trajets,
  ROUND(SUM(trip_distance_meters) / 1000.0, 1) en tant que total_km
DE processed_common_data.trajets
WHERE Voyage_start_time >= '${period_start}'::date
  ET trip_start_time < '${period_end}'::date
  ET distance_du_Voyage_en_mètres >= 5000
  ET trip_duration_seconds >= 600
Groupe BY device_id
HAVING COUNT(*) >= 5
ORDER BY total_km DESC;

Que faire lorsque SQL échoue

Les échecs d’exécution se répartissent en trois catégories : les incompatibilités structurelles avec les exigences de visualisation, les erreurs de syntaxe SQL ou les filtres qui ne renvoient aucune donnée.

Incompatibilités de structure des colonnes

Se produisent lorsque les résultats ne correspondent pas aux attentes de la visualisation. Si vous avez sélectionné un graphique à barres mais que votre requête SQL renvoie trois colonnes, Dashboard Studio ne peut pas le rendre. Consultez les exigences du jeu de données dans l’onglet Requête SQL. Le graphique à barres nécessite exactement deux colonnes (catégorie, valeur), alors ajustez votre clause SELECT :

-- Faux : trois colonnes
SELECT device_id, Voyage_start_time, COUNT(*) FROM processed_common_data.Trajets Groupe BY device_id, Voyage_start_time;

-- Correct : deux colonnes
SELECT device_id, COUNT(*) as Trajets FROM processed_common_data.Trajets Groupe BY device_id;

Erreurs de syntaxe SQL

Afficher des messages d’erreur spécifiques. Les problèmes courants incluent des préfixes de schéma manquants (Trajets au lieu de processed_common_data.Trajets), des fautes de frappe dans les noms de colonnes, ou un cast de date incorrect. Testez les instructions dans l’éditeur SQL pour voir des messages d’erreur détaillés avec les numéros de ligne.

Aucun résultat

Même si l’exécution a réussi, cela indique que les filtres excluent toutes les données. Testez la requête SQL sans clause WHERE dans l’éditeur SQL pour vérifier que la table contient des données, puis ajoutez des filtres progressivement afin d’identifier quelle condition exclut les résultats attendus.

Problèmes de performance

Si les instructions s'exécutent lentement ou expirent, ajoutez des filtres de plage de dates aux clauses WHERE. Les opérations qui analysent des tables entières traitent des millions de lignes inutilement :

-- Lent : aucun filtre de date
SÉLECTIONNER device_id, COUNT(*) FROM processed_common_data.trips Groupe BY device_id;

-- Rapide : filtre de plage de dates
SELECT device_id, COUNT(*) 
DE processed_common_data.trajets 
WHERE Voyage_start_time >= CURRENT_DATE - INTERVAL '30 days'
Groupe BY device_id;

Pour des conseils supplémentaires sur les performances, voir Comment accéder aux schémas IoT Query pour connaître les meilleures pratiques en matière de filtrage et de sélection de schéma.

Où trouver des exemples SQL

Le Recueil de recettes SQL fournit des exemples complets pour les analyses télématiques courantes. Ces recettes démontrent des modèles pour l’analyse de Voyage, les calculs de visites de zone, la détection d’inactivité et les métriques de Flotte. Chaque recette comprend l’instruction SQL complète, l’explication de la logique et des résultats d’exemple.

Adaptez les exemples du Recipe Book pour les visualisations en ajustant la clause SELECT afin de répondre aux exigences de visualisation. Une recette qui renvoie des enregistrements détaillés de Voyage peut devenir un graphique à barres en ajoutant Groupe BY et une agrégation COUNT. Une instruction calculant des métriques par Véhicules peut devenir une tuile de statistiques en ajoutant SUM sur l'ensemble des Véhicules.

Il vous suffit de :

1. Copier des exemples depuis Livre de recettes vers l’éditeur de Dashboard Studio.

2. Testez avec vos données réelles.

3. Vérifiez les résultats, puis modifiez la clause SELECT pour la visualisation cible.

La logique principale de WHERE et de JOIN reste la même ; vous ajustez uniquement la structure de sortie.

Pour plus de détails sur le schéma, consultez le Vue d'ensemble du schéma IoT Query. Cette référence explique les tables disponibles, les définitions de colonnes et les relations entre les Données brutes, la Transformation et les Couches d’Insight.