Funciones de ventana en SQL

Las funciones de ventana aplican funciones agregadas y de clasificación sobre una ventana en particular (conjunto de filas). La cláusula OVER se usa con funciones de ventana para definir esa ventana. La cláusula OVER hace dos cosas: 

• Particiones de filas en forma de conjunto de filas. (Se utiliza la cláusula PARTITION BY) 
• Ordena filas dentro de esas particiones en un orden particular. (Se utiliza la cláusula ORDER BY) 
   

Nota: 
si no se realizan las particiones, ORDER BY ordena todas las filas de la tabla. 

Sintaxis básica:  

SELECT coulmn_name1, 
 window_function(cloumn_name2)
 OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;
 
 
window_function= any aggregate or ranking function    
column_name1= column to be selected
coulmn_name2= column on which window function is to be applied
column_name3= column on whose basis partition of rows is to be done
new_column= Name of new column
table_name= Name of table

Función de ventana agregada: 
varias funciones agregadas como SUM(), COUNT(), AVERAGE(), MAX(), MIN() aplicadas sobre una ventana particular (conjunto de filas) se denominan funciones de ventana agregada. 

Considere la siguiente tabla de  empleados :

Nombre	Años	Departamento	Salario
Ramesh	20	Finanzas	50, 000
Profundo	25	Ventas	30, 000
claro	22	Finanzas	50000
RAM	28	Finanzas	20, 000
Pradeep	22	Ventas	20, 000

Ejemplo: 
encuentre el salario promedio de los empleados para cada departamento y ordene a los empleados dentro de un departamento por edad. 

SELECT Name, Age, Department, Salary, 
 AVERAGE(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary
 FROM employee

El resultado de la consulta anterior será:  

Nombre	Años	Departamento	Salario	Salario_promedio
Ramesh	20	Finanzas	50, 000	40, 000
claro	22	Finanzas	50000	40, 000
RAM	28	Finanzas	20, 000	40, 000
Pradeep	22	Ventas	20, 000	25, 000
Profundo	25	Ventas	30, 000	25, 0000

Como podemos ver en el ejemplo anterior, el salario promedio dentro de cada departamento se calcula y se muestra en la columna Avg_Salary. Además, los empleados dentro de una columna en particular están ordenados por su edad. 

Funciones de la ventana de 
clasificación: las funciones de clasificación son, RANK(), DENSE_RANK(), ROW_NUMBER() 

• RANK(): 
  como sugiere el nombre, la función de rango asigna rango a todas las filas dentro de cada partición. El rango se asigna de manera que el rango 1 dado a la primera fila y las filas que tienen el mismo valor se asignan al mismo rango. Para el siguiente rango después de dos valores de rango iguales, se omitirá un valor de rango. 

   

• DENSE_RANK(): 
  asigna rango a cada fila dentro de la partición. Al igual que la función de rango, a la primera fila se le asigna el rango 1 y las filas que tienen el mismo valor tienen el mismo rango. La diferencia entre RANK() y DENSE_RANK() es que en DENSE_RANK(), para el siguiente rango después de dos mismos rangos, se usa un número entero consecutivo, no se salta ningún rango. 

   

• ROW_NUMBER(): 
  asigna enteros consecutivos a todas las filas dentro de la partición. Dentro de una partición, dos filas no pueden tener el mismo número de fila. 

   

Nota: 
ORDER BY() debe especificarse obligatoriamente al usar las funciones de la ventana de clasificación. 

Ejemplo: 
calcule el número de fila, el rango, el rango denso de empleados es la tabla de empleados según el salario dentro de cada departamento. 

SELECT 
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) 
AS emp_row_no, Name,  Department, Salary,
RANK() OVER(PARTITION BY Department 
ORDER BY Salary DESC) AS emp_rank,
DENSE_RANK() OVER(PARTITION BY Department 
                  ORDER BY Salary DESC) 
                  AS emp_dense_rank,
FROM employee 

El resultado de la consulta anterior será:  

emp_row_no	Nombre	Departamento	Salario	emp_rank	emp_dense_rank
1	claro	Finanzas	50, 000	1	1
2	Ramesh	Finanzas	50, 000	1	1
3	RAM	Finanzas	20, 000	3	2
1	Profundo	Ventas	30, 000	1	1
2	Pradeep	Ventas	20, 000	2	2

Entonces, podemos ver que, como se menciona en la definición de ROW_NUMBER(), los números de fila son enteros consecutivos dentro de cada partición. Además, podemos ver la diferencia entre el rango y el rango denso en que en el rango denso no hay brecha entre los valores de rango, mientras que hay una brecha en los valores de rango después del rango repetido.