Usando rank y percent rank

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;

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 😦

DENSE_RANK: Rango por posición en la tabla.

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;

PERCENT_RANK
Posicion porcentual de cada registro en la agrupacion

DENSE_RANK
Posicion por lugares de cada registro en la agrupacion

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;

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;

percentila…

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

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
;

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

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.

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;

gracias por las aclaraciones

Windows functions, ejemplos de usos

¿Se han dado cuenta de que las películas del listado no existen realmente?

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.

Entendido y muy potente

Muy util!!!

Super !! muy Claro…

Excelente,

interesante