Cursos Empresas Blog Live Conf Eventos Precios

0:0013:14

Window functions

Clase 27 de 29

Clase 27 / 29

Juan José Mamani Tarqui

WINDOWS FUNCTIONS: Las funciones de ventana (window functions) son un grupo de funciones en SQL que permiten realizar cálculos en conjuntos de datos ordenados. Las funciones de ventana operan en un conjunto de filas denominadas ventana, que se especifica mediante una cláusula OVER.

Las funciones de ventana se utilizan para realizar una variedad de cálculos, incluyendo:

Cálculos de agregación: SUM(), COUNT(), AVG(), MIN(), MAX()
Cálculos de ranking: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
Cálculos de desplazamiento: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Las funciones de ventana se utilizan comúnmente en análisis de datos para calcular métricas como:

Media móvil: La media móvil de un conjunto de datos es el promedio de los valores de datos en una ventana.
Tasa de crecimiento: La tasa de crecimiento de un conjunto de datos es el porcentaje de cambio en el valor de los datos entre dos puntos en el tiempo.
N principales: Las N principales filas de un conjunto de datos son las filas con los valores más altos o más bajos.
Percentiles: Los percentiles de un conjunto de datos son los valores que dividen el conjunto de datos en partes iguales.

Ejemplo:

El siguiente ejemplo muestra cómo utilizar la función de ventana AVG() para calcular la media móvil de las ventas de los últimos 3 meses:

SQLSELECT customer_id, AVG(sales) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_sales FROM orders;

Esta consulta devuelve una columna adicional llamada moving_average_sales, que contiene la media móvil de las ventas de los últimos 3 meses para cada cliente.

Las funciones de ventana son una herramienta poderosa para realizar análisis de datos en SQL. Pueden utilizarse para realizar una variedad de cálculos, incluyendo cálculos de agregación, ranking y desplazamiento.

Comentarios (46)46 comentarios

César Leonardo Garzón Castiblanco

Hace 6 años

Window Functions

¿Qué son?

Realizan cálculos en algunas tuplas que se encuentran relacionadas con la tupla actual.

¿Para que sirven?

Evitan el uso de self joins y reduce la complejidad alrededor de la analítica, agregaciones y uso de cursores.

Patricio Sánchez Fernández

Buen resumen, César.

Andres Felipe Bolaños Fernandez

Hace 2 años

excelente, gracias.

Roberto Jassiel Montes Gutierrez

![](

Sebastian Calderón Araque

Hace 5 años

Una imagen vale más que mil palabras.

Juan Camilo Alvarez Jurado

Hace 4 años

Parece que faltó agregar el recurso; lo comparto por aquí, ya que es bastante claro y explica muy bien el tema: https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

Aaron Quiroga

++Window Functions++ Realizan cálculos que relacionan una tupla con el resto dentro de un mismo scope o partición.

Evita el uso del self joins
Reduce la complejidad alrededor de la analítica, agregaciones y cálculos
Luego de una agregación, la función OVER dicta el scope de la window function, al realizar un PARTITION BY campo
Si no se especifica un PARTITION BY, la funcion OVER() por default tomará toda la tabla
También se puede usar ORDER BY campo, esto agrega un campo de granularidad al cálculo, a la vez que agrupa todos los valores iguales dentro de la partición, que ahora se encuentran ordenados

SELECT *
SUM(colegiatura) OVER (PARTITION BY carrera_id ORDER BY colegiatura)
FROM platzi.alumnos;

Podemos usar funciones de RANK()
Las Window Function se procesan casi al final de todas las operaciones, por eso para usar estas WF como un campo en WHERE, debemos hacer un subquery

SELECT *
FROM (
SELECT *,
RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank
FROM platzi.alumnos
) AS ranked_colegiaturas_por_carrera
WHERE brand_rank < 3
ORDER BY brand_rank;

Julián Cárdenas

Excelente!

Andrés David Lizarazo Becerra

Una función de ventana es una variación de una función de agregación. Cuando una función de agregación, como sum()y mean(), toma n entradas y devuelve un solo valor, una función de ventana devuelve n valores. La salida de una función de ventana depende de todos sus valores de entrada, por lo que las funciones de ventana no incluyen funciones que funcionan por elementos, como round(). Las funciones de ventana incluyen variaciones en funciones agregadas, como cumsum()y cummean(), funciones para clasificar y ordenar, como rank(), y funciones para tomar compensaciones, como lead()y lag().

Esmeralda Palacios

muy clara tu explicación :)

Anthony Guzman Lopez

El poder de Window functions es increible, yo usualmente trabajo mucho con SQL Server y esta funcion la usaba solamente para numerar grupos, pero ya veo que hay mas posibilidades.

Ingrid Tatiana Barrios Villamizar

Dejo por acá este articulo de window functions, para profundizar un poco más, está bueno. https://mode.com/sql-tutorial/sql-window-functions/

Marlon Diego Rodríguez Castro

Excelente aporte, gracias mil.

Jhins Ledys Cárdenas Pardo

Excelente aporte. Muchas gracias.

Aaron Fabrizio Calderon Guillermo

Ronny Jair Mora Rojas

Definitivamente considero que esta clase debe estar dentro de las primeras de este curso ¿Alguien opina lo mismo?

Jeovany Esteban Hernández Cervantes

Like si vives en el mundo de Oracle y tienes que buscar alternativas a esto :(

Gabriela Navas

¿Qué tan distinto es? Actualmente trabajo con Salesforce y me es util el SQL, pero pronto vamos a migrar a Oracle.

Luis Loaeza

Pérame tantito, no que por la manera de los ranks no había rank número dos? por qué al final sí hubo rank dos?

Alejandro Rico García

buena observación!

Gerardo Mayel Fernández Alamilla

Creo que el error no está en el código aunque si en la explicación, el dice que no habría dos pero en realidad no debería existir el 3, está bien que uno y dos existan ya que la condición es <3

Sebastian Rodriguez

++WINDOW FUNCTION:++

Es la relacion que existe entre una Tupla o Row en particular y el resto de Rows en una particion o en un window frame (Es una seccion de la tabla que me interesa medir y medir cual es la relacion que existe entre uno de los rows y todo el resto de los rows que se encuentran en la particion)
Cuando No le determino una particion, toma Como Window Frame o particion toda la Tabla.
Ranking: Que lugar ocupa algo en nuestra tabla.
Las Window Function corren al final casi de todo. Corren despues de Where, having, menos por Order By. Cuando hago el Where todavia No existe ese campo y arroja error.
Para solucionar lo anterior debemos usar Sub-Queries.

Cesar Augusto Morales Godoy

Las 3 colegiaturas más caras con Join y Window Functions

Muestra el top 3 de colegiaturas más caras con el nombre de la carrera traído desde la tabla platzi.carreras:

SELECT *
FROM (
	SELECT DISTINCT c.carrera, a.colegiatura,
		RANK() OVER(PARTITION BY c.carrera ORDER BY colegiatura DESC) AS ranking
	FROM platzi.alumnos AS a
		INNER JOIN platzi.carreras AS c
		ON c.id = a.carrera_id
	GROUP BY c.carrera, a.colegiatura
	ORDER BY ranking) AS ranking_calegiatura_table	
WHERE ranking <= 3;

Mariano Gobea Alcoba

Misma query pero sin window function es mucho mas compleja:

-- Para hacer esto sin WF primero debo agrupar por carrera_id y calcular el promedio y luego hacer un join

SELECT *
FROM platzi.alumnos AS alum
	INNER JOIN (
				SELECT carrera_id, AVG(colegiatura)
				FROM platzi.alumnos
				GROUP BY carrera_id
				ORDER BY carrera_id ASC) AS prom
	ON alum.carrera_id = prom.carrera_id
ORDER BY alum.carrera_id ASC;

Anthony Campos

muy buen código. Pude repasar mis conceptos de clases pasadas, entendiendolo.

Danilo Valenzuela

Genial, con las window functions puedo resolver un problema que tenía en la cabeza hace días.

Wilner Alexander Ochoa Vivas

Debo admitir que esta clase me gusto mucho y me aclaro muchas de las dudas en cuanto a la estructura de los querys

Danny Cortez

Un dato importante al momento de usar Windows Function y se usa la clausula ORDER BY en la misma, es que el order by de la consulta general tiene que ser el mismo que se usa en la windows function, sino se pueden obtener resultados innesperados. Saludos

LIZBET ESTEFANY CASANA VERASTEGUI

Porque no se necesita un group by?

SELECT *, AVG(colegiatura) OVER (PARTITION BY carrera_id) FROM platzi.alumnos;

SELECT *, AVG(colegiatura) OVER () FROM platzi.alumnos;

Pat Villarroel

¿Para qué lo necesitarías? AVG agrupa todos los valores en un único valor, el promedio.

Miguel Angel Cáceres Valdivia

El usar GROUP BY en el query lo que hace es mostrar filas únicas con valores promedios AVG() o sumas SUM(). En cambio al usar OVER() después del Window Functions, lo que hace es realizar el calculo según el quiebre o condición que le indicas en el PARTITION BY pero te muestra todas las filas de la tabla.

Alexander Sebastian Jaramillo Ordiales

Las funciones de ventana son similares a la agregación realizada en la cláusula GROUP BY. Sin embargo, las filas no se agrupan en una sola fila, cada fila conserva su identidad separada. Es decir, una función de ventana puede devolver un solo valor para cada fila. Aquí hay una buena visualización de lo que quiero decir con eso.

!https://miro.medium.com/v2/resize:fit:721/1*bWw3tHCsXRHyOKOkm81eUg.png

Observe cómo la agregación GROUP BY en el lado izquierdo de la imagen agrupa las tres filas en una sola fila. La función de ventana en el lado derecho de la imagen puede generar cada fila con un valor de agregación. Esto puede ahorrarle tener que hacer una unión después de GROUP BY.

Cristian Velosa

La principal ventaja de usar las funciones de Windows sobre las funciones agregadas regulares es: Las funciones de Windows no hacen que las filas se agrupen en una sola fila de salida, las filas retienen sus identidades separadas y solo se añadirá un valor agregado a cada fila.

Window functions

Progreso del curso

Introducción a SQL

Ejercitando tu SQL

Conceptos de SQL Avanzado