Particiones y agregación

Sergio Emanuel Tomas

student•

- ROW_NUMBER(): nos da el numero de la tupla que estamos utilizando en ese momento. - OVER([PARTITION BY column] [ORDER BY column DIR]): nos deja Particionar y Ordenar la window function. - PARTITION BY(column/s): es un group by para la window function, se coloca dentro de OVER. - FIRST_VALUE(column): devuelve el primer valor de una serie de datos. - LAST_VALUE(column): Devuelve el ultimo valor de una serie de datos. - NTH_VALUE(column, row_number): Recibe la columna y el numero de row que queremos devolver de una serie de datos - RANK(): nos dice el lugar que ocupa de acuerdo a el orden de cada tupla, deja gaps entre los valores. - DENSE_RANK(): Es un rango mas denso que trata de eliminar los gaps que nos deja RANK. - PERCENT_RANK(): Categoriza de acuerdo a lugar que ocupa igual que los anteriores pero por porcentajes.

JAVIER SANTIAGO SALGADO

student•

Gracias por tu resumen

Juan Gustavo Daza Martínez

student•

gracias bro

Andrés David Lizarazo Becerra

student•

Debería haber un curso más enfocado a estas window functions :)

María José Medina

student•

Siiii

Catalina Murcia

student•

Mas que un curso creo que es explicar mas en la vida laboral como utilizarlas, es decir, que nos pueden pedir. Siento en mi caso que se aprende mas bajo ejemplos

Victor Inojosa

student•

Con el PERCENT_RANK puedes calcular el percentil :-)

Aaron Quiroga

student•

++Particiones y Agregaciones++

ROW_NUMBER(): nos da el numero de fila, se mantiene ordenado por más que establezcamos otro criterio de ordenamiento en donde el ID se desordene
OVER(PARTITION BY campo ORDER BY campo2): particiona según campo y campo2 ordenando por campo 2 y agrupando valores de resultados según esta partición
FIRST_VALUE(campo): trae el primer valor de una serie de datos
LAST_VALUE(campo): trae el último valor de una serie de datos
NTH_VALUE(campo, row_num): trae el enésimo valor de una serie de datos
RANK(): rankea valores según la partition y order by si cabe
DENSE_RANK(): es como el rank, pero si existen varios valores empatados, en lugar de saltar (ej: 1,1,1,4,4,6) mantiene el orden (ej:1,1,1,2,2,3)
PERCENT_RANK(): Rankea según el porcentaje al que pertenece el valor, a través de la siguiente fórmula: ( RANK() - 1 ) / ( total de filas - 1)

José Roberto Flores Flores

student•

Todo un mundo por descubrir en las Window function y particiones. ¿En qué curso se puede aprender más a detalle?

Francisco Murillo

student•

en la practica🤣🤣

Juan Gustavo Daza Martínez

student•

me imagino que con el de Ciencia de datos con PostgreSQL

Aaron Fabrizio Calderon Guillermo

student•

Para SQL Server:


-- AGREGAR COLUMNA QUE INDICA UN NUMERO POR REGISTRO
-- ORDENADO POR ID
USE [platzi]
SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_id, *
FROM platzi.alumnos
GO


-- AGREGAR COLUMNA QUE INDICA UN NUMERO POR REGISTRO
-- ORDENADO POR FECHA_INCORPORACION
USE [platzi]
SELECT ROW_NUMBER() OVER(ORDER BY fecha_incorporacion) AS row_id, *
FROM platzi.alumnos
GO

-- PRIMERA ORDERNA POR ID
-- SELECCIONA EL PRIMER VALOR COLEGIATURA
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT FIRST_VALUE(colegiatura) OVER(ORDER BY id) AS row_id, *
FROM platzi.alumnos
GO

-- PRIMERA COLEGIATURA LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT FIRST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura) AS primera_colegiatura, *
FROM platzi.alumnos
GO

-- ULTIMA COLEGIATURA LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT LAST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ultima_colegiatura, *
FROM platzi.alumnos
GO
-- TAMBIEN
USE [platzi]
SELECT FIRST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ultima_colegiatura, *
FROM platzi.alumnos
GO


