Escrevendo consultas SQL

O Dashboard Studio usa SQL para recuperar dados dos schemas do IoT Query. Você escreve SQL em dois contextos: editores de painel, onde as instruções alimentam visualizações, e o Editor SQL independente para exploração de dados. Esta página explica como escrever SQL eficaz tanto para painéis quanto para o Editor, com ênfase nos requisitos de visualização, já que eles têm restrições estruturais específicas.

Onde o SQL é usado

O Dashboard Studio fornece dois ambientes de SQL com finalidades diferentes. Entender quando usar cada um ajuda você a trabalhar com mais eficiência.

Consultas de visualização alimentam painéis individuais em relatórios. Você escreve essas instruções na guia SQL Query do editor de painel. Cada painel executa uma instrução que deve retornar dados em uma estrutura específica correspondente ao tipo de visualização. Essas instruções são executadas quando os relatórios carregam ou são atualizados, portanto o desempenho importa para a experiência do usuário. O SQL de visualização não pode modificar dados; todas as instruções são executadas como operações SELECT somente leitura contra os schemas do IoT Query.

Editor SQL suporta exploração de dados e exportação. Acesse o Editor SQL na barra lateral esquerda em Tools. Escreva qualquer instrução SELECT para examinar a estrutura dos dados, validar suposições ou exportar resultados como CSV. O Editor SQL mostra tabelas de resultados completas com ordenação de colunas e fornece métricas de execução. Use-o para testar a lógica antes de adicionar SQL aos painéis de visualização ou para extração ad-hoc de dados que não precisa de visualização.

A diferença chave: o SQL de visualização deve corresponder exatamente às estruturas de colunas, enquanto as instruções no SQL Editor podem retornar qualquer formato de resultado. Teste lógica complexa primeiro no SQL Editor e depois adapte-a para as visualizações.

Como escrever SQL para visualizações

O SQL de visualização deve retornar contagens de colunas e tipos de dados específicos. O Dashboard Studio não consegue renderizar um gráfico de barras a partir de três colunas nem um bloco de estatística a partir de dados de texto. Verifique a seção Dataset Requirements na guia SQL Query para ver exatamente o que sua visualização escolhida espera antes de escrever a instrução. A tabela abaixo contém os tipos de visualização suportados:

Visualização	Requisito de consulta	Exemplo
Bloco de estatística (stat tile)	Valor numérico único	SELECT COUNT(*) FROM schema.table
Gráfico de barras	Duas colunas: categoria, valor	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Gráfico de pizza	Duas colunas: rótulo, valor	SELECT category, SUM(value) FROM schema.table GROUP BY category
Tabela	Qualquer colunas	SELECT column1, column2, column3 FROM schema.table
Texto	Nenhuma consulta necessária	Apenas conteúdo Markdown

Blocos de estatística

Blocos de estatística exibem valores numéricos únicos. As instruções devem retornar exatamente uma linha com uma coluna numérica:

Total de viagens no mês atual

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

Distância total percorrida (km)

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

O nome da coluna não importa, apenas que o resultado seja um único valor numérico. O Dashboard Studio exibe esse valor com a formatação que você configurar em Visualization Settings.

Gráficos de barras

Gráficos de barras exigem exatamente duas colunas: categoria (texto ou data) e valor (numérico). A primeira coluna torna-se o eixo X, a segunda torna-se a altura das barras:

Viagens por tipo de veí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;

Contagem diária de viagens

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 a sequência das barras. Ordene por value para comparações ranqueadas ou por category para progressões em séries temporais.

Gráficos de pizza

Gráficos de pizza exigem exatamente duas colunas: label (texto) e value (numérico). A primeira coluna torna-se os rótulos das fatias, a segunda determina o tamanho das fatias:

Distribuição de viagens 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;

Adicione cláusulas LIMIT para categorias com muitos valores. Gráficos de pizza com mais de 20 fatias ficam ilegíveis; limite para as 10–15 principais categorias.

