No tienes acceso a esta clase

¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera

Curso Práctico de SQL

Curso Práctico de SQL

Israel Vázquez Morales

Israel Vázquez Morales

Particiones y agregación

28/29
Recursos

Aportes 29

Preguntas 5

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad?

- 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

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

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

que curso tan completo y con tanta información, el exámen será un reto !!
Principales windows function: * `ROW_NUMBER()`: Asigna un ‘id’ a la tupla según como se haya ordenado y repartido en la window funciotn, independiente del id de la tabla. * `FIRST_VALUE()`: Devuelve el primer valor (según como se lo ordene) de un grupo de tuplas (este grupo sale de la partición y para todo ese conjunto de datos en la nueva columna creada por el window funtion sale solo ese primer valor para todos ellos, si se cambia de dato (en la columna por la cual se hizo la partición) cambia el valor para todo ese nuevo conjunto de datos. * `LAST_VALUE()`: Es igual al anterior, pero trae el último valor. * `NTH_VALUE()`: Es igual a los anteriores, pero dentro de sus paréntesis aparte de pedir de que columna se quiere sacar el valor, después de una coma se coloca la posición del valor buscado (es decir el tercero, cuarto, quinto, según como este ordenado). * `RANK()`: Nos da un lugar que ocupa una fila en su grupo de partición (primer lugar, segundo lugar, etc.). Tiene una particularidad si hay 2 valores iguales y seguidos, este les asigna la misma posición, pero al siguiente valor, no lo coloca en la posición que sigue a la que coloco a los 2 anteriores, sino coloca la posición como si los 2 valores repetidos ocuparan una posición distinta. * `DENSE_RANK()`: Es igual al anterior, solo que en este no pasa lo de arriba, si hay 2 valores repetidos, a ambos los asigna en una misma posición, y al siguiente valor le asigna la posición siguiente a la asignada a los 2 anteriores. * `PERCENT_RANK()`: Hace lo mismo que los anteriores solo que asigna un “porcentaje” sigue esta fórmula `(rank()-1)/(total_fila-1)`

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

Función Descripción Sintaxis Ejemplo
AVG() Calcula el promedio de una columna en un conjunto de filas. AVG(columna) OVER (PARTITION BY columna2) AVG(ventas) OVER (PARTITION BY mes)
COUNT() Calcula el número de filas que cumplen cierta condición. COUNT(columna) OVER (PARTITION BY columna2) COUNT(producto) OVER (PARTITION BY año)
CUME_DIST() Calcula el porcentaje de filas que tienen un valor menor o igual que el valor de la fila actual, pero agrega filas con valores iguales. CUME_DIST() OVER (ORDER BY columna) CUME_DIST() OVER (ORDER BY ventas)
DENSE_RANK() Asigna un rango a cada fila en función de una columna determinada, pero sin lagunas. DENSE_RANK() OVER (PARTITION BY columna ORDER BY columna2) DENSE_RANK() OVER (PARTITION BY departamento ORDER BY salario DESC)
FIRST_VALUE() Obtiene el primer valor de una columna en un conjunto de filas. FIRST_VALUE(columna) OVER (PARTITION BY columna2 ORDER BY columna3) FIRST_VALUE(nombre) OVER (PARTITION BY departamento ORDER BY salario DESC)
LAG() Obtiene valores de la fila anterior en un conjunto de filas. LAG(columna, n) OVER (ORDER BY columna2) LAG(ventas, 1) OVER (ORDER BY fecha)
LAST_VALUE() Obtiene el último valor de una columna en un conjunto de filas. LAST_VALUE(columna) OVER (PARTITION BY columna2 ORDER BY columna3) LAST_VALUE(nombre) OVER (PARTITION BY departamento ORDER BY salario DESC)
LEAD() Obtiene valores de la fila siguiente en un conjunto de filas. LEAD(columna, n) OVER (ORDER BY columna2) LEAD(ventas, 1) OVER (ORDER BY fecha)
MAX() Obtiene el valor más alto de una columna en un conjunto de filas. MAX(columna) OVER (PARTITION BY columna2) MAX(precio) OVER (PARTITION BY departamento)
MIN() Obtiene el valor más bajo de una columna en un conjunto de filas. MIN(columna) OVER (PARTITION BY columna2) MIN(precio) OVER (PARTITION BY departamento)
NTH_VALUE() Devuelve el valor N-esimo de una columna. NTH_VALUE(columna, n) OVER (PARTITION BY columna2 ORDER BY columna3) NTH_VALUE(nombre, 3) OVER (PARTITION BY departamento ORDER BY salario DESC)
NTILE() Divide un conjunto de filas en grupos de tamaño igual. NTILE(n) OVER (ORDER BY columna) NTILE(4) OVER (ORDER BY ventas)
PERCENT_RANK() Calcula el porcentaje de filas que tienen un valor menor o igual que el valor de la fila actual. PERCENT_RANK() OVER (ORDER BY columna) PERCENT_RANK() OVER (ORDER BY ventas)
RANK() Asigna un rango a cada fila en función de una columna determinada. RANK() OVER (PARTITION BY columna ORDER BY columna2) RANK() OVER (PARTITION BY departamento ORDER BY salario DESC)
ROW_NUMBER() Asigna un número a cada fila en función del orden en que aparecen. ROW_NUMBER() OVER (ORDER BY columna) ROW_NUMBER() OVER (ORDER BY fecha)
STDDEV() Calcula la desviación estándar de una columna en un conjunto de filas. STDDEV(columna) OVER (PARTITION BY columna2) STDDEV(ventas) OVER (PARTITION BY departamento)
SUM() Calcula la suma de una columna en un conjunto de filas. SUM(columna) OVER (PARTITION BY columna2) SUM(ventas) OVER (PARTITION BY departamento)
VARIANCE() Calcula la varianza de una columna en un conjunto de filas. VARIANCE(columna) OVER (PARTITION BY columna2) VARIANCE(precio) OVER (PARTITION BY departamento)