SQL查询优化：按国家获取最常见电影类型及对应统计数据

我需要实现一条SQL查询，统计每个国家的电影总数（amountMovies）、平均评分（avg rank），以及该国家最常见的电影类型（genre）。目前编写的查询语句如下：

select count(c.filmid) as amountMovies, c.country as country, avg(r.rank) as avg rank, fgt.genre
from filmcountry c
full join filmrating r on c.filmid = r.filmid
left join (
select count(co.filmid) as no, co.country, fg.genre from filmcountry co 
full join filmgenre fg on co.filmid = fg.filmid
group by co.country, fg.genre
order by co.country, no desc)
fgt on c.country = fgt.country 
group by c.country, fgt.genre 
order by c.country;

当前查询已能获取对应数据，但无法仅保留每个国家最常见类型的单条记录，而是返回该国家所有电影类型的记录（如下方实际输出所示）。我期望每个国家仅显示一条对应最常见电影类型的记录（如下方预期输出所示），请帮忙优化该SQL语句。

预期输出 #

amountMovies  |            country             |        avg rank        |    genre    
--------+--------------------------------+--------------------+-------------
     29 | Afghanistan                    | 3.9962963086587413 | Adventure

  874 | Albania                        |  7.149999976158142 | Music

实际输出 #

amountMovies  |            country             |        avg rank        |    genre    
--------+--------------------------------+--------------------+-------------
     29 | Afghanistan                    | 3.9962963086587413 | Adventure
     29 | Afghanistan                    | 3.9962963086587413 | Music
     29 | Afghanistan                    | 3.9962963086587413 | Short
     29 | Afghanistan                    | 3.9962963086587413 | Action
     29 | Afghanistan                    | 3.9962963086587413 | Biography
     29 | Afghanistan                    | 3.9962963086587413 | Documentary
     29 | Afghanistan                    | 3.9962963086587413 | War
     29 | Afghanistan                    | 3.9962963086587413 | Drama
     29 | Afghanistan                    | 3.9962963086587413 | 
    874 | Albania                        |  7.149999976158142 | Music
    874 | Albania                        |  7.149999976158142 | Documentary
    874 | Albania                        |  7.149999976158142 | Drama
    874 | Albania                        |  7.149999976158142 | 
    874 | Albania                        |  7.149999976158142 | Family
    874 | Albania                        |  7.149999976158142 | Thriller

优化后的SQL语句 #

可以通过**窗口函数ROW_NUMBER()**筛选每个国家最常见的电影类型，具体实现如下：

WITH country_movie_stats AS (
    -- 统计每个国家的电影总数和平均评分
    SELECT 
        c.country,
        COUNT(c.filmid) AS amountMovies,
        AVG(r.rank) AS avg_rank
    FROM filmcountry c
    FULL JOIN filmrating r ON c.filmid = r.filmid
    GROUP BY c.country
),
country_genre_counts AS (
    -- 统计每个国家各类型的电影数量，并按数量排序
    SELECT 
        co.country,
        fg.genre,
        COUNT(co.filmid) AS genre_count,
        -- 按国家分组，按类型数量降序排名，数量相同则按genre排序（可选）
        ROW_NUMBER() OVER (PARTITION BY co.country ORDER BY COUNT(co.filmid) DESC, fg.genre) AS rn
    FROM filmcountry co
    FULL JOIN filmgenre fg ON co.filmid = fg.filmid
    GROUP BY co.country, fg.genre
)
-- 关联基础统计数据和最常见类型
SELECT 
    cms.amountMovies,
    cms.country,
    cms.avg_rank,
    cgc.genre
FROM country_movie_stats cms
LEFT JOIN country_genre_counts cgc 
    ON cms.country = cgc.country 
    AND cgc.rn = 1  -- 仅保留每个国家排名第一的类型
ORDER BY cms.country;

优化说明 #

1. 拆分逻辑：将统计分为两个CTE（公共表表达式），分别处理基础数据和类型统计，逻辑更清晰
2. 窗口函数筛选：用ROW_NUMBER()给每个国家的电影类型按数量降序排名，取排名为1的记录，确保每个国家只保留最常见的类型
3. 避免冗余关联：原查询中多次关联导致重复输出，优化后仅关联一次筛选后的最常见类型，避免多余记录

内容的提问来源于stack exchange，提问作者Victoria Ovedie Chruickshank L