返回基于两列sql的最大值

Question

假设我有一个模式如下的表:

User_id | Rating | Movie_id | Movie_Popularity
1          2         34          100
1          1         36          120
1          2         39          110 
2          3         34          105

我想让每个用户找到他的最高和最低评分，如果平局，选择最受欢迎的电影(假设这些都是不同的)。

在这个例子中，结果将是:

User_id | Max_rating | Min_rating | Max_rating_movie_id | Min_rating_movie_id
1          2         1                 39                  36
2          3         3                 34                  34

我尝试用评级和电影受欢迎程度对这个表进行排序，但我只能提取最大值或最小值。

如何使用sql来完成?

任何建议都会有帮助的。

Thanks in advance

Answer 1

嗨，你可以使用row_number() over () partition by ..按…订购

这是一个查询示例:

Select user_id, movie_id, row_number() over (partition by user_id order by rating desc) as max_rating, row_number() over (partition by user_id order by rating asc) as min_rating

然后你可以使用它作为子查询和选择的地方，在max_rating和min_rating等于1。

Answer 2

一种方法使用带有聚合的窗口函数:

select user_id, max(Movie_Popularity), min(Movie_Popularity),
max(case when seqnum_desc = 1 then movie_id end),
max(case when seqnum_asc = 1 then movie_id end)
from (select t.*,
row_number() over (partition by user_id order by Movie_Popularity asc) as seqnum_asc,
row_number() over (partition by user_id order by Movie_Popularity desc) as seqnum_desc
from t
) t
group by user_id;

Answer 3

不需要子查询。
您可以使用窗口函数MIN(),MAX()和FIRST_VALUE():

SELECT DISTINCT User_id,
MAX(Rating) OVER (PARTITION BY User_id) Max_rating,
MIN(Rating) OVER (PARTITION BY User_id) Min_rating,
FIRST_VALUE(Movie_id) OVER (PARTITION BY User_id ORDER BY Rating DESC, Movie_Popularity DESC) Max_rating_movie_id,
FIRST_VALUE(Movie_id) OVER (PARTITION BY User_id ORDER BY Rating, Movie_Popularity DESC) Min_rating_movie_id
FROM tablename 

Results:

<表类>User_idMax_ratingMin_ratingMax_rating_movie_idMin_rating_movie_idtbody><<tr>12139362333434