Escrevendo consultas SQL

O Dashboard Studio usa SQL para recuperar dados dos esquemas 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 para ambos os contextos, com ênfase nos requisitos de visualização, uma vez que eles têm restrições estruturais específicas.

Onde o SQL é usado

O Dashboard Studio fornece dois ambientes SQL para propósitos diferentes. Entender quando usar cada um ajuda você a trabalhar de forma mais eficiente.

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. SQL de visualização não pode modificar dados; todas as instruções são executadas como operações SELECT somente leitura contra esquemas do IoT Query.

Relatórios usam a mesma abordagem de SQL de visualização que os painéis do dashboard. Um relatório executa uma consulta que alimenta três visualizações simultaneamente: a tabela de dados, o gráfico e o mapa de localização. A instrução deve retornar todas as colunas necessárias para os três componentes, então inclua colunas de coordenadas, tempo e métricas juntas em um único SELECT.

Editor SQL suporta exploração de dados e exportação. Acesse o Editor SQL pela barra lateral esquerda em Ferramentas. 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 resultado completas com ordenação de colunas e fornece métricas de execução. Use-o para testar lógica antes de adicionar SQL aos painéis de visualização ou para extrações ad-hoc de dados que não precisam de visualização.

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

Como escrever SQL para visualizações

O SQL de visualização deve retornar contagens específicas de colunas e tipos de dados. O Dashboard Studio não consegue renderizar um gráfico de barras a partir de três colunas ou um bloco estatístico a partir de dados de texto. Verifique a seção Requisitos do Conjunto de Dados 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 da consulta	Exemplo
Bloco estatístico	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 estatísticos

Blocos estatísticos 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 valor numérico único. 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;

