Exemplo de função da Janela SQL com explicações

Interessado em como funciona a janela? Vá para baixo para ver nosso exemplo de função de janela SQL com explicações definitivas!

Asfunções da janela SQL são um pouco diferentes; elas computam seu resultado com base em um conjunto de fileiras em vez de em uma única fila. Na verdade, a "janela" em "função de janela" se refere a esse conjunto de linhas.

As funções de janela são semelhantes às funções agregadas, mas há uma diferença importante. Quando usamos funções agregadas com a GROUP BY cláusula, nós "perdemos" as linhas individuais. Não podemos misturar atributos de uma linha individual com os resultados de uma função agregada; a função é executada nas linhas como um grupo inteiro. Este não é o caso quando usamos as funções da janela SQL: podemos gerar um conjunto de resultados com alguns atributos de uma linha individual junto com os resultados da função de janela. Isto é bom para os novos desenvolvedores de SQL terem em mente. Portanto, vamos examinar um exemplo simples da função de janela SQL em ação.

Deseja aprender as funções da janela SQL? Confira nossas funções interativas Funções de Janela (Window Functions) em SQL curso!

Exemplo de função de janela SQL

As funções de janela podem ser chamadas no SELECT declaração ou no ORDER BY cláusula. Entretanto, eles nunca poderão ser chamados na WHERE cláusula. Você notará que todos os exemplos neste artigo chamam a função de janela no SELECT lista de colunas.

Vamos ao primeiro exemplo de função de janela SQL. Vamos usar a função "Employee" mesa:

Vamos começar com RANKque é um dos exemplos mais simples de funções de janela SQL. Ela retorna a posição de qualquer linha dentro da partição. Vamos usá-la para classificar os salários dentro dos departamentos:

SELECT	
		RANK() OVER (PARTITION BY department ORDER BY salary DESC) 
			AS dept_ranking,
		department,
		employee_id, 
		full_name, 
		salary
FROM employee;

Podemos ver os resultados abaixo:

E se quisermos ter o mesmo relatório, mas com todos os funcionários de primeira linha primeiro, depois todos os funcionários de segunda linha, e assim por diante? Bem, nós lhe daremos este desafio para descobrir por conta própria. Compartilhe suas idéias na seção de comentários!

Prosseguindo com nosso exemplo de função de janela SQL, vamos descobrir onde está o salário de cada funcionário em relação ao salário mais alto de seu departamento. Isto exige uma expressão matemática, como:

employee_salary / max_salary_in_depth

A próxima consulta mostrará todos os funcionários ordenados pela métrica acima; os funcionários com o salário mais baixo (relativo a seu maior salário departamental) serão listados em primeiro lugar:

SELECT
  employee_id, 
  full_name, 
  department,
  salary,
  salary / MAX(salary) OVER (PARTITION BY department ORDER BY salary DESC) 
    AS salary_metric
FROM employee
ORDER BY 5;

Interessado em aprender as funções da janela SQL? Experimente nosso curso interativo Funções de Janela (Window Functions) em SQL curso!

Outro exemplo de função da Janela SQL

Vamos mudar de um banco de dados de funcionários-salários para o seguinte banco de dados de horários de trens:

Suponha que queiramos adicionar uma nova coluna chamada "horário para a próxima estação". Para obter este valor, subtraímos os horários das estações para os pares de estações contíguas. Podemos calcular este valor sem utilizar uma função de janela SQL, mas isso pode ser muito complicado. É mais simples fazê-lo usando a função LÍDER função de janela. Esta função compara valores de uma linha com a linha seguinte para chegar a um resultado. Neste caso, ela compara os valores na "tempo". coluna para uma estação com a estação imediatamente após ela.

Portanto, aqui temos outro exemplo de função de janela SQL, desta vez para o horário do trem:

SELECT 
	train_id, 
	station,
	time as "station_time",
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
		AS time_to_next_station
FROM train_schedule;

Note que calculamos o LEAD função de janela usando uma expressão que envolve uma coluna individual e uma função de janela; isto não é possível com funções agregadas.

Aqui estão os resultados dessa consulta:

No próximo exemplo, adicionaremos uma nova coluna que mostra quanto tempo passou desde a primeira parada do trem até a estação atual. Vamos chamá-lo de "tempo decorrido de viagem". A função de janela MIN obterá a hora de início da viagem e subtrairemos o tempo atual da estação. Aqui está o próximo exemplo da função de janela SQL

SELECT 	
	train_id, 
	station,
	time as "station_time",
	time - min(time) OVER (PARTITION BY train_id ORDER BY time) 	
								AS elapsed_travel_time,
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
								AS time_to_next_station
FROM train_schedule;

Observe a nova coluna na tabela de resultados:

Descubra mais exemplos de funções da Janela SQL!

Asfunções de janela não são aspectos bem conhecidos do SQL, mas seu poder e flexibilidade os tornam muito importantes. Há cláusulas (por exemplo PARTITION BY e janelas) e tópicos que não abordamos neste artigo, mas não deixe que isso o impeça! Você pode aprender mais e encontrar outros exemplos de funções de janela SQL usando nosso blog e o Funções de Janela (Window Functions) em SQL curso em LearnSQL.com.br. Comece hoje!