Particiones y agregación

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

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

Con el PERCENT_RANK puedes calcular el percentil 😃

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)

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

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

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)

El examen estara interesante 😃

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

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:)

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;```

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

Hola dejo esta tabla
con las siguiuentes columnas

Function Description Syntax Example

la dejo tambien enj markdown por si la quieren copiar

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.

Funciones de Windows agregadas
SUM(), MAX(), MIN(), AVG(). COUNT()
Funciones de clasificación de Windows
RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
Funciones de valores de Windows
LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

este curso sin duda alguna hay que tomarlo unas 10 veces o mas, porque aparte de que es muy bueno, los ejercicios nos ayudan a mejorar en nuestra logia para crear queries

Gracias profesor, excelente curso

Dejo una tabla con las funciones de window functions junto con Función Descripción Sintaxis Ejemplo