# Rédaction de requêtes SQL Dashboard Studio utilise SQL pour récupérer des données à partir des schémas IoT Query. Vous écrivez du SQL dans deux contextes : les éditeurs de panneaux, où les instructions alimentent les visualisations, et l'Éditeur SQL autonome pour l'exploration des données. Cette page explique comment rédiger un SQL efficace pour les deux contextes, en mettant l'accent sur les exigences de visualisation puisqu'elles ont des contraintes structurelles spécifiques. ### Où le SQL est utilisé Dashboard Studio fournit deux environnements SQL pour des usages différents. Savoir quand utiliser chacun permet de travailler plus efficacement. [**Requêtes de visualisation**](#how-to-write-sql-for-visualizations) alimentent les panneaux individuels des rapports. Vous rédigez ces instructions dans l'onglet **SQL Query** de l'éditeur de panneau. Chaque panneau exécute une instruction qui doit renvoyer des données dans une structure spécifique correspondant au type de visualisation. Ces instructions s'exécutent lors du chargement ou du rafraîchissement des rapports, donc les performances influent sur l'expérience utilisateur. Le SQL de visualisation ne peut pas modifier les données ; toutes les instructions s'exécutent en lecture seule SELECT contre les 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 requête qui alimente simultanément trois vues : le tableau de données, le graphique et la carte de localisation. L'instruction 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. [**SQL Editor**](#how-to-use-the-sql-editor) soutient l'exploration et l'exportation des données. Accédez à SQL Editor depuis la barre latérale gauche sous Tools. Rédigez n'importe quelle instruction SELECT pour examiner la structure des données, valider des hypothèses ou exporter les résultats au format CSV. SQL Editor affiche les 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 ad hoc de données qui n'ont pas besoin de visualisation. {% hint style="info" %} **La différence clé** : le SQL de visualisation doit correspondre à des structures de colonnes exactes, tandis que les instructions dans SQL Editor peuvent renvoyer n'importe quel format de résultat. Testez les logiques complexes d'abord dans SQL Editor, puis adaptez-les pour les visualisations. {% endhint %} ### Comment écrire du SQL pour les visualisations
Le SQL de visualisation doit renvoyer des nombres et types de colonnes spécifiques. Dashboard Studio ne peut pas rendre un graphique en barres à partir de trois colonnes ni une tuile statistique à partir de données textuelles. Consultez la section Dataset Requirements dans l'onglet SQL Query pour voir exactement ce que votre visualisation attend avant d'écrire l'instruction. Le tableau ci-dessous contient les types de visualisations pris en charge : | Visualisation | Exigence de requête | Exemple | | ------------------------------------ | --------------------------------- | ----------------------------------------------------------------- | | [Tuile statistique](#stat-tiles) | Valeur numérique unique | `SELECT COUNT(*) FROM schema.table` | | [Graphique en barres](#bar-charts) | Deux colonnes : catégorie, valeur | `SELECT column1, COUNT(*) FROM schema.table GROUP BY column1` | | [Graphique en secteurs](#pie-charts) | Deux colonnes : étiquette, valeur | `SELECT category, SUM(value) FROM schema.table GROUP BY category` | | [Table](#tables) | N'importe quelles colonnes | `SELECT column1, column2, column3 FROM schema.table` | | [Texte](#text-panels) | Aucune requête requise | Contenu Markdown uniquement |
Tuiles statistiques Les tuiles statistiques affichent des valeurs numériques uniques. Les instructions doivent renvoyer exactement une ligne avec une colonne numérique : {% code title="Total des trajets dans le mois en cours" overflow="wrap" %} ```sql SELECT COUNT(*) as value FROM silver.trips WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE); ``` {% endcode %} {% code title="Distance totale parcourue (km)" overflow="wrap" %} ```sql SELECT SUM(distance_km) as value FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '7 days'; ``` {% endcode %} Le nom de la colonne n'a pas d'importance, seul le fait que le résultat soit une unique valeur numérique compte. Dashboard Studio affiche cette valeur avec le formatage que vous configurez dans Visualization Settings.
Graphiques en barres Les graphiques en barres exigent exactement deux colonnes : catégorie (texte ou date) et valeur (numérique). La première colonne devient l'axe X, la seconde détermine la hauteur des barres : {% code title="Trajets par type de véhicule" overflow="wrap" %} ```sql SELECT vehicle_type as category, COUNT(*) as value FROM silver.trips WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE) GROUP BY vehicle_type ORDER BY value DESC; ``` {% endcode %} {% code title="Comptes de trajets quotidiens" overflow="wrap" %} ```sql SELECT DATE_TRUNC('day', start_time)::date as category, COUNT(*) as value FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY DATE_TRUNC('day', start_time) ORDER BY category; ``` {% endcode %} Utilisez `ORDER BY` pour contrôler la séquence des barres. Triez par valeur pour des comparaisons classées ou par catégorie pour des progressions en série temporelle.
Graphiques en secteurs Les graphiques en secteurs exigent exactement deux colonnes : étiquette (texte) et valeur (numérique). La première colonne devient les libellés des parts, la seconde détermine la taille des parts : {% code title="Répartition des trajets par zone" %} ```sql SELECT zone_name as label, COUNT(*) as value FROM silver.zone_visits WHERE enter_time >= DATE_TRUNC('month', CURRENT_DATE) GROUP BY zone_name ORDER BY value DESC LIMIT 10; ``` {% endcode %} Ajoutez des clauses LIMIT pour les catégories ayant de nombreuses valeurs. Les graphiques en secteurs avec plus de 20 parts deviennent illisibles ; limitez-vous aux 10–15 catégories principales.
Tables Les tables acceptent un nombre quelconque de colonnes avec tout type de données. Sélectionnez les colonnes que vous souhaitez afficher : {% code title="Détails des trajets récents" %} ```sql SELECT device_id, start_time, end_time, distance_km, duration_minutes, max_speed_kmh FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '7 days' ORDER BY start_time DESC LIMIT 100; ``` {% endcode %} Les noms de colonnes deviennent les en-têtes du tableau. Utilisez des alias avec des espaces pour des en-têtes lisibles : `distance_km as "Distance (km)"`.
Panneaux de texte Les panneaux de texte affichent des valeurs textuelles uniques ou des chaînes formatées. Les instructions doivent renvoyer une colonne texte : {% code title="Horodatage de la dernière mise à jour des données" %} ```sql SELECT 'Last updated: ' || MAX(record_added_at)::text as value FROM bronze.tracking_data_core; ``` {% endcode %}
Les requêtes de rapport suivent les mêmes règles structurelles que les requêtes de visualisation dans les panneaux du tableau de bord. Parce qu'une seule instruction alimente simultanément 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 panneaux séparées dans un tableau de bord. Par exemple, une requête de panneau pour un graphique en barres renvoyant deux colonnes n'est pas suffisante pour un rapport qui a également besoin des coordonnées GPS pour la carte de localisation. Incluez toutes les colonnes requises pour chaque composant dans une seule instruction. La logique principale de filtrage et de JOIN reste la même que pour les requêtes de panneau ; seul le clause SELECT doit être élargie. ### 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, la requête de rapport doit renvoyer toutes les colonnes nécessaires pour chaque composant dans une seule instruction SELECT. #### Exigences de 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 | | --------------------- | ----------------------------------------------------------------------------- | ---------------------------------------------------------------------- | | Table 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 | Puisque le tableau de données accepte n'importe quelles colonnes, il n'impose pas de contraintes supplémentaires. 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 renvoie uniquement les colonnes nécessaires pour un graphique (deux colonnes : catégorie et valeur) ne peut pas non plus alimenter une carte de localisation. Vous devez inclure toutes les colonnes requises ensemble. 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. ```sql SELECT t.device_id, o.object_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.tracking_data_core t JOIN raw_business_data.objects o ON t.device_id = o.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` alimentent le graphique, `latitude` et `longitude` alimentent la carte de localisation, et toutes les colonnes apparaissent dans la table de données. {% hint style="info" %} Les tables de télématique 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 depuis `raw_telematics_data` tables. {% endhint %} #### Adapter les requêtes de panneaux du tableau de bord pour les rapports Toute requête de panneau d'un tableau de bord est un point de départ valide pour un rapport. L'ajustement nécessaire dépend des composants que vous souhaitez activer. Si la requête de panneau est déjà une visualisation de type table renvoyant plusieurs colonnes, elle peut déjà inclure tout ce qui est nécessaire. Ajoutez les colonnes de coordonnées si la carte de localisation est requise. Si la requête de panneau est un graphique en barres ou une tuile statistique renvoyant des résultats agrégés, elle manque probablement du détail au niveau des lignes nécessaire pour la table de données et la carte de localisation. Dans ce cas, supprimez l'agrégation et travaillez à partir des données brutes ou de la couche Silver sous-jacente. [SQL Recipe Book](https://www.navixy.com/docs/analytics/fr/example-queries) contient des exemples de requêtes prêts à l'emploi pour des analyses de flotte courantes. Les recettes du livre peuvent être adaptées pour les rapports en ajoutant des colonnes de coordonnées lorsque la carte de localisation est nécessaire. La logique WHERE et JOIN de base se transfère 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 des variables dans **Settings > Configuration > Global Variables**, puis référencez-les en utilisant `${variable_name}` la syntaxe.
Définissez des variables pour des valeurs qui changent périodiquement mais restent cohérentes entre plusieurs panneaux : plages de dates d'analyse, filtres par type de véhicule, ou valeurs seuil. Lorsque ces valeurs changent, mettez à jour la définition de la variable une fois au lieu de modifier chaque instruction SQL individuellement. {% code title="Utiliser des variables de plage de dates" %} ```sql SELECT DATE_TRUNC('day', start_time)::date as category, COUNT(*) as value FROM silver.trips WHERE start_time >= '${analysis_start_date}'::date AND start_time < '${analysis_end_date}'::date GROUP BY DATE_TRUNC('day', start_time) ORDER BY category; ``` {% endcode %} Les variables stockent des valeurs textuelles. Convertissez-les en types appropriés dans SQL : `'${variable_name}'::date` pour les dates, `'${variable_name}'::integer` pour les nombres. Pour des paramètres spécifiques à une instruction qui changent fréquemment, vous pouvez utiliser des blocs de paramètres CTE au début : ```sql WITH params AS ( SELECT 5 as min_idle_minutes, 10 as max_idle_speed_kmh, '${analysis_start_date}'::date as date_from, '${analysis_end_date}'::date as date_to ) SELECT device_id, COUNT(*) as idle_count, SUM(duration_minutes) as total_idle_minutes FROM silver.idle_events e CROSS JOIN params p WHERE e.event_time >= p.date_from AND e.event_time < p.date_to AND e.speed_kmh <= p.max_idle_speed_kmh AND e.duration_minutes >= p.min_idle_minutes GROUP BY device_id ORDER BY total_idle_minutes DESC; ``` Ce modèle combine des variables globales (plages de dates) avec des paramètres spécifiques à l'instruction (seuils), en regroupant toutes les valeurs ajustables en haut pour faciliter la maintenance. ### Comment accéder aux schémas IoT Query IoT Query organise les données en couches Raw data, Transformation et Insight. Comprendre quelle couche utiliser permet de gagner du temps et d'améliorer la clarté du SQL. Pour les détails complets des schémas, voir le [IoT Query Schema Overview](https://www.navixy.com/docs/analytics/iotquery/schema-overview). **Couche Raw data** contient les points de suivi bruts provenant des dispositifs : `bronze.tracking_data_core` stocke chaque position GPS avec horodatages, coordonnées et lectures de capteurs. Utilisez Raw data pour l'analyse au niveau des points ou lorsque vous avez besoin de valeurs de capteurs brutes non traitées dans les couches supérieures. **Couche Transformation** fournit des entités traitées : `silver.trips` agrège les points de suivi en enregistrements de trajet avec heures de début/fin, distance et durée. `silver.zone_visits` enregistre les entrées et sorties des géo-clôtures par les dispositifs. `silver.idle_events` identifie les périodes où les véhicules restent immobiles avec le moteur en marche. Utilisez Transformation pour la plupart des besoins de visualisation car elle fournit des structures prêtes pour l'analyse. **Couche Insight** offre des métriques pré-agrégées et des modèles dimensionnels pour des analyses complexes. Utilisez Insight pour des statistiques à l'échelle de la flotte ou une analyse multidimensionnelle qui exigerait des jointures complexes contre les tables Silver. Référencez les tables en utilisant `schema.table` format : `silver.trips`, et non simplement `trips`. Incluez des filtres de plage de dates dans les clauses WHERE pour limiter les données scannées : {% code title="Filtrez toujours par plages temporelles" %} ```sql SELECT device_id, COUNT(*) as trip_count FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY device_id; ``` {% endcode %} La plupart des instructions SQL filtrent par dispositif, plage temporelle, ou les deux. Ajoutez ces filtres tôt dans les clauses WHERE pour réduire le volume de données traité. ### Comment utiliser SQL Editor Accédez à SQL Editor depuis la barre latérale gauche sous Tools. Utilisez-le pour trois usages principaux : tester la logique avant d'ajouter aux panneaux, explorer les schémas de données pour comprendre les colonnes disponibles, et exporter des données qui n'ont pas besoin de visualisation.
SQL Editor prend en charge plusieurs onglets pour différentes instructions. Rédigez du SQL dans les onglets, exécutez avec le bouton "Execute Query", 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 un examen rapide des données. Exportez les résultats au format CSV à l'aide du bouton "Export CSV". Cela fonctionne pour des rapports ad hoc ou des extractions de données pour analyse externe. SQL Editor n'impose pas de limite de lignes de résultat, contrairement au SQL de visualisation qui doit renvoyer des jeux de données ciblés. Testez le SQL de visualisation dans SQL Editor avant de l'ajouter aux panneaux. Rédigez l'instruction, vérifiez qu'elle renvoie les colonnes et types attendus, puis copiez-la dans l'onglet SQL Query 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 de nouvelles données : {% code expandable="true" %} ```sql -- 1. Examiner la structure de la table SELECT * FROM silver.trips LIMIT 10; -- 2. Vérifier la couverture de la plage de dates SELECT MIN(start_time) as earliest, MAX(start_time) as latest, COUNT(*) as total_trips FROM silver.trips; -- 3. Tester la logique de filtrage SELECT device_id, start_time, distance_km FROM silver.trips WHERE start_time >= '2024-01-01' AND device_id = 12345 ORDER BY start_time; -- 4. Adapter pour la visualisation (2 colonnes pour un graphique en barres) SELECT DATE_TRUNC('day', start_time)::date as day, COUNT(*) as trips FROM silver.trips WHERE start_time >= '2024-01-01' AND device_id = 12345 GROUP BY DATE_TRUNC('day', start_time) ORDER BY day; ``` {% endcode %} ### Modèles SQL courants La plupart des SQL de visualisation suivent des modèles similaires. Copiez ces structures et ajustez les filtres, colonnes et agrégations selon vos besoins spécifiques.
Comptes en séries temporelles pour suivre les tendances ```sql SELECT DATE_TRUNC('hour', start_time) as time_bucket, COUNT(*) as event_count FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '24 hours' GROUP BY DATE_TRUNC('hour', start_time) ORDER BY time_bucket; ```
Classements par catégorie pour comparer des groupes ```sql SELECT category_column, COUNT(*) as count FROM schema.table WHERE filter_conditions GROUP BY category_column ORDER BY count DESC LIMIT 15; ```
Calculs de métriques pour des statistiques agrégées ```sql SELECT SUM(distance_km) as total_distance, AVG(duration_minutes) as avg_duration, COUNT(*) as trip_count FROM silver.trips WHERE start_time >= DATE_TRUNC('week', CURRENT_DATE); ```
Résumés filtrés avec plusieurs conditions ```sql SELECT device_id, COUNT(*) as trips, SUM(distance_km) as total_km FROM silver.trips WHERE start_time >= '${period_start}'::date AND start_time < '${period_end}'::date AND distance_km >= 5 AND duration_minutes >= 10 GROUP BY device_id HAVING COUNT(*) >= 5 ORDER BY total_km DESC; ```
### Que faire lorsque le SQL échoue Les échecs d'exécution se répartissent en trois catégories : incompatibilités structurelles avec les exigences de visualisation, erreurs de syntaxe SQL, ou filtres qui ne renvoient aucune donnée. #### **Incompatibilités de structure de colonnes** Se produisent lorsque les résultats ne correspondent pas aux attentes de la visualisation. Si vous avez sélectionné un graphique en barres mais que votre SQL renvoie trois colonnes, Dashboard Studio ne peut pas l'afficher. Consultez Dataset Requirements dans l'onglet SQL Query. Le graphique en barres nécessite exactement deux colonnes (catégorie, valeur), donc ajustez votre clause SELECT : ```sql -- Incorrect : trois colonnes SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time; -- Correct : deux colonnes SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id; ``` #### **Erreurs de syntaxe SQL** Affichent des messages d'erreur spécifiques. Les problèmes courants incluent les préfixes de schéma manquants (`trips` au lieu de `silver.trips`), des fautes de frappe dans les noms de colonnes, ou des conversions de date incorrectes. Testez les instructions dans SQL Editor pour voir des messages d'erreur détaillés avec numéros de ligne. #### **Résultats vides** Malgré une exécution réussie indiquent que les filtres excluent toutes les données. Testez le SQL sans clauses WHERE dans SQL Editor pour vérifier que la table contient des données, puis ajoutez les filtres progressivement pour 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 scannent des tables entières traitent inutilement des millions de lignes : ```sql -- Lent : aucun filtre de date SELECT device_id, COUNT(*) FROM silver.trips GROUP BY device_id; -- Rapide : filtre de plage de dates SELECT device_id, COUNT(*) FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY device_id; ``` Pour des conseils supplémentaires de performance, voir [Comment accéder aux schémas IoT Query](#how-to-access-iot-query-schemas) pour les meilleures pratiques sur le filtrage et la sélection de schéma. ### Où trouver des exemples SQL Le [SQL Recipe Book](https://www.navixy.com/docs/analytics/fr/example-queries) fournit des exemples complets pour des analyses télématiques courantes. Ces recettes démontrent des modèles pour l'analyse des trajets, les calculs de visites de zones, la détection d'inactivité et les métriques de flotte. Chaque recette inclut l'instruction SQL complète, une explication de la logique et des résultats d'exemple. Adaptez les exemples du Recipe Book pour les visualisations en ajustant la clause SELECT pour correspondre aux exigences de visualisation. Une recette qui renvoie des enregistrements de trajets détaillés peut devenir un graphique en barres en ajoutant GROUP BY et l'agrégation COUNT. Une instruction calculant des métriques par véhicule peut devenir une tuile statistique en ajoutant SUM sur tous les véhicules. Il vous suffit de : 1. Copier les exemples depuis [Recipe Book](https://www.navixy.com/docs/analytics/fr/example-queries) vers l'éditeur de Dashboard Studio. 2. Tester avec vos données réelles. 3. Vérifier les résultats, puis modifier la clause SELECT pour votre visualisation cible. La logique de base WHERE et JOIN reste la même ; vous n'ajustez que la structure de sortie. Pour les détails des schémas, voir le [IoT Query Schema Overview](https://www.navixy.com/docs/analytics/iotquery/schema-overview). Cette référence explique les tables disponibles, les définitions de colonnes et les relations entre les couches Raw data, Transformation et Insight.