Redacción de consultas SQL

Dashboard Studio utiliza SQL para recuperar datos de los esquemas de IoT Query. Usted escribe SQL en dos contextos: los editores de paneles, donde las sentencias alimentan las visualizaciones, y el Editor SQL independiente para la exploración de datos. Esta página explica cómo escribir SQL efectivo para ambos contextos, con énfasis en los requisitos de visualización ya que tienen restricciones estructurales específicas.

Dónde se usa SQL

Dashboard Studio proporciona dos entornos SQL para diferentes propósitos. Entender cuándo usar cada uno le ayuda a trabajar de forma más eficiente.

Consultas de visualización alimentan paneles individuales en los informes. Usted escribe estas sentencias en la pestaña SQL Query del editor de panel. Cada panel ejecuta una sentencia que debe devolver datos con una estructura específica que coincida con el tipo de visualización. Estas sentencias se ejecutan cuando los informes se cargan o actualizan, por lo que el rendimiento influye en la experiencia del usuario. El SQL para visualizaciones no puede modificar datos; todas las sentencias se ejecutan como operaciones SELECT de solo lectura contra los esquemas de IoT Query.

Editor SQL soporta la exploración y exportación de datos. Acceda al Editor SQL desde la barra lateral izquierda bajo Herramientas. Escriba cualquier sentencia SELECT para examinar la estructura de los datos, validar suposiciones o exportar resultados como CSV. El Editor SQL muestra tablas de resultados completas con ordenamiento de columnas y proporciona métricas de ejecución. Use esto para probar la lógica antes de añadir SQL a los paneles de visualización, o para extracción ad-hoc de datos que no necesita visualización.

La diferencia clave: el SQL de visualización debe coincidir con estructuras de columnas exactas, mientras que las sentencias en el Editor SQL pueden devolver cualquier formato de resultado. Pruebe la lógica compleja primero en el Editor SQL y luego adáptela para las visualizaciones.

Cómo escribir SQL para visualizaciones

El SQL de visualización debe devolver conteos de columnas y tipos de datos específicos. Dashboard Studio no puede renderizar un gráfico de barras a partir de tres columnas ni una ficha estadística a partir de datos de texto. Revise la sección Requisitos del conjunto de datos en la pestaña SQL Query para ver exactamente lo que su visualización elegida espera antes de escribir la sentencia. La tabla a continuación contiene los tipos de visualización compatibles:

Visualización	Requisito de la consulta	Ejemplo
Ficha estadística	Valor numérico único	SELECT COUNT(*) FROM schema.table
Gráfico de barras	Dos columnas: categoría, valor	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Gráfico de pastel	Dos columnas: etiqueta, valor	SELECT category, SUM(value) FROM schema.table GROUP BY category
Tabla	Cualquier columnas	SELECT column1, column2, column3 FROM schema.table
Texto	No se requiere consulta	Solo contenido Markdown

Fichas estadísticas

Las fichas estadísticas muestran valores numéricos únicos. Las sentencias deben devolver exactamente una fila con una columna numérica:

Total de viajes en el mes actual

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

Distancia total recorrida (km)

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

El nombre de la columna no importa, solo que el resultado sea un único valor numérico. Dashboard Studio muestra este valor con el formato que usted configure en Configuración de visualización.

Gráficos de barras

Los gráficos de barras requieren exactamente dos columnas: categoría (texto o fecha) y valor (numérico). La primera columna se convierte en el eje X, la segunda en la altura de las barras:

Viajes por tipo de vehículo

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;

Recuento diario de viajes

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;

Use ORDER BY para controlar la secuencia de las barras. Ordene por valor para comparaciones clasificadas o por categoría para progresiones de series temporales.

Gráficos de pastel

Los gráficos de pastel requieren exactamente dos columnas: etiqueta (texto) y valor (numérico). La primera columna se convierte en las etiquetas de las porciones, la segunda determina el tamaño de las porciones:

Distribución de viajes por zona

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;

Añada cláusulas LIMIT para categorías con muchos valores. Los gráficos de pastel con más de 20 porciones se vuelven ilegibles; limite a las 10-15 principales categorías.

Tablas

Las tablas aceptan cualquier número de columnas con cualquier tipo de dato. Seleccione las columnas que desea mostrar:

