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?

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)

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

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

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

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

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

Otros conceptos

La cláusula PARTITION BY en las funciones de ventana se utiliza para dividir el conjunto de resultados en particiones basadas en una o más columnas. Esto es útil cuando deseas realizar cálculos de funciones de ventana dentro de grupos específicos de datos en lugar de en el conjunto completo. La agregación en este contexto se refiere a funciones como SUM(), AVG(), COUNT(), MAX(), MIN(), etc., que operan sobre conjuntos de datos dentro de cada partición.

• calcular la suma acumulativa de colegiaturas para cada carrera en la tabla platzi.alumnos y platzi.carreras. Aquí hay ejemplos que ilustran cómo usar PARTITION BY y funciones de agregación:

SELECT
    a.id,
    a.nombre,
    a.apellido,
    a.colegiatura,
    c.carrera,
    SUM(a.colegiatura) OVER (PARTITION BY c.carrera ORDER BY a.id) AS suma_acumulativa_por_carrera
FROM
    platzi.alumnos a
JOIN
    platzi.carreras c ON a.carrera_id = c.id;

• la función SUM() se aplica sobre la columna colegiatura, pero se particiona por la columna carrera. La suma acumulativa se reinicia para cada carrera.

AVG() y PARTITION BY:

SELECT
    a.id,
    a.nombre,
    a.apellido,
    a.colegiatura,
    c.carrera,
    AVG(a.colegiatura) OVER (PARTITION BY c.carrera ORDER BY a.id) AS promedio_por_carrera
FROM
    platzi.alumnos a
JOIN
    platzi.carreras c ON a.carrera_id = c.id;

• En este caso, la función AVG() calcula el promedio de las colegiaturas, particionado por carrera. Nuevamente, el promedio se calcula de manera independiente para cada carrera.

Gracias profesor, excelente curso

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