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 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() 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 à expressão : order by 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: Primeiro de tudo, agora está classificado em ordem decrescente. Employee_id 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 , 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. de Employee_id O parâmetro order by na expressão especifica a ordem. No caso de agregação de funções de janela, determina a ordem do total acumulado. over() Na expressão , os atributos partição por e ordem por podem ser especificados. over() 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 , a partição by sempre vem primeiro, seguida pela . Por exemplo: . over() ordem by 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 dentro da cláusula 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. ORDER BY OVER 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 atribuiu números às linhas sem alterar sua ordem. Até agora isso não traz muito valor para nós, não é mesmo? row_number() over() 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: A função de janela 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. row_number() over(order by salarial desc) A função de janela ) 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. rank() over(order by salarial desc A função de janela atribui classificações em ordem decrescente de salário. Ele atribui a mesma classificação para valores idênticos. denso_rank() over(order by salarial desc) A função de janela é a classificação relativa (porcentagem) da linha atual, calculada pela fórmula: (classificação - 1) / (número total de linhas na partição - 1). percent_rank() over(order by salarial desc) A função de janela 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) over(order by salarial desc) 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 e, é claro, também. row_number() over() densa_rank() over() 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 e exibem o primeiro e o último valor salarial dentro da seção (departamento). first_value(salary) over(partition by Department) last_value(salary) over(partition by Department) Por sua vez, a função mostra o valor do segundo salário dentro da seção (departamento). Observe que em , 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. nth_value(salário, 2) over(partição por departamento) nth_value() Além do acima, também existem funções e . A função é 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. lag() lead() lag() 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 desloca os salários para baixo em uma linha, e a função desloca os salários para cima em uma linha. Embora essas funções sejam bastante semelhantes, acho mais conveniente usar . lag (salário) over (ordem por salário) lead(salário) over (ordem por salário) 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, executa a mesma função de antes, mas agora especificamente dentro de seções (departamentos). lag() 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 especificando a base da classificação. ordenar por As funções da janela de deslocamento incluem f , e , 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 e . A função pode ser útil para obter o valor da linha que precede a linha atual, enquanto a função é usada para obter o valor de uma linha que sucede a linha atual. irst_value() last_value() nth_value() lag() lead() lag() lead() 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.
