Aprenda la Cláusula OVER() en SQL con 7 Ejemplos

Aprende a utilizar la cláusula OVER() en tus consultas SQL y darás un paso adelante en tus conocimientos de SQL.

La mayoría de los analistas de datos y desarrolladores SQL conocen bien las cláusulas SELECT, FROM, WHERE, GROUP BY, y ORDER BY. Sin embargo, estas cláusulas son sólo un subconjunto limitado del lenguaje SQL. Existen otras cláusulas, como OVER(), que nos permiten crear informes SQL flexibles y añaden una enorme capacidad expresiva al lenguaje SQL.

En este artículo, vamos a explicar la cláusula OVER() mostrando varios ejemplos de su uso. Comencemos.

La cláusula OVER() en SQL

SQL tiene una curva de aprendizaje bastante sencilla; cualquiera puede aprender a crear consultas simples en SQL. De hecho, el profesional de TI o desarrollador de SQL promedio maneja las cláusulas básicas SELECT, FROM, WHERE, GROUP BY y ORDER BY con facilidad. Estas cláusulas estaban disponibles en SQL-92, ¡que tiene 30 años! Sin embargo, el lenguaje SQL no acaba ahí; desde 1992 se han añadido muchos elementos nuevos. Uno de ellos es la cláusula OVER(), que nos permite utilizar funciones ventana en las consultas SQL.

En SQL, las funciones ventana son similares a GROUP BY en el sentido de que trabajan sobre un grupo de filas. Sin embargo, las funciones ventana se basan en una ventana de datos, o un conjunto de filas relacionadas con la fila actual. A diferencia de GROUP BY, las funciones de ventana no contraen filas; mantienen intactos los detalles de las filas individuales.

Para aquellas personas que quieran profundizar en OVER() y las funciones ventana en SQL, les sugiero nuestro curso online Funciones de ventana . Contiene muchos ejemplos usando diferentes funciones ventana.

Bien, ahora volvamos a la función OVER(). Primero, veamos nuestros datos.

Presentación de los datos

Nuestras consultas de ejemplo se basarán en los siguientes datos.

Supongamos que tenemos una marisquería en la costa mediterránea. También tenemos una tabla de base de datos donde almacenamos el historial de la actividad del restaurante. Nuestra base de datos tiene una tabla llamada restaurant_activity con estas columnas

• table_number
• waiter_name
• start_date
• start_time
• served_time
• end_time
• total_diners
• amount_payment
• total_tips.

A continuación se muestra un ejemplo de los datos:

Restaurant_activity

Todo esto debería explicarse por sí mismo, pero repasemos rápidamente algunas columnas. Las start_date y start_time son la fecha y hora en que los clientes pidieron la comida en table_number; served_time es cuando se sirvió la comida, y end_time es cuando los clientes pidieron la cuenta. amount_payment es el coste de la comida, sin incluir la propina (que se almacena como total_tips).

Ejemplo de consulta nº 1: Uso sencillo de OVER()

Empecemos utilizando la cláusula OVER() para calcular el porcentaje de cada pedido del total de ingresos diarios en 5/5/2022. Podemos calcular los ingresos totales del restaurante en este día (excluyendo las propinas) utilizando OVER() combinado con la función de ventana SUM(). La cláusula OVER() siempre está asociada a una función ventana; las funciones ventana calculan un valor basándose en un conjunto de registros definidos por la cláusula OVER(). La consulta es:

SELECT 
  start_date AS date,
  SUM(amount_payment) OVER () AS daily_revenue,
  amount_payment AS total_order,
  (amount_payment / SUM(amount_payment) OVER ())*100 AS order_percentage
FROM   restaurant_activity
WHERE  start_date = ’5/5/2022’

La consulta anterior calcula el daily_revenue para 5/5/2022 sumando cada importe de pago para este día. La cláusula OVER() está vacía, lo que significa que la ventana de registros utilizada para calcular la función SUM() es el conjunto completo de registros devueltos por la consulta. En otras palabras, la ventana se compone de todos los registros para esta fecha.

