Window functions
Contenido del curso
César Leonardo Garzón Castiblanco
Patricio Sánchez Fernández
Andres Felipe Bolaños Fernandez
Roberto Jassiel Montes Gutierrez
Sebastian Calderón Araque
Juan Camilo Alvarez Jurado
Aaron Quiroga
Julián Cárdenas
Andrés David Lizarazo Becerra
Esmeralda Palacios
Anthony Guzman Lopez
Ingrid Tatiana Barrios Villamizar
Marlon Diego Rodríguez Castro
Jhins Ledys Cárdenas Pardo
Aaron Fabrizio Calderon Guillermo
Ronny Jair Mora Rojas
Jeovany Esteban Hernández Cervantes
Gabriela Navas
Luis Loaeza
Alejandro Rico García
Gerardo Mayel Fernández Alamilla
Sebastian Rodriguez
Cesar Augusto Morales Godoy
Mariano Gobea Alcoba
Anthony Campos
Danilo Valenzuela
Juan José Mamani Tarqui
Wilner Alexander Ochoa Vivas
Danny Cortez
LIZBET ESTEFANY CASANA VERASTEGUI
Pat Villarroel
Miguel Angel Cáceres Valdivia
Alexander Sebastian Jaramillo Ordiales
Cristian Velosa
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.
Buen resumen, César.
excelente, gracias.
Una imagen vale más que mil palabras.
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
++Window Functions++ Realizan cálculos que relacionan una tupla con el resto dentro de un mismo scope o partición.
SELECT * SUM(colegiatura) OVER (PARTITION BY carrera_id ORDER BY colegiatura) FROM platzi.alumnos;
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;
Excelente!
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().
muy clara tu explicación :)
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.
Dejo por acá este articulo de window functions, para profundizar un poco más, está bueno. https://mode.com/sql-tutorial/sql-window-functions/
Excelente aporte, gracias mil.
Excelente aporte. Muchas gracias.
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
Definitivamente considero que esta clase debe estar dentro de las primeras de este curso ¿Alguien opina lo mismo?
Like si vives en el mundo de Oracle y tienes que buscar alternativas a esto :(
¿Qué tan distinto es? Actualmente trabajo con Salesforce y me es util el SQL, pero pronto vamos a migrar a Oracle.
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?
buena observación!
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
++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.
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;
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;
muy buen código. Pude repasar mis conceptos de clases pasadas, entendiendolo.
Genial, con las window functions puedo resolver un problema que tenía en la cabeza hace días.
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:
Las funciones de ventana se utilizan comúnmente en análisis de datos para calcular métricas como:
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.
Debo admitir que esta clase me gusto mucho y me aclaro muchas de las dudas en cuanto a la estructura de los querys
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
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;
¿Para qué lo necesitarías? AVG agrupa todos los valores en un único valor, el promedio.
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.
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.
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.
