Rangos y Percentiles con Funciones de Ventana en SQL

Clase 29 de 34 • Curso de PostgreSQL Aplicado a Ciencia de Datos

Resumen

¿Qué son las funciones de ventanas?

Las funciones de ventanas, o "Windows Functions", son potentes herramientas en las bases de datos relacionales que facilitan el análisis y manipulación de datos. Permiten establecer una relación entre un registro específico y el conjunto completo de datos. Gracias a su capacidad para realizar cálculos matemáticos de manera rápida y eficiente, estas funciones ofrecen ventajas significativas en términos de rendimiento y precisión.

¿Cómo se aplica el ranking estándar y el percentil?

Diferentes métodos de ranking se utilizan en análisis de datos.

Ranking Estándar: Otorga un orden basado en un conteo estricto. En este método, datos con el mismo valor obtienen la misma posición en el ranking. Si dos valores son iguales en el conteo de, por ejemplo, rentas de películas, ambos recibirán el mismo rango.
Percentil Ranking: Calcula el percentil de un registro en comparación con el resto del dataset. Este percentil se calcula dividiendo el rango del dato menos uno por el total de posibles rangos menos uno, resultando en un número entre 0 y 1 que puede interpretarse como un porcentaje.

El uso de estos tipos de ranking proporciona una visión más completa sobre la distribución de los datos dentro de un conjunto, ya que no solo se ven los mayores valores, sino cómo se distribuyen en porcentajes.

¿Cómo ajustar el orden de clasificación?

Manipular el orden de clasificación es esencial para obtener la perspectiva necesaria de un conjunto de datos.

Orden Ascendente: Al usar un orden ascendente, los datos se organizan desde el menor hasta el mayor valor. En el contexto de las rentas de películas, esto mostraría películas desde aquellas con menos hasta aquellas con más rentas.
Orden Descendente: Por el contrario, un orden descendente mostrará al principio del conjunto a aquellas películas con más rentas. Es útil cuando se busca identificar directamente los líderes en una categoría determinada.

Al modificar el orden, es importante configurar correctamente la cláusula ORDER BY en SQL, ya que determina cómo la base de datos organizará y filtrará los resultados.

¿Qué beneficios ofrecen las funciones de ventanas en el contexto de SQL?

La utilización de funciones de ventanas optimiza y simplifica los procesos de evaluación de datos sin la necesidad de realizar complejas operaciones manuales, como calcular el número total de registros o segmentos para determinar el ranking. Su implementación en las consultas SQL ayuda a:

Ahorrar tiempo y recursos: Las funciones de ventanas calculan automáticamente rankings y percentiles, reduciendo la carga de trabajo manual y los errores potenciales.
Mejorar la eficiencia: Dado que las bases de datos manejan gran parte del procesamiento, las consultas se ejecutan rápidamente.
Facilitar el análisis comparativo: Proporcionan una clara visión sobre cómo se ubican los datos en relación con el todo, permitiendo comparaciones directas.

Estos beneficios contribuyen significativamente a un análisis de datos más profundo y preciso, ofreciendo perspectivas valiosas sobre cualquier conjunto de datos.

Siguiendo el principio básico de estas funciones, es importante dominar su uso y adaptarlas a las necesidades específicas de análisis que se presenten. A medida que avances, te resultará natural integrarlas en tus rutinas de procesamiento de datos. ¡Sigue explorando y sacando el máximo provecho de estas herramientas!

Sebastian Granda Gallego

student•

Si quisiéramos usar WHERE para especificar que solo queremos los lugares menores o iguales a 10, deberíamos convertir la consulta como una subconsulta, de esta forma podremos usar lugar dentro del WHERE.

De esta forma:

SELECT *
FROM (
	SELECT
		peliculas.pelicula_id AS id,
		peliculas.titulo,
		COUNT(*) AS numero_rentas,
		DENSE_RANK() OVER(
			ORDER BY COUNT(*) DESC
		) AS lugar
	FROM rentas
		INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
		INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
	GROUP BY peliculas.pelicula_id
	ORDER BY numero_rentas DESC
) AS top_10
WHERE lugar <= 10;

Simon López Ramos

student•

Gracias, me sirvió mucho y no me estaba dando

Fernanda Arteaga Guivin

student•