Detalles recientes de viajes

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;

Los nombres de columna se convierten en los encabezados de la tabla. Use alias con espacios para encabezados legibles: distance_km as "Distance (km)".

Paneles de texto

Los paneles de texto muestran valores de texto únicos o cadenas formateadas. Las sentencias deben devolver una columna de texto:

Marca temporal de la última actualización de datos

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

Cómo usar variables globales

Las variables globales proporcionan valores reutilizables en múltiples sentencias SQL. Defina variables en Settings > Configuration > Global Variables, luego haga referencia a ellas usando ${variable_name} sintaxis.

Defina variables para valores que cambian periódicamente pero permanecen consistentes en varios paneles: rangos de fechas de análisis, filtros por tipo de vehículo o valores umbral. Cuando estos valores cambien, actualice la definición de la variable una vez en lugar de editar sentencias SQL individuales.

Uso de variables de rango de fechas

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;

Las variables almacenan valores de texto. Conviértalas al tipo apropiado en SQL: '${variable_name}'::date para fechas, '${variable_name}'::integer para números.

Para parámetros específicos de una sentencia que cambian con frecuencia, puede usar bloques CTE de parámetros al inicio:

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;

Este patrón combina variables globales (rangos de fechas) con parámetros específicos de la sentencia (umbrales), manteniendo todos los valores ajustables en la parte superior para facilitar el mantenimiento.

Cómo acceder a los esquemas de IoT Query

IoT Query organiza los datos en capas Bronze, Silver y Gold. Entender qué capa usar ahorra tiempo y mejora la claridad del SQL. Para detalles completos del esquema, vea el IoT Query Schema Overview.

Capa Bronze contiene puntos de rastreo sin procesar de los dispositivos: bronze.tracking_data_core almacena cada posición GPS con marcas temporales, coordenadas y lecturas de sensores. Use Bronze para análisis a nivel de punto o cuando necesite valores de sensores sin procesar que no estén transformados en capas superiores.

Capa Silver proporciona entidades procesadas: silver.trips agrega puntos de rastreo en registros de viaje con tiempos de inicio/fin, distancia y duración. silver.zone_visits registra cuando los dispositivos entran y salen de geocercas. silver.idle_events identifica periodos en los que los vehículos permanecen inmóviles con el motor en marcha. Use Silver para la mayoría de las necesidades de visualización ya que proporciona estructuras listas para análisis.

Capa Gold ofrece métricas preagregadas y modelos dimensionales para análisis complejos. Use Gold para estadísticas a nivel de flota o análisis multidimensional que requerirían joins complejos contra tablas Silver.

Referencie tablas usando schema.table formato: silver.trips, no solo trips. Incluya filtros de rango de fechas en las cláusulas WHERE para limitar los datos escaneados:

Filtre siempre por rangos temporales

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

La mayoría de las sentencias SQL filtran por dispositivo, rango de tiempo o ambos. Añada estos filtros al inicio de las cláusulas WHERE para reducir el volumen de datos procesados.

Cómo usar el Editor SQL

Acceda al Editor SQL desde la barra lateral izquierda bajo Herramientas. Úselo para tres propósitos principales: probar la lógica antes de añadirla a los paneles, explorar esquemas de datos para entender las columnas disponibles y exportar datos que no necesitan visualización.

El Editor SQL soporta múltiples pestañas para diferentes sentencias. Escriba SQL en pestañas, ejecútelas con el botón "Execute Query" y vea los resultados en la tabla inferior. Los resultados muestran métricas de ejecución (tiempo de ejecución, filas devueltas) y permiten ordenar columnas para un examen rápido de los datos.

Exporte resultados como CSV usando el botón "Export CSV". Esto funciona para informes ad-hoc o extractos de datos para análisis externos. El Editor SQL no tiene límite de filas de resultado, a diferencia del SQL de visualización que debe devolver conjuntos de datos focalizados.

Pruebe el SQL de visualización en el Editor SQL antes de añadirlo a los paneles. Escriba la sentencia, verifique que devuelve las columnas y tipos de datos esperados, y luego cópiela a la pestaña SQL Query del editor de panel. Este flujo detecta problemas estructurales antes de configurar las opciones de visualización.

Patrón de exploración para datos nuevos:

