O SQL possui diversas funções que são muito úteis para as consultas mais comuns do dia-a-dia. Algumas funções são úteis para o tratamento de tipos dos dados, como a função CAST, outras são funções de agregação, como as funções MAX, MIN e SUM. Algumas funções podem variar dependendo do banco de dados utilizado, mas existem um grupo de funções em comum que podemos encontrar em todos os bancos de dados (ou pelo menos na maioria). Dentre estas funções estão um grupo de funções classificadas como Window Functions que geralmente são desconhecidas dos profissionais mais iniciantes, porém são de conhecimento essencial para os profissionais mais seniores.

O nome Window Function (Função Janela, em português) já traz uma ideia sobre o funcionamento destas funções, já que elas executam operações em um range de valores específico. Estas funções são comumente aplicadas em cálculos estatísticos, principalmente em séries temporais e se até aqui você tem achado este conceito complexo e de difícil entendimento, isto está prestes a ficar extremamente simples. Se pensarmos, por exemplo, em uma tabela onde em uma coluna é exibido o total de vendas diárias de uma loja e em outra coluna é exibido o total de vendas nos últimos três dias, esta última coluna seria criada através de uma Window Function. Contudo, as Window Functions não se limitam ao tempo, elas podem ser aplicadas à qualquer valor que possa ser agrupado, podendo ser unidades de medida como quilômetros, litros, idade ou outros valores como área de negócio, andar, cargo e até mesmo as próprias linhas da tabela.

A sintaxe utilizada para as Window Functions é a seguinte:

função_agregadora(coluna1) OVER ([PARTITION BY coluna2] [ORDER BY coluna3]) 

Porém, podem haver variações.No nosso exemplo, para a seguinte tabela de vendas poderia ser construída com a Window Function contida na query abaixo.

SELECT
  data,
  vendas,
  AVG(vendas) OVER (ORDER BY data ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media3dias
FROM
  tabela;
-- Não removi a dízima periódica para deixar query o mais simples possível

Vamos falar agora sobre a cláusula PARTITION. Ela é muito semelhante ao GROUP BY e pode ser utilizada para agrupar o resultado por uma característica em comum entre os elementos. Voltando para o nosso exemplo, se houvessem duas lojas, uma no Brasil e outra em Portugal, e nós quiséssemos fazer a mesma média de vendas para os três últimos dias, porém sem misturar os dados das duas lojas. Para isso nós poderíamos utilizar o PARTITION BY, mantendo assim o cálculo da média separado para as duas lojas.

SELECT
  pais,
  data,
  vendas,
  AVG(vendas) OVER (PARTITION BY pais ORDER BY data ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media3dias
FROM
  tabela;

Para este exemplo eu utilizei a função AVG que é a função que retorna a média dos valores (average em inglês), porém as funções de SUM, COUNT, MAX e MIN também poderiam ser utilizadas. Há ainda outro tipo de Window Functions que são as funções de ranqueamento, como RANK, DENSE_RANK, ROW_NUMBER e PERCENT_RANK. Pode parecer muita coisa, mas é bem fácil de demonstrar o funcionamento de cada uma delas com os dados da tabela anterior.

Se quisermos criar uma coluna que contenha o ranking das dez maiores vendas, podemos utilizar a função RANK() como no exemplo abaixo.

SELECT
  data,
  vendas,
  RANK() OVER (ORDER BY vendas ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rank_de_vendas
FROM tabela
  LIMIT 10;

Repare que algumas vendas ficaram empatadas na mesma posição e a linha posterior ao empate pulou um número do ranking. Se não quisermos este efeito, neste caso devemos aplicar a função DENSE_RANK.

SELECT
  data,
  vendas,
  DENSE_RANK() OVER (ORDER BY vendas ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rank_de_vendas
FROM tabela
  LIMIT 10;

Se quisermos que o ranking represente o percentual de cada elemento de acordo com a sua posição, podemos utilizar a função PERCENT_RANK. Como o nosso exemplo possui dez registros, esta função pode não parecer muito útil. Porém, ela pode se revelar útil em outros casos.

SELECT
  pais,
  data,
  vendas,
PERCENT_RANK() OVER (PARTITION BY pais ORDER BY vendas) * 100 AS rank_de_vendas
  FROM tabela;

Por fim, temos a função ROW_NUMBER, esta talvez seja a função mais utilizada no dia a dia e serve para criar um índice para as linhas dentro de cada grupo.

SELECT
  pais,
  data,
  vendas,
  ROW_NUMBER() OVER (PARTITION BY pais) AS rank_de_vendas
FROM tabela;  

Como vimos, as Window Functions podem ser muito úteis e são muito importantes para a criação de queries mais avançadas. É comum que entrevistadores perguntem sobre elas durante entrevistas técnicas para posições seniores ou principal, portanto, se você ainda não as conhecia, recomendo que busque se aprofundar um pouco mais. Um bom lugar para começar é este pequeno tutorial da W3School (está em inglês).

Fonte

https://www.geeksforgeeks.org/sql/window-functions-in-sql/