mysql - 需要 SQL 查询改进/建议

Question

我试图解决这个问题：

编写查询以返回每部电影的租借次数，以及同一类别中的平均租借次数。

• 您只需要返回film_id <= 10 的结果。返回4 列：film_id、类别名称、租借数量以及该类别的平均租借数量。

Table 1: inventory
Each row is unique; Inventoy_id is the primary key of the table

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint
Table 2: film_category
A film can only belong to one category

  col_name   | col_type
-------------+--------------------------
 film_id     | smallint
 category_id | smallint
Table 3: rental
   col_name   | col_type
--------------+--------------------------
 rental_id    | integer
 rental_ts    | timestamp with time zone
 inventory_id | integer
 customer_id  | smallint
 return_ts    | timestamp with time zone
 staff_id     | smallint
Table 4: category
Movie categories.

  col_name   | col_type
-------------+--------------------------
 category_id | integer
 name        | text
Sample results

 film_id | category_name | rentals | avg_rentals_category
---------+---------------+---------+----------------------
       1 | Documentary   |      23 |  16.6666666666666667
       2 | Horror        |       7 |  15.9622641509433962
       3 | Documentary   |      12 |  16.6666666666666667
       4 | Horror        |      23 |  15.9622641509433962

我的查询：

select film_id, category_name, rentals,
       avg(rentals) over(partition by category_name)
from
(
  select fc.film_id, 
       c.name as category_name , 
       count(distinct rental_id) as rentals
from category c 
join film_category fc on fc.category_id = c.category_id
join inventory i on i.film_id = fc.film_id
join rental r on i.inventory_id=r.inventory_id
group by fc.film_id, category_name
) temp
where film_id <=10;

我只是在最后一列得到不同的值。我想知道我做错了什么？请尝试帮助我对我的书面查询提出建议，以便我可以继续提高我的窗口功能技能。谢谢！

标签： mysqlsql