Comandos SQL | SQL

• Comandos SQL | SQL

Explicações sobre Right Join, Left Join, Inner Join, Full Outer Join | Comandos SQL

• A left join B
  • A terá todos os registros mostrados mesmo que não tenha relação com B

ROW_NUMBER() RANK() DENSE_RANK() NTILE()

As funções de janela no SQL Server, como ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE(), são usadas para calcular valores de forma cumulativa ou distribuir registros em conjuntos. Vamos entender cada uma delas com exemplos e explicações.

1. ROW_NUMBER()

A função ROW_NUMBER() atribui um número sequencial único a cada linha em uma partição ou conjunto de resultados, começando de 1. Ela não considera empates e, portanto, sempre gera números únicos.

Sintaxe:

ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column)

Exemplo:

Imagine uma tabela Vendas com os seguintes dados:

Vendedor	ValorVenda
João	1000
Maria	1500
Pedro	1000
Ana	2000
Carlos	1500

A consulta abaixo atribui um número de linha único a cada venda, ordenada por ValorVenda:

SELECT 
    Vendedor, 
    ValorVenda, 
    ROW_NUMBER() OVER (ORDER BY ValorVenda DESC) AS RowNumber
FROM Vendas;

Resultado:

Vendedor	ValorVenda	RowNumber
Ana	2000	1
Maria	1500	2
Carlos	1500	3
João	1000	4
Pedro	1000	5

2. RANK()

A função RANK() atribui um número de classificação para cada linha, levando em consideração empates. Quando duas ou mais linhas têm os mesmos valores, elas recebem a mesma classificação, e o próximo número de classificação é pulado.

Sintaxe:

RANK() OVER ([PARTITION BY column] ORDER BY column)

Exemplo:

Vamos usar os mesmos dados da tabela Vendas e agora aplicar a função RANK():

SELECT 
    Vendedor, 
    ValorVenda, 
    RANK() OVER (ORDER BY ValorVenda DESC) AS Ranking
FROM Vendas;

Resultado:

Vendedor	ValorVenda	Ranking
Ana	2000	1
Maria	1500	2
Carlos	1500	2
João	1000	4
Pedro	1000	4

Explicação:

• Ana tem a maior venda e recebe o ranking 1.
• Maria e Carlos estão empatados em 1500, ambos com ranking 2.
• Como houve um empate, o próximo número de ranking é pulado, indo diretamente para 4.

3. DENSE_RANK()

A função DENSE_RANK() é similar à RANK(), mas não pula números de classificação após um empate. Isso significa que se houver empates, a próxima linha receberá o próximo número de classificação sequencial.

Sintaxe:

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Exemplo:

Aplicando a função DENSE_RANK() à mesma tabela Vendas:

SELECT 
    Vendedor, 
    ValorVenda, 
    DENSE_RANK() OVER (ORDER BY ValorVenda DESC) AS DenseRanking
FROM Vendas;

Resultado:

Vendedor	ValorVenda	DenseRanking
Ana	2000	1
Maria	1500	2
Carlos	1500	2
João	1000	3
Pedro	1000	3

Explicação:

• A principal diferença é que, após o empate entre Maria e Carlos, a próxima classificação continua de forma sequencial (sem pular o número 3).

4. NTILE()

A função NTILE() distribui as linhas de um conjunto de resultados em um número definido de grupos (ou "baldes"), e atribui um número de grupo para cada linha. Isso é útil quando você quer dividir os dados em partes aproximadamente iguais.

Sintaxe:

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

• n representa o número de grupos em que você deseja dividir os dados.

Exemplo:

Vamos aplicar a função NTILE(3) para dividir as vendas em 3 grupos, com base em ValorVenda:

SELECT 
    Vendedor, 
    ValorVenda, 
    NTILE(3) OVER (ORDER BY ValorVenda DESC) AS NTileGroup
FROM Vendas;

Resultado:

Vendedor	ValorVenda	NTileGroup
Ana	2000	1
Maria	1500	1
Carlos	1500	2
João	1000	2
Pedro	1000	3

Explicação:

• A função NTILE(3) divide as vendas em 3 grupos aproximadamente iguais. Cada grupo recebe um número de 1 a 3.
• Como o número de registros não é divisível por 3, alguns grupos podem ter mais ou menos registros.

---

Resumo das diferenças principais:

• ROW_NUMBER(): Atribui números únicos e sequenciais a cada linha.
• RANK(): Atribui a mesma classificação para valores empatados, mas pula a próxima classificação após o empate.
• DENSE_RANK(): Atribui a mesma classificação para valores empatados, mas não pula a próxima classificação após o empate.
• NTILE(): Divide o conjunto de resultados em um número especificado de grupos e atribui a cada linha um número correspondente ao seu grupo.

Essas funções são amplamente utilizadas para análise de dados e para gerar rankings, numeração ou distribuição em grupos de forma eficiente.

LEAD() e LAG()

As funções LEAD e LAG no SQL Server são funções de janela que permitem acessar dados de linhas anteriores ou posteriores em relação à linha atual dentro de um conjunto de resultados. Essas funções são muito úteis quando você deseja comparar valores de diferentes linhas em uma mesma consulta sem precisar fazer um JOIN ou subconsulta complexa.

1. Função LAG

A função LAG retorna o valor de uma linha anterior em relação à linha atual. É útil para calcular diferenças entre a linha atual e a linha anterior.

Sintaxe:

LAG (valor_a_ser_retornado, deslocamento, valor_padrão) OVER (PARTITION BY <coluna_partição> ORDER BY <coluna_ordem>)

• valor_a_ser_retornado: A coluna ou expressão da qual você quer acessar o valor da linha anterior.
• deslocamento (opcional): O número de linhas anteriores que você deseja acessar. O valor padrão é 1.
• valor_padrão (opcional): Um valor retornado quando não houver linha anterior (ex: na primeira linha do conjunto). O valor padrão é NULL.
• OVER: Define como os dados serão particionados e ordenados.

Exemplo de uso:

Imagine que temos uma tabela Vendas com as colunas venda_id, data_venda, e valor_venda. Queremos ver a diferença entre o valor da venda atual e a venda anterior:

SELECT 
    venda_id,
    data_venda,
    valor_venda,
    LAG(valor_venda, 1, 0) OVER (ORDER BY data_venda) AS venda_anterior,
    valor_venda - LAG(valor_venda, 1, 0) OVER (ORDER BY data_venda) AS diferenca
FROM 
    Vendas;

Neste exemplo:

• A função LAG(valor_venda, 1, 0) acessa o valor da venda anterior.
• OVER (ORDER BY data_venda) indica que os resultados devem ser ordenados pela coluna data_venda.
• O resultado incluirá o valor da venda anterior e a diferença em relação à venda atual.

2. Função LEAD

A função LEAD funciona de maneira semelhante ao LAG, mas em vez de acessar valores de linhas anteriores, ela acessa valores de linhas posteriores em relação à linha atual.

Sintaxe:

LEAD (valor_a_ser_retornado, deslocamento, valor_padrão) OVER (PARTITION BY <coluna_partição> ORDER BY <coluna_ordem>)

• valor_a_ser_retornado: A coluna ou expressão da qual você quer acessar o valor da linha posterior.
• deslocamento (opcional): O número de linhas à frente que você deseja acessar. O valor padrão é 1.
• valor_padrão (opcional): Um valor retornado quando não houver uma linha posterior. O valor padrão é NULL.
• OVER: Define como os dados serão particionados e ordenados.

Exemplo de uso:

Usando a mesma tabela Vendas, agora queremos ver o valor da venda seguinte:

SELECT 
    venda_id,
    data_venda,
    valor_venda,
    LEAD(valor_venda, 1, 0) OVER (ORDER BY data_venda) AS proxima_venda
