Consultas SQL avançadas que você deve conhecer

1. Window Functions
 

Funções de janela são usadas para cálculos em um conjunto de linhas relacionadas à linha atual. Vamos considerar um exemplo onde calculamos o total acumulado de vendas usando a função SUM( ) com a cláusula OVER( ). Imagine que temos uma tabela de dados de vendas chamada ‘Sales_Data’ que registra os valores de vendas em várias datas. Queremos calcular o total acumulado de vendas para cada data, o que significa o total de vendas até e incluindo cada data.
 

SELECT
 

    Date,

    Sales_Amount,

    SUM(Sales_Amount) OVER (ORDER BY Date) AS Running_Total
 

FROM
 

    Sales_Data;

Neste exemplo, a função SUM(Sales_Amount) OVER (ORDER BY Date) calcula o total acumulado de vendas até a data atual para cada linha na tabela 'Sales_Data'.
 

Funções de janela podem ser usadas para várias tarefas, como calcular totais acumulados, médias móveis, classificações e muito mais, sem colapsar o conjunto de resultados em uma única linha por grupo.
 

2. Common Table Expressions (CTEs)
 

CTEs (Common Table Expressions) fornecem uma maneira de criar conjuntos de resultados temporários que podem ser referenciados dentro de uma consulta. Eles melhoram a legibilidade e simplificam consultas complexas. Aqui está como podemos usar um CTE para calcular a receita total para cada categoria de produto.
 

WITH category_revenue AS (

    SELECT

        category,

        SUM(revenue) AS total_revenue

    FROM

        sales

    GROUP BY

        category

)

SELECT

    *

FROM

    category_revenue;
 

A consulta define um CTE chamado ‘category_revenue’. Ele calcula a receita total para cada categoria somando a receita da tabela de vendas e agrupando os resultados pela coluna de categoria. A consulta principal seleciona todas as colunas do CTE ‘category_revenue’, exibindo efetivamente a receita total calculada para cada categoria.
 

3. Consultas Recursivas
 

Consultas recursivas permitem percorrer estruturas de dados hierárquicas, como organogramas ou listas de materiais. Suponha que temos uma tabela que representa os relacionamentos de funcionários e queremos encontrar todos os subordinados de um determinado gerente.
 

WITH RECURSIVE subordinates AS (

    SELECT

        employee_id,

        name,

        manager_id

    FROM

        employees

    WHERE

        manager_id = 'manager_id_of_interest'

   

    UNION ALL

   

    SELECT

        e.employee_id,

        e.name,

        e.manager_id

    FROM

        employees e

    JOIN

        subordinates s

    ON

        e.manager_id = s.employee_id

)

SELECT

    *

FROM

    subordinates;
 

Este CTE recursivo encontra todos os funcionários que se reportam direta ou indiretamente a um gerente específico 'manager_id_of_interest'. Ele começa com os funcionários que se reportam diretamente ao gerente e, em seguida, encontra recursivamente seus subordinados, construindo a hierarquia.
 

4. Tabelas Dinâmicas
 

Tabelas dinâmicas transformam linhas em colunas, resumindo dados em um formato tabular. Digamos que temos uma tabela contendo dados de vendas e queremos dinamizar os dados para exibir as vendas totais de cada produto em diferentes meses.
 

SELECT

    product,

    SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,

    SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,

    SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar

FROM

    sales_data

GROUP BY

    product;
 

Esta consulta agrega os dados de vendas para cada produto por mês usando agregação condicional. Ela soma as vendas de janeiro, fevereiro e março separadamente para cada produto, resultando em uma tabela que mostra as vendas totais por produto para esses meses.
 

5.Funções Analíticas
 

Funções analíticas calculam valores agregados com base em um grupo de linhas. Por exemplo, podemos usar a função ROW_NUMBER() para atribuir um número de linha único a cada registro em um conjunto de dados.
 

SELECT

    customer_id,

    order_id,

    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank

FROM

    orders;
 

Esta consulta atribui uma classificação única a cada pedido por cliente com base na data do pedido, usando a função de janela ROW_NUMBER(). O resultado mostra a sequência de pedidos feitos por cada cliente.
 

6. Unpivot
 

Unpivoting é o oposto de pivotar, onde colunas são transformadas em linhas. Digamos que temos uma tabela com dados de vendas agregados por mês e queremos "despivotar" para analisar as tendências ao longo do tempo.
 

SELECT

    product,

    month,

    sales