Tabelas

Tabelas aceitam qualquer número de colunas com quaisquer tipos de dados. Selecione as colunas que deseja exibir:

Detalhes das viagens recentes

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;

Os nomes das colunas tornam-se cabeçalhos da tabela. Use aliases com espaços para cabeçalhos legíveis: distance_km as "Distance (km)".

Painéis de texto

Painéis de texto exibem valores de texto únicos ou strings formatadas. As instruções devem retornar uma coluna de texto:

Timestamp da última atualização de dados

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

Como usar variáveis globais

Variáveis globais fornecem valores reutilizáveis em múltiplas instruções SQL. Defina variáveis em Settings > Configuration > Global Variables, depois faça referência a elas usando ${variable_name} sintaxe.

Defina variáveis para valores que mudam periodicamente mas permanecem consistentes entre vários painéis: intervalos de data para análise, filtros por tipo de veículo ou valores de limiar. Quando esses valores mudarem, atualize a definição da variável uma vez em vez de editar instruções SQL individuais.

Uso de variáveis de intervalo de datas

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;

As variáveis armazenam valores de texto. Converta-os para os tipos apropriados no SQL: '${variable_name}'::date para datas, '${variable_name}'::integer para números.

Para parâmetros específicos de uma instrução que mudam com frequência, você pode usar blocos CTE de parâmetros no início:

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;

Esse padrão combina variáveis globais (intervalos de datas) com parâmetros específicos da instrução (limiares), mantendo todos os valores ajustáveis no topo para fácil manutenção.

Como acessar schemas do IoT Query

O IoT Query organiza dados em camadas Bronze, Silver e Gold. Entender qual camada usar economiza tempo e melhora a clareza do SQL. Para detalhes completos dos schemas, veja o IoT Query Schema Overview.

Camada Bronze contém pontos de rastreamento brutos dos dispositivos: bronze.tracking_data_core armazena cada posição GPS com timestamps, coordenadas e leituras de sensores. Use Bronze para análise no nível do ponto ou quando precisar de valores brutos de sensores que não foram processados nas camadas superiores.

Camada Silver fornece entidades processadas: silver.trips agrega pontos de rastreamento em registros de viagem com tempos de início/fim, distância e duração. silver.zone_visits registra quando dispositivos entram e saem de geofences. silver.idle_events identifica períodos em que veículos permanecem parados com o motor ligado. Use Silver para a maioria das necessidades de visualização, pois fornece estruturas prontas para análise.

Camada Gold oferece métricas pré-agregadas e modelos dimensionais para análises complexas. Use Gold para estatísticas de frota ou análise multidimensional que exigiriam junções complexas contra tabelas Silver.

Referencie tabelas usando schema.table formato: silver.trips, não apenas trips. Inclua filtros de intervalo de datas nas cláusulas WHERE para limitar os dados escaneados:

Sempre filtre por intervalos de tempo

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

A maioria das instruções SQL filtra por dispositivo, intervalo de tempo ou ambos. Adicione esses filtros cedo nas cláusulas WHERE para reduzir o volume de dados processados.

Como usar o Editor SQL

Acesse o Editor SQL na barra lateral esquerda em Tools. Use-o para três finalidades principais: testar a lógica antes de adicionar aos painéis, explorar schemas de dados para entender colunas disponíveis e exportar dados que não precisam de visualização.

O Editor SQL suporta múltiplas abas para diferentes instruções. Escreva SQL nas abas, execute com o botão "Execute Query" e veja os resultados na tabela abaixo. Os resultados mostram métricas de execução (tempo de execução, linhas retornadas) e suportam ordenação de colunas para exame rápido dos dados.

Exporte resultados como CSV usando o botão "Export CSV". Isso funciona para relatórios ad-hoc ou extrações de dados para análise externa. O Editor SQL não tem limite de linhas de resultado, ao contrário do SQL de visualização, que deve retornar conjuntos de dados focados.