que lindas las window function! pensar que antes sacaba los datos de SQL y tenia que pasarlo a un excel para sacar porcentajes o los ranking :(

Julián Cárdenas

student•

Así es!

Luis Fernando Úbeda Camacho

student•

DENSE_RANK: Rango por posición en la tabla.

Pedro Quiñonez Verdugo

student•

PERCENT_RANK Posicion porcentual de cada registro en la agrupacion

DENSE_RANK Posicion por lugares de cada registro en la agrupacion

Diego Camacho

student•

Using alias for less verbose

SELECT 
	p.pelicula_id AS id,
	p.titulo,
	COUNT(*) AS numero_rentas,
	DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS lugar
FROM rentas AS r
JOIN inventarios AS i
ON   r.inventario_id = i.inventario_id
JOIN peliculas AS p
ON   i.pelicula_id = p.pelicula_id
GROUP BY p.pelicula_id
ORDER BY numero_rentas DESC;

Orlando Reyes

student•

Y más digerible inluso! Esto lo vi en otro curso que hice en datacamp y me pareció súper bien! Así hasta es más rápido identificar con que tablas estás trabajando.

Joel Sierra Mendoza

student•

Inclusive se puede acortar más. Aprovechando que las llaves foráneas son iguales en nombre, en vez de usar ON podemos usar USING y sólo hay que escribir la llave foránea una sola vez.

Martin Davíd Altamar Peña

student•

Me gusto mucho lo del DENSE_RANK, es muy util para sacar los datos directamente de SQL sin tener que convertirlos con otros programas.

José Alberto Ortiz Vargas

student•

SELECT
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	DENSE_RANK () OVER (
		ORDER BY COUNT(*) DESC
	) AS percentil
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC;

JAVIER SANTIAGO SALGADO

student•

SELECT 
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	ROW_NUMBER () OVER (
		ORDER BY COUNT(*) DESC
	) AS lugar
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC
LIMIT 10;

SELECT 
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	PERCENT_RANK() OVER (
		ORDER BY COUNT(*) DESC
	) AS lugar
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC;


SELECT 
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	PERCENT_RANK() OVER (
		ORDER BY COUNT(*) ASC
	) AS lugar
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC;

SELECT 
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	DENSE_RANK() OVER (
		ORDER BY COUNT(*) DESC
	) AS lugar
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC;

Jon Francis Perez

student•

Héroe sin capa.

Jon Francis Perez

student•

Top Ten. Percentil Rank (asc). Percentil Rank (desc). Dense Rank (overall).

Cristian Monzón

student•

percentila...

JOSE BENJAMIN GUTIERREZ RINCON

student•

¿Cuál Window Function nos ayuda a tener el lugar del row actual en el dataset sin repetir valores?

Pablo Alejandro Figueroa

student•

SELECT 
	COUNT(*)			AS "Cantidad Rentas", 
	peliculas.titulo	AS "Título",
	DENSE_RANK()   OVER (ORDER BY COUNT(*) DESC),
	RANK() 		   OVER (ORDER BY COUNT(*) DESC),
	PERCENT_RANK() OVER (ORDER BY COUNT(*) DESC),
	ROW_NUMBER()   OVER (ORDER BY COUNT(*) DESC)

FROM rentas
	JOIN inventarios USING(inventario_id)
	JOIN peliculas   USING(pelicula_id)

GROUP BY "Título"
ORDER BY "Cantidad Rentas" DESC
LIMIT 10;

Jhon Freddy Tavera Blandon

student•

Algo mas sencillo

clasificar las películas en función de algún criterio y calcular el percentil de alguna métrica.

SELECT
    titulo,
    precio_renta,
    RANK() OVER (ORDER BY precio_renta) AS ranking,
    PERCENT_RANK() OVER (ORDER BY precio_renta) AS percentil
FROM
    public.peliculas;

Utilizamos RANK para asignar un rango a cada película basado en el precio de renta, y PERCENT_RANK para calcular el percentil de cada película en función de ese mismo criterio.

Baldwin Monasterio

student•

Inspirado en el ejemplo de uno de los compañeros de aquí, hice una pequeña modificación que me parece de lo más lógica, aquí la tienen:

-- 'TOP TEN' usando WHERE y 'lugar' para los 10 primeros puestos.
SELECT *
FROM (
    SELECT
        peliculas.pelicula_id AS id,
        peliculas.titulo,
        COUNT(*) AS numero_rentas,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS lugar
    FROM rentas
        INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
        INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
    GROUP BY peliculas.pelicula_id
    ORDER BY lugar ASC
) AS top_10 -- Es imprescindible tener un alias por ser una subconsulta.
WHERE lugar < 11;

Además, no me gusta ponerles alias de un sólo carácter a las tablas, puede que se 'escriba' menos, pero aumenta mucho la atención que hay que poner al leer el código, me explico, hay que tener en cuenta constantemente que 'esa letra' es la tabla 'fulana', y la otra 'mengana', sí se pone el nombre completo, es innecesario hacer 'conversiones' mentales para tal menester.

Andres Sanchez

student•

29. Usando rank y percent rank

SELECT 
	peliculas.pelicula_id AS id,
	peliculas.titulo,
	COUNT(*) AS numero_rentas,
	DENSE_RANK () OVER (
		ORDER BY COUNT(*) DESC
	) AS lugar
FROM rentas
	INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id
	INNER JOIN peliculas ON inventarios.pelicula_id = peliculas.pelicula_id
GROUP BY peliculas.pelicula_id
ORDER BY numero_rentas DESC
;

Ricardo Gomez

student•

Excelente explicación y ejemplo al grano, ahorra tiempo. Lo otro para reducir el código podemos remplazar el nombre de la tabla por una letra, eso hacer ver mas limpio el código

Sergio Javier Lopez Olivera

student•

Todas las películas ordenadas que NO son las 10 rankeadas

WITH peliculas_rankeadas AS (
	SELECT
		DENSE_RANK() OVER(ORDER BY COUNT(r.renta_id) DESC) AS lugar, 
		p.pelicula_id AS id,
		p.titulo AS titulo_pelicula,
		COUNT(r.renta_id) AS numero_rentas
	FROM
		rentas r
		JOIN 
			inventarios i ON i.inventario_id = r.inventario_id
		JOIN
			peliculas p ON p.pelicula_id = i.pelicula_id
	-- WHERE ranking_pelicula <= 10 ← no existe "todavía" al momento de crear la consulta, debemos hacer una subconsulta o
	-- una CTE
	GROUP BY
		p.pelicula_id
	ORDER BY 
		numero_rentas DESC
) 
SELECT *
FROM peliculas_rankeadas
WHERE lugar > 10 ;
// todas las que le siguen a las 10 rankeadas

Ian Cristian Ariel Yané

student•

Alguien que sepa

++¿ me ayuda a sacar el porcentaje de peliculas por categoria? ++

Adjunto mi codigo ..

WITH categorias_top AS(
	SELECT 
			DISTINCT(categorias.CATEGORIA_ID) AS CATEGORIA,
			categorias.nombre,
			SUM(PELICULA_ID) OVER(PARTITION BY categorias.CATEGORIA_ID) AS PELICULAS_CATEGORIAS
	FROM 
			peliculas_categorias INNER JOIN categorias ON categorias.categoria_id = peliculas_categorias.categoria_id
	group by 
			CATEGORIA,
			categorias.nombre,
			PELICULA_ID
	order by 
			PELICULAS_CATEGORIAS desc)
SELECT *,
	   PERCENT_RANK() OVER(PARTITION BY CATEGORIA) AS PORCENTAJE
FROM categorias_top
order by porcentaje;

Ian Cristian Ariel Yané

student•

Alguien que sepa

¿ me ayuda a sacar el porcentaje de peliculas por categoria?

Adjunto mi codigo

WITH categorias_top AS(
	SELECT 
			DISTINCT(categorias.CATEGORIA_ID) AS CATEGORIA,
			categorias.nombre,
			SUM(PELICULA_ID) OVER(PARTITION BY categorias.CATEGORIA_ID) AS PELICULAS_CATEGORIAS
	FROM 
			peliculas_categorias INNER JOIN categorias ON categorias.categoria_id = peliculas_categorias.categoria_id
	group by 
			CATEGORIA,
			categorias.nombre,
			PELICULA_ID
	order by 
			PELICULAS_CATEGORIAS desc)
SELECT *,
	   PERCENT_RANK() OVER(PARTITION BY CATEGORIA) AS PORCENTAJE
FROM categorias_top
order by porcentaje;

Ottmar Joseph Torres Peña

student•

por ejemplo lo que se plantea en clase

SELECT *

FROM (

SELECT

peliculas.pelicula_id AS id,

peliculas.titulo,

COUNT(rentas.rentas_id) AS numero_rentas,

DENSE_RANK() OVER (ORDER BY COUNT(rentas.renta_id) DESC) as lugar

FROM rentas

INNER JOIN inventarios ON rentas.inventario_id = inventarios.inventario_id

INNER JOIN peliculas ON inventarios.pelicula_id =peliculas.pelicula_id

GROUP BY peliculas.pelicula_id, peliculas.titulo

ORDER BY numero_rentas DESC

WHERE LUGAR <=10

Rodrigo Eduardo Méndez Villabona

student•

gracias por las aclaraciones