FROM

    sales_data

UNPIVOT (

    sales FOR month IN (sales_jan AS 'Jan', sales_feb AS 'Feb', sales_mar AS 'Mar')

) AS unpivoted_sales;
 

Esta consulta transforma as colunas de vendas mensais em linhas, facilitando a análise de tendências ao longo do tempo por produto. Cada linha representa as vendas de um produto em um mês específico.
 

7. Agregação Condicional
 

A agregação condicional envolve a aplicação de funções de agregação condicionalmente com base em critérios especificados. Por exemplo, podemos querer calcular o valor médio de vendas apenas para pedidos feitos por clientes recorrentes.
 

SELECT

    customer_id,

    AVG(CASE WHEN order_count > 1 THEN order_total ELSE NULL END) AS avg_sales_repeat_customers

FROM (

    SELECT

        customer_id,

        COUNT(*) AS order_count,

        SUM(order_total) AS order_total

    FROM

        orders

    GROUP BY

        customer_id

) AS customer_orders;
 

Esta consulta calcula o total médio de pedidos para clientes que fizeram mais de um pedido. Ela agrega a contagem de pedidos e o valor total dos pedidos para cada cliente, em seguida, calcula a média para os clientes recorrentes.
 

8. Funções de Data
 

Funções de data em SQL permitem a manipulação e extração de informações relacionadas a datas. Por exemplo, podemos usar a função DATE_TRUNC() para agrupar dados de vendas por mês.
 

SELECT

    DATE_TRUNC('month', order_date) AS month,

    SUM(sales_amount) AS total_sales

FROM

    sales

GROUP BY

    DATE_TRUNC('month', order_date);
 

Esta saída mostra o valor total de vendas (total_sales) agregado para cada mês como month, onde cada mês é representado pelo primeiro dia daquele mês (por exemplo, 2023-01-01 para janeiro). As vendas totais são somadas para cada respectivo mês.
 

9. Instrução MERGE ou UPSERT
 

As instruções MERGE (também conhecidas como UPSERT ou ON DUPLICATE KEY UPDATE) permitem inserir, atualizar ou excluir registros em uma tabela de destino com base nos resultados de uma junção com uma tabela de origem. Vamos supor que queremos sincronizar duas tabelas contendo dados de clientes.
 

MERGE INTO customers_target t

USING customers_source s

ON t.customer_id = s.customer_id

WHEN MATCHED THEN

    UPDATE SET

        t.name = s.name,

        t.email = s.email

WHEN NOT MATCHED THEN

    INSERT (customer_id, name, email)

    VALUES (s.customer_id, s.name, s.email);
 

A instrução MERGE atualiza a tabela customers_target com base na tabela customers_source. Se um customer_id em customers_source corresponder a um em customers_target, o nome e o email são atualizados. Se não houver correspondência, uma nova linha é inserida.
 

10. Instruções CASE
 

Instruções CASE permitem lógica condicional dentro de consultas SQL. Por exemplo, podemos usar uma instrução CASE para categorizar clientes com base no seu valor total de compra.
 

SELECT

    customer_id,

    CASE

        WHEN total_purchase_amount >= 1000 THEN 'Platinum'

        WHEN total_purchase_amount >= 500 THEN 'Gold'

        ELSE 'Silver'

    END AS customer_category

FROM (

    SELECT

        customer_id,

        SUM(order_total) AS total_purchase_amount

    FROM

        orders

    GROUP BY

        customer_id

) AS customer_purchases;
 

A consulta classifica os clientes em categorias com base no seu valor total de compra. Clientes com um valor total de compra de $1000 ou mais são rotulados como 'Platinum', aqueles com $500 a $999 são rotulados como 'Gold' e aqueles com menos de $500 são rotulados como 'Silver'.
 

**Explicação:**
 

1. **Customer 1:** Total de compras = 200 + 300 = 500. Classificado como 'Gold'.

2. **Customer 2:** Total de compras = 800. Classificado como 'Gold'.

3. **Customer 3:** Total de compras = 150 + 400 = 550. Classificado como 'Silver'.

4. **Customer 4:** Total de compras = 1200. Classificado como 'Platinum'.
 

11. Funções de String
 

Funções de string em SQL permitem a manipulação de dados textuais. Por exemplo, podemos usar a função CONCAT() para concatenar nomes e sobrenomes.
 

SELECT

    CONCAT(first_name, ' ', last_name) AS full_name

