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 25

Preguntas 5

Ordenar por:

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

o inicia sesió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

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

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

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)