Window functions

Clase 27 de 29 • Curso Práctico de SQL

Clase anteriorSiguiente clase

César Leonardo Garzón Castiblanco

student•

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

student•

Buen resumen, César.

Andres Felipe Bolaños Fernandez

student•

excelente, gracias.

Roberto Jassiel Montes Gutierrez

student•

![](

Sebastian Calderón Araque

student•

Una imagen vale más que mil palabras.

Juan Camilo Alvarez Jurado

student•

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

student•

++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

student•

Excelente!

Andrés David Lizarazo Becerra

student•

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

student•

muy clara tu explicación :)

Anthony Guzman Lopez

student•

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

student•

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

student•

Excelente aporte, gracias mil.

Jhins Ledys Cárdenas Pardo

student•

Excelente aporte. Muchas gracias.

Aaron Fabrizio Calderon Guillermo

student•

Para SQL Server:




-- PROMEDIO DE CALEGIATURA POR CARRERA_ID
-- Y MOSTRARLO POR CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT carrera_id, AVG(colegiatura) AS promedio
FROM platzi.alumnos
GROUP BY carrera_id
GO


-- PROMEDIO DE COLEGIATURA POR CARRERA_ID
-- Y MOSTRARLO EN CADA REGISTRO
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT *, AVG(colegiatura) OVER (PARTITION BY carrera_id) AS promedio
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- ORDEN ASCEDENTE POR COLEGIATURA
USE [platzi]
SELECT *, SUM(colegiatura) OVER (PARTITION BY colegiatura) AS suma
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCEDENTE POR COLEGIATURA
USE [platzi]
SELECT *, SUM(colegiatura) OVER (ORDER BY colegiatura ASC) AS suma
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR CARRERA_ID
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCEDENTE POR CARRERA_ID
USE [platzi]
SELECT *, SUM(colegiatura) OVER (ORDER BY carrera_id ASC) AS suma
FROM platzi.alumnos


-- SUMA DE COLEGIATURA POR CARRERA_ID Y COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCENDENTE POR CARRERA_ID
-- Y ORDEN ASCENDENTE POR COLEGIATURA EN EL GRUPO CARRERA_ID
USE [platzi]
SELECT *, SUM(colegiatura) OVER (PARTITION BY carrera_id ORDER BY colegiatura ASC) AS suma
FROM platzi.alumnos


-- MUESTRA EL ORDEN POR COLEGIATURA
-- DENTRO DE CADA CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
FROM platzi.alumnos
GO



-- LO ANTERIOR
-- DONDE EL RANKING < 3
USE [platzi]
SELECT *
FROM
(
	SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
	FROM platzi.alumnos
) AS ranked_colegiaturas_por_carrera
WHERE ranking < 3
GO


-- MUESTRA EL ORDEN POR COLEGIATURA
-- DENTRO DE CADA CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID Y RANKING
USE [platzi]
SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
FROM platzi.alumnos
ORDER BY carrera_id, ranking
GO

Ronny Jair Mora Rojas

student•

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

Jeovany Esteban Hernández Cervantes

student•

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

Gabriela Navas

student•

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

Luis Loaeza

student•

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

student•

buena observación!

Gerardo Mayel Fernández Alamilla

student•

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

student•

++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

student•

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

student•

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

student•

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

Danilo Valenzuela

student•

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

Juan José Mamani Tarqui

student•

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.

Wilner Alexander Ochoa Vivas

student•

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

student•

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

student•

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;

Patricio Villarroel Durán

student•

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

Miguel Angel Cáceres Valdivia

student•

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

student•

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

student•

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.