En mi trabajo, he escrito innumerables consultas SQL para extraer información de los datos. Siempre es una tarea desafiante porque no solo es importante escribir consultas eficientes, sino también lo suficientemente simple como para mantener con el tiempo.
Con cada nuevo problema viene una nueva lección, y recientemente, me he estado sumergiendo en las funciones de la ventana SQL. Estas herramientas poderosas son increíblemente útiles cuando necesitas realizar cálculos en un conjunto de filas. sin perder la granularidad de los registros individuales.
En este artículo, desglosaré las funciones de la ventana SQL paso a paso. Al principio pueden parecer complejos o no intuitivos, pero una vez que comprenda cómo funcionan, verá cuán indispensables pueden ser. ¿Estás listo? ¡Vamos a sumergirlos y dominarlos juntos!
Tabla de contenido
- ¿Por qué necesitamos funciones de ventana?
- Sintaxis de la función de la ventana
- Cuatro ejemplos simples
¿Por qué necesitamos funciones de ventana?
Para comprender el poder de las funciones de la ventana, comencemos con un ejemplo simple. Imagine que tenemos una tabla que contiene seis pedidos de un sitio web de comercio electrónico. Cada fila incluye la ID de pedido, la fecha, el producto, su marca y precio.
Supongamos que queremos calcular el precio total para cada marca. Usando el Agrupar Cláusula, podemos escribir una consulta como esta:
SELECT
brand,
SUM(price) as total_price
FROM Orders
GROUP BY brandEsto devuelve un resultado en el que cada fila representa una marca, junto con el precio total de todos los pedidos bajo esa marca.
|brand |total_price|
|-------|-----------|
|carpisa|30 |
|nike |175 |
|parfois|25 |
|zara |65 |Esta agregación elimina los detalles de los pedidos individuales, ya que la salida solo incluye una fila para la marca. ¿Qué pasa si queremos mantener todas las filas originales y agregar el precio total para cada marca como un campo adicional?
Utilizando SUM(price) OVER (PARTITION BY brand)podemos calcular el precio total de cada marca sin colapsar las filas:
SELECT
order_id,
date,
product,
brand,
price,
SUM(price) OVER (PARTITION BY brand) as total_price
FROM OrdersHemos obtenido un resultado como este:
|order_id|date |product|brand |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|6 |2025/05/01|bag |carpisa|30 |30 |
|1 |2024/02/01|shoes |nike |90 |175 |
|3 |2024/06/01|shoes |nike |85 |175 |
|5 |2025/04/01|bag |parfois|25 |25 |
|2 |2024/05/01|dress |zara |50 |65 |
|4 |2025/01/01|t-shirt|zara |15 |65 |Esta consulta devuelve las seis filas, preservando cada pedido individual, y agrega una nueva columna que muestra el precio total por marca. Por ejemplo, el pedido con la marca de la marca muestra un total de 30, ya que es el único pedido de carpisa, los dos pedidos de Nike Show 175 (90+85), y así sucesivamente.
Puede notar que la tabla ya no está ordenada por orden_id. Esto se debe a que la función de la ventana se divide por marca, y SQL no garantiza el orden de fila a menos que se especifique explícitamente. Para restaurar el orden original, simplemente necesitamos agregar un ORDER BY cláusula:
SELECT
order_id,
date,
product,
brand,
price,
SUM(price) OVER (PARTITION BY brand) as total_price
FROM Orders
ORDER BY order_idFinalmente, tenemos la salida que contiene todos los detalles requeridos:
|order_id|date |product|brand |price|total_price|
|--------|----------|-------|-------|-----|-----------|
|1 |2024/02/01|shoes |nike |90 |175 |
|2 |2024/05/01|dress |zara |50 |65 |
|3 |2024/06/01|shoes |nike |85 |175 |
|4 |2025/01/01|t-shirt|zara |15 |65 |
|5 |2025/04/01|bag |parfois|25 |25 |
|6 |2025/05/01|bag |carpisa|30 |30 |Ahora, hemos agregado la misma agregación que GROUP BYmientras mantiene todos los detalles del orden individual.
Sintaxis de las funciones de la ventana
En general, la función de la ventana tiene una sintaxis que se ve así:
f(col2) OVER(
[PARTITION BY col1]
[ORDER BY col3]
)Vamos a desglosarlo. f(col2) es la operación que desea realizar, como suma, recuento y clasificación. OVER La cláusula define la “ventana” o el subconjunto de filas sobre las cuales funciona la función de la ventana. PARTITION BY col1 divide los datos en grupos y ORDER BY col1 determina el orden de las filas dentro de cada partición.
Además, las funciones de la ventana se dividen en tres categorías principales:
- función agregada:
COUNT,SUM,AVG,MINyMAX - Función de rango:
ROW_NUMBER,RANK,DENSE_RANK,CUME_DIST,PERCENT_RANKyNTILE - función de valor:
LEAD,LAG,FIRST_VALUEyLAST_VALUE
Cuatro ejemplos simples
Mostremos diferentes ejemplos a las funciones de la ventana maestra.
Ejemplo 1: función de ventana simple
Para comprender el concepto de funciones de la ventana, comencemos con un ejemplo directo. Supongamos que queremos calcular el precio total de todos los pedidos de la tabla. Usando un GROUP BY La cláusula nos daría un valor único: 295. Sin embargo, eso colapsaría las filas y perdería los detalles del orden individual. En cambio, si queremos mostrar el precio total junto con cada registro, podemos usar una función de ventana como esta:
SELECT
order_id,
date,
product,
brand,
price,
SUM(price) OVER () as tot_price
FROM OrdersEsta es la salida:
|order_id|date |product|brand |price|tot_price|
|--------|----------|-------|-------|-----|---------|
|1 |2024-02-01|shoes |nike |90 |295 |
|2 |2024-05-01|dress |zara |50 |295 |
|3 |2024-06-01|shoes |nike |85 |295 |
|4 |2025-01-01|t-shirt|zara |15 |295 |
|5 |2025-04-01|bag |parfois|25 |295 |
|6 |2025-05-01|bag |carpisa|30 |295 |De esta manera, obtuvimos la suma de todos los precios en todo el conjunto de datos y lo repitimos para cada fila.
Ejemplo 2: Partición por cláusula
Ahora calculemos el precio promedio por año mientras mantenemos todos los detalles. Podemos hacer esto usando el PARTITION BY cláusula dentro de una función de ventana para agrupar filas por año y calcular el promedio dentro de cada grupo:
SELECT
order_id,
date,
product,
brand,
price,
round(AVG(price) OVER (PARTITION BY YEAR(date) as avg_price
FROM OrdersAsí es como se ve la salida:
|order_id|date |product|brand |price|avg_price|
|--------|----------|-------|-------|-----|---------|
|1 |2024-02-01|shoes |nike |90 |75 |
|2 |2024-05-01|dress |zara |50 |75 |
|3 |2024-06-01|shoes |nike |85 |75 |
|4 |2025-01-01|t-shirt|zara |15 |23.33 |
|5 |2025-04-01|bag |parfois|25 |23.33 |
|6 |2025-05-01|bag |carpisa|30 |23.33 |¡Genial! Vemos el precio promedio de cada año junto con cada fila.
Ejemplo 3: Orden por cláusula
Una de las mejores maneras de comprender cómo funciona el pedido dentro de las funciones de la ventana es aplicar un categoría función. Digamos que queremos clasificar todas las órdenes de El precio más alto al más bajo. Así es como podemos hacerlo usando el RANK() función:
SELECT
order_id,
date,
product,
brand,
price,
RANK() OVER (ORDER BY price DESC) as Rank
FROM OrdersObtenemos una salida como esta:
|order_id|date |product|brand |price|Rank|
|--------|----------|-------|-------|-----|----|
|1 |2024-02-01|shoes |nike |90 |1 |
|3 |2024-06-01|shoes |nike |85 |2 |
|2 |2024-05-01|dress |zara |50 |3 |
|6 |2025-05-01|bag |carpisa|30 |4 |
|5 |2025-04-01|bag |parfois|25 |5 |
|4 |2025-01-01|t-shirt|zara |15 |6 |Como se muestra, el orden con el precio más alto obtiene el rango 1, y el resto sigue en orden descendente.
Ejemplo 4: Combine la partición y agrupar por las cláusulas
En el ejemplo anterior, clasificamos todos los pedidos del precio más alto a más bajo en todo el conjunto de datos. Pero, ¿qué pasa si queremos reiniciar la clasificación para cada año? Podemos hacer esto agregando el PARTITION BY cláusula en la función de la ventana. Esto permite dividir los datos en grupos separados por año y clasificar los pedidos del precio más alto al más bajo.
SELECT
order_id,
date,
product,
brand,
price,
RANK() OVER (PARTITION BY YEAR(date) ORDER BY price DESC) as Rank
FROM OrdersEl resultado debería verse así:
|order_id|date |product|brand |price|Rank|
|--------|----------|-------|-------|-----|----|
|1 |2024-02-01|shoes |nike |90 |1 |
|3 |2024-06-01|shoes |nike |85 |2 |
|2 |2024-05-01|dress |zara |50 |3 |
|6 |2025-05-01|bag |carpisa|30 |1 |
|5 |2025-04-01|bag |parfois|25 |2 |
|4 |2025-01-01|t-shirt|zara |15 |3 |Ahora, la clasificación se reinicia para cada año, como decidimos.
Pensamientos finales:
Espero que esta guía lo ayude a obtener una introducción clara y práctica a las funciones de la ventana SQL. Al principio, pueden sentirse un poco poco intuitivos, pero una vez que los compares junto con el GROUP BY Cláusula, el valor que aportan se vuelve mucho más fácil de entender.
Desde mi propia experiencia, las funciones de la ventana han sido increíblemente poderosas para extraer ideas sin perder detalles a nivel de fila, algo que se esconden las agregaciones tradicionales. Son increíblemente útiles al extraer métricas como totales, clasificaciones, comparaciones año tras año o mes a mes.
Sin embargo, hay algunas limitaciones. Las funciones de la ventana pueden ser computacionalmente costosas, especialmente en grandes conjuntos de datos o particiones complejas. Es importante evaluar si la flexibilidad adicional justifica la compensación de rendimiento en su caso de uso específico.
¡Gracias por leer! ¡Que tenga un lindo día!
Recursos útiles: