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, pois 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 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 atualizam, 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 os 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, portanto 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 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 a painéis de visualização ou para extrações ad-hoc de dados que não precisam de visualização.

A diferença fundamental: 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 de colunas e tipos de dados específicos. O Dashboard Studio não pode renderizar um gráfico de barras a partir de três colunas nem um tile de estatística a partir de dados de texto. Verifique a seção Dataset Requirements na guia SQL Query para ver exatamente o que a 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
Tile de estatística	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

Tiles de estatística

Tiles 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 se torna o eixo X, a segunda se torna 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 valor para comparações ranqueadas ou por categoria 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 se torna 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 à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 de 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 se tornam 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 textuais ú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;

As consultas de relatório seguem as mesmas regras estruturais que as 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, você pode precisar combinar colunas que seriam escritas como consultas de painel separadas em um dashboard. Por exemplo, uma consulta de painel de 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 única instrução. A lógica central de filtragem e JOIN permanece a mesma que nas consultas de painel; apenas a cláusula SELECT precisa ser mais abrangente.

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, a consulta de relatório deve retornar todas as colunas necessárias para cada componente em uma única instrução SELECT.

Requisitos de colunas por componente

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

Componente	Colunas exigidas	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 de 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 dirigem 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 exigidas em conjunto.

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 servem ao gráfico, latitude e longitude servem ao mapa de localização, e todas as colunas aparecem na tabela de dados.

As tabelas brutas de telemática armazenam coordenadas e velocidade como inteiros escalados. 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 de 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 múltiplas 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 uma consulta de tile de estatística retornando resultados agregados, provavelmente ela não terá o detalhe em nível de 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 subjacentes brutos ou da camada Silver em vez disso.

Livro de Receitas SQL contém exemplos de consultas prontos para uso para análises comuns de frota. As 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 central de WHERE e JOIN transfere diretamente; ajuste apenas a cláusula SELECT para cobrir todos os componentes necessários.

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, então faça referência a elas usando ${variable_name} na sintaxe.

Defina variáveis para valores que mudam periodicamente, mas permanecem consistentes entre vários painéis: intervalos de data de análise, filtros por tipo de veículo ou valores de limite. Quando esses valores mudarem, 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 os tipos apropriados no 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 de parâmetros CTE 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 os esquemas do IoT Query

O IoT Query organiza dados nas camadas Bronze, Silver e Gold. Entender qual camada usar economiza tempo e melhora a clareza do SQL. Para detalhes completos do esquema, 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 em nível de 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 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 motores ligados. 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 em nível de frota ou análise multidimensional que exigiria joins complexos contra tabelas Silver.

Referencie tabelas usando schema.table no 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 processado.

Como usar o Editor SQL

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

O Editor SQL suporta várias abas para diferentes instruções. Escreva SQL em 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 possui 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 depois copie-a para a guia SQL Query do editor de painel. Esse fluxo captura problemas estruturais antes de configurar as opçõ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 maior parte do SQL 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;

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

Resumo filtrado 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 pode 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 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 então adicione filtros incrementalmente para identificar qual condição está excluindo os resultados esperados.

Problemas de desempenho

Se as instruções executarem 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ção adicional de desempenho, consulte Como acessar os esquemas do IoT Query para práticas recomendadas sobre filtragem e seleção de esquema.

Onde encontrar exemplos de SQL

O Livro de Receitas SQL 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 de visualização. Uma receita que retorna registros de viagem detalhados 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 tile de estatística somando across 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 central 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 as tabelas disponíveis, definições de colunas e relacionamentos entre as camadas Bronze, Silver e Gold.