Una introducción a las funciones de la ventana SQL

La característica muy poderosa que te encanta odiar (pero necesitas saber)

Las funciones de la ventana SQL brindan algunas características extremadamente poderosas y útiles. Pero para muchos, dado que son tan extraños al SQL estándar, son difíciles de aprender y comprender, tienen una sintaxis extraña y muy a menudo se evitan.

Las funciones de ventana se pueden explicar simplemente como funciones de cálculo similares a la agregación, pero donde la agregación normal a través de la cláusula GROUP BY se combina y luego oculta las filas individuales que se agregan, las funciones de ventana tienen acceso a filas individuales y pueden agregar algunos de los atributos de esas filas en el conjunto resultante.

En este tutorial de funciones de ventana de SQL, lo ayudaré a comenzar con las funciones de ventana, explicaré los beneficios y cuándo los usaría, y le daré ejemplos reales para ayudarlo con los conceptos.

Una ventana a sus datos

Una de las características más utilizadas e importantes de SQL es la capacidad de agregar o agrupar filas de datos de formas particulares. En algunos casos, sin embargo, la agrupación puede volverse extremadamente compleja, dependiendo de lo que se requiera.

¿Alguna vez ha querido recorrer los resultados de su consulta para obtener una clasificación, una lista de las mejores x o algo similar? ¿Ha tenido algún proyecto de análisis en el que quería preparar sus datos correctamente para una herramienta de visualización, pero lo encontró casi imposible o tan complejo que no valía la pena?

Las funciones de ventana pueden facilitar las cosas. Después de obtener el resultado de su consulta, es decir, después de la cláusula WHERE y cualquier agregación estándar, las funciones de ventana actuarán en las filas restantes (la ventana de datos) y obtendrán lo que desea.

Algunas de las funciones de ventana que vamos a ver incluyen:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Más que fácil

La cláusula OVER es lo que especifica una función de ventana y siempre debe incluirse en la instrucción. El valor predeterminado en una cláusula OVER es el conjunto de filas completo. Como ejemplo, observemos una tabla de empleados en la base de datos de una empresa y mostremos el número total de empleados en cada fila, junto con la información de cada empleado, incluso cuándo comenzaron en la empresa.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Lo anterior, como muchas funciones de ventana, también se puede escribir de una manera más familiar sin ventana, lo cual, en este ejemplo simple, no está tan mal:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Pero ahora, digamos que deseamos mostrar la cantidad de empleados que comenzaron en el mismo mes que el empleado en la fila. Tendremos que reducir o restringir el conteo a solo ese mes para cada fila. ¿Cómo se hace eso? Usamos la cláusula de PARTITION de la ventana, así:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Las particiones le permiten filtrar la ventana en secciones por un valor o valores determinados. Cada sección a menudo se llama el marco de la ventana.

Para ir más lejos, digamos que no solo queremos saber cuántos empleados comenzaron en el mismo mes, sino que queremos mostrar en qué orden comenzaron ese mes. Para eso, podemos usar la familiar cláusula ORDER BY . Sin embargo, dentro de una función de ventana, ORDER BY actúa de manera un poco diferente a como lo hace al final de una consulta.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

En este caso, ORDER BY modifica la ventana para que vaya desde el inicio de la partición (en este caso el mes y año en que comenzó el empleado) hasta la fila actual. Por lo tanto, el conteo se reinicia en cada partición.

Clasificarlo

Las funciones de ventana pueden ser muy útiles para fines de clasificación. Anteriormente vimos que el uso de la función de agregación COUNT nos permitía ver en qué orden los empleados se unieron a la empresa. También podríamos haber usado funciones de clasificación de ventanas, como ROW_NUMBER() , RANK() y DENSE_RANK() .

Las diferencias se pueden ver después de que agreguemos un nuevo empleado el mes siguiente y eliminemos la partición:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Puedes ver las diferencias. ROW_NUMBER() da un conteo secuencial dentro de una partición dada (pero con la ausencia de una partición, pasa por todas las filas). RANK() proporciona el rango de cada fila según la cláusula ORDER BY . Muestra empates y luego salta a la siguiente clasificación. DENSE_RANK también muestra empates, pero luego continúa con el siguiente valor consecutivo como si no hubiera empate.

Otras funciones de clasificación incluyen:

• CUME_DIST : calcula el rango relativo de la fila actual dentro de una partición
• NTILE : divide las filas para cada partición de ventana de la manera más equitativa posible
• PERCENT_RANK : clasificación porcentual de la fila actual

Observe también en este ejemplo que puede tener varias funciones de Windows en una sola consulta, ¡y tanto la partición como el orden pueden ser diferentes en cada una!

Filas, rangos y marcos, ¡Dios mío!

Para definir o limitar aún más el marco de su ventana dentro de la cláusula OVER() , puede usar ROWS y RANGE . Con la cláusula ROWS , puede especificar las filas incluidas en su partición como las anteriores o posteriores a la fila actual.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

En este ejemplo, el marco de la ventana va desde la primera fila hasta la fila actual menos 1, y el tamaño de la ventana continúa aumentando para cada fila.

El rango funciona un poco diferente y podemos obtener un resultado diferente.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

El rango incluirá aquellas filas en el marco de la ventana que tengan los mismos valores ORDER BY que la fila actual. Por lo tanto, es posible que pueda obtener duplicados con RANGE si ORDER BY no es único.

Algunos describen ROWS como un operador físico mientras que RANGE es un operador lógico. En cualquier caso, los valores predeterminados para ROWS y RANGE son siempre UNBOUNDED PRECEDING AND CURRENT ROW .

¿Qué otra cosa?

La mayoría de las funciones agregadas estándar funcionan con funciones de ventana. Ya hemos visto COUNT en los ejemplos. Otros incluyen SUM , AVG , MIN , MAX , etc.

Con las funciones de ventana, también puede acceder tanto a los registros anteriores como a los posteriores utilizando LAG y LEAD y FIRST_VALUE y LAST_VALUE . Por ejemplo, supongamos que desea mostrar en cada fila una cifra de ventas del mes actual y la diferencia entre la cifra de ventas del mes anterior. Podrías hacer algo como esto:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Básicamente, las funciones de la ventana SQL son muy poderosas

Si bien esta es una introducción rápida a las funciones de la ventana SQL, esperamos que despierte su interés por ver todo lo que pueden hacer. Aprendimos que las funciones de ventana realizan cálculos similares a las funciones de agregación, pero con el beneficio adicional de que tienen acceso a los datos dentro de las filas individuales, lo que las hace bastante poderosas. Siempre contienen la cláusula OVER y pueden contener PARTITION BY , ORDER BY y una gran cantidad de funciones de agregación ( SUM , COUNT , etc.) y otras funciones posicionales ( LEAD , LAG ). También aprendimos sobre marcos de ventanas y cómo encapsulan secciones de datos.

Tenga en cuenta que las diferentes versiones de SQL pueden implementar funciones de ventana de manera diferente, y es posible que algunas no hayan implementado todas las funciones o cláusulas de ventana. Asegúrese de consultar la documentación de la plataforma que está utilizando.

Si, como desarrollador de SQL, está interesado en ajustar el rendimiento de su base de datos SQL, consulte Ajuste del rendimiento de la base de datos SQL para desarrolladores .

¡Feliz ventana!

Para obtener más información sobre implementaciones específicas, consulte:

• Documentación de funciones de ventana de PostgreSQL para una implementación de PostgreSQL.
• Documentos de la cláusula SELECT - OVER (Transact-SQL) de Microsoft.
• Funciones de ventana en SQL Server para obtener una excelente descripción general de las implementaciones de SQL Server y su parte 2.