Guia para iniciantes para entender as funções da janela SQL - Parte 2

No artigo anterior , falamos sobre funções de janela — sendo uma ótima ferramenta para resolver diversos problemas analíticos; eles podem fornecer acesso a recursos como análises avançadas e manipulação de dados sem a necessidade de escrever consultas complexas. Antes de continuar lendo, recomendo começar com a primeira parte, pois ela ajudará a compreender a ideia básica de como as funções de janela funcionam em SQL.

Agora que você está familiarizado com o básico, vamos explorar conceitos SQL mais avançados. Pode parecer um pouco complicado à primeira vista, mas fornecerei exemplos simples, adequados para iniciantes, para todos os casos possíveis, para que seja fácil entender o que quero dizer.

Visão geral do conteúdo

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

Soma Cumulativa

Já consideramos exemplos em que a expressão over() não tinha parâmetros ou tinha uma partição por parâmetro. Agora, veremos o segundo parâmetro possível para a expressão over() — ordenar por.

Vamos solicitar o ID do funcionário, nome do funcionário, departamento, salário e a soma de todos os salários:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

Agora, adicionaremos o parâmetro order by à expressão over() :

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

Acho que precisamos dar uma olhada mais de perto no que aconteceu aqui:

1. Primeiro de tudo, Employee_id agora está classificado em ordem decrescente.

   
   

2. Na coluna resultante da aplicação da função janela passa a existir uma soma cumulativa.

   
   

Acredito que você esteja familiarizado com soma cumulativa. Sua essência é simples – a soma acumulada ou total corrente significa “quanto até agora”. A definição de soma cumulativa é a soma de uma determinada sequência que aumenta ou aumenta com mais adições.

Aqui está o que temos em nosso exemplo: para o funcionário com o maior valor de Employee_id , o salário é 3.700 e a soma acumulada também é 3.700. O segundo funcionário tem um salário de 1.500 e a soma acumulada é 5.200. , com salário de 2.900, tem soma acumulada de 8.100, e assim por diante.

O parâmetro order by na expressão over() especifica a ordem. No caso de agregação de funções de janela, determina a ordem do total acumulado.

Na expressão over() , os atributos partição por e ordem por podem ser especificados.

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

Neste caso, o total acumulado será calculado por seções.

Depois de discutirmos a soma cumulativa, precisamos dizer que talvez seja o único tipo de total cumulativo usado com frequência. A média cumulativa e a contagem cumulativa são, pelo contrário, raramente utilizadas.

No entanto, daremos um exemplo de cálculo da média cumulativa — indica-nos a média de uma série de valores até um determinado ponto:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

Funções da janela de classificação

Usamos as funções da janela de classificação para determinar a posição de um valor dentro de um conjunto de valores. A expressão ORDER BY dentro da cláusula OVER determina a base para classificação, com cada valor atribuído a uma classificação dentro de sua partição designada. Quando as linhas compartilham valores idênticos para os critérios de classificação, elas recebem a mesma classificação.

Para ver como funcionam as funções da janela de classificação, vamos solicitar as seguintes colunas da tabela salarial: ID do funcionário, nome do funcionário, departamento e salário:

 select employee_id, employee_name, department, salary from salary 

Agora, adicionamos mais uma coluna com a função de janela row_number() over() :

 select employee_id, employee_name, department, salary, row_number() over() from salary 

A função de janela row_number() over() atribuiu números às linhas sem alterar sua ordem. Até agora isso não traz muito valor para nós, não é mesmo?

Mas e se quisermos numerar as linhas em ordem decrescente de salário? Para conseguir isso, precisamos especificar a ordem de classificação, ou seja, passar a ordem por parâmetro para a expressão over().

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Adicionaremos as funções de classificação restantes à consulta para comparação:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

Vamos examinar cada função da janela de classificação:

1. A função de janela row_number() over(order by salarial desc) classifica as linhas em ordem decrescente de salário e atribui números de linha. Observe que Annie e Tony têm o mesmo salário, mas recebem números diferentes.

   
   

