Um guia para iniciantes para entender as funções da janela SQL e seus recursos

Ao longo do processo de contratação, tive o prazer de conhecer muitos candidatos talentosos para minha equipe. Como nosso trabalho envolve lidar com conjuntos de dados complexos, era importante para mim avaliar a capacidade de cada candidato em encontrar soluções inteligentes. Perguntei sobre sua experiência com funções de janela em SQL para avaliar sua proficiência. Embora a maioria deles conhecesse essas funções, poucos eram capazes de usá-las efetivamente.

Embora as funções de janela existam há quase 20 anos, muitos desenvolvedores de SQL ainda as acham difíceis de entender. Não é incomum, mesmo para desenvolvedores experientes, apenas copiar e colar o código do StackOverflow sem realmente entender o que ele faz. Este artigo está aqui para ajudar! Explicarei as funções da janela de uma maneira fácil de entender e fornecerei exemplos para mostrar como elas funcionam no mundo real.

Você já ouviu falar sobre funções de janela? Eles são ferramentas analíticas incríveis que podem resolver muitos problemas. Por exemplo, digamos que você precise calcular um conjunto de linhas que compartilham um atributo comum, como um ID de cliente. É aqui que as funções de janela são úteis! Eles funcionam como funções agregadas, mas permitem que você mantenha a exclusividade de cada linha em vez de agrupá-las. Além disso, os resultados das funções da janela aparecem como um campo extra na seleção de saída. Isso é muito útil quando você está fazendo relatórios analíticos, calculando médias móveis e totais de execução ou descobrindo diferentes modelos de atribuição.

Bem-vindo ao mundo do SQL e das funções de janela! Se você está apenas começando, está no lugar certo. Este artigo é para iniciantes, com explicações claras e sem terminologia complicada ou conceitos avançados. Você será capaz de acompanhar com facilidade, mesmo que seja completamente novo no assunto.

Visão geral do conteúdo

• Tipos de funções usadas com funções de janela
  • funções agregadas
  • funções de classificação
  • funções de valor
• Funções de Janela Agregadas
• Principais conclusões

Tipos de funções usadas com funções de janela

Existem três tipos principais de funções às quais as funções de janela podem ser aplicadas em um conjunto de linhas (a chamada janela): são funções agregadas, de classificação e de valor. Na imagem abaixo, você pode ver os nomes das diferentes funções que se enquadram em cada categoria.

funções agregadas

Eles executam operações matemáticas em um grupo de dados, resultando em um único valor cumulativo. Eles são usados para calcular vários agregados, incluindo a média, o número total de linhas, os valores máximos ou mínimos ou a soma total dentro de cada janela ou partição.

• SOMA: soma todos os valores da coluna

• COUNT: calcula o número de valores na coluna, excluindo valores NULL

• AVG: encontra o valor médio na coluna

• MAX: identifica o maior valor na coluna

• MIN: identifica o menor valor na coluna

  
  

funções de classificação

Eles são usados para dar a cada linha em uma partição uma classificação ou ordem. Isso é feito avaliando critérios específicos, como atribuir números sequenciais ou basear a classificação em valores específicos.

• ROW_NUMBER: atribui um número de classificação sequencial a cada novo registro em uma partição
• RANK: especifica a classificação de cada linha no conjunto de resultados. Neste caso, se o sistema detectar valores idênticos, atribuirá a eles o mesmo posto e pulará o próximo valor.
• DENSE_RANK: atribui uma classificação a cada linha dentro de uma partição do conjunto de resultados. Ao contrário da função RANK, a função retorna classificações para valores idênticos sem pular nenhum valor subsequente.
• NTILE: permite determinar a que grupo pertence a linha atual. O número de grupos é dado entre parênteses.
  

funções de valor

Isso facilita a comparação de valores entre diferentes linhas em um grupo e também permite comparar valores com o primeiro ou o último valor desse grupo. Isso significa que você pode mover-se facilmente por diferentes linhas em uma janela e verificar os valores no início ou no final da janela.

• LAG ou LEAD: acessa os dados da linha anterior ou posterior sem ter que realizar uma operação de autojunção. Essas funções são particularmente úteis ao resolver problemas que exigem a comparação de uma linha com outra linha dentro do mesmo conjunto de resultados ou partição, como cálculo de diferenças ao longo do tempo.
• FIRST_VALUE ou LAST_VALUE: recupere o primeiro ou o último valor de uma janela ou partição definida. Essas funções são especialmente úteis quando você deseja calcular diferenças dentro de um período de tempo específico.

Para começar com as funções de janela, vamos criar uma tabela de 'salários' hipotética e preenchê-la com dados.

Criação da tabela:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

Preenchendo a tabela:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