En el cálculo de la columna order_percentage, dividimos el importe del pedido individual por el total de ingresos diarios para obtener el porcentaje; este es un punto central en la consulta, porque estamos combinando columnas de nivel de fila con resultados de función de ventana en una sola expresión.

Comprender qué registros forman parte de la ventana es fundamental para entender cómo funcionan las funciones ventana; volveremos sobre este punto más adelante en el artículo. Por ahora, veamos los resultados:

Antes de entrar en ejemplos más complejos de OVER(), me gustaría sugerirle nuestro Conjunto de PrácticasFunciones de ventana . Si quiere aprender realmente a utilizar OVER(), estos 100 ejercicios interactivos le servirán.

Ejemplo de consulta nº 2: Uso de la subcláusula PARTITION BY

En este ejemplo, utilizaremos la subclausa PARTITION BY; funciona con OVER() para definir ventanas de datos.

Supongamos que queremos un informe similar al anterior, pero ampliado a cualquier día de mayo de 2022. Para cada día, queremos ver los ingresos diarios totales, el importe individual de todos los pedidos y qué porcentaje de los ingresos diarios se atribuye a cada pedido. Esta es la consulta:

SELECT start_date AS date,
  		SUM(amount_payment) OVER (PARTITION BY start_date) AS daily_revenue,
  		amount_payment AS total_order,
  		(amount_payment / SUM(amount_payment) OVER (PARTITION BY start_date)) * 100 AS
    order_percentage
FROM restaurant_activity
WHERE start_date BETWEEN ’5/1/2022’ AND ’5/31/2022’ 

Hemos mencionado que las funciones de ventana trabajan en base a una ventana de registros (o un conjunto de registros) relacionados con el registro actual. La consulta anterior utiliza la subclausa PARTITION BY start_date para definir qué registros pertenecen a cada ventana. En nuestro ejemplo, todos los registros con el mismo valor start_date que la fila actual formarán parte de la ventana. Como sólo tenemos dos valores únicos para start_date, sólo tenemos dos ventanas diferentes. Se muestran en verde y rojo en el resultado siguiente:

Ejemplo de consulta nº 3: Uso de la subcláusula ORDER BY

Ahora vamos a introducir la subclausa ORDER BY, que permite poner las filas en un orden específico dentro de la ventana.

Supongamos que queremos un informe sencillo con los cinco pedidos más caros junto con el camarero, la fecha y el número de mesa. La consulta es:

WITH ranking AS (
	SELECT 
table_number, 
amount_payment, 
waiter_name, 
start_date, 
	       RANK() OVER (ORDER BY amount_payment DESC) AS position
	FROM restaurant_activity
)
SELECT 
  amount_payment, 
  waiter_name, 
  start_date, 
  table_number, 
  position
FROM ranking
WHERE position <= 5
ORDER BY position

Aquí, utilizamos una CTE (o Expresión de Tabla Común) para construir el ranking. En la CTE, calculamos la posición de cada pedido utilizando la función de ventana RANK() combinada con la siguiente cláusula OVER():

RANK() OVER (ORDER BY amount_payment DESC)

La cláusula anterior define una ventana de registros que está formada por todos los registros de la tabla, por lo que se incluyen todos los pedidos del restaurante. Esta ventana se ordena por amount_payment en orden descendente: el mayor importe es el primer registro de la ventana, y así sucesivamente. La función RANK() devuelve la posición de la fila actual en la ventana ordenada, almacenando este valor en la columna posición del CTE.

Después de construir el CTE de clasificación, el resto de la consulta utiliza ranking como cualquier otra tabla. Filtramos los registros para obtener sólo los 5 primeros puestos y ordenamos los resultados por position. Esto es lo que obtenemos:

En este punto, me gustaría sugerir el artículo SQL Funciones de ventana vs. GROUP BY: ¿Cuál es la diferencia? Explica las diferencias entre la cláusula GROUP BY y las funciones de ventana a través de varios ejemplos de consultas SQL. Otro artículo interesante con detalles adicionales es ¿Qué es la cláusula OVER() en SQL?.

