Rédiger des 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 insistant sur les exigences de visualisation car elles imposent 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 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 influencent 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 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, incluez donc les colonnes de coordonnées, de temps et de métriques ensemble dans un seul SELECT.

SQL Editor prise en charge de l'exploration et de 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 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 ad hoc qui n'ont pas besoin d'être visualisées.

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 de colonnes et des types de données spécifiques. Dashboard Studio ne peut pas rendre un graphique en barres à partir de trois colonnes ni une tuile de statistique à 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 de rédiger l'instruction. Le tableau ci-dessous contient les types de visualisation pris en charge :

Visualisation	Exigence de la requête	Exemple
Tuile stat	Valeur numérique unique	SELECT COUNT(*) FROM schema.table
Graphique en 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
Tableau	N'importe quelles colonnes	SELECT column1, column2, column3 FROM schema.table
Texte	Aucune requête requise	Contenu Markdown uniquement

Tuiles stat

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

Total des trajets du mois en cours

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.

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 :

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 journaliers 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 exigent exactement deux colonnes : étiquette (texte) et valeur (numérique). La première colonne devient les étiquettes des portions, la seconde détermine la taille des portions :

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 avec de nombreuses valeurs. Les diagrammes en secteurs avec plus de 20 portions deviennent illisibles ; limitez-vous aux 10-15 catégories principales.

Tableaux

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

Détails récents des trajets

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 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 :

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;

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 le tableau de données, le graphique et la carte de localisation ensemble, vous devrez peut-être combiner des colonnes qui seraient écrites comme des requêtes distinctes pour des panneaux dans un tableau de bord. Par exemple, une requête de panneau de graphique en 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 principale de filtrage et de JOIN reste la même que dans les requêtes de panneaux ; seule la clause SELECT doit être plus large.

Comment écrire du SQL pour les rapports

Un rapport exécute une requête SQL unique qui alimente trois composants simultanément : 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 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
Tableau de données	N'importe quelles 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

Parce que 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 le tableau de données.

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 le tableau de données.

Les tables brutes de télématique 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 convertir en degrés décimaux, et la vitesse est divisée par 100 (10²) pour convertir en km/h. Appliquez ces conversions dans toute requête qui lit depuis raw_telematics_data tables.

Adapter les requêtes de panneaux de 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 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 de panneau est un graphique en barres ou une tuile stat 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, retirez l'agrégation et travaillez à partir des données brutes ou de la couche Silver sous-jacente.

SQL Recipe Book 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 principale des clauses WHERE et JOIN se transpose directement ; ajustez seulement 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 en utilisant ${variable_name} syntaxe.

Définissez des variables pour les valeurs qui changent périodiquement mais restent cohérentes entre plusieurs panneaux : plages de dates d'analyse, filtres de type de véhicule ou valeurs seuils. Lorsque ces valeurs changent, mettez à jour la définition de la variable une seule 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 les 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 tête pour une maintenance facile.

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 lectures de capteurs. Utilisez Bronze pour l'analyse au niveau du point ou lorsque vous avez besoin de valeurs de capteurs 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 géorepères 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 car 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 nécessitant des jointures complexes contre 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 scanné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 objectifs 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 le SQL dans des onglets, exécutez avec le bouton "Execute Query", et visualisez les résultats dans la table 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 en utilisant le bouton "Export CSV". Cela fonctionne pour des rapports ad hoc ou des extractions de données pour analyses externes. 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. Rédigez l'instruction, vérifiez qu'elle renvoie les colonnes et les 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.

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

Patrons SQL courants

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

Comptes en séries temporelles 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);

Synthèses filtrées 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 classent 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 le rendre. Vérifiez Dataset Requirements dans l'onglet SQL Query. Le graphique en barres nécessite exactement deux colonnes (catégorie, valeur), donc ajustez 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 colonnes, 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 vos résultats attendus.

Problèmes de performance

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

-- 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 supplémentaires sur les performances, consultez Comment accéder aux schémas IoT Query pour les bonnes 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, 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, 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 COUNT pour l'agrégation. Une instruction calculant des métriques par véhicule peut devenir une tuile stat en ajoutant SUM sur tous les véhicules.

Vous devez simplement :

1. Copier les exemples depuis Recipe Book vers l'Editor 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 ; vous n'ajustez que la structure de sortie.

Pour les détails du schéma, 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.