Teste o SQL de visualização no Editor SQL antes de adicioná-lo aos painéis. Escreva a instrução, verifique se ela retorna as colunas e tipos de dados esperados e então copie-a para a guia SQL Query do editor de painel. Esse fluxo de trabalho detecta problemas estruturais antes de você configurar as definições de visualização.

Padrão de exploração para novos dados:

-- 1. Examine a estrutura da tabela
SELECT * FROM silver.trips LIMIT 10;

-- 2. Verifique a cobertura do intervalo de datas
SELECT 
  MIN(start_time) as earliest,
  MAX(start_time) as latest,
  COUNT(*) as total_trips
FROM silver.trips;

-- 3. Teste a lógica de filtragem
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 visualização (2 colunas 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;

Padrões SQL comuns

A maioria dos SQLs de visualização segue padrões semelhantes. Copie essas estruturas e ajuste filtros, colunas e agregações para suas necessidades específicas.

Contagens em séries temporais para acompanhar tendências

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;

Classificações por categoria 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 estatí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);

Sumários filtrados com múltiplas condições

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;

O que fazer quando o SQL falha

Falhas de execução se enquadram em três categorias: incompatibilidades estruturais com os requisitos de visualização, erros de sintaxe SQL ou filtros que retornam nenhum dado.

Incompatibilidades na estrutura de colunas

Ocorrem quando os resultados não correspondem às expectativas da visualização. Se você selecionou um gráfico de barras mas seu SQL retorna três colunas, o Dashboard Studio não consegue renderizá-lo. Verifique Dataset Requirements na guia SQL Query. O gráfico de barras precisa exatamente de duas colunas (categoria, valor), então ajuste sua cláusula SELECT:

-- Errado: três colunas
SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time;

-- Correto: duas colunas
SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id;

Erros de sintaxe SQL

Mostram mensagens de erro específicas. Problemas comuns incluem prefixos de schema ausentes (trips em vez de silver.trips), erros de digitação em nomes de colunas ou conversões de data incorretas. Teste as instruções no Editor SQL para ver mensagens de erro detalhadas com números de linha.

Resultados vazios

Apesar da execução bem-sucedida, indicam que os filtros excluem todos os dados. Teste o SQL sem cláusulas WHERE no Editor SQL para verificar se a tabela contém dados e então adicione filtros incrementalmente para identificar qual condição exclui os resultados esperados.

Problemas de desempenho

Se as instruções executam lentamente ou expiram, adicione filtros de intervalo de datas nas cláusulas WHERE. Operações que escaneiam tabelas inteiras processam milhões de linhas desnecessariamente:

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

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

Para orientações adicionais de desempenho, veja Como acessar schemas do IoT Query para melhores práticas sobre filtragem e seleção de schema.

Onde encontrar exemplos de SQL

O SQL Recipe Book fornece exemplos completos para análises telemáticas comuns. Essas receitas demonstram padrões para análise de viagens, cálculos de visitas a zonas, detecção de ociosidade e métricas de frota. Cada receita inclui a instrução SQL completa, explicação da lógica e resultados de exemplo.

Adapte os exemplos do Recipe Book para visualizações ajustando a cláusula SELECT para corresponder aos requisitos da visualização. Uma receita que retorna registros detalhados de viagens pode se tornar um gráfico de barras adicionando GROUP BY e agregação COUNT. Uma instrução que calcula métricas por veículo pode se tornar um bloco de estatística somando (SUM) sobre todos os veículos.

Você só precisa:

1. Copiar exemplos de Recipe Book para o Editor do Dashboard Studio.

2. Testar com seus dados reais.

3. Verificar os resultados e então modificar a cláusula SELECT para sua visualização alvo.

A lógica principal de WHERE e JOIN permanece a mesma; você ajusta apenas a estrutura de saída.

Para detalhes de schema, veja o IoT Query Schema Overview. Esta referência explica tabelas disponíveis, definições de colunas e relacionamentos entre as camadas Bronze, Silver e Gold.