Ejemplo de Consulta nº 4: Calculando el Tiempo Libre de una Tabla con OVER() y LAG()

Las funciones de ventana incluyen muchas formas de facilitar los cálculos complejos. Una de ellas es la función LAG(), que devuelve un valor de cualquier columna de la fila anterior relacionada con la fila actual de la ventana.

El dueño del restaurante quiere saber cuánto tiempo están libres las mesas, es decir, el tiempo que transcurre entre cliente y cliente. Podemos hacerlo creando un informe con las columnas table_number, date, free_start, free_end, y free_time_duration.

Para calcular el tiempo libre, necesitamos acceder a dos filas. Necesitamos la end_time de la ocupación anterior y la start_time de la ocupación siguiente; entonces podemos calcular el tiempo transcurrido entre las dos. Aquí es donde entra en juego la función de ventana LAG(), ya que LAG() permite acceder a cualquier columna del registro anterior. Esta es la consulta que utilizaríamos:

    SELECT 
start_date AS date,
table_number,
-- ending time of the previous occupation 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS start_free_time,
-- starting time of current occupation
start_time AS end_free_time,
-- calculating the free time when the table was unoccupied
start_time - 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS free_time_duration
    FROM   restaurant_activity

La clave en la consulta anterior es la función ventana LAG(). La utilizamos para obtener el end_time de la ocupación anterior. La cláusula ...

OVER (PARTITION BY start_date, table_number ORDER BY start_time)

... define una ventana (o conjunto de filas) para cada par distinto de <start_date, table_number>, y cada una de estas ventanas se ordena por start_time. A continuación, LAG(end_time) devuelve la hora de finalización de la ocupación anterior de la tabla.

Probablemente se haya dado cuenta de que utilizamos LAG() dos veces. La primera para obtener la hora de inicio del periodo libre, y la segunda para calcular la duración del tiempo libre mediante la siguiente expresión:

start_time - 
coalesce(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00')

Los resultados de la consulta son:

Antes de pasar a la siguiente sección, sugiero los siguientes artículos para más detalles:

• Ejemplo de función ventana en SQL con explicaciones
• Cómo Ordenar Filas en SQL: Una Guía Completa
• La Función LAG y la Función LEAD en SQL
• Cómo Calcular la Longitud de una Serie con SQL

Ejemplo de Consulta nº 5: Cálculo de Rangos con la Cláusula OVER()

En esta sección, cubriremos una situación de negocios donde la cláusula SQL OVER() puede ser aplicada para crear un ranking como los 10 vendedores principales o los 5 productos más vendidos. Puede utilizar la cláusula OVER() combinada con la función de ventana RANK() para obtener este tipo de informe. Veamos una consulta de ejemplo que devolverá las dos mayores propinas del día y el camarero que las recibió:

SELECT  *
FROM (
  		SELECT 	waiter_name,
  			start_date AS date,
			total_tips AS tip_amount,
			RANK() OVER(PARTITION BY start_date ORDER BY total_tips DESC) AS ranking
  	  	FROM restaurant_activity
     	     ) AS ranking_table
WHERE ranking <= 2;

Utilizamos una subconsulta en la cláusula FROM para crear una "tabla" temporal llamada ranking_table. Utiliza la columna ranking para almacenar la posición de la propina en el ranking diario de propinas. La posición en la clasificación se calcula con la función de ventana RANK(). Otras columnas de la tabla temporal son waiter_name, date y tip_amount.

En la consulta externa, filtramos sólo los consejos clasificados 1 y 2. El resultado de la consulta se muestra a continuación:

Ejemplo de consulta nº 6: Cálculo de las diferencias con respecto a un período anterior

Otra posibilidad interesante es mostrar la diferencia de valor entre el período anterior y el actual. La cláusula OVER() combinada con funciones de ventana como LEAD() y LAG() se utilizan para crear este tipo de informe. Para obtener más información, consulte Cómo calcular la diferencia entre dos filas en SQL.

Supongamos que queremos ver los ingresos diarios de cada camarero junto con la diferencia respecto al día anterior. En la misma fila, también queremos ver la diferencia expresada como porcentaje. Esta es la consulta:

SELECT	
  waiter_name,
  date,
  today_revenue,
  -- revenue variation ----------------------------------------------
  LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) yesterday_revenue,
  today_revenue - LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) AS revenue_variation,
  -- -----------------------------------------------------------------
  -- revenue variation percentage ------------------------------------------------
  round((today_revenue - 
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date)) /
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date),2) * 100 
AS revenue_variation_percentage
  -- -----------------------------------------------------------------------------		
FROM (
  SELECT  DISTINCT 
    start_date::date AS "date",
    waiter_name,
    SUM(total_tips) OVER ( 
      PARTITION BY waiter_name, start_date::date ORDER BY start_date)
AS today_revenue
  FROM 	restaurant_activity
) AS daily_revenue_per_waiter;

Hemos creado una subconsulta en la cláusula FROM llamada daily_revenue_per_waiter que tiene los datos waiter_name, date, y el total de ingresos realizados por este camarero en este día. En la consulta externa, utilizamos la función de ventana LAG() para obtener los ingresos del día anterior, luego obtenemos la variación de los ingresos entre ayer y hoy y el porcentaje de variación. El resultado es:

Ejemplo de consulta nº 7: Media móvil

Las medias móviles son una métrica que se utiliza con frecuencia para suavizar las fluctuaciones a corto plazo. Puede obtener más información en el artículo Qué es una Media Móvil y Cómo Calcularla en SQL; veamos cómo podemos construir una utilizando la cláusula OVER().

Como ejemplo, supongamos que el propietario de un restaurante desea conocer el importe medio pagado por persona en la última mesa servida en un momento dado. Utiliza esta media como métrica para saber cuánto dinero están pagando los comensales y activar algunas promociones o descuentos. Podemos calcular fácilmente esta media con la expresión amount_payment/total_diners; sin embargo, el propietario se ha dado cuenta de que esta métrica tiene grandes fluctuaciones, por lo que ha decidido utilizar el importe medio pagado por persona en las últimas 3 y 6 mesas servidas. La consulta para calcular este informe es

SELECT start_date AS "date",
start_time AS "time",
table_number,
amount_payment AS total_amount,
total_diners,
       	ROUND(amount_payment/total_diners,2) AS diner_avg,
ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_3_tables_served,
	ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_6_tables_served
FROM   restaurant_activity

La consulta anterior calcula 3 promedios diferentes. La primera es una media simple basada en la expresión:

ROUND(amount_payment/total_diners,2)

La segunda media es la media móvil para las 3 últimas mesas servidas; la tercera media es igual, pero para las 6 últimas mesas servidas:

ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2)
…
OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 

El término "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" especifica que la media se calcula a partir de 3 filas: la fila actual y las 2 filas inmediatamente anteriores. La ventana se ordena por la hora de inicio de la mesa. He aquí el resultado:

Otros usos comerciales de la cláusula OVER() incluyen el cálculo de totales corridos (útil en todo tipo de escenarios de análisis financiero) y el cálculo de la longitud de una serie de datos.

¿Listo para practicar la cláusula SQL OVER()?

Hemos demostrado varias consultas que incluyen la cláusula SQL OVER(). Como OVER() debe usarse combinada con una función de ventana, cubrimos algunas de esas también: SUM(), AVG(), LAG() y RANK().

Si quieres poner en práctica tus nuevos conocimientos sobre la cláusula SQL OVER(), te recomiendo nuestro curso interactivo Funciones de ventana seguido de nuestro Conjunto de PrácticasFunciones de ventana . Puedes leer sobre el curso en el artículo Curso SQL del Mes - Funciones de ventana. También puede obtener una copia de nuestra hoja de trucos gratuita SQL Funciones de ventana para ayudarle en su camino de aprendizaje. ¡Desarrolle sus habilidades y aumente sus activos!