SQL Avançado: Explorando a Fundo as 6 Funções Mais Poderosas

Se você quer elevar seu conhecimento em SQL para o próximo nível, dominar funções avançadas é essencial. Aqui, detalhamos cada uma das 6 funções mais poderosas com exemplos completos e explicações aprofundadas. Vamos nessa?

SQL Avançado

1. Funções de Janela (Window Functions)

Funções de janela permitem executar cálculos sobre um conjunto de linhas relacionadas sem alterar a estrutura das linhas retornadas. São amplamente usadas em análise de dados.

Exemplo: ROW_NUMBER()

Gera um número sequencial para cada linha dentro de uma partição.

Query:

SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    employee_name,
    department,
    salary
FROM employees;

Explicação:

• PARTITION BY department: Cria grupos (partições) para cada departamento.

• ORDER BY salary DESC: Ordena os salários em ordem decrescente dentro de cada partição.

• ROW_NUMBER(): Gera o número sequencial baseado na ordem definida..

Resultado:

Rank	Employee Name	Department	Salary
1	Marcos	Sales	7000
2	Fernanda	Sales	5000
1	Monica	IT	9000
2	Maria Clara	IT	8000

Em geral é usado para criar rankings, identificar duplicatas ou aplicar lógicas condicionais baseadas na posição.

2. CTEs Recursivas (Recursive Common Table Expressions)

CTEs recursivas são ideais para trabalhar com estruturas hierárquicas, como organogramas ou gráficos.

Exemplo: Hierarquia de Gerentes e Subordinados em uma empresa

Query:

WITH RECURSIVE EmployeeHierarchy AS (
	SELECT employee_id, manager_id, employee_name
	FROM employees
	WHERE manager_id IS NULL
UNION ALL
	SELECT e.employee_id, e.manager_id, e.employee_name
	FROM employees e
	INNER JOIN 
	EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)

SELECT * FROM EmployeeHierarchy;

Explicação:

• Base case: WHERE manager_id IS NULL identifica o nível raiz (gerente principal).

• Recursive part: INNER JOIN conecta subordinados com seus gerentes a cada iteração.

• Resultado acumulado: Retorna uma árvore completa de gerentes e subordinados.

Resultado:

Employee ID	Manager ID	Employee Name
1	NULL	CEO
2	1	Manager A
3	2	Employee B
4	2	Employee C

Resumindo, é perfeito para construir estruturas de árvore ou realizar cálculos cumulativos baseados em hierarquia.

3. Funções para JSON

Manipular dados semi-estruturados como JSON é essencial em bancos de dados modernos.

Exemplo: Extraindo Valores do JSON

Query:

SELECT JSON_VALUE(data, '$.employee.name') AS employee_name 
FROM employee_data;

Explicação:

• JSON_VALUE(): Extrai um valor específico de uma chave JSON.

• '$.employee.name': O caminho que aponta para o valor dentro do JSON.

Dado de exemplo:

{
	"employee": {
		"name": "Alice",
		"department": "Sales"
	}
}

Resultado:

Employee Name

Alice

Em geral, a função JSON_VALUE é útil para integrar bancos relacionais com APIs e dados em formato JSON.

4. Expressões Regulares (Regex)

As funções regex ajudam a identificar padrões complexos em strings e é Ideal para padronizar formatos de dados antes de análises ou validações.

Exemplo: Limpeza de Números de Telefone

Query:

SELECT REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_number
FROM contacts;

Explicação:

• REGEXP_REPLACE(): Substitui partes da string que correspondem ao padrão.

• [^0-9]: Seleciona tudo que não é número.

• '' : Remove os caracteres selecionados.

Dado de exemplo:

Phone Number

(123) 456-7890

+1-800-555-0199

Resultado:

Clean Number

1234567890

18005550199

5. Funções de Particionamento

O particionamento é muito comum em práticas de SQL Avançado, neste caso o particionamento divide dados em grupos para cálculos estatísticos ou análises comparativas.

Exemplo: Quartis de Salários

Query:

SELECT 
	NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
	employee_name,
	salary
FROM employees;

Explicação:

• NTILE(4): Divide as linhas em 4 grupos iguais.

• OVER (ORDER BY salary DESC): Define a ordem dos dados antes de criar os grupos.

Dado de exemplo:

Employee Name	Salary
Clara	9000
Alice	7000
David	8000
Bob	5000

Resultado:

Quartile	Employee Name	Salary
1	Clara	9000
1	Alice	7000
2	David	8000
2	Bob	5000

6. Exemplo de PIVOT com SQL Avançado

A cláusula PIVOT é extremamente útil quando queremos transformar dados de linhas em colunas, o que facilita análises e visualizações. Essa funcionalidade é frequentemente usada para relatórios que exigem a reorganização dos dados de maneira mais intuitiva.

Exemplo: Total de Vendas por Mês e Categoria

Imagine que você tem uma tabela com vendas contendo as seguintes colunas:

• product_category: Categoria do produto.

• sales_month: Mês da venda.

• sales_amount: Valor total das vendas.

Estrutura da Tabela:

Product Category	Sales Month	Sales Amount
Electronics	January	1000
Electronics	February	1500
Clothing	January	2000
Clothing	February	2500
Furniture	January	3000
Furniture	February	3500

Queremos transformar os meses de vendas em colunas e mostrar os valores de vendas como dados de cada célula.

Query:

SELECT 
	product_category,
	[January] AS January_Sales,
	[February] AS February_Sales
FROM 
	(SELECT 
		product_category, 
		sales_month, 
		sales_amount 
	FROM sales_data) AS SourceTable
PIVOT
	(SUM(sales_amount) 
		FOR sales_month IN ([January], [February])
	) AS PivotTable;

Detalhamento da Query:

1. Subconsulta SourceTable:

   • Seleciona os dados originais que serão pivotados.

     
     

2. Cláusula PIVOT:

   • SUM(sales_amount): Soma os valores de vendas para cada categoria e mês.

   • FOR sales_month IN ([January], [February]): Especifica quais valores de sales_month serão transformados em colunas.

     
     

3. Colunas Pivotadas:

   • Os meses (January e February) se tornam colunas.

   
   

Resultado:

Product Category	January_Sales	February_Sales
Electronics	1000	1500
Clothing	2000	2500
Furniture	3000	3500

Conclusão

Essas funções avançadas do SQL tornam suas consultas mais poderosas e eficientes. Experimente cada uma em cenários reais para entender seu impacto.

Curtiu o conteúdo? Inscreva-se na nossa newsletter para receber mais tutoriais e dicas diretamente no seu e-mail!