SQL中如何对数组类型列应用MAX()函数取各genre最高评分影片

因为genres是数组类型，无法直接对数组内的单个元素分组聚合，需要先把数组拆分为单个体裁对应的明细行，再做聚合计算，步骤如下：

• 用unnest()表函数将数组类型的genres列展开，让每个体裁单独对应一条影片记录，同时过滤掉imdb_score为空的无效数据
• 按拆分后的单个体裁分组，用MAX()函数计算每个体裁对应的最高imdb_score
• 将分组得到的最高评分结果和展开后的明细数据关联，匹配到对应最高评分的影片标题

通用写法（兼容所有PostgreSQL版本，支持并列最高评分的影片全部返回） #

-- 假设存储影片数据的表名为movies
WITH expanded_genres AS (
    SELECT
        title,
        unnest(genres) AS single_genre,
        imdb_score
    FROM movies
    WHERE imdb_score IS NOT NULL
),
genre_top_score AS (
    SELECT
        single_genre,
        MAX(imdb_score) AS highest_score
    FROM expanded_genres
    GROUP BY single_genre
)
SELECT
    gs.single_genre AS 体裁,
    gs.highest_score AS 最高imdb评分,
    eg.title AS 对应影片标题
FROM genre_top_score gs
JOIN expanded_genres eg
  ON gs.single_genre = eg.single_genre
 AND gs.highest_score = eg.imdb_score
ORDER BY gs.single_genre;

基于你给出的示例数据，该查询返回结果如下：

注：原数据中documentation体裁对应的影片评分为NULL，会被前置过滤条件排除，不会出现在最终结果中。

简化写法（PostgreSQL专属，仅返回每个体裁下的一部最高评分影片） #

如果不需要保留并列最高评分的所有影片，可以用PostgreSQL的DISTINCT ON语法简化查询，不需要写多层CTE：

SELECT DISTINCT ON (single_genre)
    unnest(genres) AS single_genre,
    imdb_score AS highest_score,
    title
FROM movies
WHERE imdb_score IS NOT NULL
ORDER BY single_genre, imdb_score DESC;

该语法会按single_genre去重，保留ORDER BY排序后每组的第一条记录，也就是每个体裁下评分最高的影片；如果同体裁有多个评分相同的最高评分影片，会按排序规则取排在最前的一条。

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