Principales funciones de ventana

En los artículos anteriores vimos cómo funcionan las funciones de ventana y conocimos el concepto de ventana de datos que se pasa a una función de ventana. Ha llegado el momento de ver qué tipos de funciones de ventana existen.

Tipos de funciones de ventana

Las funciones de ventana se pueden dividir en 3 grupos:

• Funciones de ventana de agregación
• Funciones de ventana de ranking
• Funciones de ventana de desplazamiento

Funciones de ventana de agregación

Las funciones de agregación son funciones que hacen cálculos aritméticos sobre un conjunto de datos y devuelven un valor total.

• SUM: calcula la suma total de los valores;
• COUNT: cuenta el número total de registros de una columna;
• AVG: calcula la media aritmética;
• MAX: encuentra el valor más alto;
• MIN: encuentra el valor más bajo.

MySQL 8.4
SELECT id,
	home_type,
	price,
	SUM(price) OVER(PARTITION BY home_type) AS "Sum",
	COUNT(price) OVER(PARTITION BY home_type) AS "Count",
	AVG(price) OVER(PARTITION BY home_type) AS "Avg",
	MAX(price) OVER(PARTITION BY home_type) AS "Max",
	MIN(price) OVER(PARTITION BY home_type) AS "Min"
FROM Rooms;

Funciones de ventana de ranking

Las funciones de ventana de ranking son funciones que asignan una posición (rango) a cada fila de la ventana.

En las funciones de ranking, después de la palabra clave OVER es obligatorio indicar la cláusula ORDER BY, que define la ordenación con la que se asignan los rangos.

• ROW_NUMBER: devuelve el número de fila y se usa para numerar;
• RANK: devuelve el rango de cada fila. Así es como funciona:
  • Ordenación: primero, las filas se ordenan por una o varias columnas. Estas columnas se indican en ORDER BY, dentro de la cláusula OVER.
  • Asignación de rangos: a cada fila única o grupo de filas con los mismos valores en las columnas de ordenación se le asigna un rango. El rango empieza en 1.
  • Valores iguales: si varias filas tienen los mismos valores en las columnas de ordenación, reciben el mismo rango. Por ejemplo, si dos filas ocupan el segundo puesto, las dos reciben el rango 2.
  • Salto de rangos: después de un grupo de filas con el mismo rango, el siguiente rango aumenta en el número de filas de ese grupo. Por ejemplo, si dos filas tienen el rango 2, la siguiente fila recibirá el rango 4, no el 3.
  • Continuación: el proceso se repite hasta que todas las filas del conjunto de resultados tengan un rango asignado.
• DENSE_RANK: también devuelve el rango de cada fila. Pero, a diferencia de RANK, no se salta rangos: después de un grupo de valores iguales, el rango aumenta en uno, y no en el número de filas. Por ejemplo, si dos filas tienen el rango 2, la siguiente fila recibirá el rango 3, no el 4.

MySQL 8.4
SELECT id,
	home_type,
	price,
	ROW_NUMBER() OVER(PARTITION BY home_type ORDER BY price) AS "row_number",
	RANK() OVER(PARTITION BY home_type ORDER BY price) AS "rank",
	DENSE_RANK() OVER(PARTITION BY home_type ORDER BY price) AS "dense_rank"
FROM Rooms;

Funciones de ventana de desplazamiento

Las funciones de ventana de desplazamiento son funciones que permiten moverse por la ventana y acceder a otras filas respecto a la fila actual, así como a los valores del principio o del final de la ventana.

• LAG: accede a los datos de las filas anteriores de la ventana.

  Tiene tres argumentos: la columna cuyo valor hay que devolver, el número de filas que hay que desplazarse (1 por defecto) y el valor que hay que devolver si tras el desplazamiento se obtiene NULL.

• LEAD: accede a los datos de las filas siguientes. Igual que LAG, tiene 3 argumentos.

• FIRST_VALUE: devuelve el primer valor de la ventana. Recibe como argumento la columna cuyo valor hay que devolver.

• LAST_VALUE: devuelve el último valor de la ventana. Recibe como argumento la columna cuyo valor hay que devolver.

MySQL 8.4
SELECT id,
	home_type,
	price,
	LAG(price) OVER(PARTITION BY home_type ORDER BY price) AS "lag",
	LAG(price, 2) OVER(PARTITION BY home_type ORDER BY price) AS "lag_2",
	LEAD(price) OVER(PARTITION BY home_type ORDER BY price) AS "lead",
	FIRST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS "first_value",
	LAST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "last_value"
FROM Rooms;