FROM 
    Vendas;

Neste exemplo:

• A função LEAD(valor_venda, 1, 0) acessa o valor da próxima venda.
• OVER (ORDER BY data_venda) ordena os resultados pela data da venda.
• O resultado incluirá o valor da próxima venda para cada linha.

Exemplo prático de saída:

Suponha que nossa tabela de Vendas tenha os seguintes dados:

venda_id	data_venda	valor_venda
1	2024-01-01	100
2	2024-01-02	150
3	2024-01-03	200

Resultado da consulta com LAG:

venda_id	data_venda	valor_venda	venda_anterior	diferenca
1	2024-01-01	100	0	100
2	2024-01-02	150	100	50
3	2024-01-03	200	150	50

Resultado da consulta com LEAD:

venda_id	data_venda	valor_venda	proxima_venda
1	2024-01-01	100	150
2	2024-01-02	150	200
3	2024-01-03	200	0

Considerações:

• LAG e LEAD são extremamente úteis em cenários como análise de séries temporais, cálculo de variações ou para encontrar tendências em dados históricos.
• Ambas as funções evitam a necessidade de auto-joins ou subconsultas que acessam linhas de outras partes da tabela, melhorando a legibilidade e eficiência das consultas.

Se você quiser mais detalhes sobre como aplicar essas funções em um contexto específico, posso ajudar a elaborar!

ROLLUP

O ROLLUP é uma extensão da cláusula GROUP BY no SQL Server, que ajuda a gerar subtotais e totais gerais para os dados agregados em uma única consulta, sem precisar escrever consultas adicionais para cada nível de agregação. Ele é muito usado em relatórios e análises de dados, onde queremos ver totais parciais (por categoria, ano, etc.) e totais gerais no mesmo resultado.

Como o ROLLUP Funciona?

O ROLLUP cria uma hierarquia de agregação dos dados. Vamos entender isso com um exemplo:

Suponha que temos uma tabela de vendas (Vendas) com as colunas:

• Ano (o ano da venda),
• Mes (o mês da venda),
• TotalVendas (o valor total das vendas).

E queremos saber o total de vendas por ano, por mês e também o total geral.

Exemplo de Consulta com ROLLUP

SELECT 
    Ano, 
    Mes, 
    SUM(TotalVendas) AS TotalVendas
FROM 
    Vendas
GROUP BY 
    ROLLUP(Ano, Mes);

Explicação do Resultado

Vamos imaginar que o resultado dessa consulta seja algo assim:

Ano	Mes	TotalVendas
2022	1	10000
2022	2	12000
2022	NULL	22000
2023	1	15000
2023	2	13000
2023	NULL	28000
NULL	NULL	50000

Estrutura do ROLLUP:

1. Agrupamento normal: Para cada combinação de Ano e Mes, o ROLLUP calcula o total, assim como um GROUP BY faria.
2. Subtotal: Em seguida, para cada Ano, ele calcula o subtotal (somando todos os meses) e exibe com NULL na coluna Mes.
3. Total geral: Finalmente, o ROLLUP calcula o total geral de todas as vendas, exibindo NULL em ambas as colunas (Ano e Mes).

Sintaxe Geral do ROLLUP

A sintaxe para usar o ROLLUP é:

GROUP BY ROLLUP(coluna1, coluna2, ... colunaN)

Isso indica ao SQL Server que deve agrupar e gerar:

• Resultados para cada combinação de todas as colunas especificadas.
• Subtotais para os níveis intermediários.
• Um total geral no final.

Casos de Uso do ROLLUP

O ROLLUP é útil em relatórios e dashboards, onde você quer fornecer um resumo detalhado dos dados, permitindo visualizar as informações agregadas em vários níveis hierárquicos:

• Relatórios financeiros: Total de vendas por região, subregião e país, com subtotais por região e total geral.
• Análises de tempo: Total de vendas ou transações por ano, mês e dia, com subtotais anuais e total geral.

