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

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.

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	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
Graphique 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 statistiques

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

Total des trajets dans le 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, 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 :

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 de trajets quotidiens

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.

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 :

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

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

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.

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.

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

Utiliser 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 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 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.

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 :

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

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

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

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

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

Résumés filtrés avec plusieurs conditions

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 :

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

-- 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 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 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 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. Cette référence explique les tables disponibles, les définitions de colonnes et les relations entre les couches Raw data, Transformation et Insight.