Over（）函数不能按预期覆盖所有行

Question

我一直在练习SQL，并遇到了我无法解释的这种行为。 （我也是问这个问题的人： （）函数不涵盖表中的所有行） - ＆gt;这是一个不同的问题。

假设我有一张像这样的

移动表：

movie_id	user_id	rating	create_at
1	1	1 3	2020-01-12
1	2	4	2020-02-11
1	3	2	2020-02-12
1	4 1	4 1	2020-01-01
2	1	5	2020 -02-17
2	2	2	2020-02-01
2	3	2	2020-03-01
3	1	3	2020-02-22
3	2	4	2020-02-25

我要做的是通过评级来对电影进行排名，我有此SQL查询：

SELECT
  movie_id,
  rank() over(partition by movie_id order by avg(rating) desc) as rank_rate
FROM
  MovieRating

从我的上一个问题中，我了解到 Over（）函数将在查询选择的窗口中运行，基本上是窗口此查询返回：

SELECT movie_id FROM MovieRating

所以我希望能有关ID 1、2和3，请参见至少3行。

但是，结果只是一行：

{"headers": ["movie_id", "rank_rate"], "values": [[1, 1]]}

为什么？我对（）函数的工作方式的理解有问题吗？

Answer 1

您需要一个聚合查询并使用 rank（）窗口函数在其结果上函数：

SELECT movie_id,
       AVG(rating) AS average_rating, -- you may remove this line if you don't actually need the average rating
       RANK() OVER (ORDER BY AVG(rating) DESC) AS rank_rate
FROM MovieRating
GROUP BY movie_id
ORDER BY rank_rate;

请参阅 demo 。

您的查询是一个没有 by 子句的没有组的聚合查询，这意味着它在整个表上运行，而不是每个> Movie_id 。此类查询仅返回1行与聚合结果。
当您应用 rank（）窗口函数时，它将在该行上进行操作，而不是在表上。


Answer 2

我认为您的意思是每部电影的平均评分。

您应该使用来使用组，而不是窗口函数：

SELECT movie_id, AVG(rating) AS avg_rating
FROM MovieRating
GROUP BY movie_id
ORDER BY avg_rating DESC;

---

您只有一行的原因是，当您使用像 avg（）avg（）的聚合函数时查询汇总查询。查询的结果是每组一行。

说：

如果您在包含子句的不包含组的语句中使用汇总函数，则相当于将所有行分组。

换句话说，如果您使用 avg（），则整个表被视为一个“组”，但不要通过表达式指定组。因为整个表是一个组，所以结果是一行。

窗口功能定义的窗口与汇总函数定义的组不同。通过聚合减少行后，将应用窗口函数。由于您的结果中只有一组，因此排名为1。