Anatomía de las funciones de la ventana SQL

Para comprender los datos de la empresa; hay que consultarlo mucho. Cuando digo 'Mucho', lo digo en serio. Trabajar con montones de datos desconocidos a menudo es desalentador y siempre es una buena práctica tomarse un tiempo para explorar y comprender los datos en sí. Es bueno tener habilidades básicas de recuperación de datos, pero conocer las funciones analíticas para obtener información útil de sus datos es la guinda del pastel y también es divertido.

Provengo de la visualización de datos y es crucial para mí no solo comprender los datos, sino también descubrir cualquier hallazgo notable para destacarlo ante los equipos más amplios. Además, la creación de paneles complejos suele ser un proceso de ida y vuelta en el que regresa a su fuente de datos para contar los datos y las funciones de ventana SQL siempre me han acompañado en mi viaje de análisis de datos.

Aunque son muy útiles para el análisis de datos, existe cierta confusión y la gente suele tener miedo de usarlos. Mientras escribía una guía detallada de las funciones de ventana de SQL, me di cuenta de que se estaba volviendo demasiado descriptiva y, sin embargo, no quería omitir detalles, especialmente sobre la sintaxis y las cláusulas que se usan junto con ella. Es importante entender los componentes básicos, ¿sí? Por lo tanto, intentaré desglosar los componentes básicos de la función de ventana en este artículo para que no sea abrumador procesarla e implementarla.

Como de costumbre, usaremos la base de datos de muestra MySQL de classicmodels para la demostración, que contiene los datos comerciales de un minorista de automóviles. A continuación se muestra el diagrama ER como referencia,

Lo primero es lo primero, ¿qué es una función de ventana?

La definición de libro de texto de una función de ventana es,

Una función de ventana realiza el cálculo en un conjunto de filas de la tabla que de alguna manera están relacionadas con la fila actual.

¿Qué crees que ve este amiguito desde la ventana? Una vista parcial de la escena fuera de la ventana de esta habitación o edificio. ¿bien? Eso es exactamente lo que hace una función de ventana. Le permite realizar cálculos contra un subconjunto de datos sin agregar filas actuales.

¿Cuál es la necesidad? ¿Por qué la función de ventana? ¿Dónde se queda atrás la función agregada?

Aquí están los datos de muestra de la tabla PRODUCTOS , para propósitos de demostración los he limitado a LÍNEAS DE PRODUCTO: Aviones , Barcos y Trenes .

--sample data from table PRODUCTS.
SELECT
    *
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

--total quantity in stock for each productline
SELECT 
    PRODUCTLINE,
    SUM(QUANTITYINSTOCK) AS TOTAL_QUNATITY
FROM 
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
GROUP BY PRODUCTLINE;

      
                

                    
                    
                
            

Vamos a renovar el requisito ahora,

1. Muestre la cantidad de cada producto dentro de la LÍNEA DE PRODUCTOS junto con la cantidad total en stock para esa LÍNEA DE PRODUCTOS en particular .
2. Organice el conjunto de resultados agrupados por LÍNEA DE PRODUCTOS.

--sum() as a window function
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Tipos de funciones de ventana

Honestamente, no existe una categorización oficial de las funciones de Windows, pero según el uso, podemos categorizarlas brevemente de 3 maneras:

• Funciones agregadas : la función agregada regular se puede usar como una función de ventana para calcular agregaciones para columnas numéricas dentro de particiones de ventana, como ventas totales acumuladas, valor mínimo o máximo dentro de la partición, etc.
• Funciones de clasificación : estas funciones devuelven un valor de clasificación para cada fila en una partición.
• Funciones de valor : estas funciones son útiles para generar estadísticas simples o análisis de series temporales.

La sintaxis común de una función de ventana es,

Antes de profundizar en ello; primero comprendamos el significado de cada cláusula dentro de él,

Cláusula OVER()

La cláusula OVER() especifica una función como una función de ventana y, por lo tanto, siempre debe incluirse en la declaración. Define un subconjunto especificado por el usuario (una ventana) de filas en las que se aplicará la función de ventana. Si no proporciona nada dentro de OVER() , la función de ventana se aplicará en todo el conjunto de resultados.

Continuando con el ejemplo anterior,

--empty OVER() clause
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER () AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

PARTICIÓN POR Cláusula

PARTITION BY se usa con la cláusula OVER . Divide el conjunto de resultados de la consulta en particiones o depósitos en función de la expresión especificada por el usuario y luego se aplica la función de ventana en cada partición o depósito.

Es opcional, por lo que si no especifica la cláusula PARTITION BY , la función trata todas las filas como una sola partición. Exactamente lo que hicimos en el ejemplo anterior, simplemente especificamos una cláusula OVER() vacía sin la cláusula PARTITION BY y, por lo tanto, se calculó la cantidad total para todos los PRODUCTLINE s.

¿Qué sucede si especificamos uno,

--OVER() with PARTITION BY
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Ahora, ¿cuál es el trato con las cláusulas PARTITION BY y GROUP BY ? ¿Son iguales o diferentes?

