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.

---

😊