Vamos verificar se preenchemos a tabela 'salário' com sucesso:

 select * from salary 

A próxima consulta mostrará os nomes e salários dos funcionários da nossa tabela:

 select employee_name, salary from salary 

Calcular a soma de salários, salário médio, máximo, mínimo e número de linhas são alguns casos de uso comuns de funções agregadas:

Quando uma função agregada é aplicada, os salários são agregados e mostrados em uma linha.

Mas e se quisermos exibir os nomes e salários dos funcionários da tabela 'salário' e, na terceira coluna, a soma de todos os salários? Este valor deve ser o mesmo para todas as linhas.

É uma ótima oportunidade para usar uma função de janela!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

Vamos dar uma olhada mais de perto na função window que calcula a soma dos salários em cada linha de sum(salary) over() .

A expressão over() define uma janela ou um conjunto de linhas sobre as quais a função opera. Em nosso exemplo, a janela é a tabela inteira, o que significa que a função será aplicada em todas as linhas.

A expressão over() só funciona quando emparelhada com funções que foram solicitadas antes de over() .

Por exemplo, sum(salary) over() , onde sum() é uma função agregada. E toda a expressão sum(salary) over() é uma função de janela agregada.

Como eu disse antes, todas as funções às quais as funções de janela são aplicadas podem ser divididas em três grupos: agregar, classificar e funções de valor.

As funções agregadas sum() , count() , avg() , min() , max() junto com a expressão over() formam um grupo de funções de janela agregadas.

Neste artigo, vamos nos concentrar neste tipo específico de funções de janela.

Funções de Janela Agregadas

Voltando aos exemplos!

Vamos solicitar os nomes dos funcionários; seus salários; a soma de todos os salários; salário médio, máximo e mínimo; o número de empregados.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

Agora que está mais claro o que são funções de janela, vamos explorar alguns casos em que elas podem ser úteis em seu trabalho.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

Calculamos a porcentagem do orçamento salarial total para cada salário na quarta coluna. O salário de Jessa equivale a quase 15% de todo o orçamento para salários.

Observe que também colocamos a fórmula que calcula as porcentagens salary/sum(salary)over() na classificação após order by . Uma função de janela pode ser encontrada não apenas na select de saída, mas também na order by .

Outro exemplo: vamos comparar os salários com a média salarial da empresa.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

Como podemos ver, o salário de Andrew é 2.110 abaixo da média e o de Jessa está 1.690 acima da média.

Vamos solicitar três colunas: nome do funcionário, departamento e salário. Além disso, vamos classificá-los por departamento.

 select employee_name, department, salary from salary order by department 

Agora vamos pedir as mesmas três colunas, mais uma coluna com a soma dos salários de todos os funcionários. Você já sabe que isso pode ser feito com uma função de janela.

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

Mas e se quisermos solicitar não a soma de todos os salários, mas a soma dos salários de cada departamento, conforme a última coluna:

Podemos fazer isso adicionando a partition by parâmetro à expressão over() :

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

Partition by nos permite aplicar a função de janela não a todas as linhas (a janela inteira), mas às seções da coluna.

Não parece um agrupamento simples? Para calcular a soma dos salários de cada departamento, faríamos um agrupamento por departamentos (seções na gíria de funções de janela) e calcularíamos o valor:

 select department, sum(salary) from salary group by department 

Em essência, a diferença entre grouping e partition by é que group by retorna uma linha por grupo, enquanto partition by , embora os resultados da função sejam idênticos aos resultados de uma função agregada com group by , fornece todas as linhas com a função agregada baseada em um grupo.

Voltemos às funções da janela:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

Utilizando a função janela, em particular a partition by parâmetro, podemos calcular a parcela do salário de cada funcionário a partir da soma dos salários do departamento. Ou, por exemplo, para comparar os salários com o salário médio do departamento.

Principais conclusões

Para recapitular:

• Uma função de janela executa um cálculo em um conjunto de linhas que estão de alguma forma relacionadas à linha atual,

• Os principais tipos de funções às quais as funções de janela são aplicadas são funções agregadas, de classificação e de valor,

• Para usar uma função de janela, você precisa aplicar a cláusula over() que define uma janela (um conjunto de linhas) dentro de um conjunto de resultados de consulta. A função de janela então calcula um valor para cada linha na janela,

• Para especificar a coluna para a qual você deseja realizar a agregação, você precisa adicionar a partition by à cláusula over() . Partition by é um pouco semelhante ao agrupamento, mas retorna todas as linhas com a função de agregação aplicada, em vez de uma linha por grupo.

  
  

Isso é tudo por agora! Nos próximos artigos, explorarei conceitos SQL mais avançados com exemplos simples adequados para iniciantes, portanto, fique atento!