FROM

    employees;
 

Vamos considerar um conjunto de dados de exemplo e explicar a saída.
 

**Dados de exemplo na tabela employees:**

| first_name | last_name |

|------------|-----------|

| John | Doe |

| Jane | Smith |

| Alice | Johnson |

| Bob | Brown |

**Saída:**

| full_name |

|----------------|

| John Doe |

| Jane Smith |

| Alice Johnson |

| Bob Brown |
 

A consulta concatena as colunas first_name e last_name da tabela employees com um espaço entre elas, criando um full_name para cada funcionário.
 

**Explicação:**
 

1. **John Doe:** As colunas first_name ("John") e last_name ("Doe") são concatenadas com um espaço entre elas, resultando em "John Doe".

2. **Jane Smith:** As colunas first_name ("Jane") e last_name ("Smith") são concatenadas, resultando em "Jane Smith".

3. **Alice Johnson:** As colunas first_name ("Alice") e last_name ("Johnson") são concatenadas, resultando em "Alice Johnson".

4. **Bob Brown:** As colunas first_name ("Bob") e last_name ("Brown") são concatenadas, resultando em "Bob Brown".
 

12. Conjuntos de Agrupamento
 

Os conjuntos de agrupamento (Grouping Sets) permitem a agregação de dados em múltiplos níveis de granularidade em uma única consulta. Vamos supor que queremos calcular a receita total de vendas por mês e ano.
 

SELECT

    YEAR(order_date) AS year,

    MONTH(order_date) AS month,

    SUM(sales_amount) AS total_revenue

FROM

    sales

GROUP BY

    GROUPING SETS (

        (YEAR(order_date), MONTH(order_date)),

        YEAR(order_date),

        MONTH(order_date)

    );

**Dados de exemplo na tabela sales:**

| order_date | sales_amount |

|------------|--------------|

| 2023-01-15 | 1000 |

| 2023-01-20 | 1500 |

| 2023-02-10 | 2000 |

| 2023-03-05 | 2500 |

| 2024-01-10 | 3000 |

| 2024-01-20 | 3500 |

| 2024-02-25 | 4000 |

**Saída:**

| year | month | total_revenue |

|------|-------|---------------|

| 2023 | 1 | 2500 |

| 2023 | 2 | 2000 |

| 2023 | 3 | 2500 |

| 2024 | 1 | 6500 |

| 2024 | 2 | 4000 |

| 2023 | NULL | 7000 |

| 2024 | NULL | 10500 |

| NULL | 1 | 9000 |

| NULL | 2 | 6000 |

| NULL | 3 | 2500 |

**Explicação:**

1. **Agrupamento por ano e mês:**

 - 2023-01: 1000 + 1500 = 2500

 - 2023-02: 2000

 - 2023-03: 2500

 - 2024-01: 3000 + 3500 = 6500

 - 2024-02: 4000

2. **Agrupamento por ano:**

 - 2023: 2500 (Jan) + 2000 (Fev) + 2500 (Mar) = 7000

 - 2024: 6500 (Jan) + 4000 (Fev) = 10500

3. **Agrupamento por mês:**

 - Janeiro (todos os anos): 2500 (2023) + 6500 (2024) = 9000

 - Fevereiro (todos os anos): 2000 (2023) + 4000 (2024) = 6000

 - Março (todos os anos): 2500
 

Este resultado fornece subtotais para cada mês de cada ano, totais gerais para cada ano e totais gerais para cada mês em todos os anos.
 

13. Cross Joins
 

Os joins cruzados (CROSS JOIN) produzem o produto cartesiano de duas tabelas, resultando em uma combinação de cada linha de cada tabela. Por exemplo, podemos usar um CROSS JOIN para gerar todas as possíveis combinações de produtos e clientes.
 

SELECT

    p.product_id,

    p.product_name,

    c.customer_id,

    c.customer_name

FROM

    products p

CROSS JOIN

    customers c;
 

Vamos considerar um conjunto de dados de exemplo para as tabelas products e customers.
 

**Tabela products:**

| product_id | product_name |

|------------|--------------|

| 1 | Product A |

| 2 | Product B |

**Tabela customers:**

| customer_id | customer_name |

|-------------|---------------|

| 101 | Customer X |

| 102 | Customer Y |

**Saída:**

| product_id | product_name | customer_id | customer_name |

|------------|--------------|-------------|---------------|

| 1 | Product A | 101 | Customer X |

