La Cláusula ORDER BY en las Funciones de Ventana SQL

En este artículo, obtendrá una visión general de ORDER BY en las funciones de ventana. Aprenderá cómo se compara con PARTITION BY y el normal ORDER BY, todo ello acompañado de ejemplos y explicaciones.

Si desea crear informes que vayan más allá de simples agregaciones, necesitará las funciones de ventana de SQL. Las funciones de ventana le ayudarán a crear clasificaciones, calcular totales y medias móviles y hallar la diferencia entre filas. Para utilizar las funciones ventana de forma eficaz, debe comprender el papel de la cláusula ORDER BY. No sólo cambia el comportamiento de las funciones ventana, sino que algunas funciones ventana ni siquiera se ejecutarán sin ORDER BY.

Este artículo es para aquellos que ya tienen una comprensión general de las funciones de ventana. Si no estás familiarizado con ellas, te recomiendo encarecidamente que sigas nuestro curso de Funciones de ventana curso. Le proporcionará un conocimiento exhaustivo de las funciones ventana, incluidas las funciones de clasificación, las funciones analíticas y las cláusulas ORDER BY y PARTITION BY. El curso incluye 218 desafíos de codificación que esperan ser resueltos; puede encontrar más en el conjunto de prácticasFunciones de ventana .

Aunque conozca las funciones de ventana de SQL, le recomendamos que tenga a mano nuestra hoja de referencia Funciones de ventana para consultarla rápidamente.

¿Qué es Funciones de ventana?

Las funciones de ventana en SQL realizan operaciones en un marco de ventana, que consiste en la fila actual y las filas relacionadas con ella. A diferencia de las funciones agregadas, las funciones de ventana no contraen filas individuales, sino que añaden una columna a cada fila con el resultado de la función. Esto significa que las funciones de ventana permiten ver datos individuales y agregados simultáneamente.

Sintaxis

A continuación se muestra la sintaxis de las funciones de ventana SQL:

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

He aquí una breve descripción de cada parte de la sintaxis:

• window_function: La función ventana que desea utilizar.
• OVER(): Una cláusula obligatoria para crear una función ventana.
• PARTITION BY: Una cláusula opcional que particiona (divide) los datos.
• ORDER BY: Una cláusula opcional que ordena los datos dentro del marco de la ventana.

¿Qué es la cláusula ORDER BY en Funciones de ventana?

ORDER BY (junto con PARTITION BY) es una parte fundamental de muchas funciones ventana. ORDER BY en la función ventana ordena las filas dentro del marco de la ventana. Define el orden en el que se realizará el cálculo de la función ventana.

ORDER BY puede ordenar los datos dentro de una ventana de forma ascendente (de A a Z, de 1 a 10) o descendente (de Z a A, de 10 a 1). Puede ordenar los datos de texto en orden alfabético o alfabético inverso, los datos numéricos de menor a mayor (o viceversa) y los datos de fecha/hora de más antiguo a más reciente (o de más reciente a más antiguo).

Ejemplo: ORDENAR POR con una función de ventana de clasificación

Veamos un ejemplo de cómo ORDER BY en funciones de ventana influye en la ejecución de la consulta.

La siguiente consulta ordena los datos de la tabla product_sales por ventas en orden descendente, es decir, de mayor a menor venta.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() es una de las funciones ventana de clasificación. Ordena todo el conjunto de datos de mayor a menor volumen de ventas, que se especifica en ORDER BY.

La tabla product_sales original tiene este aspecto:

Por lo tanto, el código anterior ordenará las filas del conjunto de datos de forma descendente: de mayor a menor.

Este es el resultado de la consulta, con los rangos mostrados en una columna separada:

Ahora bien, la clasificación sería significativamente diferente si sustituyera DESC por ASC en ORDER BY, como se muestra a continuación:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Ahora la clasificación se realiza de menor a mayor venta:

ORDER BY y PARTITION BY en Funciones de ventana

Sabemos lo que hace ORDER BY en las funciones de ventana. ¿Qué pasa con PARTITION BY? Se trata de una cláusula opcional que divide los datos en subconjuntos basados en una o más categorías. Lo hacemos especificando columnas en la cláusula PARTITION BY de la siguiente manera: PARTITION BY product_name. Demostraré cómo funciona en la siguiente sección.

Sin PARTITION BY, ORDER BY ordena los datos en todo el conjunto de resultados. Pero si se utiliza con PARTITION BY, ordenará los datos de cada partición por separado.

Ejemplo: Uso de ORDER BY con PARTITION BY en una función de ventana

