paint-brush
Guia para iniciantes para entender as funções da janela SQL - Parte 2by@yonatansali
13,910
13,910

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

Yonatan Sali8m2024/01/20
Read on Terminal Reader

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.
featured image - Guia para iniciantes para entender as funções da janela SQL - Parte 2
Yonatan Sali HackerNoon profile picture

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.


Atenção! Se ambos os atributos forem especificados na expressão over() , a partição by sempre vem primeiro, seguida pela ordem by . Por exemplo: over(partition by Department Order by Employee_id) .


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) .


Atenção! Ao contrário das funções agregadas, por exemplo, soma(salário), as funções de classificação, por exemplo, row_number(), não levam uma coluna dentro. No entanto, na função ntile(5), o número de partes é especificado.

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 


Atenção! Em todas as três funções de janela, é essencial para especificar a partição por parâmetro. O parâmetro order by não é obrigatório, mas ao especificá-lo, você pode alterar a ordem das linhas dentro da partição. Por exemplo, na consulta abaixo, classificamos por salário dentro da seção (departamento), e agora o primeiro_valor é o salário mais alto da seção.

 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() .

Atenção! Para essas funções, é obrigatório especificar o parâmetro order by na expressão over(). Você também pode especificar o particionamento usando partição por, mas isso não é obrigatório.

 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.