Cómo definir un marco de ventana en las funciones de ventana de SQL

La función ventana es un recurso súper poderoso del lenguaje SQL. En el núcleo de cualquier función ventana, hay un conjunto de registros, llamado marco de ventana, definido mediante una cláusula cláusula OVER. Saber qué registros hay en el marco de la ventana, cómo están ordenados y cuáles son sus límites superior e inferior, es fundamental para entender cómo funcionan las funciones ventana. En este artículo, analizaremos y explicaremos con ejemplos cómo se pueden definir diferentes tipos de marcos de ventana. ¡Siga leyendo para dar un paso importante en el crecimiento de sus habilidades SQL!

Uso de PARTITION BY para Definir un Marco de Ventana

Las funciones de ventana de SQL realizan cálculos basados en un conjunto de registros. Por ejemplo, puede querer calcular el salario medio de un grupo específico de registros de empleados. Este grupo de registros se llama marco de ventana, y su definición es fundamental para entender cómo funcionan las funciones de ventana y cómo podemos aprovecharlas.

El marco de la ventana es un conjunto de filas relacionadas con la fila actual donde se utiliza la función de ventana para el cálculo. El marco de la ventana puede ser un conjunto diferente de filas para la siguiente fila del resultado de la consulta, ya que depende de la fila actual que se está procesando. Cada fila del conjunto de resultados de la consulta tiene su propio marco de ventana.

En el resto de este artículo, mostraremos ejemplos de consulta basados en una base de datos de un grupo de concesionarios de automóviles. El grupo almacena la información de las ventas agrupadas por meses en una tabla llamada monthly_car_sales. A continuación se muestra la tabla con algunos datos de ejemplo:

monthly_car_sales

Una forma sencilla de crear un marco de ventana es utilizando una cláusula OVER con una subcláusula PARTITION BY. En el siguiente ejemplo de SQL, generamos un informe de ingresos por marca del coche para el año 2021.

SELECT make,
       SUM(revenue) OVER (PARTITION BY make) AS total_revenue
FROM   monthly_car_sales
WHERE  year = 2021

A continuación, los marcos de ventana generados por la consulta anterior se muestran en diferentes colores (rojo para Ford y azul para Renault). Todos los registros con el mismo valor en la columna make (las filas con código de color de abajo) pertenecen al mismo marco de ventana. Como sólo tenemos dos valores diferentes en la columna make, tenemos dos marcos de ventana.

El resultado de la consulta es:

Me gustaría sugerir 2 artículos donde puedes encontrar mucha información introductoria sobre las funciones de ventana de SQL: "SQL Course of The Month - Funciones de ventana " y "When Do I Use SQL Funciones de ventana?".

Ordenando las filas dentro de un marco de ventana con ORDER BY

Además de PARTITION BY, podemos utilizar una subclausa ORDER BY para ordenar las filas dentro de un marco de ventana. Tener el marco de ventana ordenado por algún criterio nos permite utilizar funciones analíticas de ventana como LEAD(), LAG(), y FIRST_VALUE(), entre otras.

Por ejemplo, si queremos obtener la diferencia de ingresos entre meses consecutivos, podemos ordenar el marco de la ventana por meses. Entonces, dada cualquier fila actual, la función de ventana LAG() puede devolver cualquier columna del mes anterior. Veamos un ejemplo que obtiene la diferencia de ingresos entre cada par de meses consecutivos.

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER ( ORDER BY month) AS previous_month_revenue,
	 revenue - LAG (revenue) OVER (ORDER BY month) AS delta_revenue
FROM monthly_car_sales
WHERE year = 2021
  AND model = 'Mustang'

El resultado de la consulta anterior es:

La primera fila del resultado tiene valores de NULL en las columnas previous_month_revenue y delta_revenue. Esto se debe a que no hay un mes anterior para enero. La columna delta_ingreso se calcula entre los ingresos del mes actual y los ingresos del mes anterior, estos últimos se obtienen con la función de ventana LAG().

Cuando utilizamos ORDER BY en una cláusula OVER, hay un nuevo elemento a considerar: los límites del marco de la ventana. Si no especificamos ninguna cláusula ORDER BY, toda la partición se convierte en el marco de la ventana. Sin embargo, cuando utilizamos una subcláusula ORDER BY, la fila actual se convierte en el límite superior del marco de la ventana. En otras palabras, las filas que siguen a la fila actual (según el criterio ORDER BY ) no se incluyen en el marco de la ventana. En la siguiente sección, trataremos el concepto de límites del marco de la ventana en detalle.

Definición de los límites del marco de la ventana con las filas

Una característica muy interesante de la cláusula OVER es la posibilidad de especificar los límites superior e inferior de un marco de ventana. Estos límites se pueden especificar utilizando una de las dos subcláusulas de la cláusula OVER: ROWS o RANGE. En esta sección, explicaremos cómo utilizar la subcláusula ROWS de la cláusula OVER.

El marco de la ventana es un conjunto de filas que están relacionadas de alguna manera con la fila actual. Sus límites pueden definirse para cada fila del resultado de la consulta con una subcláusula ROWS, que tiene la siguiente sintaxis:

ROWS BETWEEN lower_bound AND upper_bound

Como ya hemos mencionado en la sección anterior, es importante saber cuáles son los límites por defecto del marco de la ventana. Cuando especificamos una subclausa ORDER BY, la fila actual es el límite superior del marco de la ventana por defecto. Sin embargo, en algunos casos, necesitamos cambiar este límite superior (o el inferior) como veremos a continuación.

Veamos un ejemplo en el que necesitamos especificar los límites de un marco de ventana. Supongamos que queremos un informe con el total de ventas del mes actual, el total de ventas del mes anterior y el máximo de ventas en cualquier mes individual a lo largo del año, todo ello por marca y modelo. La consulta para obtener dicho informe es:

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER (PARTITION BY make, model ORDER BY month) AS    
                                                   prev_month,
	 MAX(revenue) OVER (PARTITION BY make, model ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_year_revenue
FROM monthly_car_sales
WHERE year = 2021

Si hubiéramos omitido la subclausa ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING en la función de ventana MAX() en la consulta anterior, habríamos obtenido el máximo entre el primer mes y el mes actual. Esto es erróneo, ya que queremos el máximo de ingresos mensuales considerando todo el año (incluyendo los meses posteriores al actual). Por lo tanto, tenemos que incluir todos los meses disponibles en la tabla. Lo hacemos añadiendo la subcláusula ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING a la cláusula OVER.

La siguiente imagen muestra todos los límites posibles que podemos especificar para definir los límites inferior y superior de un marco de ventana:

Las opciones para los límites inferior y superior en la cláusula OVER son:

• UNBOUNDED PRECEDING
• N PRECEDING
• CURRENT ROW
• N FOLLOWING
• UNBOUNDED FOLLOWING

Debemos asegurarnos de que el límite inferior es menor que el superior.

Por último, me gustaría sugerir el artículo ¿Por qué aprender SQL Funciones de ventana en 2021? donde puedes aprender a utilizar las funciones de ventana.

Definiendo los Límites del Marco de la Ventana con RANGE

En la sección anterior, definimos los límites de un marco de ventana en términos de ROWS. En esta sección, explicaremos cómo utilizar la subclausa RANGE para especificar los límites de un marco de ventana en rangos de filas. La sintaxis de la subclausa RANGE es la siguiente:

RANGE BETWEEN lower_bound AND upper_bound

Un rango es un conjunto de filas con el mismo valor para el criterio PARTITION BY. Por ejemplo, si tenemos un mes PARTITION BY, podemos ver la diferencia en la siguiente imagen cuando utilizamos ROWS o RANGE para definir un marco de ventana:

OVER ( PARTITION BY …... 

             ORDER BY month 

             ROWS BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

OVER ( PARTITION BY ….. 

             ORDER BY month 

             RANGE BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

Si queremos un informe de ingresos por marca para el mes actual y para cada uno de los tres últimos meses, podemos utilizar la siguiente consulta:

SELECT make,
       model,
       month,
	 revenue AS model_revenue_current_month,
       SUM(revenue) OVER ( PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 0 PRECEDING AND CURRENT ROW
                         ) AS make_current_month,
	 SUM(revenue) OVER (PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                        ) AS make_last_2_months,
	   SUM(revenue) OVER (PARTITION BY make
                            ORDER BY month
                            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                        ) AS make_last_3_months
FROM monthly_car_sales
WHERE year = 2021
ORDER BY 1,3,2

La consulta anterior utiliza la subcláusula RANGE para especificar un marco de ventana con todos los registros de la marca actual para un rango de N meses. Por ejemplo:

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                  ) AS make_last_2_months

La subclausa anterior RANGE BETWEEN 1 PRECEDING AND CURRENT ROW especifica un marco de ventana que incluye el mes anterior y el mes actual. Entonces, la función SUM() devolverá el total de ingresos de los dos últimos meses.

Del mismo modo, podemos utilizar la siguiente cláusula OVER para obtener el total de ingresos en los últimos tres meses.

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                  ) AS make_last_3_months

Existen varias abreviaturas para facilitar la sintaxis de estas cláusulas de delimitación:

Por último, pero no menos importante, aquí está mi hoja de trucos preferida sobre las funciones de ventana con muchos detalles de sintaxis: Hoja de trucos de las funciones de ventana de SQL.

¡Aprovecha Funciones de ventana!

Una idea central en la función ventana es el marco de la ventana, es decir, el grupo de registros sobre el que trabaja la función ventana. En este artículo, hemos explicado que el marco de la ventana depende de la fila actual y se define mediante la cláusula OVER. También hemos mostrado varios ejemplos para definir qué registros incluir en el marco de la ventana, ordenar las filas dentro de él y definir sus límites.

Para aquellos que quieran profundizar, sugiero el curso online Funciones de ventana SQL, con multitud de ejemplos utilizando diferentes funciones de ventana. ¡¡Desarrolla tus habilidades y aumenta tus activos!!