Cuando se usa con PARTITION BY, ORDER BY ordena los datos dentro de cada partición.

El uso de ORDER BY con la función de ventana SUM() produce una suma acumulativa (es decir, la suma del valor de la fila actual más todas las filas anteriores en la partición). Por ejemplo, el código siguiente calcula la suma acumulada de las ventas por nombre de producto (como se especifica en PARTITION BY) desde la fecha más antigua a la más reciente (como se especifica en ORDER BY).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

La consulta dividirá primero el conjunto de datos en particiones por nombre de producto. A continuación, los valores de cada partición se ordenarán de la fecha más antigua a la más reciente, que se especifica en ORDER BY. A continuación, la función de ventana SUM() calculará el total acumulado sumando las ventas de la fecha actual con las ventas de todas las ventas anteriores dentro de la partición.

Este es el resultado, que muestra cada fila y las ventas acumuladas de cada producto:

Si eliminamos ORDER BY del código, como se muestra a continuación ...

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

... el código devolverá la suma total de ventas para cada producto. En otras palabras, simplemente omitiendo ORDER BY de una función de ventana, se pierde la capacidad de calcular acumulativamente. Sin ORDER BY, todas las filas de la partición forman el marco de la ventana.

ORDER BY en Funciones de ventana vs. ORDER BY regular

Cuando digo regular, me refiero al estándar ORDER BY al final de la consulta. ¿En qué se diferencia de ORDER BY en una función de ventana?

Un ORDER BY normal ordena la salida de una consulta, mientras que ORDER BY en funciones de ventana ordena una ventana de datos o una partición de datos.

Ejemplo: ORDER BY en Funciones de ventana y ORDER BY normal

Esta consulta utiliza la función de ventana DENSE_RANK() con PARTITION BY y ORDER BY para ordenar las fechas de venta de cada producto:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

Puede ver que la salida global no está ordenada de mayor a menor venta. La ordenación descendente de las fechas se aplica sólo dentro de cada partición.

Si desea que la salida se ordene de forma descendente por ventas, tiene que hacerlo explícitamente añadiendo ORDER BY al final de la consulta:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Ahora obtendrá el resultado ordenado por ventas y fecha. Puede ver cómo se compara el rango de ventas de una fecha con las ventas de otras fechas del mismo producto.

Funciones de ventana Eso requiere ORDER BY

Como ha visto en el ejemplo anterior, las funciones ventana pueden funcionar sin ORDER BY. ORDER BY se considera generalmente una cláusula opcional en las funciones ventana.

Pero aún cuando ORDER BY en las funciones de ventana es vista como una cláusula opcional, algunas funciones de ventana requieren ORDER BY para trabajar. En esos casos, ORDER BY se convierte en obligatoria; esas funciones de ventana requieren un orden de clasificación para funcionar correctamente. Estas funciones de ventana son:

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Marcos de ventana por defecto con y sin ORDER BY

El comportamiento de una función de ventana cambia dependiendo de si está escrita con o sin ORDER BY. Más concretamente, la presencia o ausencia de ORDER BY afecta al marco de ventana por defecto.

Si no hay ORDER BY, el marco de ventana por defecto incluye la fila actual y todas las filas que la preceden y la siguen. En otras palabras, se incluyen todas las filas de la partición. Lo hemos visto con el ejemplo de la suma acumulada: sin ORDER BY, el marco de la ventana por defecto es toda la partición y la suma se convierte en la suma total.

Si hay ORDER BY, el marco de la ventana incluye el valor actual y todos los valores anteriores. Lo hemos visto con el ejemplo de la suma acumulada: con ORDER BY, el marco de la ventana incluye todas las filas anteriores a la fila actual y la fila actual.

Si no le gusta el marco de ventana por defecto, puede definirlo explícitamente utilizando las palabras clave FILAS y RANGO.

Uso de ORDER BY en Funciones de ventana

Ahí lo tiene: ORDER BY - a veces obligatoria, a veces opcional - es la cláusula que hace y a veces rompe las funciones de ventana.

Sin embargo, saber todo sobre ORDER BY en las funciones de ventana no sirve de nada si no puede utilizarla en sus consultas. Así pues, pon a prueba lo que has aprendido aquí resolviendo los retos de codificación de nuestro curso sobre funciones de ventana y el conjunto de prácticasFunciones de ventana .

Si quieres hacer aún más ejercicios, resuelve estos 11 ejercicios de funciones de ventana y responde a las 10 mejores preguntas de entrevista sobre funciones de ventana ¡Feliz aprendizaje!