PostgreSQL如何统计两表逗号分隔演员值按年取Top10

首先确认前提：假设两张结构一致的表名为table_1、table_2，表中存储年份的字段为year，存储逗号分隔演员阵容的字段为cast，实际使用时替换成你自己的表名、字段名即可。

核心实现SQL（全量统计Top10演员） #

WITH merged_data AS (
    -- 合并两张表的全量数据，注意用UNION ALL保留所有重复记录，避免去重导致计数错误
    SELECT year, cast FROM table_1
    UNION ALL
    SELECT year, cast FROM table_2
),
unfold_actor AS (
    -- 拆分逗号分隔的演员字符串为独立行，统一去除姓名前后多余空格
    SELECT
        year,
        trim(unnest(string_to_array(cast, ','))) AS actor
    FROM merged_data
    -- 过滤空的cast字段避免无效计算
    WHERE cast IS NOT NULL AND length(trim(cast)) > 0
)
-- 分组计数排序取前10
SELECT
    actor AS actor_name,
    COUNT(*) AS appear_times
FROM unfold_actor
-- 过滤拆分后产生的空演员名
WHERE length(actor) > 0
GROUP BY actor
ORDER BY appear_times DESC, actor
LIMIT 10;

关键逻辑说明 #

• 合并表必须用UNION ALL：如果用UNION会自动对两张表的重复行做去重，最终统计的出现次数会比实际值低。
• 字符串拆分用PostgreSQL内置的string_to_array+unnest组合：不需要额外安装扩展，原生支持把逗号分隔的字符串拆成数组后展开为多行，每个演员对应一条独立记录。
• 统一加trim()处理：你之前Python跑出的结果里大量演员名前置空格，就是拆分后没做首尾空格清理导致的，同个演员如果前后带空格会被识别为不同值，直接影响统计准确性。
• 多层空值校验：过滤掉cast字段为空、拆分后演员名为空的无效记录，避免统计结果出现空值行。

按年份维度细分统计的扩展写法 #

如果需要按年份分别统计每年的高频演员，只需要加窗口函数做分组排名即可：

WITH merged_data AS (
    SELECT year, cast FROM table_1
    UNION ALL
    SELECT year, cast FROM table_2
),
unfold_actor AS (
    SELECT
        year,
        trim(unnest(string_to_array(cast, ','))) AS actor
    FROM merged_data
    WHERE cast IS NOT NULL AND length(trim(cast)) > 0
),
actor_count AS (
    SELECT
        year,
        actor,
        COUNT(*) AS appear_times,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY COUNT(*) DESC, actor) AS rk
    FROM unfold_actor
    WHERE length(actor) > 0
    GROUP BY year, actor
)
SELECT year, actor, appear_times
FROM actor_count
WHERE rk <= 10
ORDER BY year DESC, appear_times DESC;

注意：如果你的PostgreSQL版本低于12，unnest的用法完全兼容，不需要做语法调整。如果cast字段里的逗号是中文逗号，把string_to_array里的分隔符换成'，'即可。

内容的提问来源于stack exchange，提问作者Tomás Jullier