¿Qué es la cláusula OVER() en SQL?

Las funciones de ventana son uno de los recursos más poderosos de SQL, pero no son usadas frecuentemente por el desarrollador promedio de SQL. En este artículo, explicaremos cómo puede definir diferentes tipos de marcos de ventana utilizando la cláusula OVER cláusula.

La cláusula OVER es esencial para las funciones ventana de SQL. Al igual que las funciones de agregación, las funciones ventana realizan cálculos basados en un conjunto de registros, por ejemplo, encontrar el salario medio de un grupo de empleados.

En algunos casos, las funciones de agregación no se pueden utilizar porque agrupan todos los registros individuales en un grupo; esto hace imposible referirse a valores específicos (como el salario de un empleado del grupo). En estas situaciones, se prefieren las funciones de ventana porque no colapsan las filas; se puede hacer referencia a un valor de columna a nivel de fila, así como al valor agregado.

Hay otros escenarios en los que las funciones ventana son útiles. Por ejemplo, podemos necesitar hacer una aritmética que implique una columna individual y un cálculo basado en un conjunto de filas. Un ejemplo del mundo real es el cálculo de la diferencia entre el salario medio del departamento y el salario de cada empleado del departamento.

Cuando se utilizan funciones de ventana, la definición del conjunto de registros donde se calculará la función es fundamental. Este conjunto de registros se denomina marco de ventana; lo definimos mediante la cláusula SQL OVER.

A lo largo de este artículo, demostraremos las consultas SQL utilizando la base de datos de una pequeña empresa de relojes de lujo. La empresa almacena su información de ventas en una tabla llamada sales:

El marco de ventana es un conjunto de registros que depende de la fila actual; por lo tanto, el conjunto de registros podría cambiar para cada fila procesada por la consulta. Los marcos de ventana se definen mediante la cláusula OVER. La sintaxis es:

  OVER ([PARTITION BY columns] [ORDER BY columns])

La subcláusula PARTITION BY define los criterios que deben satisfacer los registros para formar parte del marco de ventana. En otras palabras, PARTITION BY define los grupos en los que se dividen las filas; esto quedará más claro en nuestro siguiente ejemplo de consulta. Por último, la cláusula ORDER BY define el orden de los registros en el marco de la ventana.

Veamos la cláusula SQL OVER en acción. He aquí una consulta sencilla que devuelve la cantidad total de unidades vendidas de cada artículo.

SELECT sale_day, sale_time, 
       branch, article, quantity, revenue,
       SUM(quantity) OVER (PARTITION BY article) AS total_units_sold
FROM   sales

Esta consulta mostrará todos los registros de la tabla sales tabla con una nueva columna que muestra el número total de unidades vendidas del artículo correspondiente. Podemos obtener la cantidad de unidades vendidas utilizando la función de agregación SUM, pero entonces no podríamos mostrar los registros individuales.

En esta consulta, la subcláusula OVER PARTITION BY article indica que el marco de la ventana está determinado por los valores de la columna article; todos los registros con el mismo valor article estarán en un grupo. A continuación, tenemos el resultado de esta consulta:

La columna total_units_sold del informe fue obtenida por la expresión:

SUM(quantity) OVER (PARTITION BY article) total_units_sold

Para aquellos lectores que quieran profundizar en el tema, les sugiero los siguientes dos artículos: Cuál es la diferencia entre GROUP BY y PARTITION BY y Funciones de ventana en SQL Server: Primera Parte: La Cláusula OVER()

La Cláusula OVER de SQL en Acción

Para cada artículo, supongamos que queremos comparar la cantidad total de este artículo vendido en cada mes de 2021 con la cantidad total de este artículo vendido en todo el año. Para ello, crearemos un informe sencillo con las columnas article, month, units_sold_month y units_sold_year. La consulta es:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year
FROM  sales 
WHERE EXTRACT('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Los resultados de la consulta son:

En este caso, calculamos el total de unidades vendidas utilizando dos granularidades de agrupación diferentes: mes y año. La primera cláusula OVER...

OVER (PARTITION BY article, sale_month) 

... nos permite obtener el número de unidades de cada artículo vendidas en un mes. La segunda cláusula OVER...

OVER (PARTITION BY article)

... nos permite calcular el número total de unidades vendidas de un determinado artículo en todo el año.

En la siguiente consulta, sólo añadiremos la columna month_percentage para mostrar el porcentaje que ocupa un mes concreto dentro del total anual. Podemos calcularlo con la siguiente consulta:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) as month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year,
       ( ( SUM(quantity) OVER (PARTITION BY article, sale_month)::decimal /
           SUM(quantity) OVER (PARTITION BY article)::decimal ) * 100
       ) AS month_percentage
FROM sales 
WHERE extract('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Observe que en la consulta anterior no utilizamos diferentes cláusulas de OVER; simplemente reutilizamos las mismas funciones de ventana y calculamos un porcentaje. Puede ver los resultados a continuación:

Ahora vamos a crear un informe diferente que mire el rendimiento de las distintas sucursales. Queremos ver las columnas branch y month. También necesitamos cálculos para obtener el:

• Los ingresos totales de ese mes.
• Ingresos agrupados por sucursal y mes.
• La media mensual de ingresos de la sucursal.
• Diferencia entre los ingresos de cada sucursal y la media mensual de ingresos.

SELECT DISTINCT branch,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(revenue) OVER (PARTITION BY sale_month) AS total_revenue_month,
       SUM(revenue) OVER (PARTITION BY branch, sale_month) AS branch_revenue_month,
      
 -- Next column is the branch average revenue in the current month
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS average_month_branch,
 
 -- Next column is the difference between branch revenue and average branch revenue

        SUM(revenue) OVER (PARTITION BY branch, sale_month) -
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS gap_branch_average

FROM sales 
WHERE extract('YEAR' from sale_day) = 2021
ORDER BY branch, month

Nuevamente utilizamos sólo dos cláusulas de OVER, pero utilizamos diferentes expresiones aritméticas para obtener ciertos valores. Hemos utilizado ...

SUM(revenue) OVER (PARTITION BY sale_month) 

... para calcular los ingresos totales del mes, pero también lo utilizamos en una expresión aritmética para obtener los ingresos medios mensuales de la sucursal.

Utilizamos ...

SUM(revenue) OVER (PARTITION BY branch, sale_month) 

... para calcular los ingresos mensuales de la sucursal y la diferencia entre los ingresos mensuales de esa sucursal y la media.

La siguiente tabla es el resultado de la consulta. Observe que la columna gap_branch_average puede contener números positivos o negativos. Un número negativo indica que los ingresos mensuales de esta sucursal fueron menores que el promedio de ingresos.

Para obtener información adicional sobre las funciones de ventana en SQL, sugiero SQL Window Function Example With Explanations, un artículo de nivel básico sobre las funciones de ventana. Para lectores más avanzados, How to Rank Rows Within a Partition in SQL muestra cómo crear clasificaciones en sus informes utilizando la función de ventana RANK().

La Cláusula OVER y la Analítica Funciones de ventana

En las consultas anteriores, utilizamos funciones de ventana para comparar cifras mensuales (ingresos y unidades vendidas, respectivamente) con cifras anuales. En esta sección, utilizaremos marcos de ventana ordenados, lo que nos permite elegir un registro en el marco en función de su posición. Por ejemplo, podemos elegir el primer registro del marco de la ventana, o el registro anterior al actual, o el registro posterior al actual. Estas funciones analíticas de ventana proporcionan una gran capacidad de expresión a SQL.

En la siguiente consulta, mostraremos el aumento/disminución de los ingresos de una misma sucursal en dos meses contiguos. Para ello, necesitamos calcular la diferencia entre los ingresos del mes actual y los del mes anterior. Para ello es necesario utilizar la función de ventana analítica LAG(), que puede obtener un valor de columna de una fila anterior a la actual.

WITH branch_month_sales AS (
	SELECT    DISTINCT
		    branch,
		    EXTRACT('MONTH' FROM sale_day) AS month,
		    SUM(revenue) OVER (PARTITION BY branch, sale_month ) AS revenue
	FROM sales
)
SELECT branch, 
	month,
	revenue AS revenue_current_month,
	LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_prev_month,
	revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_delta
FROM branch_month_sales
ORDER BY branch, month

En esta consulta, utilizamos una expresión de tabla común (CTE) llamada branch_month_sales para almacenar los ingresos totales de cada rama y mes. Luego escribimos una segunda consulta que utiliza la función de ventana LAG() para obtener los ingresos del mes anterior (utilizando la información de branch_month_sales). Tenga en cuenta que el marco de la ventana está ordenado por mes.

Estos son los resultados:

En todas las consultas mostradas en este artículo, hemos utilizado sólo unas pocas funciones ventana. Hay bastantes otras funciones de ventana en SQL. Aquí hay una lista de cada una:

Aprenda más sobre la cláusula OVER de SQL

En este artículo, hemos cubierto la cláusula OVER y las subcláusulas PARTITION BY y ORDER BY.

Si quieres seguir aprendiendo sobre las funciones de ventana, hay un par de artículos que me gustaría compartir. El primero es 8 Best SQL Window Function Articles, que te llevará a otros grandes artículos. El segundo es una hoja de trucos sobre funciones de ventana que incluye sintaxis, ejemplos e imágenes; es mi artículo favorito sobre funciones de ventana.

Para aquellos que quieran profundizar, sugiero nuestro curso interactivo Funciones de ventana SQL. Si quieres aumentar tus conocimientos de SQL en general, prueba el tema SQL de la A a la Z. Es una mirada completa a todo lo que necesitas saber para trabajar eficazmente con SQL.