按国家统计最高均分电影类型的SQL查询问题排查

原代码问题 #

• 语法/拼写错误
  • leftJOIN缺少空格，正确写法是LEFT JOIN
  • 表名前后不统一：评分表一会写movirates一会写movratings，电影类型关联表一会写movgenres一会写movenres，执行时会直接报表不存在错误
  • 关联逻辑可能有误：你用users.city关联国家表的c.code，如果users.city存的是城市编码不是国家编码，这里需要先关联城市表再关联国家表，不然关联结果会出错，可根据实际表结构调整
• 核心逻辑缺项：你现在按国家+类型分组，只能拿到每个国家下所有类型的评分统计结果，没有做「筛选每个国家平均评分最高的类型」这一步，这就是你没理清的实现环节。

修正方法 #

用窗口函数RANK()实现分组内排序取Top1：先计算每个国家各类型的聚合评分，再按国家分区、平均评分降序打排名，最后取排名为1的记录即可。如果有多个类型平均分并列第一，RANK()会全部返回；如果需要每个国家只返回唯一一条记录，把RANK()换成ROW_NUMBER()即可。

修正后代码：

WITH genre_stats AS (
    SELECT 
        c.code AS c_CODE, 
        menres.genre AS GENRE, 
        AVG(RATE) AS AVERAGE_rate,
        MAX(RATE) AS MAXIMUM_rate, 
        MIN(RATE) AS MINIMUM_rate
    FROM movratings
    LEFT JOIN movgenres ON movgenres.movieid = movratings.movieid
    LEFT JOIN users ON users.userid = movratings.userid
    LEFT JOIN c ON c.code = users.city
    LEFT JOIN menres ON movgenres.genreid = menres.code
    GROUP BY menres.genre, c.code
)
SELECT c_CODE, GENRE, AVERAGE_rate, MAXIMUM_rate, MINIMUM_rate
FROM (
    SELECT *, RANK() OVER(PARTITION BY c_CODE ORDER BY AVERAGE_rate DESC) AS rnk
    FROM genre_stats
) t
WHERE rnk = 1
ORDER BY c_CODE ASC;

如果你的数据库版本不支持CTE写法，把CTE部分替换为普通子查询嵌套即可。

内容的提问来源于stack exchange，提问作者user17386361