2. A função de janela rank() over(order by salarial desc ) atribui classificações em ordem decrescente de salário. Ele atribui a mesma classificação para valores idênticos, mas o próximo valor recebe um novo número de linha.

   
   

3. A função de janela denso_rank() over(order by salarial desc) atribui classificações em ordem decrescente de salário. Ele atribui a mesma classificação para valores idênticos.

   
   

4. A função de janela percent_rank() over(order by salarial desc) é a classificação relativa (porcentagem) da linha atual, calculada pela fórmula: (classificação - 1) / (número total de linhas na partição - 1).

   
   

5. A função de janela ntile(5) over(order by salarial desc) divide o número de linhas em 5 partes iguais e atribui um número a cada parte. O número de partes é especificado dentro da função ntile(5) .

   
   

Casos de uso

É hora de explorar tarefas práticas usando funções de janela de classificação. Exibiremos o ID do funcionário, o nome do funcionário, o departamento e o salário, e atribuiremos os números das linhas em ordem decrescente de salário.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Às vezes, pode ser necessário numerar as linhas em ordem decrescente de salário dentro dos departamentos (seções). Isso pode ser feito adicionando o atributo partição por à expressão over():

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

Vamos tornar a tarefa mais desafiadora. Precisamos reter apenas um funcionário por departamento com o salário mais alto. Isso pode ser conseguido usando uma subconsulta:

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

E mais um exemplo, se precisarmos exibir três funcionários em cada cidade com o salário mais alto, faremos o seguinte:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

Esses tipos de tarefas são muito comuns, principalmente quando é necessário exibir um número específico de linhas dentro de seções (grupos) em ordem crescente ou decrescente de algum atributo. Na prática, eu uso consistentemente a função de janela row_number() over() e, é claro, densa_rank() over() também.

Funções de janela de deslocamento

Essas funções permitem retornar dados de outras linhas com base na distância da linha atual. Para torná-lo mais visual, vamos examinar as funções first_value(), last_value() e nth_value().

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

As funções first_value(salary) over(partition by Department) e last_value(salary) over(partition by Department) exibem o primeiro e o último valor salarial dentro da seção (departamento).

Por sua vez, a função nth_value(salário, 2) over(partição por departamento) mostra o valor do segundo salário dentro da seção (departamento). Observe que em nth_value() , um argumento adicional é especificado – o número da linha dentro da seção. No nosso caso, o número da linha é 2, então a função exibe o valor do segundo salário.

Além do acima, também existem funções lag() e lead() . A função lag() é usada para obter o valor da linha que precede a linha atual. A função lead() é usada para obter o valor de uma linha que sucede a linha atual.

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

Como você pode ver, a função lag (salário) over (ordem por salário) desloca os salários para baixo em uma linha, e a função lead(salário) over (ordem por salário) desloca os salários para cima em uma linha. Embora essas funções sejam bastante semelhantes, acho mais conveniente usar lag() .

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

Aqui, lag() executa a mesma função de antes, mas agora especificamente dentro de seções (departamentos).

Principais conclusões

E, finalmente, uma rápida visão geral do que cobrimos hoje:

• A soma cumulativa representa o total acumulado de uma sequência, acumulando com cada adição subsequente.

  
  

• As funções da janela de classificação são usadas para determinar a posição de um valor dentro de um conjunto de valores, com a expressão ordenar por especificando a base da classificação.

  
  

• As funções da janela de deslocamento incluem f irst_value() , last_value() e nth_value() , permitindo a recuperação de dados de outras linhas com base na distância da linha atual. Não se esqueça das funções lag() e lead() . A função lag() pode ser útil para obter o valor da linha que precede a linha atual, enquanto a função lead() é usada para obter o valor de uma linha que sucede a linha atual.

  
  

Obrigado por se juntar a mim. Espero que este artigo ajude você a entender melhor os recursos das funções de janela em SQL e o deixe mais confiante e rápido nas tarefas rotineiras.