| 1 | Product A | 102 | Customer Y |

| 2 | Product B | 101 | Customer X |

| 2 | Product B | 102 | Customer Y |
 

A consulta realiza um CROSS JOIN entre as tabelas products e customers, resultando em um produto cartesiano. Isso significa que cada produto é emparelhado com cada cliente, gerando todas as possíveis combinações de produtos e clientes.
 

**Explicação:**

1. **Product A com Customer X:** Combinação de product_id 1 e customer_id 101.

2. **Product A com Customer Y:** Combinação de product_id 1 e customer_id 102.

3. **Product B com Customer X:** Combinação de product_id 2 e customer_id 101.

4. **Product B com Customer Y:** Combinação de product_id 2 e customer_id 102.
 

O resultado é um conjunto completo de combinações entre produtos e clientes, demonstrando o produto cartesiano das duas tabelas.
 

14. Tabelas Derivadas
 

As visualizações inline (também conhecidas como tabelas derivadas) permitem a criação de conjuntos de resultados temporários dentro de uma consulta SQL. Vamos supor que queremos encontrar clientes que fizeram compras acima do valor médio dos pedidos.
 

SELECT

    customer_id,

    order_total

FROM (

    SELECT

        customer_id,

        SUM(order_total) AS order_total

    FROM

        orders

    GROUP BY

        customer_id

) AS customer_orders

WHERE

    order_total > (

        SELECT

            AVG(order_total)

        FROM

            orders

    );

**Tabela orders:**

| customer_id | order_total |

|-------------|-------------|

| 1 | 100 |

| 1 | 200 |

| 2 | 500 |

| 3 | 300 |

| 3 | 200 |

| 4 | 700 |

**Cálculo do total de pedidos para cada cliente:**

| customer_id | order_total |

|-------------|-------------|

| 1 | 300 |

| 2 | 500 |

| 3 | 500 |

| 4 | 700 |

**Cálculo do valor médio dos pedidos:**
 

Para calcular o valor médio dos pedidos, somamos todos os valores de pedidos e dividimos pelo número total de pedidos:
 

(100 + 200 + 500 + 300 + 200 + 700) / 6 = 2000 / 6 ≈ 333.33

**Filtragem de clientes com pedidos totais acima da média:**

| customer_id | order_total |

|-------------|-------------|

| 2 | 500 |

| 3 | 500 |

| 4 | 700 |

**Explicação:**

1. **Cálculo do total de pedidos para cada cliente:** A subconsulta agrupa os pedidos por customer_id e soma os valores de order_total para cada cliente.

2. **Cálculo do valor médio dos pedidos:** Outra subconsulta calcula a média dos valores dos pedidos em toda a tabela orders.

3. **Filtragem de clientes:** A consulta externa filtra os clientes cujos totais de pedidos são maiores que o valor médio dos pedidos.
 

Dessa forma, a consulta finaliza exibindo os clientes cujos totais de pedidos excedem a média, resultando na saída correta.
 

15. Operadores de Conjunto
 

Operadores de conjunto como UNION, INTERSECT e EXCEPT permitem combinar os resultados de duas ou mais consultas. Por exemplo, podemos usar o operador UNION para mesclar os resultados de duas consultas em um único conjunto de resultados.
 

SELECT

    product_id,

    product_name

FROM

    products

UNION

SELECT

    product_id,

    product_name

FROM

    archived_products;
 

Esta consulta combina os resultados das tabelas products e archived_products, eliminando quaisquer entradas duplicadas, para criar uma lista unificada de IDs e nomes de produtos. O operador UNION garante que cada produto apareça apenas uma vez no resultado final.
 

**Dados de exemplo:**

**Tabela products:**

| product_id | product_name |

|------------|-----------------|

| 1 | Chocolate Bar |

| 2 | Dark Chocolate |

| 3 | Milk Chocolate |

**Tabela archived_products:**

| product_id | product_name |

|------------|-----------------|

| 3 | Milk Chocolate |

| 4 | White Chocolate |

| 5 | Almond Chocolate|

**Saída:**

| product_id | product_name |

|------------|-----------------|

| 1 | Chocolate Bar |

| 2 | Dark Chocolate |

| 3 | Milk Chocolate |

| 4 | White Chocolate |

| 5 | Almond Chocolate|
 

A consulta mescla os resultados das tabelas products e archived_products, eliminando os duplicados (no caso, Milk Chocolate com product_id 3), criando uma lista unificada de produtos.