Diferença entre ROLLUP e GROUP BY

• GROUP BY: Retorna um agrupamento direto para as colunas especificadas.
• ROLLUP: Retorna todos os agrupamentos que GROUP BY faria, mas adiciona subtotais para cada nível hierárquico e um total geral.

Essa é uma forma poderosa de agregar dados, economizando a necessidade de múltiplas consultas para cada nível de detalhe.

CUBE

O CUBE no SQL Server é uma extensão da cláusula GROUP BY, assim como o ROLLUP, mas com uma diferença importante: ele cria combinações de agregação em todas as dimensões fornecidas, gerando subtotais e um total geral para cada possível combinação de colunas. Isso o torna especialmente útil em relatórios e análises, onde queremos ver totais e subtotais de dados em várias dimensões.

Comparação entre CUBE e ROLLUP

Enquanto o ROLLUP cria uma hierarquia de agregação ao longo das colunas especificadas, o CUBE gera todas as combinações possíveis de agregação, sem se limitar a uma hierarquia. Em outras palavras:

• ROLLUP: Agrupa os dados em uma hierarquia definida, indo do nível mais específico até o total geral.
• CUBE: Agrupa os dados em todas as combinações possíveis de colunas, resultando em subtotais para todas as colunas e combinações de colunas, além do total geral.

Como o CUBE Funciona?

Suponha que você tenha uma tabela Vendas com as seguintes colunas:

• Ano – o ano da venda.
• Regiao – a região onde a venda foi realizada.
• TotalVendas – o valor total das vendas.

Se queremos saber o total de vendas por ano, por região, e por todas as combinações de ano e região, o CUBE é a opção ideal. A consulta ficaria assim:

SELECT 
    Ano,
    Regiao,
    SUM(TotalVendas) AS TotalVendas
FROM 
    Vendas
GROUP BY 
    CUBE(Ano, Regiao);

Explicação do CUBE

O CUBE(Ano, Regiao) cria um conjunto de agregações que incluem:

• Total de vendas por Ano e Região: O valor total das vendas para cada combinação específica de Ano e Regiao.
• Subtotal por Ano: Soma todas as vendas para cada ano, ignorando a coluna Regiao (exibida como NULL).
• Subtotal por Região: Soma todas as vendas para cada região, ignorando o ano (exibido como NULL).
• Total Geral: Soma todas as vendas em todos os anos e regiões, com ambos Ano e Regiao exibidos como NULL.

Exemplo de Resultado do CUBE

Considerando a consulta anterior, o CUBE geraria um resultado semelhante a este:

Ano	Regiao	TotalVendas
2022	Norte	10000
2022	Sul	8000
2022	NULL	18000
2023	Norte	15000
2023	Sul	12000
2023	NULL	27000
NULL	Norte	25000
NULL	Sul	20000
NULL	NULL	45000

Vantagens do CUBE

O CUBE é vantajoso quando:

1. Queremos analisar dados em múltiplas dimensões: Como ele agrega todas as combinações, é ideal para relatórios complexos.
2. Precisamos de subtotais por cada coluna e combinações de colunas: Ele permite ver o total em cada dimensão.
3. Desejamos evitar consultas adicionais: Com uma única consulta, temos todas as agregações possíveis.

Casos de Uso do CUBE

O CUBE é muito útil em:

• Análises de Vendas: Vendas totais por ano, região e categoria.
• Relatórios Financeiros: Receita por departamento, país e trimestre.
• Relatórios de Performance: Dados agregados de performance por diferentes combinações de período, equipe, e localidade.

Observações sobre Performance

Embora poderoso, o CUBE pode ser pesado em termos de processamento, especialmente em grandes conjuntos de dados, pois calcula todas as combinações possíveis. Avaliar o impacto de performance é importante, especialmente quando o número de colunas no CUBE aumenta.