Cómo utilizar la cláusula SQL PARTITION BY con OVER

En el corazón de cada llamada a una función de ventana hay una cláusula OVER que define cómo se construyen las ventanas de los registros. Dentro de la cláusula OVER, puede haber una subcláusula opcional PARTITION BY que define los criterios para identificar qué registros incluir en cada ventana. Siga leyendo y dé un paso importante para aumentar sus conocimientos de SQL.

¿Qué es la cláusula PARTITION BY en SQL?

La expresión SQL PARTITION BY es una subcláusula de la cláusula OVER, que se utiliza en casi todas las invocaciones de funciones de ventana como AVG(), MAX(), y RANK(). Como muchos lectores probablemente saben, las funciones de ventana operan sobre marcos de ventana que son conjuntos de filas que pueden ser diferentes para cada registro en el resultado de la consulta. Aquí es donde entra la subcláusula SQL PARTITION BY: se utiliza para definir qué registros deben formar parte del marco de ventana asociado a cada registro del resultado.

Este artículo explica el SQL PARTITION BY y sus usos con ejemplos. Dado que está profundamente relacionado con las funciones de ventana, es posible que primero quieras leer algunos artículos sobre funciones de ventana, como "Ejemplo de función de ventana SQL con explicaciones", donde encontrarás muchos ejemplos. Si quieres aprender más sobre las funciones de ventana, también hay un interesante artículo con muchos punteros a otros artículos sobre funciones de ventana.

Lo primero en lo que hay que centrarse es en la sintaxis. Aquí se explica cómo utilizar la cláusula SQL PARTITION BY:

SELECT
    ,
     OVER(PARTITION BY  [ORDER BY ])
FROM table;

Veamos un ejemplo que utiliza la cláusula PARTITION BY. Utilizaremos la siguiente tabla llamada car_list_prices:

Para cada coche, queremos obtener la marca, el modelo, el precio, el precio medio de todos los coches, y el precio medio sobre el mismo tipo de coche (para tener una mejor idea de cómo el precio de un coche dado comparado con otros coches). Esta es la consulta:

SELECT
    car_make,
    car_model,
    car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

El resultado de la consulta es el siguiente:

La consulta anterior utiliza dos funciones de ventana. La primera se utiliza para calcular el precio medio de todos los coches de la lista de precios. Utiliza la función ventana AVG() con una cláusula vacía OVER como vemos en la siguiente expresión:

AVG(car_price) OVER() AS "overall average price"

La segunda función de ventana se utiliza para calcular el precio medio de un car_type específico como estándar, premium, sport, etc. Aquí es donde usamos una cláusula OVER con una subcláusula PARTITION BY como vemos en esta expresión:

AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"

Las funciones de ventana son bastante potentes, ¿verdad? Si quieres aprender más haciendo ejercicios bien preparados, te sugiero el curso Funciones de ventanadonde podrás aprender y sentirte cómodo con el uso de las funciones ventana en las bases de datos SQL.

Profundizando en la cláusula SQL PARTITION BY

La cláusula GROUP BY agrupa un conjunto de registros basándose en criterios. Esto nos permite aplicar una función (por ejemplo, AVG() o MAX()) a grupos de registros para obtener un resultado por grupo.

Como ejemplo, digamos que queremos obtener el precio medio y el precio máximo de cada marca. Utilice la siguiente consulta:

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

Este es el resultado de esta consulta:

En comparación con las funciones de ventana, GROUP BY agrupa los registros individuales en un grupo. En consecuencia, no se puede hacer referencia a ningún campo individual del registro; es decir, sólo se puede hacer referencia a las columnas de la cláusula GROUP BY.

Por ejemplo, supongamos que quiere crear un informe con el modelo, el precio y el precio medio de la marca. No puede hacerlo utilizando GROUP BY, porque los registros individuales de cada modelo están colapsados debido a la cláusula GROUP BY car_make. Para algo así, es necesario utilizar funciones de ventana, como vemos en el siguiente ejemplo:

SELECT car_make,
       car_model,
       car_price,
       AVG(car_price) OVER (PARTITION BY car_make) AS average_make
FROM   car_list_prices

El resultado de esta consulta es el siguiente:

Para los que quieran profundizar, sugiero el artículo "¿Cuál es la diferencia entre un GROUP BY y un PARTITION BY?" con multitud de ejemplos utilizando funciones agregadas y de ventana.

Además de la cláusula PARTITION BY, existe otra cláusula llamada ORDER BY que establece el orden de los registros dentro del marco de la ventana. Algunas funciones de ventana requieren una cláusula ORDER BY. Por ejemplo, las funciones de ventana LEAD() y LAG() necesitan que la ventana de registros esté ordenada, ya que acceden al registro anterior o al siguiente desde el registro actual.

Un marco de ventana se compone de varias filas definidas por los criterios de la cláusula PARTITION BY. Sin embargo, podemos especificar límites o fronteras al marco de la ventana como vemos en la siguiente imagen:

Los límites inferior y superior en la cláusula OVER pueden ser:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Cuando no especificamos ningún límite en una cláusula OVER, su marco de ventana se construye en base a unos valores de límite por defecto. Éstos dependen de la sintaxis utilizada para llamar a la función de ventana. La siguiente tabla muestra los límites por defecto del marco de la ventana.

Hay un artículo detallado llamado "SQL Funciones de ventana Cheat Sheet" donde puedes encontrar muchos detalles de sintaxis y ejemplos sobre los diferentes límites del marco de la ventana.

La cláusula SQL PARTITION BY en acción

En esta sección, mostramos algunos ejemplos de la cláusula SQL PARTITION BY. Todos se basan en la tabla paris_london_flights, utilizada por una aerolínea para analizar los resultados comerciales de esta ruta para los años 2018 y 2019. Aquí tenemos un subconjunto de datos:

Ejemplo 1

La primera consulta genera un informe que incluye el flight_number, aircraft_model con la cantidad de pasajeros transportados, y los ingresos totales. La consulta es la siguiente:

SELECT DISTINCT
       flight_number,
       aircraft_model,
	SUM(num_of_passengers) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_passengers,
	SUM(total_revenue) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_revenue
FROM paris_london_flights
ORDER BY flight_number, aircraft_model;

Como el total de pasajeros transportados y el total de ingresos se generan para cada combinación posible de flight_number y aircraft_model, utilizamos la siguiente cláusula PARTITION BY para generar un conjunto de registros con el mismo número de vuelo y modelo de avión:

OVER (PARTITION BY flight_number, aircraft_model)

A continuación, para cada conjunto de registros, aplicamos las funciones de ventana SUM(num_of_passengers) y SUM(total_revenue) para obtener las métricas total_passengers y total_revenue que se muestran en el siguiente conjunto de resultados.

Ejemplo 2

En la siguiente consulta, mostramos la evolución del negocio comparando las métricas de un mes con las del mes anterior. Creamos un informe utilizando funciones de ventana para mostrar la variación mensual de pasajeros e ingresos.

WITH year_month_data AS (
  SELECT DISTINCT
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   SUM(number_of_passengers)
              OVER (PARTITION BY EXTRACT(YEAR FROM scheduled_departure),
                                  EXTRACT(MONTH FROM scheduled_departure)
                   ) AS passengers
   FROM  paris_london_flights
  ORDER BY 1, 2
)
SELECT  year,
        month,
	 passengers,
	 LAG(passengers) OVER (ORDER BY year, month) passengers_previous_month,
	 passengers - LAG(passengers) OVER (ORDER BY year, month) AS passengers_delta
FROM year_month_data;

En la consulta anterior, utilizamos una cláusula WITH para generar una CTE (CTE significa expresiones de tabla comunes y es un tipo de consulta para generar una tabla virtual que se puede utilizar en el resto de la consulta). Rellenamos los datos en una tabla virtual llamada year_month_dataque tiene 3 columnas: year, month, y passengers con el total de pasajeros transportados en el mes.

A continuación, la segunda consulta (que toma el CTE year_month_data como entrada) genera el resultado de la consulta. La columna pasajeros contiene el total de pasajeros transportados asociados al registro actual. Con la función de ventana LAG(passenger), obtenemos el valor de la columna pasajeros del registro anterior al actual. Nos ORDER BY year and month:

LAG(passengers) OVER (ORDER BY year, month)
passengers_previous_month

Se obtiene el número de pasajeros del registro anterior, correspondiente al mes anterior. A continuación, tenemos el número de pasajeros del mes actual y del anterior. Finalmente, en la última columna, se calcula la diferencia entre ambos valores para obtener la variación mensual de pasajeros.

Ejemplo 3

Para nuestro último ejemplo, vamos a analizar los retrasos de los vuelos. Queremos obtener diferentes medias de retrasos para explicar las razones de los mismos.

Utilizamos un CTE para calcular una columna llamada month_delay con el retraso medio de cada mes y obtener el modelo de avión. Luego, en la consulta principal, obtenemos los diferentes promedios como vemos a continuación:

WITH paris_london_delays AS (
  SELECT DISTINCT
	   aircraft_model,
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   AVG(real_departure - scheduled_departure) AS month_delay
   FROM  paris_london_flights
   GROUP BY 1, 2, 3
)
SELECT  DISTINCT
     aircraft_model,
     year,
     month,
     month_delay AS monthly_avg_delay,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year) AS year_avg_delay,
     AVG(month_delay) OVER (PARTITION BY year) AS year_avg_delay_all_models,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year 
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                            ) AS rolling_average_last_4_months

FROM paris_london_delays
ORDER BY 1,2,3

Esta consulta calcula varios promedios. La primera es la media por modelo de avión y año, que es muy clara. La segunda es la media por año de todos los modelos de avión. Obsérvese que sólo utilizamos la columna año en la cláusula PARTITION BY. La tercera y última es la media móvil, en la que utilizamos los 3 meses más recientes y el mes actual (es decir, la fila) para calcular la media con la siguiente expresión:

AVG(month_delay) OVER (PARTITION BY aircraft_model, year
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                           ) AS rolling_average_last_4_months

La cláusula ROWS BETWEEN 3 PRECEDING AND CURRENT ROW en PARTITION BY restringe el número de filas (es decir, meses) que se incluirán en la media: los 3 meses anteriores y el mes actual. A continuación puede ver un resultado parcial de esta consulta:

El artículo "The RANGE Clause in SQL Funciones de ventana: 5 Practical Examples" explica cómo definir un subconjunto de filas en el marco de la ventana utilizando RANGE en lugar de ROWS, con varios ejemplos. Otro artículo interesante es "Common SQL Funciones de ventana: Using Partitions With Ranking Functions" en el que se trata en detalle la cláusula PARTITION BY.

El poder de Funciones de ventana y la cláusula SQL PARTITION BY

Las funciones de ventana son un recurso muy poderoso del lenguaje SQL, y la cláusula SQL PARTITION BY juega un papel central en su uso. En este artículo, hemos cubierto cómo funciona esta cláusula y hemos mostrado varios ejemplos utilizando diferentes sintaxis.

Antes de terminar, le sugiero un SQL avanzado curso, donde podrás ir más allá de lo básico y convertirte en un maestro de SQL. Si quieres leer sobre la cláusula OVER, hay un artículo completo sobre el tema: "Cómo definir un marco de ventana en SQL Funciones de ventana." ¡Mejora tus habilidades y haz crecer tus activos!