# Rédaction de requêtes SQL Dashboard Studio utilise SQL pour récupérer des données à partir des schémas IoT Query. Vous rédigez 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, car celles-ci imposent des contraintes structurelles spécifiques. ### Où SQL est utilisé Dashboard Studio propose deux environnements SQL pour des usages différents. Comprendre quand utiliser chacun d’eux vous aide à travailler plus efficacement. [**Requêtes de visualisation**](#how-to-write-sql-for-visualizations) alimentent des panneaux individuels dans les rapports. Vous rédigez ces instructions dans l’onglet **Requête SQL** 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 au chargement ou à l’actualisation des rapports, donc les performances comptent pour l’expérience utilisateur. Le SQL de visualisation ne peut pas modifier les données ; toutes les instructions s’exécutent comme des opérations SELECT en lecture seule sur 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 aux trois composants, donc incluez les colonnes de coordonnées, de temps et de métriques ensemble dans un seul SELECT. [**Éditeur SQL**](#how-to-use-the-sql-editor) prend en charge l’exploration et l’export des données. Accédez à l’Éditeur SQL depuis la barre latérale gauche sous Outils. 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. 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. {% hint style="info" %} **La différence clé**: le SQL de visualisation doit correspondre à des structures de colonnes exactes, 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. {% endhint %} ### Comment rédiger du SQL pour les visualisations
Le SQL de visualisation doit renvoyer des nombres de colonnes et des types de données spécifiques. Dashboard Studio ne peut pas afficher un histogramme à partir de trois colonnes ni une tuile statistique à partir de données textuelles. Consultez la section Exigences du jeu de données dans l’onglet de requête SQL pour voir exactement ce qu’attend la visualisation choisie avant de rédiger l’instruction. Le tableau ci-dessous contient les types de visualisation pris en charge : | Visualisation | Exigence de requête | Exemple | | ----------------------------------- | --------------------------------- | ----------------------------------------------------------------- | | [Tuile statistique](#stat-tiles) | Valeur numérique unique | `SELECT COUNT(*) FROM schema.table` | | [Histogramme](#bar-charts) | Deux colonnes : catégorie, valeur | `SELECT column1, COUNT(*) FROM schema.table GROUP BY column1` | | [Diagramme circulaire](#pie-charts) | Deux colonnes : libellé, valeur | `SELECT category, SUM(value) FROM schema.table GROUP BY category` | | [Tableau](#tables) | Toutes les 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="Nombre total de trajets au cours du 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 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 le formatage que vous configurez dans les Paramètres de visualisation.
Graphiques à barres Les histogrammes nécessitent exactement deux colonnes : catégorie (texte ou date) et valeur (numérique). La première colonne devient l’axe X, la seconde devient 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="Nombre de trajets par jour" 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 hiérarchisées ou par catégorie pour des progressions chronologiques.
Diagrammes circulaires Les diagrammes circulaires nécessitent exactement deux colonnes : libellé (texte) et valeur (numérique). La première colonne devient les libellés des secteurs, la seconde détermine leur taille : {% 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 comportant de nombreuses valeurs. Les diagrammes circulaires avec plus de 20 secteurs deviennent illisibles ; limitez-vous aux 10 à 15 premières catégories.
Tableaux Les tableaux acceptent n’importe quel nombre de colonnes avec n’importe quel 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 obtenir 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 seule colonne textuelle : {% 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. 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 d’histogramme renvoyant deux colonnes n’est pas suffisante pour un rapport qui nécessite aussi des 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 principale reste la même que dans les requêtes de panneau ; seule la clause SELECT doit être plus large. ### Comment rédiger du SQL pour les rapports Un rapport exécute une seule 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 possède sa propre requête ciblée, une requête de rapport doit renvoyer toutes les colonnes nécessaires à chaque composant dans une seule instruction SELECT. #### Exigences de colonnes par composant Chaque composant du rapport a des exigences de colonnes spécifiques. Votre requête doit satisfaire tous les composants que vous avez activés. | Composant | Colonnes requises | Notes | | --------------------- | ------------------------------------------------------------------------------- | ---------------------------------------------------------------------- | | Tableau de données | Toutes les colonnes | Toutes les colonnes renvoyées apparaissent comme colonnes du tableau | | Graphique | Au moins une colonne temporelle 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 le tableau de données accepte n’importe quelles colonnes, il n’impose aucune contrainte supplémentaire. Le graphique et la carte de localisation déterminent la plupart des décisions structurelles. #### Combiner les composants dans une seule requête Une requête qui renvoie uniquement les colonnes nécessaires à un graphique (deux colonnes : catégorie et valeur) ne peut pas aussi 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, ainsi que des attributs supplémentaires qui apparaissent dans le tableau 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` servent au graphique, `latitude` et `longitude` servent à la carte de localisation, et toutes les colonnes apparaissent dans le tableau de données. {% hint style="info" %} 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 des `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 constitue un point de départ valide pour un rapport. L’adaptation nécessaire dépend des composants que vous souhaitez activer. Si la requête du panneau est déjà une visualisation 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 une requête d’histogramme ou de tuile statistique renvoyant des résultats agrégés, elle manque probablement du niveau de détail ligne par ligne nécessaire au tableau de données et à la carte de localisation. Dans ce cas, supprimez l’agrégation et basez-vous plutôt sur les données brutes sous-jacentes ou sur la couche Silver. [SQL Recipe Book](/docs/analytics/fr/example-queries.md) contient des exemples de requêtes prêts à l’emploi pour des analyses de flotte courantes. Les recettes du livre peuvent être adaptées aux rapports en ajoutant des colonnes de coordonnées lorsque la carte de localisation est nécessaire. La logique principale des clauses WHERE et JOIN 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 **Settings > Configuration > Global Variables**, puis référencez-les à l’aide de la syntaxe `${variable_name}` .
Définissez des variables pour les valeurs qui changent périodiquement mais restent cohérentes dans 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 chaque instruction SQL individuellement. {% code title="Utilisation de 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 vers les types appropriés dans SQL : `'${variable_name}'::date` pour les dates, `'${variable_name}'::integer` pour les nombres. Pour les paramètres spécifiques à une instruction et 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 les variables globales (plages de dates) et les paramètres spécifiques à l’instruction (seuils), en gardant toutes les valeurs ajustables en haut pour une maintenance facile. ### Comment accéder aux schémas IoT Query IoT Query organise les données en couches Données brutes, Transformation et Insight. Savoir quelle couche utiliser fait gagner du temps et améliore la clarté du SQL. Pour les détails complets des schémas, consultez le [Aperçu du schéma IoT Query](https://www.navixy.com/docs/analytics/iotquery/schema-overview). **Couche de données brutes** contient les points de suivi bruts provenant des appareils : `bronze.tracking_data_core` stocke chaque position GPS avec des horodatages, des coordonnées et des relevés de capteurs. Utilisez les données brutes pour une analyse au niveau des points ou lorsque vous avez besoin de valeurs de capteurs brutes non traitées dans les couches supérieures. **couche de transformation** fournit des entités traitées : `silver.trips` agrège les points de suivi en enregistrements de trajets avec les heures de début/fin, la distance et la durée. `silver.zone_visits` enregistre les moments où les appareils entrent dans des géofences et en sortent. `silver.idle_events` identifie les périodes pendant lesquelles les véhicules restent à l’arrêt moteur en marche. Utilisez la couche Transformation pour la plupart des besoins de visualisation, car elle fournit des structures prêtes à l’analyse. **Couche Insight** propose 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 pour des analyses multidimensionnelles qui nécessiteraient des jointures complexes sur les tables Silver. Référencez les tables à l’aide du format `schema.table` : `silver.trips`et non pas seulement `trips`. Incluez des filtres de plage de dates dans les clauses WHERE pour limiter le volume de données analysé : {% code title="Filtrez toujours par plages de temps" %} ```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 appareil, plage de temps, ou les deux. Ajoutez ces filtres tôt dans les clauses WHERE afin de réduire le volume de données traité. ### 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 des données qui n’ont pas besoin de visualisation.
L’Éditeur SQL prend en charge plusieurs onglets pour différentes instructions. Rédigez le SQL dans les onglets, exécutez-le avec le bouton "Execute Query", puis 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 consultation rapide des données. Exportez les résultats au format CSV à l’aide du bouton "Export CSV". Cela convient aux rapports ponctuels ou aux extractions de données destinées à une analyse externe. L’Éditeur SQL n’a aucune limite de lignes de résultats, contrairement au SQL de visualisation qui doit renvoyer des jeux de données ciblés. Testez le SQL de visualisation 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 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. Méthode 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 l’histogramme) 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.
Comptages de 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); ```
Synthèses filtrées 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 en cas d’échec du SQL Les échecs d’exécution relèvent de trois catégories : inadéquation structurelle avec les exigences de visualisation, erreurs de syntaxe SQL, ou filtres ne renvoyant aucune donnée. #### **Inadéquations de structure des colonnes** Surviennent lorsque les résultats ne correspondent pas aux attentes de la visualisation. Si vous avez sélectionné un histogramme mais que votre SQL renvoie trois colonnes, Dashboard Studio ne peut pas l’afficher. Consultez Exigences du jeu de données dans l’onglet de requête SQL. L’histogramme a besoin exactement de 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 l’absence de préfixes de schéma (`trips` au lieu de `silver.trips`), des fautes de frappe dans les noms de colonnes, ou un mauvais typage des dates. Testez les instructions dans l’Éditeur SQL pour voir des messages d’erreur détaillés avec numéros de ligne. #### **Résultats vides** Bien qu’une exécution réussie indique que les filtres excluent toutes les données. Testez le SQL sans clauses WHERE dans l’Éditeur SQL pour vérifier que la table contient des données, puis ajoutez les 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 inutilement des millions de lignes : ```sql -- Lent : sans 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 en matière de performance, consultez [Comment accéder aux schémas IoT Query](#how-to-access-iot-query-schemas) pour connaître les bonnes pratiques de filtrage et de sélection de schémas. ### Où trouver des exemples SQL Le [SQL Recipe Book](/docs/analytics/fr/example-queries.md) fournit des exemples complets pour des analyses télématiques courantes. Ces recettes démontrent des modèles pour l’analyse des trajets, le calcul des visites de zones, la détection d’inactivité et les métriques de flotte. Chaque recette inclut l’instruction SQL complète, l’explication de la logique et des résultats d’exemple. Adaptez les exemples du Recipe Book aux visualisations en ajustant la clause SELECT pour qu’elle corresponde aux exigences de visualisation. Une recette qui renvoie des enregistrements de trajets détaillés peut devenir un histogramme en ajoutant GROUP BY et COUNT. Une instruction calculant des métriques par véhicule peut devenir une tuile statistique en ajoutant SUM sur l’ensemble des véhicules. Il vous suffit de : 1. Copier les exemples de [Recipe Book](/docs/analytics/fr/example-queries.md) dans l’Éditeur de Dashboard Studio. 2. Les tester avec vos données réelles. 3. Vérifier les résultats, puis modifier la clause SELECT pour la visualisation cible. La logique principale des clauses WHERE et JOIN reste la même ; vous ajustez uniquement la structure de sortie. Pour les détails des schémas, consultez le [Aperçu du schéma IoT Query](https://www.navixy.com/docs/analytics/iotquery/schema-overview). Cette référence explique les tables disponibles, les définitions des colonnes et les relations entre les couches Données brutes, Transformation et Insight. --- # Agent Instructions: 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: ``` GET https://navixy.com/docs/analytics/fr/dashboard-studio/writing-sql-queries.md?ask= ``` The question should be specific, self-contained, and written in natural language. 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.