PostgreSQL中用正则/CASE语句聚合相似字符串的Count(*)统计

针对你遇到的合作艺人条目无法合并统计的问题，可以通过正则表达式匹配+标准化艺人名的方式解决，以下是具体实现方案：

1. 单个目标艺人的快速处理 #

如果只需要合并"The Weeknd"的所有相关条目，直接修改原查询，用CASE结合正则表达式匹配包含该艺人的所有条目，统一归为"The Weeknd"统计：

SELECT 
    year,
    -- 匹配所有包含"The Weeknd"的艺人条目，统一标准化为"The Weeknd"
    CASE
        WHEN artist ~* '\mThe Weeknd\M' THEN 'The Weeknd'
        ELSE artist
    END AS normalized_artist,
    COUNT(*) AS artist_count
FROM billboard_chart
GROUP BY year, normalized_artist
ORDER BY year, artist_count DESC;

正则说明： #

• ~*：PostgreSQL中不区分大小写的正则匹配运算符，确保不会因为大小写差异漏匹配
• \m 和 \M：分别是词首和词尾锚点，确保匹配的是完整的"The Weeknd"单词，避免误匹配类似"The Weekndxyz"这类错误条目

2. 多艺人批量处理方案 #

如果需要同时处理多个艺人（比如Ariana Grande、Daft Punk等），可以扩展CASE语句，或者用更灵活的艺人映射表方案：

步骤1：创建艺人映射表 #

CREATE TABLE artist_mappings (
    original_pattern TEXT, -- 匹配原艺名的正则表达式
    normalized_name TEXT   -- 统一后的艺人名
);

-- 插入需要合并的艺人规则
INSERT INTO artist_mappings VALUES
('\mThe Weeknd\M', 'The Weeknd'),
('\mAriana Grande\M', 'Ariana Grande'),
('\mDaft Punk\M', 'Daft Punk');

步骤2：关联映射表统计 #

SELECT 
    bc.year,
    -- 优先使用映射表的标准化艺名，没有匹配的保留原艺名
    COALESCE(am.normalized_name, bc.artist) AS normalized_artist,
    COUNT(*) AS artist_count
FROM billboard_chart bc
LEFT JOIN artist_mappings am ON bc.artist ~* am.original_pattern
GROUP BY bc.year, normalized_artist
ORDER BY bc.year, artist_count DESC;

这种方式的优势是后续新增需要合并的艺人时，只需往映射表插入数据，无需修改统计SQL，维护更方便。

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