Contagens diárias 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: rótulo (texto) e valor (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 20+ fatias tornam-se ilegíveis; limite às 10-15 principais categorias.

Tabelas

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

Detalhes recentes de viagens

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:

Carimbo de data/hora da última atualização de dados

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

As consultas de relatório seguem as mesmas regras estruturais das consultas de visualização em painéis do dashboard. Como uma única instrução alimenta a tabela de dados, o gráfico e o mapa de localização juntos, pode ser necessário combinar colunas que seriam escritas como consultas de painéis separadas em um dashboard. Por exemplo, uma consulta de painel para gráfico de barras que retorna duas colunas não é suficiente para um relatório que também precisa de coordenadas GPS para o mapa de localização. Inclua todas as colunas necessárias para cada componente em uma instrução. A lógica principal de filtragem e JOIN permanece a mesma das consultas de painel; apenas a cláusula SELECT precisa ser mais ampla.

Como escrever SQL para relatórios

Um relatório executa uma consulta SQL que alimenta três componentes simultaneamente: a tabela de dados, o gráfico e o mapa de localização. Ao contrário dos painéis do dashboard, onde cada painel tem sua própria consulta focada, uma consulta de relatório deve retornar todas as colunas necessárias para cada componente em uma única instrução SELECT.

Requisitos de coluna por componente

Cada componente do relatório tem requisitos específicos de colunas. Sua consulta deve satisfazer todos os componentes que você habilitou.

Componente	Colunas obrigatórias	Observações
Tabela de dados	Qualquer colunas	Todas as colunas retornadas aparecem como colunas da tabela
Gráfico	Pelo menos uma coluna de tempo ou categoria, pelo menos uma coluna numérica	As colunas do eixo são selecionadas nas configurações do gráfico
Mapa de localização	Latitude e longitude em graus decimais	O Dashboard Studio detecta automaticamente as colunas de coordenadas

Como a tabela de dados aceita quaisquer colunas, ela não impõe restrições adicionais. O gráfico e o mapa de localização guiam a maioria das decisões estruturais.

Combinando componentes em uma consulta

Uma consulta que retorna apenas as colunas necessárias para um gráfico (duas colunas: categoria e valor) não pode também alimentar um mapa de localização. Você deve incluir todas as colunas obrigatórias juntos.

O exemplo a seguir retorna colunas para os três componentes: uma coluna de tempo e uma coluna numérica para o gráfico, colunas de coordenadas para o mapa de localização e atributos adicionais que aparecem na tabela de dados.

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

Nessa consulta, device_time e speed alimentam o gráfico, latitude e longitude alimentam o mapa de localização, e todas as colunas aparecem na tabela de dados.

As tabelas brutas de telemetria armazenam coordenadas e velocidade como inteiros escalonados. As coordenadas são divididas por 10.000.000 (10⁷) para converter em graus decimais, e a velocidade é dividida por 100 (10²) para converter em km/h. Aplique essas conversões em qualquer consulta que leia das raw_telematics_data tables.

Adaptando consultas de painel do dashboard para relatórios

Qualquer consulta de painel de um dashboard é um ponto de partida válido para um relatório. O ajuste necessário depende de quais componentes você deseja habilitar.

Se a consulta do painel já for uma visualização de tabela retornando várias colunas, ela pode já incluir tudo o que é necessário. Adicione colunas de coordenadas se o mapa de localização for requerido.

Se a consulta do painel for um gráfico de barras ou bloco estatístico que retorna resultados agregados, provavelmente faltará o detalhamento por linha necessário para a tabela de dados e o mapa de localização. Nesse caso, remova a agregação e trabalhe a partir dos dados brutos ou da camada Silver subjacente.

SQL Recipe Book contém exemplos de consultas prontos para uso em análises comuns de frota. Receitas do livro podem ser adaptadas para relatórios adicionando colunas de coordenadas quando o mapa de localização for necessário. A lógica principal de WHERE e JOIN transfere diretamente; ajuste apenas a cláusula SELECT para abranger todos os componentes exigidos.

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, em seguida referencie-as usando ${variable_name} sintaxe.

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

Usando 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-as para tipos apropriados em SQL: '${variable_name}'::date para datas, '${variable_name}'::integer para números.

Para parâmetros específicos de instruções 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 esquemas do IoT Query

O IoT Query organiza dados nas camadas Raw data, Transformation e Insight. Entender qual camada usar economiza tempo e melhora a clareza do SQL. Para detalhes completos do esquema, veja o IoT Query Schema Overview.

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

Camada Transformation fornece entidades processadas: silver.trips agrega pontos de rastreamento em registros de viagem com horários 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 Transformation para a maioria das necessidades de visualização, pois fornece estruturas prontas para análise.

Camada Insight oferece métricas pré-agregadas e modelos dimensionais para análises complexas. Use Insight para estatísticas de frota ou análises multidimensionais que exigiriam joins complexos contra tabelas Silver.

Faça referência às tabelas usando schema.table formato: silver.trips, não apenas trips. Inclua filtros de intervalo de datas em 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 no início das cláusulas WHERE para reduzir o volume de dados processados.

Como usar o Editor SQL

Acesse o Editor SQL pela barra lateral esquerda em Ferramentas. Use-o para três propósitos principais: testar lógica antes de adicionar aos painéis, explorar esquemas de dados para entender as 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 visualize 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, diferente 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, em seguida, copie-a para a guia SQL Query do editor de painel. Esse fluxo de trabalho detecta problemas estruturais antes que você configure 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 conforme 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);

Resumos 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 de estrutura de coluna

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 pode renderizá-lo. Verifique Requisitos do Conjunto de Dados 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 esquema 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, em seguida, adicione filtros incrementalmente para identificar qual condição exclui os resultados esperados.

Problemas de desempenho

Se as instruções forem executadas lentamente ou expirarem, 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 de 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 esquemas do IoT Query para melhores práticas sobre filtragem e seleção de esquema.

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

Adapte os exemplos do Recipe Book para visualizações ajustando a cláusula SELECT para corresponder aos requisitos de visualização. Uma receita que retorna registros detalhados de viagens pode tornar-se um gráfico de barras adicionando GROUP BY e agregação COUNT. Uma instrução que calcula métricas por veículo pode tornar-se um bloco estatístico adicionando SUM em 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 do esquema, veja o IoT Query Schema Overview. Esta referência explica tabelas disponíveis, definições de colunas e relacionamentos entre as camadas Raw data, Transformation e Insight.