BigQuery SQL

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() com OVER: 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 o UNION ALL permite 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 > 1000000 ocorre 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_DIFF e TIMESTAMP_TRUNC ajudam a analisar tendências temporais e criar relatórios.

😊