Visión general
- Conozca las funciones de la ventana SQL
- Comprender de qué carecen las funciones agregadas y por qué necesitamos funciones de ventana en SQL
Introducción
Los datos están proliferando a un ritmo asombroso, ¡creciendo a 44 zettabytes en 2020! Y no hace falta decir que la tecnología para manejar cantidades tan gigantescas de datos también está cambiando a un ritmo proporcional.
Hoy tenemos una gran variedad de herramientas como Hive y Spark para manejar Big Data. Pero, a pesar de que difieren en ciertos aspectos, aún emplean los fundamentos de SQL, lo que hace que sea muy fácil para personas de todos los ámbitos de la vida manipular Big Data con una brisa. Aunque todavía fallamos en ciertos aspectos de SQL. Entonces, en este artículo, voy a hablar sobre uno de esos aspectos en particular: las funciones de ventana.
¡Eso es correcto! Existen funciones de ventana en SQL, ¡no es broma! Y teniendo en cuenta la expresión de asombro en su rostro, este artículo parece ser la necesidad del momento. Incluso yo no estaba muy al tanto de estas funciones hasta hace poco, lo que demuestra cuán subestimadas son estas funciones.
Pero espere hasta el final de este artículo porque Window Functions realmente le dejará boquiabierto con la simplicidad con la que resuelven problemas tan complejos. Y sí, los ingenieros de datos, los científicos de datos, los analistas de datos y todos los demás que flirtean con los datos deben dar a estas funciones el mérito que les corresponde.
Antes de seguir adelante, le sugiero que se familiarice con las funciones básicas de SQL revisando este artículo: 24 funciones de SQL de uso común. Y si está interesado en aprender SQL en un formato de curso, consulte nuestro curso: Lenguaje de consulta estructurado (SQL) para ciencia de datos.
Tabla de contenido
- Presentamos el conjunto de datos
- ¿Dónde se retrasan las funciones agregadas?
- ¿Qué son las funciones de ventana en SQL?
- Comprensión de las funciones de la ventana SQL – Cláusula Over
- Ventanas con PARTITION BY
- Organizar filas dentro de particiones
- Funciones de ventana
- Numero de fila
- Rango vs Dense_Rank
- Nth_Value
- Ntile
- Adelanto y retraso
- Archivo de código SQL
Presentamos el conjunto de datos
Antes de continuar, permítame presentarle el conjunto de datos ficticios en el que trabajaremos en este artículo. Suponga que hay una empresa que mantiene los registros de nombre, trabajo y salario del empleado de la siguiente manera:
Usaremos este conjunto de datos de muestra para comprender los conceptos de este artículo. Muy bien, ¡comencemos!
¿Dónde se retrasan las funciones agregadas?
Suponga que desea determinar el salario total de todos los empleados de la empresa. ¿Cómo lo harías? Simplemente puede usar la función agregada SUM () en la columna SALARIO.
Fácil.
¿Qué tal determinar el salario total de los empleados por categoría de trabajo? Utilice la última consulta y agregue una cláusula GROUP BY en la columna JOB.
¡Excelente!
Ahora déjame hacerte dos preguntas más:
- Muestre el salario total y el salario total por categoría de trabajo junto con el valor de cada fila.
- Organice el salario en orden decreciente dentro de cada categoría de trabajo.
¿Lo obtuviste? ¿No? ¿Probablemente?
Estos definitivamente no fueron tan fáciles como los primeros que pudiste obtener al instante. ¿Pero por qué?
Bueno, si lo piensa bien, las consultas anteriores requerían funciones agregadas simples para resolver el problema. Las funciones agregadas de SQL solo nos dan un valor único para el grupo de filas agregadas (piense en la primera consulta que escribimos).
Pero las últimas consultas no se pueden resolver simplemente usando tales funciones. Esas consultas quieren que mantengamos la identidad original de las filas individuales, algo que las funciones agregadas no logran abordar. Por lo tanto, para resolver este tipo de consultas, necesitamos diferentes tipos de funciones: las funciones de ventana.
¿Qué son las funciones de ventana en SQL?
Funciones de ventana realizar cálculos en un conjunto de filas que están relacionadas entre sí. Pero, a diferencia de las funciones agregadas, las funciones de ventana no contraen el resultado de las filas en un solo valor. En cambio, todas las filas mantienen su identidad original y el resultado calculado se devuelve para cada fila.
Comprensión de las funciones de la ventana SQL – Cláusula Over
Por ejemplo, si tuviera que mostrar el salario total de los empleados junto con cada valor de fila, se vería así:
los SOBRE cláusula significa una ventana de filas sobre las que se aplica una función de ventana. Se puede usar con funciones agregadas, como lo hemos usado aquí con la función SUM, convirtiéndola así en una función de ventana. O también se puede usar con funciones no agregadas que solo se usan como funciones de ventana (aprenderemos más sobre ellas en las secciones posteriores).
Entonces, la sintaxis para definir una función de ventana simple que genera el mismo valor para todas las filas es la siguiente:
nombre_función_ventana () OVER ()
Pero, ¿qué tal si aplicamos la función de ventana a filas específicas en lugar de a toda la tabla?
Ventanas con PARTITION BY
los PARTICIÓN POR La cláusula se utiliza junto con la cláusula OVER. Divide las filas en diferentes particiones. A continuación, la función de ventana actúa sobre estas particiones.
Por ejemplo, para mostrar el salario total por categoría de trabajo para todas las filas, tendríamos que modificar nuestra consulta SQL original de la siguiente manera:
Como puede ver, el salario_trabajo_total La columna representa la suma de las ventas para esa categoría de trabajo específica y no para toda la tabla.
Entonces, la sintaxis para definir la función de ventana para la partición de filas es la siguiente:
nombre_función_ventana () OVER ()
Ahora, ¿qué tal organizar las filas dentro de cada partición?
Organizar filas dentro de particiones
Sabemos que para organizar filas en una tabla, podemos usar la cláusula ORDER BY. Entonces, para organizar filas dentro de cada partición, tenemos que modificar la cláusula OVER con la cláusula ORDER BY.
Aquí, las filas se han dividido según su categoría de trabajo, como se indica en la columna TRABAJO. A medida que se desplaza hacia abajo, notará que la columna SALARIO se ha ordenado en orden descendente y la ordenado_trabajo_salario La columna representa el total acumulado de la categoría de trabajo (comenzar de nuevo después de cada partición).
Entonces, la sintaxis para definir la función de ventana para la partición de filas y organizarlas en orden es la siguiente:
nombre_función_ventana () OVER ( )
Funciones de ventana
Ahora que sabemos cómo definir funciones de ventana usando la cláusula OVER y algunas de sus versiones modificadas, ¡finalmente podemos pasar a trabajar con las funciones de ventana!
1. Row_Number
A veces, es posible que su conjunto de datos no tenga una columna que describa el orden secuencial de las filas, como es el caso de nuestro conjunto de datos. En ese caso, podemos hacer uso de la NUMERO DE FILA() función de ventana. Asigna un número secuencial único a cada fila de la tabla.
Observe que la numeración comienza desde 1. Además, para evitar cualquier conflicto con la palabra clave MySQL para la función, he puesto el nombre de la columna entre comillas.
Pero, dado que es una función de ventana, también podemos limitarla a particiones y luego ordenar esas particiones.
Aquí, hemos dividido las filas en la columna TRABAJO y las hemos ordenado según el SALARIO del empleado. Observe cómo la numeración se reinicia cada vez que comienza una nueva partición.
Pero supongamos que queremos clasificar a los empleados en función de sus salarios.
2. Clasificación vs Dense_Rank
los RANGO() La función de ventana, como su nombre indica, clasifica las filas dentro de su partición según la condición dada.
Observe la parte resaltada. En el caso de ROW_NUMBER (), tenemos un número secuencial. Por otro lado, en el caso de RANK (), tenemos el mismo rango para filas con el mismo valor.
Pero aquí hay un problema. Aunque a las filas con el mismo valor se les asigna el mismo rango, el rango subsiguiente omite el rango que falta. Esto no nos daría los resultados deseados si tuviéramos que devolver valores “principales N distintos” de una tabla. Por lo tanto, tenemos una función diferente para resolver este problema.
los DENSE_RANK () La función es similar a RANK () excepto por una diferencia, no omite ningún rango al clasificar filas.
Aquí, todos los rangos son distintos y aumentan secuencialmente dentro de cada partición. En comparación con la función RANK (), no se ha saltado ningún rango dentro de una partición.
3. Nth_Value
Si desea recuperar el enésimo valor de un marco de ventana para una expresión, puede usar la función de ventana NTH_VALUE (expresión, N).
Por ejemplo, para recuperar el tercer salario más alto en cada categoría de TRABAJO, podemos dividir las filas de acuerdo con la columna TRABAJO, luego ordenar las filas dentro de las particiones según el salario decreciente y, finalmente, usar la función NTH_VALUE para recuperar el valor. El comando será el siguiente:
Debes haber notado algo diferente después de la cláusula Order By. Eso es el Cláusula marco. Determina el subconjunto de la partición (o marco) que será utilizado por la función de ventana para calcular el valor de la fila actual.
Aquí, mencioné que todas las filas anteriores y siguientes de una fila actual se considerarán dentro del marco al aplicar la función de ventana. Pero, ¿por qué usé la cláusula marco aquí y no con otras funciones? Esto se debe a que las otras funciones de la ventana funcionan en toda la partición, incluso si se proporciona una cláusula de marco. Pero solo NTH_VALUE () puede funcionar en marcos dentro de una partición.
Ahora suponga que desea generar el primer valor de cada partición. Aunque hay un FIRST_VALUE () función también, voy a usar el NTH_VALUE para el mismo.
Del mismo modo, también tenemos un LAST_VALUE () función. Pero voy a determinar el último valor dentro de cada partición como arriba, aunque usando el orden decreciente de filas.
4. Ntile
A veces, es posible que desee ordenar las filas dentro de la partición en un cierto número de grupos. Esto es útil cuando desea determinar el percentil, cuartil, etc. en el que se encuentra una fila en particular. los NTILE () La función se utiliza para tales fines. Devuelve el número de grupo para cada una de las filas de la partición.
Por ejemplo, busquemos el cuartil de cada fila según el SALARIO del empleado:
De manera similar, puede dividir las filas en diferentes números de grupos y calcular el NTILE para diferentes particiones.
5. Adelanto y retraso
A menudo, es posible que desee comparar el valor de la fila actual con el de la fila anterior o siguiente. Ayuda en el análisis fácil de los datos. los DIRIGIR() y RETRASO() Las funciones de ventana están ahí solo para este propósito.
Aquí, creamos una nueva columna que contiene SALARIO de la siguiente fila dentro de cada partición ordenada por salario usando la función LEAD. Observe que la última fila de cada partición contiene un valor nulo porque no hay una fila posterior de la que extraer datos.
Ahora, hagamos lo mismo con la función LAG.
Aquí, creamos dos nuevas columnas. La primera columna contiene SALARIO de la fila anterior dentro de cada partición ordenada por salario. Mientras que la segunda columna contiene la diferencia entre SALARIO de la fila anterior y la fila actual. Como puede ver, esto es muy útil para un análisis rápido de la diferencia entre los salarios dentro de la misma partición.
Archivo de código SQL
Todo el código SQL relacionado con las funciones de ventana para este artículo se puede encontrar en este enlace.
Notas finales
Ya hemos visto bastantes funciones de ventana y espero que ahora pueda apreciar la belleza de las funciones de ventana en SQL. Pero el aprendizaje no se detiene aquí. Después de dominar los fundamentos, es hora de dominar las herramientas para manejar Big Data.
Si desea pasar al dominio de Ingeniería de datos, le sugiero los siguientes artículos para una transición fácil:
