Estou tentando descobrir o SQL para listar os 5 principais aluguéis de cada mês no MySQL ou SQL Server. O banco de dados é o banco de dados Sakila Sample e a mesa é alugada. Aqui estão as primeiras linhas no meu cliente de banco de dados (Navicat Premium):
Eu gostaria de tirar o mês do campo rental_date.
Cada filme tem um inventário_id exclusivo.
Até agora eu tenho isso:
SELECT
MONTH( rental_date ) AS month,
inventory_id,
COUNT ( inventory_id ) AS no_of_rentals
FROM
rental
GROUP BY
MONTH ( rental_date ),
inventory_id
HAVING
COUNT ( inventory_id ) > 1
ORDER BY
MONTH ( rental_date ),
inventory_id;
Infelizmente, isso retorna todos os filmes com mais de uma locação por mês, o que não é o que eu quero.
Eu gostaria de ver algo mais como:
month inventory_id no_of_rentals
_________________________________
April 367 5
April 98 4
(3 more rows)
May 144 5
May 32 5
(3 more rows)
etc...
Pontos de bônus se você conseguir o título do filme!
Obrigado!
Roubar
ATUALIZAR:
Obrigado a Dominique Boucher pela resposta.
Aqui está minha consulta finalizada:
WITH cte AS (
SELECT
MONTH(rental_date) rental_month_no,
DATENAME(month, rental_date) rental_month,
inventory_id,
COUNT( inventory_id ) no_of_rental,
ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
FROM
rental
GROUP BY
MONTH(rental_date),
DATENAME(month, rental_date),
inventory_id
HAVING COUNT( inventory_id ) > 1
)
SELECT
rental_month,
cte.inventory_id,
title,
no_of_rental,
rank
FROM
cte
JOIN inventory ON cte.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
WHERE
Rank < 6
ORDER BY rental_month_no, no_of_rental DESC;
Adicionei código para buscar os títulos e resultados limitados aos filmes que foram alugados mais de uma vez por mês.
Aqui estão os resultados no Navicat:
