Aqui está uma coleção de consultas avançadas em SQL, incluindo técnicas poderosas como funções de janela, manipulação de strings, consultas recursivas, otimização e exemplos específicos de BigQuery. Esses exemplos são úteis para resolver problemas complexos em projetos reais.
1. Funções de janela (Window Functions)
As funções de janela são ótimas para calcular agregações em subconjuntos de dados sem perder os detalhes linha a linha.
-- Exemplo: Classificar filmes por avaliação (rating) dentro de cada ano de lançamento
SELECT
title,
release_year,
rating,
ROW_NUMBER() OVER (PARTITION BY release_year ORDER BY rating DESC) AS rank
FROM netflix;
-- Exemplo: Calcular a média acumulada de avaliações
SELECT
title,
release_year,
rating,
AVG(rating) OVER (PARTITION BY release_year ORDER BY release_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS media_acumulada
FROM netflix;
Explicação:
-
ROW_NUMBER(): Atribui uma numeração a cada linha dentro de uma partição (nesse caso, por ano de lançamento). -
AVG()comOVER: Calcula uma média acumulada considerando as linhas anteriores dentro da mesma partição.
2. Manipulação de Strings
-- Exemplo: Dividir nomes dos filmes e extrair a primeira palavra
SELECT
title,
SPLIT(title, ' ')[OFFSET(0)] AS primeira_palavra
FROM netflix;
-- Exemplo: Buscar títulos que começam com "The"
SELECT
title
FROM netflix
WHERE title LIKE 'The%';
-- Exemplo: Substituir espaços por traços
SELECT
title,
REPLACE(title, ' ', '-') AS titulo_formatado
FROM netflix;
Explicação:
-
SPLIT(): Divide strings com base em um delimitador, útil para extração de partes. -
LIKE: Permite buscas com padrões. -
REPLACE(): Substitui partes específicas de uma string.
3. Consultas Recursivas
Consultas recursivas ajudam a resolver problemas hierárquicos, como árvores ou cadeias.
-- Exemplo: Montar uma hierarquia fictícia de categorias de filmes
WITH RECURSIVE categorias AS (
SELECT
id,
nome_categoria,
id_categoria_pai
FROM categorias_filmes
WHERE id_categoria_pai IS NULL -- Começa pela raiz
UNION ALL
SELECT
c.id,
c.nome_categoria,
c.id_categoria_pai
FROM categorias_filmes c
INNER JOIN categorias cat ON c.id_categoria_pai = cat.id
)
SELECT * FROM categorias;
Explicação:
-
WITH RECURSIVE: Define uma consulta que se auto-referência para explorar estruturas hierárquicas. - A consulta base (
WHERE id_categoria_pai IS NULL) identifica a raiz, e oUNION ALLpermite que as subcategorias sejam adicionadas.
4. Otimização com Indexes e Subconsultas CTE
Usar índices e dividir consultas em etapas pode melhorar a eficiência.
-- Exemplo: Calcular a receita total por gênero usando uma CTE
WITH receita_por_genero AS (
SELECT
genero,
SUM(receita) AS total_receita
FROM filmes
GROUP BY genero
)
SELECT
genero,
total_receita
FROM receita_por_genero
WHERE total_receita > 1000000;
Explicação:
-
WITH: Cria uma consulta temporária reutilizável (CTE) para tornar o código mais limpo e eficiente. - Filtragem em
total_receita > 1000000ocorre somente após o agrupamento.
5. Consultas BigQuery Avançadas
BigQuery usa SQL padrão, mas com recursos específicos para grandes volumes de dados e análises avançadas.
Exemplo 1: Análise de logs com ARRAYs e STRUCTs
-- Dataset fictício: Análise de cliques em anúncios
SELECT
user_id,
ARRAY_AGG(STRUCT(ad_id, click_time)) AS cliques
FROM `projeto.dataset.logs_cliques`
GROUP BY user_id;
Explicação:
-
ARRAY_AGG: Agrupa os registros em um array, mantendo múltiplos cliques por usuário. -
STRUCT: Permite armazenar colunas associadas dentro do array.
Exemplo 2: Análise de Tabelas Particionadas
BigQuery funciona bem com tabelas particionadas, o que melhora o desempenho em consultas específicas.
-- Consultar apenas partições recentes (últimos 7 dias)
SELECT
user_id,
COUNT(*) AS total_cliques
FROM `projeto.dataset.logs_cliques`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_id;
Explicação:
-
_PARTITIONTIME: Coluna especial usada em tabelas particionadas para identificar partições. - Limitar as partições reduz o custo da consulta.
Exemplo 3: Funções Analíticas com Geolocalização
-- Exemplo: Identificar o país mais frequente de acesso para cada usuário
SELECT
user_id,
APPROX_TOP_COUNT(country, 1) AS pais_mais_frequente
FROM `projeto.dataset.logs_acessos`
GROUP BY user_id;
Explicação:
-
APPROX_TOP_COUNT: Função otimizada para identificar valores mais frequentes em grandes conjuntos de dados.
Exemplo 4: Exploração de JSON em BigQuery
-- Exemplo: Extrair dados estruturados de uma coluna JSON
SELECT
JSON_VALUE(payload, '$.user.id') AS user_id,
JSON_VALUE(payload, '$.user.name') AS user_name
FROM `projeto.dataset.logs_api`;
Explicação:
-
JSON_VALUE: Extrai valores de uma string JSON com base em um caminho especificado. - Ideal para logs de APIs ou dados semiestruturados.
6. Consultas de Data e Tempo
-- Exemplo: Agrupar por dia da semana
SELECT
EXTRACT(DAYOFWEEK FROM data_acesso) AS dia_semana,
COUNT(*) AS total_acessos
FROM acessos
GROUP BY dia_semana
ORDER BY total_acessos DESC;
-- Exemplo: Encontrar a diferença em dias entre duas datas
SELECT
user_id,
DATE_DIFF(data_fim, data_inicio, DAY) AS dias_diferenca
FROM acessos;
-- Exemplo: Criar intervalos de tempo customizados
SELECT
TIMESTAMP_TRUNC(data_acesso, HOUR) AS hora,
COUNT(*) AS acessos_por_hora
FROM acessos
GROUP BY hora
ORDER BY hora;
Explicação:
- Funções de data/tempo como
EXTRACT,DATE_DIFFeTIMESTAMP_TRUNCajudam a analisar tendências temporais e criar relatórios.
😊
Top comments (0)