-- INDICADOR DE ORDEN (DISCONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO

-- INDICADOR DE ORDEN (CONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT DENSE_RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO


-- INDICADOR DE ORDEN (CONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT PERCENT_RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO
-- FORMULA: (RANK_CONTINUO - 1) / (TOTAL_ROWS - 1)

Fernanda Arteaga Guivin

student•

@_@ he quedado confundida con las Windows Fuction... se me hace complejo seguirlas.

Carlos Fernando Aguilar González

student•

No te rindas. Con un poco de práctica seguro las dominarás.

Saludos!

Fernanda Arteaga Guivin

student•

graciasss! en el curso de postgre aplicado a ciencia de datos lo entendí muchísimo mejor :D

Braulio Alberto Bueno Pabon

student•

El examen estara interesante :smiley:

Braylin Alexander Jiménez Reynoso

student•

Eso pensaba yo, que cosas nos traerá este examen.

Jeiner Muñoz Olivera

student•

La utilidad de SQL abarca muchos campos. Increíble todo lo que se puede hacer. ;)

Cristian Camilo Parada Moreno

student•

Haciendo unos queries me di cuenta de algo, tal vez sea algo obvio pero es bueno descubrirlo cuando juegas con el codigo: ---> Usando esta window function notamos que el orderamiento del query sera desde el primer valor que se ingreso teniendo en cuenta año, mes, dia, hour, minute y second.

SELECT *, ROW_NUMBER() OVER(ORDER BY fecha_incorporacion) 
FROM platzi.alumnos;

Antes de conocerla esta herramienta tan practica hacia este query:

SELECT *
FROM (SELECT *, EXTRACT (YEAR FROM fecha_incorporacion) AS year_incorporation,
	  EXTRACT (MONTH FROM fecha_incorporacion) AS month_incorporation,
	  EXTRACT (DAY FROM fecha_incorporacion) AS day_incorporation,
	  EXTRACT (HOUR FROM fecha_incorporacion) AS hour_incorporation,
	  EXTRACT (MINUTE FROM fecha_incorporacion) AS minute_incorporation,
	  EXTRACT ( SECOND FROM fecha_incorporacion) AS second_incorporation
	 FROM platzi.alumnos) AS incorporation
ORDER BY year_incorporation, month_incorporation, day_incorporation,
hour_incorporation,minute_incorporation,second_incorporation;

Practicamente es lo mismo. O bueno quizas lo mismo, aun soyr begginner. Pero es bueno conocer herramientas que te ahorran lineas de codigo:)

Sebastian Rodriguez

student•

Exactamente entediste para que funcionan. En vez de hacer esa consulta tan larga la Window Function nos la vuelve super corta. Y ademas usamos menos capacidad computacional y es mas rapida. Genial tu aporte.

Pablo Alejandro Figueroa

student•

Esmeralda Palacios

student•

wo wo wo, ¿se puede realizar lo mismo con group by? es que eso de PARTITIONme suena muy parecido a group by, ¿cuál es la diferencia? perdón si mi pregunta parece obvia.

Sebastian Calderón Araque

student•

Tienes razón, son bastante parecidas. La diferencia es que el GROUP BY realiza tal cual una agrupación y te lo va a mostrar así agrupado. El PARTITION BY te realiza una agrupación para ejecutar la función que le hayas indicado pero no te lo va a mostrar agrupada, sino que mostrará el resultado de la función en la nueva columna que genera la Window Function.

Hector Vasquez

student•

En mysql, una manera de emular el ROW_NUMBER() y lograr el mismo resultado, con poco impacto en el rendimiendo:

SELECT (@row_number:=@row_number + 1) AS row_id, a.* 
FROM alumnos a, (SELECT @row_number:=0) AS t
limit 10

Michael Gamba

student•

Hola dejo esta tabla con las siguiuentes columnas

Function Description Syntax Example

la dejo tambien enj markdown por si la quieren copiar

Function	Description	Syntax	Example
ROW_NUMBER()	Returns the row number, remains ordered even if another sorting criterion is established where the ID is disordered.	ROW_NUMBER() OVER (ORDER BY column)	SELECT name, ROW_NUMBER() OVER (ORDER BY name) FROM employees
OVER (PARTITION BY campo ORDER BY campo2)	Partitions according to field and field2, ordering by field 2 and grouping result values according to this partition.	OVER (PARTITION BY column1 ORDER BY column2)	SELECT name, SUM(salary) OVER (PARTITION BY department ORDER BY salary) FROM employees
FIRST_VALUE(campo)	Brings the first value of a series of data.	FIRST_VALUE(column) OVER (ORDER BY column)	SELECT name, FIRST_VALUE(name) OVER (ORDER BY name) FROM employees
LAST_VALUE(campo)	Brings the last value of a series of data.	LAST_VALUE(column) OVER (ORDER BY column)	SELECT name, LAST_VALUE(name) OVER (ORDER BY name) FROM employees
NTH_VALUE(campo, row_num)	Brings the nth value of a series of data.	NTH_VALUE(column, n) OVER (ORDER BY column)	SELECT name, NTH_VALUE(name, 3) OVER (ORDER BY name) FROM employees
RANK()	Ranks values according to partition and order by if applicable.	RANK() OVER (ORDER BY column)	SELECT name, RANK() OVER (ORDER BY salary) FROM employees
DENSE_RANK()	Like rank, but if there are several tied values, instead of jumping (e.g.: 1,1,1,4,4,6) it maintains the order (e.g.:1,1,1,2,2,3).	DENSE_RANK() OVER (ORDER BY column)	SELECT name, DENSE_RANK() OVER (ORDER BY salary) FROM employees
PERCENT_RANK()	Ranks according to the percentage to which the value belongs, through the following formula: ( RANK() - 1 ) / ( total rows - 1).	PERCENT_RANK() OVER (ORDER BY column)	SELECT name, PERCENT_RANK() OVER (ORDER BY salary) FROM employees

Martin DAVILA

student•

SELECT ROW_NUMBER() OVER() AS row_id, * FROM platzi.alumnos;  SELECT ROW_NUMBER() OVER(ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT FIRST_VALUE(colegiatura) OVER() AS row_id, * FROM platzi.alumnos;  SELECT FIRST_VALUE(colegiatura) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT LAST_VALUE(colegiatura) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT nth_value(colegiatura, 3) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank;  SELECT *, DENSE_RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank; SELECT *, PERCENT_RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura ASC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank;```

Jose Cardenas

student•

Según a mi entender se salta del 1 al 5 y del 5 al 7 porque está siguiendo el orden de carrera_id, y despues el de colegiatura_rank, si quitamos el order by carrera_id si te muestra el orden correcto. Creo que faltó aclarar esto nadamas.

Jackssuriss Tatiana Herrera Florez

student•

Gracias profe, excelente curso super completo.

Valentina Cobo Paz

student•

que curso tan completo y con tanta información, el exámen será un reto !!

Pablo Alejandro Figueroa

student•

Pablo Alejandro Figueroa

student•

-- AGREGAR COLUMNA QUE INDICA UN NUMERO POR REGISTRO
-- ORDENADO POR ID
USE [platzi]
SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_id, *
FROM platzi.alumnos
GO


-- AGREGAR COLUMNA QUE INDICA UN NUMERO POR REGISTRO
-- ORDENADO POR FECHA_INCORPORACION
USE [platzi]
SELECT ROW_NUMBER() OVER(ORDER BY fecha_incorporacion) AS row_id, *
FROM platzi.alumnos
GO

-- PRIMERA ORDERNA POR ID
-- SELECCIONA EL PRIMER VALOR COLEGIATURA
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT FIRST_VALUE(colegiatura) OVER(ORDER BY id) AS row_id, *
FROM platzi.alumnos
GO

-- PRIMERA COLEGIATURA LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT FIRST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura) AS primera_colegiatura, *
FROM platzi.alumnos
GO

-- ULTIMA COLEGIATURA LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT LAST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ultima_colegiatura, *
FROM platzi.alumnos
GO
-- TAMBIEN
USE [platzi]
SELECT FIRST_VALUE (colegiatura) OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ultima_colegiatura, *
FROM platzi.alumnos
GO


-- INDICADOR DE ORDEN (DISCONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO

-- INDICADOR DE ORDEN (CONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT DENSE_RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO


-- INDICADOR DE ORDEN (CONTINUO) LUEGO DE ORDENAR POR COLEGIATURA
-- POR CADA TIPO DE CARRERA_ID
-- Y LO COLOCA EN UNA COLUMNA
USE [platzi]
SELECT PERCENT_RANK() OVER(PARTITION BY carrera_id ORDER BY colegiatura DESC) AS colegiatura_rank, *
FROM platzi.alumnos
ORDER BY carrera_id, colegiatura_rank
GO
-- FORMULA: (RANK_CONTINUO - 1) / (TOTAL_ROWS - 1)

SELECT *
FROM (SELECT *, EXTRACT (YEAR FROM fecha_incorporacion) AS year_incorporation,
	  EXTRACT (MONTH FROM fecha_incorporacion) AS month_incorporation,
	  EXTRACT (DAY FROM fecha_incorporacion) AS day_incorporation,
	  EXTRACT (HOUR FROM fecha_incorporacion) AS hour_incorporation,
	  EXTRACT (MINUTE FROM fecha_incorporacion) AS minute_incorporation,
	  EXTRACT ( SECOND FROM fecha_incorporacion) AS second_incorporation
	 FROM platzi.alumnos) AS incorporation
ORDER BY year_incorporation, month_incorporation, day_incorporation,
hour_incorporation,minute_incorporation,second_incorporation;

Function

Description

Syntax

Example

ROW_NUMBER()

Returns the row number, remains ordered even if another sorting criterion is established where the ID is disordered.

ROW_NUMBER() OVER (ORDER BY column)

SELECT name, ROW_NUMBER() OVER (ORDER BY name) FROM employees

OVER (PARTITION BY campo ORDER BY campo2)

Partitions according to field and field2, ordering by field 2 and grouping result values according to this partition.

OVER (PARTITION BY column1 ORDER BY column2)

SELECT name, SUM(salary) OVER (PARTITION BY department ORDER BY salary) FROM employees

FIRST_VALUE(campo)

Brings the first value of a series of data.

FIRST_VALUE(column) OVER (ORDER BY column)

SELECT name, FIRST_VALUE(name) OVER (ORDER BY name) FROM employees

LAST_VALUE(campo)

Brings the last value of a series of data.

LAST_VALUE(column) OVER (ORDER BY column)

SELECT name, LAST_VALUE(name) OVER (ORDER BY name) FROM employees

NTH_VALUE(campo, row_num)

Brings the nth value of a series of data.

NTH_VALUE(column, n) OVER (ORDER BY column)

SELECT name, NTH_VALUE(name, 3) OVER (ORDER BY name) FROM employees

RANK()

Ranks values according to partition and order by if applicable.

RANK() OVER (ORDER BY column)

SELECT name, RANK() OVER (ORDER BY salary) FROM employees

DENSE_RANK()

Like rank, but if there are several tied values, instead of jumping (e.g.: 1,1,1,4,4,6) it maintains the order (e.g.:1,1,1,2,2,3).

DENSE_RANK() OVER (ORDER BY column)

SELECT name, DENSE_RANK() OVER (ORDER BY salary) FROM employees

PERCENT_RANK()

Ranks according to the percentage to which the value belongs, through the following formula: ( RANK() - 1 ) / ( total rows - 1).

PERCENT_RANK() OVER (ORDER BY column)

SELECT name, PERCENT_RANK() OVER (ORDER BY salary) FROM employees

SELECT ROW_NUMBER() OVER() AS row_id, * FROM platzi.alumnos;  SELECT ROW_NUMBER() OVER(ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT FIRST_VALUE(colegiatura) OVER() AS row_id, * FROM platzi.alumnos;  SELECT FIRST_VALUE(colegiatura) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT LAST_VALUE(colegiatura) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT nth_value(colegiatura, 3) OVER(PARTITION BY carrera_id ORDER BY fecha_incorporacion) AS row_id, * FROM platzi.alumnos;  SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank;  SELECT *, DENSE_RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank; SELECT *, PERCENT_RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura ASC) AS brand_rank FROM platzi.alumnos ORDER BY carrera_id, brand_rank;```

Particiones y agregación

Introducción a SQL

Breve historia de SQL

Álgebra relacional

Instalación de la BD de ejemplo

Qué es una proyección (SELECT)

Origen (FROM)

Productos cartesianos (JOIN)

Selección (WHERE)

Ordenamiento (ORDER BY)

Agregación y limitantes (GROUP BY y LIMIT)

Ejercitando tu SQL

El primero

El segundo más alto

Seleccionar de un set de opciones

En mis tiempos

Seleccionar por año

Duplicados

Selectores de rango

Eres lo máximo

Egoísta (selfish)

Resolviendo diferencias

Todas las uniones

Triangulando

Generando rangos

Regularizando expresiones

Conceptos de SQL Avanzado

Bases de datos distribuidas

Queries distribuídos

Sharding

Window functions

Particiones y agregación

El futuro de SQL