¿Ha estado utilizando funciones SQL Window? ¿Necesita algunas consultas rápidas de ejemplo que pueda consultar siempre que trabaje con bases de datos relacionales o una hoja de trucos que pueda incorporar a su flujo de trabajo de desarrollador?
Para responder a esta inquietud, hemos creado este tutorial para ayudarle a mejorar sus operaciones SQL. Si no ha utilizado las funciones de ventana de SQL, este artículo es un buen comienzo.
Las funciones ventana son funciones de cálculo que reducen la complejidad de las consultas SQL, aumentando su eficacia. Se derivan de dos componentes: una ventana, un conjunto de filas, y funciones, que implican bloques de código SQL predefinidos para realizar operaciones con datos. Utilizando las funciones de la ventana SQL, podrá realizar análisis avanzados sin necesidad de escribir consultas complejas.
Al analizar o crear informes de datos, puede utilizar las funciones de ventana SQL para agregar o comparar datos dentro de ventanas especificadas de filas. Estas funciones le permiten evitar autouniones o subconsultas, agregar datos sin colapsar los resultados en un único valor para toda una tabla y comparar valores de filas mediante cálculos como totales, porcentajes o incluso clasificaciones.
Las funciones de ventana SQL funcionan en tres sencillos pasos. En primer lugar, se define una ventana. Esto se debe a que las funciones ventana operan sobre un conjunto de filas definidas mediante la cláusula OVER(). A continuación, la cláusula PARTITION BY divide el conjunto de resultados en particiones a las que se aplica la función. Y por último, la cláusula ORDER by para determinar el orden de las filas en cada partición.
NOTA: La ventana en SQL no debe confundirse con la agregación . La agregación implica funciones tradicionales, por ejemplo, (SUM y AVG), que operarán sobre múltiples filas, colapsándolas en un único resultado. Para distinguirlas, recuerde que las funciones de Ventana trabajan sobre una ventana (conjunto especificado de filas), manteniendo las filas individuales en su salida.
Pongámonos manos a la obra para trabajar con las funciones de ventana de SQL. Para cada categoría, dispondrá de una tabla resumen de referencia rápida.
Sintaxis de las funciones de ventana SQL
La sintaxis general de las funciones de ventana consta de tres componentes: la propia función, la cláusula OVER y un PARTITION BY opcional. He aquí un desglose de cada uno.
#1. Función
En cuanto a la función, indica la operación de cálculo a la que se dirige la fila. Podría tratarse de funciones agregadas estándar (por ejemplo, SUM, AVG y COUNT). O una función analítica (por ejemplo, ROW_NUMBER, RANK, LEAD y LAG).
He aquí un ejemplo en el que se utiliza la función SUM.
SELECCIONE
columna1
columna2
columna3,
SUM(columna3) OVER () COMO peso_total
DE
su_tabla;
#2. Cláusula OVER
Con la cláusula OVER se definen las filas sobre las que operará la función. Existen dos constituyentes: PARTITION BY y ORDER BY. El primero es opcional y divide los resultados en secciones a las que se aplica la función de ventana.
Para el segundo, especifica el orden de las filas en cada partición. Cuando no se especifica, la función trata la ventana como un conjunto desordenado. He aquí un ejemplo.
SELECCIONE
columna1
columna2
columna3,
SUM(columna3) OVER (PARTITION BY columna1 ORDER BY columna2) AS running_total_weight
DE
su_tabla;
#3. Cláusula PARTITION BY
Esta cláusula, opcional como es, divide el resultado en particiones a las que se aplica la función ventana. Su superpotencia puede materializarse al realizar los cálculos de forma independiente con cada partición. He aquí una ilustración.
SELECCIONE
columna1
columna2
columna3,
AVG(columna3) OVER (PARTITION BY columna1) AS avg_weight_per_group
DE
su_tabla;
En el ejemplo anterior, la función AVG calcula la media de la columna3 de forma independiente para cada valor distinto de la columna1. En pocas palabras, para cada grupo de elementos de la columna1, tendrá la columna3 con el peso medio de ese elemento concreto.
La sintaxis general para una función SQL de ventana es
SELECT
columna1
columna2
columna3,
SUM(columna3) OVER (PARTITION BY columna1 ORDER BY columna2) AS window_function_result
DE
su_tabla;
No obstante, la sintaxis anterior puede variar ligeramente en función de la base de datos elegida, pero la estructura general sigue siendo coherente.
Funciones ventana agregadas en SQL
Las funciones de ventana de agregación en SQL reutilizan las funciones de agregación predominantes, como COUNT() o SUM(), alterando la forma en que se define la agregación y el formato de los resultados. Esto significa que realizan cálculos en una ventana relacionada con la fila actual dentro del conjunto de resultados. Puede utilizarlas para obtener valores agregados basados en una ventana/cuadro especificado. He aquí una breve descripción de cada uno.
#1. MIN()
Esta función devuelve el valor mínimo de una expresión especificada sobre un marco.
MIN(columna) SOBRE (PARTICIÓN POR expresión_partición ORDER POR expresión_ordenación
FILAS ENTRE inicio Y fin)
Veamos una consulta de ejemplo, en particular el mínimo móvil, deslizante o rodante. En este ejemplo, el valor mínimo se calcula para cada fila del conjunto de resultados para un rango específico de filas alrededor de esa fila.
SELECCIONE
columna1
columna2,
MIN(columna2) OVER (ORDER BY date_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_min
FROM
su_tabla;
Así, columna1 y columna2 son las columnas que está seleccionando, MIN(columna2) es la función agregada que calcula el valor mínimo y OVER(ORDER BY fecha_columna FILAS ENTRE 2 FILAS PRECEDENTES Y FILA ACTUAL) define la ventana para el cálculo. Especifica la ventana, que incluye la fila actual y las dos anteriores, basándose en el especificador de orden alguna_columna. Esto, a su vez, dicta que cada fila del conjunto de resultados moving_min contenga el valor mínimo de la columna2. Observe que no he incluido la cláusula PARTITION BY.
#2. MAX()
Nuestra función aquí devuelve el valor máximo de una expresión especificada sobre una ventana. Esta es la sintaxis
MAX(columna) OVER (PARTITION BY expresión_partición ORDER BY expresión_ordenación
FILAS ENTRE inicio Y fin)He aquí una consulta de ejemplo que calcula el máximo actual de una columna sobre todo el conjunto de resultados, ordenado por la columna_fecha.
SELECCIONE
columna
MAX(columna) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS running_max
DE
su_tabla;#3. AVG()
Esta función devuelve el valor medio de una expresión especificada sobre una trama. Compruebe la sintaxis.
AVG(columna) SOBRE (PARTICIÓN POR expresión_partición ORDER POR expresión_ordenación
FILAS ENTRE inicio Y fin)Para ver un ejemplo de consulta, considere el siguiente ejemplo en el que la consulta calcula la media sobre una ventana que incluye la fila actual, la que la precede y la que la sigue ordenadas por la columna_fecha.
SELECCIONE
columna
AVG(columna) OVER (ORDER BY date_column FILAS ENTRE 1 PRECEDENTE Y 1 SIGUIENTE) AS moving_avg
DE
su_tabla;
#4. SUMA()
Se utiliza cuando se desea devolver la suma de una expresión especificada sobre un marco. A continuación se muestra la sintaxis.
SUM(columna) OVER (PARTITION BY expresión_partición ORDER BY expresión_ordenación
FILAS ENTRE inicio Y fin)Para una consulta de ejemplo, considere la suma acumulativa que se muestra a continuación. En este caso, la consulta calcula la suma de una columna sobre todo el conjunto de resultados, ordenados por la columna_fecha.
SELECCIONE
columna
SUM(columna) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS cumulative_sum
DE
su_tabla;#5. CONTAR()
Función utilizada para devolver el número de filas de una ventana. Su sintaxis puede escribirse como
COUNT(columna) OVER (PARTITION BY partition_expression ORDER BY sort_expression
FILAS ENTRE inicio Y fin)Para ver un ejemplo de uso, considere el caso siguiente, en el que la consulta calcula el recuento de filas sobre todo el conjunto de resultados, ordenado por la columna_fecha.
SELECCIONE
columna
COUNT(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS running_count
DE
su_tablaHe aquí una rápida hoja de trucos para las funciones de ventana agregadas.
| Función | Propósito | Ejemplo de uso |
|---|---|---|
| MIN() | Devuelve el valor mínimo de una expresión especificada sobre un marco. | MIN(columna) OVER (ORDER BY fecha_columna FILAS ENTRE 2 FILAS PRECEDENTES Y FILA ACTUAL) – Calcula el mínimo móvil de cada fila. |
| MÁX() | Devuelve el valor máximo de una expresión especificada sobre una ventana. | MAX(columna) OVER (ORDER BY fecha_columna FILAS PRECEDENTE NO LIMITADA) – Calcula el máximo móvil sobre todo el conjunto de resultados |
| AVG() | AVG() Devuelve el valor medio de una expresión especificada sobre un marco. | AVG(columna) OVER (ORDER BY fecha_columna FILAS ENTRE 1 PRECEDENTE Y 1 SIGUIENTE) – Calcula la media móvil sobre una ventana. |
| SUMA() | Devuelve la suma de una expresión especificada sobre un marco. | SUM(columna) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) – Calcula la suma acumulada sobre todo el conjunto de resultados. |
| CONTAR() | Devuelve el número de filas de una ventana o el recuento total de filas. | COUNT(column) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) – Calcula el recuento acumulado de filas sobre todo el conjunto de resultados. |
Funciones de ventana de valor en SQL
En SQL, las funciones de ventana de valor se utilizan cuando se asignan valores de filas a partir de otras filas. A diferencia de las funciones de agregación – que devuelven un único valor para cada grupo – devolverán un valor para cada fila según una ventana o marco de filas específico.
Este modelo de funcionamiento, a su vez, le permite acceder a los datos de otras filas en relación con la ventana actual, lo que facilita potentes capacidades analíticas y de elaboración de informes.
He aquí un breve resumen de cada una de las funciones de ventana de valor.
#1. LEAD()
La función de ventana SQL LEAD () funciona de forma opuesta a LAG() y se utiliza para devolver valores de filas sucesivas. Simplificando, desplaza los valores una fila hacia arriba. La sintaxis para llamarla es similar a LAG(). He aquí cómo escribirla.
LEAD(columna, desplazamiento, valor_por_defecto) OVER (PARTITION BY expresión_partición ORDER BY expresión_ordenación)Para su consulta de ejemplo, vea el caso siguiente, que recupera el siguiente valor de una columna basándose en el criterio de ordenación, `columna_fecha`, con un valor 0 por defecto si no hay siguiente valor.
SELECCIONE
columna
LEAD(columna, 1, 0) OVER (ORDER BY fecha_columna) AS lead_value
DE
su_tabla;#2. LAG()
La función LAG () es la más popular. Asigna a cada fila un valor de la anterior. En otras palabras, desplaza cualquier columna una fila, lo que le permite realizar consultas utilizando valores desplazados. En estos casos, la función ventana incluirá el ORDER BY en la cláusula OVER, ya que es necesario tener en cuenta el aspecto del orden. La sintaxis es la siguiente
LAG(columna, desplazamiento, valor_por_defecto) OVER (PARTITION BY expresión_partición ORDER BY expresión_ordenación)Un ejemplo de consulta puede escribirse como se muestra a continuación. La consulta extrae el valor anterior de una columna ordenada por la columna_fecha. Cuando no hay valor anterior, se produce un valor por defecto de 0.
SELECCIONE
columna,
LAG(columna, 1, 0) OVER (ORDER BY columna_fecha) AS lag_value
DE
su_tabla;
#3. FIRST_VALUE()
Puede utilizar esta función para recuperar el valor de una expresión especificada para la primera fila dentro de una ventana. La sintaxis:
FIRST_VALUE(columna) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Ejemplo:
SELECCIONE
columna
FIRST_VALUE(columna) OVER (PARTITION BY columna_categoria ORDER BY columna_fecha) AS first_value
DE
su_tabla;
La consulta recupera el último valor de una columna dentro de cada partición, ordenado por columna_fecha.
#4. ÚLTIMO_VALOR()
Con esta función, podrá recuperar el último valor de un especificado dentro de un marco definido. La sintaxis:
LAST_VALUE(columna) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Ejemplo:
SELECT
columna
LAST_VALUE(column) OVER (PARTITION BY category_column ORDER BY date_column) AS last_value
DE
su_tabla;
A estas alturas, debería ser un poco obvio…
#5. N_TH VALOR()
Como último miembro del grupo, esta función puede recuperar el valor en una posición especificada dentro de un marco de ventana. También especificará la expresión que desea recuperar el enésimo valor. La expresión puede ser una columna, una expresión matemática o cualquier expresión SQL válida que represente los datos que le interesan. Para nuestros ejemplos, hemos utilizado una columna. Ha sido la misma para LAST_VALUE() y FIRST_VALUE().
La sintaxis:
NTH_VALUE(columna, n) OVER (PARTITION BY partition_expression ORDER BY sort_expression)Ejemplo:
SELECCIONE
columna
NTH_VALUE(columna, 3) OVER (PARTITION BY columna_categoria ORDER BY columna_fecha) AS third_value
DE
su_tabla;
| Función | Finalidad | Ejemplo de uso |
|---|---|---|
| FILAS() | Devuelve los valores de las filas siguientes dentro de una ventana. | LEAD(columna, 1, 0) OVER (ORDER BY fecha_columna) AS lead_value – Recupera el siguiente valor basándose en el criterio de ordenación, con un valor por defecto si no hay siguiente valor. |
| LAG() | Devuelve los valores de las filas precedentes dentro de una ventana. | LEAD(columna, 1, 0) OVER (ORDER BY fecha_columna) AS lead_value – Recupera el siguiente valor basándose en el criterio de ordenación, con un valor por defecto si no hay siguiente valor. |
| PRIMER_VALOR() | Recupera el valor de una expresión especificada para la primera fila dentro de una ventana. | FIRST_VALUE(columna) OVER (PARTITION BY columna_categoria ORDER BY columna_fecha) AS first_value – Obtiene el primer valor dentro de cada categoría basándose en el orden de fechas. |
| ÚLTIMO_VALOR() | Recupera el último valor de una expresión especificada dentro de un marco definido. | LAST_VALUE(columna) OVER (PARTITION BY columna_categoria ORDER BY columna_fecha) AS last_value – Obtiene el último valor dentro de cada categoría basándose en el orden de fechas. |
| NTH_VALOR() | Recupera el valor en una posición especificada dentro de un marco de ventana. | NTH_VALUE(columna, 3) OVER (PARTITION BY columna_categoria ORDER BY columna_fecha) AS third_value – Obtiene el tercer valor dentro de cada categoría basándose en la ordenación por fechas. |
Función de ventana de clasificación en SQL
Las funciones de ventana de clasificación en SQL son útiles cuando se asignan números (rangos o posiciones) a filas dentro de un conjunto de resultados basándose en un criterio de ordenación específico. Son útiles cuando se analizan y ordenan datos y se identifican las posiciones relativas de las filas.
Si no dispusiera de funciones de ventana, tendría que escribir varias consultas anidadas, lo que resulta ineficaz. Nota clave en las funciones ventana de ordenación: la cláusula ORDER BY debe estar siempre presente. A continuación encontrará breves explicaciones sobre las funciones de clasificación de la familia.
#1. RANK()
La función RANK () es la más común y asigna valores de clasificación basados en el orden especificado. Su sintaxis..
RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expressionPara ponerlo en perspectiva, observe la consulta siguiente. Asigna un rango a cada empleado en función del rendimiento de las ventas; las ventas más altas obtienen el rango más bajo.
SELECCIONE
empleado_id,
ventas,
RANK() OVER (ORDER BY sales DESC) AS sales_rank
DE
tabla_ventas;#2. DENSE RANK()
Aunque funciona de forma similar a `RANK()`, tiene una característica distintiva crítica: si hay empates en el conjunto de datos, obtienen el mismo valor de clasificación. No se salta ningún número, asignando los valores sucesivos a la fila consecuente. Escríbalo como..
DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)Para su caso de prueba, considere la consulta siguiente. Asigna un rango denso a cada alumno en función de la puntuación_prueba, sin dejar huecos incluso en presencia de puntuaciones de empate.
SELECCIONE
student_id,
test_score,
DENSE_RANK() OVER (ORDER BY test_score DESC) AS score_rank
DE
tabla_puntuaciones#3. NÚMEROS DE FILA()
Es la más sencilla de todas. Siguiendo el orden definido por la cláusula ORDER BY en la subsección OVER da números a todas las filas, empezando por 1. La sintaxis es la siguiente..
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY sort_expression)En la consulta de ejemplo siguiente, asignamos números de fila únicos a cada artículo del producto en función de las ventas, empezando por 1 a partir de las ventas más altas.
SELECCIONE
producto_id,
category,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num
DE
tabla_productos;#4. RANGO_PORCENTA()
Esta función utiliza la función RANK para definir la clasificación final. Su objetivo es esbozar una clasificación relativa para las filas del conjunto de resultados, expresándola como un porcentaje, de forma que el resultado esté comprendido entre 0 y 1. El valor 0 se asigna a la primera fila y el 1 a la última. La sintaxis es..
PERCENT_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)He aquí una consulta en la que asignamos un rango a cada empleado en función de sus resultados de ventas.
SELECCIONE
id_cliente,
ingresos,
PERCENT_RANK() OVER (ORDER BY revenue DESC) AS revenue_percent_rank
FROM
tabla_ingresos;#5. QCUT (NÚMERO)
Aunque se utiliza raramente, funciona como ROW_NUMBER. Sin embargo, da números a una colección de filas (buckets) en lugar de numerar filas. El número de buckets se pasa como argumento a la función N-TILE. Por ejemplo, N-TILE(10) divide el conjunto de datos en 10 buckets. La sintaxis es..
NTILE(número_de_cubos) OVER (PARTICIÓN POR expresión_partición ORDER POR expresión_ordenación)El ejemplo siguiente divide a los empleados en cuartiles, tal como se describe en la función.
SELECCIONE
empleado_id,
salario,
NTILE(4) OVER (ORDER BY salario) AS salario_cuartil
DE
tabla_salario_empleado;#6. CUME_DIST
Como último miembro, calcula la distribución acumulativa de un valor dentro de un conjunto de datos ordenados. Asigna un valor entre 0 y 1 para representar las posiciones relativas de las filas. 0 al principio y 1 al final. Sintaxis:
CUME_DIST() OVER (PARTITION BY partition_expression ORDER BY sort_expression)En el ejemplo siguiente, la consulta evalúa la distribución de los clientes en función de la edad, con un porcentaje de clientes – menor o igual que la fila actual.
SELECCIONE
customer_id,
edad,
CUME_DIST() OVER (ORDER BY age) AS age_cume_dist
DE
datos_cliente;A continuación encontrará una hoja de referencia rápida para las funciones de la ventana de clasificación.
| Función | Propósito | Ejemplos de uso |
|---|---|---|
| RANKING() | Asigna valores de ranking basados en el orden especificado. | RANK() OVER (ORDER BY sales DESC) AS sales_rank – Asigna un rango a cada empleado en función de su rendimiento en ventas. |
| DENSE_RANK() | Similar a RANK(), pero maneja los empates sin espacios. | DENSE_RANK() OVER (ORDER BY test_score DESC) AS score_rank – Asigna un rango denso a los estudiantes basándose en las puntuaciones de los exámenes. |
| NÚMERO_FILA() | Asigna números de fila únicos siguiendo la cláusula ORDER BY. | ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num – Asigna números de fila a los productos basándose en las ventas dentro de cada categoría. |
| PERCENT_RANK() | Define la clasificación relativa como un porcentaje (0 a 1). | PERCENT_RANK() OVER (ORDER BY revenue DESC) AS revenue_percent_rank – Asigna un rango porcentual a los clientes en función de los ingresos. |
| NTILE(número_de_cubos) | Divide las filas en los buckets especificados | NTILE(4) OVER (ORDER BY salary) AS salary_quartile – Divide a los empleados en cuartiles en función del salario. |
| CUME_DIST() | Calcula la distribución acumulativa (0 a 1) de los valores. | CUME_DIST() OVER (ORDER BY age) AS age_cume_dist – Evalúa la distribución acumulativa de clientes basada en la edad, representada como porcentaje. |
Conclusion
En este artículo, hemos introducido las funciones de ventana SQL, una técnica excelente para recorrer sus consultas SQL, simplificando sus operaciones con datos. Hemos examinado los modelos disponibles para las funciones Ventana, mostrado su sintaxis, previsualizado sus ejemplos, explicado su uso y concluido con una hoja de trucos de ejemplo que puede adoptar en su flujo de trabajo de datos.
Por supuesto, la sintaxis puede variar en función de su selección de base de datos. Asegúrese de buscar la sintaxis correcta para su elección. Para profundizar en el tema, consulte siempre la documentación. Esto significa trabajar con la correcta, ya sea en SQL o en PostgreSQL.
Ahora puede consultar nuestra completa hoja de trucos de SQL que mi colega y yo utilizamos en nuestro quehacer diario como desarrolladores.