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 panneau, où les instructions alimentent les visualisations, et l'Éditeur SQL autonome pour l'exploration des données. Cette page explique comment écrire 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 propose deux environnements SQL pour des usages différents. Comprendre quand utiliser chacun vous aide à travailler plus efficacement.

Requêtes de visualisation alimentent des panneaux individuels dans les rapports. Vous écrivez 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 via des opérations SELECT sur les schémas IoT Query.

SQL Editor prise en charge de l'exploration et de l'exportation de données. Accédez à SQL Editor depuis la barre latérale gauche sous Tools. É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. SQL Editor affiche les tables de résultats complètes 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 dans SQL Editor peuvent renvoyer n'importe quel format de résultat. Testez d'abord la logique complexe dans SQL Editor, puis adaptez-la pour les visualisations.

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 diagramme à barres à partir de trois colonnes ni une tuile de statistiques à partir de données textuelles. Consultez la section Dataset Requirements dans l'onglet SQL Query 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 statistiques	Valeur numérique unique	SELECT COUNT(*) FROM schema.table
Diagramme à barres	Deux colonnes : catégorie, valeur	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Diagramme en secteurs	Deux colonnes : étiquette, valeur	SELECT category, SUM(value) FROM schema.table GROUP BY category
Table	N'importe quelles colonnes	SELECT column1, column2, column3 FROM schema.table
Texte	Aucune requête requise	Contenu Markdown uniquement

Tuiles de statistiques

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

Nombre total de trajets ce mois

SELECT COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE);

Distance totale parcourue (km)

SELECT SUM(distance_km) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days';

Le nom de la colonne n'a pas d'importance, seule la présence d'une valeur numérique unique compte. Dashboard Studio affiche cette valeur avec le formatage que vous configurez dans Visualization Settings.

Diagrammes à barres

Les diagrammes à barres requièrent 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 :

Trajets par type de véhicule

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;

Comptes quotidiens de trajets

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;

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.

Diagrammes en secteurs

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

Répartition des trajets par zone

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;

Ajoutez des clauses LIMIT pour les catégories ayant de nombreuses valeurs. Les diagrammes en secteurs avec plus de 20 parts deviennent illisibles ; limitez-vous aux 10-15 premières catégories.

Tables

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

Détails des trajets récents

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;

Les noms de colonnes deviennent les en-têtes de table. 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 :

Horodatage de la dernière mise à jour des données

SELECT 
  'Last updated: ' || MAX(record_added_at)::text as value
FROM bronze.tracking_data_core;

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 en utilisant ${variable_name} 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 seuils. Lorsque ces valeurs changent, mettez à jour la définition de la variable une fois au lieu de modifier chaque instruction SQL individuellement.

Utilisation des variables de plage de dates

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;

Les variables stockent des valeurs textuelles. Convertissez-les en types appropriés dans le 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 :

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 gardant toutes les valeurs ajustables en haut pour une maintenance facilitée.

Comment accéder aux schémas IoT Query

IoT Query organise les données en couches Bronze, Silver et Gold. Comprendre quelle couche utiliser fait gagner du temps et améliore la clarté du SQL. Pour les détails complets des schémas, consultez le IoT Query Schema Overview.

Couche Bronze contient les points de suivi bruts provenant des appareils : bronze.tracking_data_core stocke chaque position GPS avec horodatages, coordonnées et relevés de capteurs. Utilisez Bronze pour l'analyse au niveau du point ou lorsque vous avez besoin de valeurs de capteur brutes non traitées dans les couches supérieures.

Couche Silver 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 geofences par les appareils. silver.idle_events identifie les périodes où les véhicules restent immobiles avec le moteur en marche. Utilisez Silver pour la plupart des besoins de visualisation puisqu'elle fournit des structures prêtes pour l'analyse.

Couche Gold offre des métriques pré-agrégées et des modèles dimensionnels pour des analyses complexes. Utilisez Gold pour des statistiques à l'échelle de la flotte ou des analyses multidimensionnelles qui nécessiteraient des jointures complexes sur les tables Silver.

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

Toujours filtrer par plages temporelles

SELECT device_id, COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

La plupart des instructions SQL filtrent par appareil, 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. Écrivez du SQL dans des onglets, exécutez avec le bouton "Execute Query" et visualisez les résultats dans le tableau ci-dessous. Les résultats affichent des métriques d'exécution (durée d'exécution, lignes retournées) et permettent le tri des colonnes pour un examen rapide des données.

Exportez les résultats au format CSV en utilisant le bouton "Export CSV". Cela fonctionne pour des rapports ad hoc ou des extractions de données pour analyse externe. SQL Editor n'a 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. Écrivez 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 :

-- 1. Examiner la structure de la table
SELECT * FROM silver.trips LIMIT 10;

-- 2. Vérifier la couverture des plages 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 diagramme à 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;

Schémas SQL courants

La plupart des SQL de visualisation suivent des schémas similaires. Copiez ces structures et ajustez filtres, colonnes et agrégations selon vos besoins spécifiques.

Comptes en série temporelle pour suivre les tendances

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 de catégories pour comparer des groupes

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

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);

Sommaires filtrés avec conditions multiples

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 choisi un diagramme à barres mais que votre SQL renvoie trois colonnes, Dashboard Studio ne peut pas le rendre. Vérifiez Dataset Requirements dans l'onglet SQL Query. Le diagramme à barres nécessite exactement deux colonnes (catégorie, valeur), ajustez donc votre clause SELECT :

-- Faux : 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 des préfixes de schéma manquants (trips au lieu de silver.trips), des fautes de frappe dans les noms de colonne ou un mauvais cast de date. 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, cela indique 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 :

-- Lent : pas de 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 de performance supplémentaires, voir Comment accéder aux schémas IoT Query pour les meilleures pratiques sur le filtrage et la sélection de schéma.

Où trouver des exemples SQL

Le SQL Recipe Book fournit des exemples complets pour des analyses télématiques courantes. Ces recettes démontrent des schémas 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 diagramme à barres en ajoutant GROUP BY et une agrégation COUNT. Une instruction calculant des métriques par véhicule peut devenir une tuile de statistiques en additionnant (SUM) sur tous les véhicules.

Vous avez juste besoin de :

1. Copier les exemples depuis Recipe Book 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 votre visualisation cible.

La logique principale des clauses WHERE et JOIN reste la même ; n'ajustez que la structure de sortie.

Pour les détails des schémas, consultez le IoT Query Schema Overview. Cette référence explique les tables disponibles, les définitions de colonnes et les relations entre les couches Bronze, Silver et Gold.