Cláusula GROUP BY ,

• Agrupa varias filas en filas resumidas en función de una o varias columnas/expresiones (devolviendo 1 fila para cada grupo). En palabras más simples, reduce la cantidad de filas en su conjunto de resultados.
• Se usa junto con funciones agregadas como SUM(), AVG(), MAX(), etc.
• Se coloca después de la cláusula WHERE pero antes de las cláusulas HAVING, ORDER BY . La sintaxis común es,

• Se usa con la cláusula OVER() en la función de ventana. Divide el conjunto de resultados de la consulta en particiones y luego se aplica la función de ventana en cada partición.
• PARTITION BY es similar a GROUP BY ya que agrega el resultado en función de la expresión; sin embargo, la principal diferencia es que no reducirá las filas del conjunto de resultados.
• Es opcional, por lo que si no especifica la cláusula PARTITION BY , la función trata todas las filas como una sola partición.
• La sintaxis común es,

ORDEN POR Cláusula

Se utiliza para clasificar el conjunto de resultados en orden ascendente o descendente dentro de cada partición del conjunto de resultados. Por defecto está en orden ascendente.

Cláusula ROWS/RANGE

Ahora ya sabemos que la característica clave de una función de ventana es crear una ventana o una partición del conjunto de resultados utilizando la cláusula PARTITION BY y luego realizar cálculos en cada partición. ¿Qué pasa si además queremos crear subconjuntos dentro de estas particiones? ¡Guau! partición dentro de la partición? Sí, por eso tenemos la cláusula FRAME .

La cláusula FRAME define además un subconjunto de la partición actual. Utiliza ROW o RANGE para definir los puntos inicial y final de este subconjunto. Requiere la cláusula ORDER BY .

Los marcos se determinan con respecto a la fila actual, lo que simplemente significa que toma la ubicación de su fila actual como punto base y con esa referencia define su marco dentro de la partición.

• FILAS : define el comienzo y el final del marco especificando el número de filas que preceden o siguen a la fila actual.
• RANGO : al contrario de ROWS , RANGE especifica el rango de valores en comparación con el valor de la fila actual para definir un marco dentro de la partición.

{FILAS | RANGO} ENTRE <frame_starting> Y <frame_ending>

Antes de continuar, comprendamos algunos términos básicos que definen el marco.

• PRECEDENTE SIN LÍMITES : especifica todas las filas (a partir de la primera fila) antes de la fila actual en la partición.
• N PRECEDENTE : especifica el número 'N' de filas antes de la fila actual en la partición.
• SIGUIENTE SIN LÍMITES : especifica todas las filas después de la fila actual (hasta la última fila) en la partición.
• M SIGUIENTE : especifica el número 'M' de filas debajo de su fila actual en la partición.

SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TOTAL
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Estos son algunos ejemplos de cláusulas FRAME ,

• FILAS ENTRE PRECEDENTES SIN LÍMITES Y SIGUIENTES SIN LÍMITES : esto significa considerar el marco desde la primera fila de la partición hasta la última fila de la partición.
• FILAS ENTRE PRECEDENTES SIN LÍMITES Y 4 SIGUIENTES : esto significa considerar el marco desde la primera fila de la partición hasta 4 filas después de la fila actual.
• FILAS ENTRE 4 PRECEDENTES Y 1 PRECEDENTE - El marco serán las 4 filas anteriores hasta 1 fila antes de la fila actual.

{ROWS/RANGE} ENTRE LA FILA ACTUAL Y LA PRECEDENTE ILIMITADA

Esto significa considerar el marco como todas las filas desde la fila número uno hasta la fila actual en la partición.

Sin la cláusula ORDER BY , el marco predeterminado es,

{ROWS/RANGE} ENTRE EL PRECEDENTE SIN LÍMITES Y EL SIGUIENTE SIN LÍMITES

Esto simplemente significa la partición completa.

Definición de un alias de ventana,

Si hay más de una función de ventana en su consulta que utiliza la misma ventana, es posible que desee utilizar un alias de ventana.

--finding out minimum and maximum MSRP for each productline
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MIN_MSRP,
    MAX(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

--using window alias
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER MSRP_WINDOW AS MIN_MSRP,
    MAX(MSRP) OVER MSRP_WINDOW AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
WINDOW MSRP_WINDOW AS (PARTITION BY PRODUCTLINE);

Durante la ejecución de la consulta, las funciones de ventana se realizan en el conjunto de resultados,

• Después de las cláusulas JOIN , WHERE , GROUP BY y HAVING y
• Antes de la cláusula ORDER BY , LIMIT y SELECT DISTINCT .

Es posible que desee explorar datos de 100 formas diferentes y las funciones de ventana son perfectas para dicho análisis. Este artículo fue solo el comienzo para comprender la sintaxis básica y las cláusulas para que no sea abrumador para la función de ventana, seguramente mejora con la práctica.

• Hoja de referencia de la función de ventana SQL
• Cláusula Marco
• HackerRank o LeetCode para practicar problemas SQL básicos/intermedios/avanzados.