-- 1. Examine la estructura de la tabla
SELECT * FROM silver.trips LIMIT 10;

-- 2. Compruebe la cobertura de rango de fechas
SELECT 
  MIN(start_time) as earliest,
  MAX(start_time) as latest,
  COUNT(*) as total_trips
FROM silver.trips;

-- 3. Pruebe la lógica de filtrado
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. Adapte para visualización (2 columnas para gráfico de barras)
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;

Patrones SQL comunes

La mayoría del SQL de visualización sigue patrones similares. Copie estas estructuras y ajuste filtros, columnas y agregaciones para sus necesidades específicas.

Recuentos en series temporales para seguir tendencias

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;

Clasificaciones por categoría para comparar grupos

SELECT 
  category_column,
  COUNT(*) as count
FROM schema.table
WHERE filter_conditions
GROUP BY category_column
ORDER BY count DESC
LIMIT 15;

Cálculos de métricas para estadísticas agregadas

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

Resúmenes filtrados con múltiples condiciones

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;

Qué hacer cuando el SQL falla

Los fallos de ejecución se dividen en tres categorías: desajustes estructurales con los requisitos de visualización, errores de sintaxis SQL o filtros que devuelven sin datos.

Desajustes en la estructura de columnas

Ocurren cuando los resultados no coinciden con las expectativas de la visualización. Si seleccionó un gráfico de barras pero su SQL devuelve tres columnas, Dashboard Studio no puede renderizarlo. Consulte Requisitos del conjunto de datos en la pestaña SQL Query. El gráfico de barras necesita exactamente dos columnas (categoría, valor), así que ajuste su cláusula SELECT:

-- Incorrecto: tres columnas
SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time;

-- Correcto: dos columnas
SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id;

Errores de sintaxis SQL

Muestran mensajes de error específicos. Los problemas comunes incluyen prefijos de esquema faltantes (trips en lugar de silver.trips), errores tipográficos en los nombres de columna o conversión de fechas incorrecta. Pruebe las sentencias en el Editor SQL para ver mensajes de error detallados con números de línea.

Resultados vacíos

A pesar de la ejecución exitosa indican que los filtros excluyen todos los datos. Pruebe el SQL sin cláusulas WHERE en el Editor SQL para verificar que la tabla contiene datos, luego añada filtros incrementalmente para identificar cuál condición excluye los resultados esperados.

Problemas de rendimiento

Si las sentencias se ejecutan lentamente o agotan el tiempo, añada filtros de rango de fechas a las cláusulas WHERE. Las operaciones que escanean tablas completas procesan millones de filas innecesariamente:

-- Lento: sin filtro de fecha
SELECT device_id, COUNT(*) FROM silver.trips GROUP BY device_id;

-- Rápido: filtro de rango de fechas
SELECT device_id, COUNT(*) 
FROM silver.trips 
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Para orientación adicional sobre rendimiento, vea Cómo acceder a los esquemas de IoT Query para mejores prácticas sobre filtrado y selección de esquemas.

Dónde encontrar ejemplos de SQL

El SQL Recipe Book proporciona ejemplos completos para análisis telemáticos comunes. Estas recetas demuestran patrones para análisis de viajes, cálculos de visitas a zonas, detección de inactividad y métricas de flota. Cada receta incluye la sentencia SQL completa, explicación de la lógica y resultados de ejemplo.

Adapte los ejemplos del Recipe Book para visualizaciones ajustando la cláusula SELECT para que coincida con los requisitos de visualización. Una receta que devuelve registros detallados de viajes puede convertirse en un gráfico de barras añadiendo GROUP BY y la agregación COUNT. Una sentencia que calcula métricas por vehículo puede convertirse en una ficha estadística añadiendo SUM a través de todos los vehículos.

Solo necesita:

1. Copiar ejemplos desde Recipe Book al Editor de Dashboard Studio.

2. Probar con sus datos reales.

3. Verificar resultados, luego modificar la cláusula SELECT para su visualización objetivo.

La lógica principal de WHERE y JOIN permanece igual; usted ajusta solo la estructura de salida.

Para detalles del esquema, consulte el IoT Query Schema Overview. Esta referencia explica las tablas disponibles, definiciones de columnas y relaciones entre las capas Bronze, Silver y Gold.