实现News_Articles表Headline列词频、唯一ID出现数及总次数统计的查询

以下是基于PostgreSQL的实现代码，适配你提出的所有需求：

WITH split_words AS (
    -- 拆分标题为单个单词，并做基础清洗
    SELECT 
        ID,
        -- 处理缩写：去掉单引号及后面内容 + 移除非字母字符 + 转小写
        LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_SPLIT_TO_TABLE(Headline, '\s+'), -- 按空格拆分单词
                    '''.*$', '' -- 去掉单引号及后缀，处理Today's这类缩写
                ),
                '[^a-zA-Z]', '' -- 移除所有非字母字符，比如标点、感叹号等
            )
        ) AS word
    FROM News_Articles
),
filtered_words AS (
    -- 过滤空值和停用词
    SELECT ID, word
    FROM split_words
    WHERE 
        word != ''
        -- 手动添加需要过滤的停用词，有内置停用词表可替换为关联过滤
        AND word NOT IN ('the', 'a', 'an', 'to', 'of', 'is', 'in')
)
-- 统计结果
SELECT 
    INITCAP(word) AS Word, -- 首字母大写输出，和示例格式匹配
    COUNT(DISTINCT ID) AS Unique_Count,
    COUNT(*) AS Total_Count
FROM filtered_words
GROUP BY word
ORDER BY Total_Count DESC;

需求适配说明 #

• 缩写处理：通过REGEXP_REPLACE(word, '''.*$', '')实现，自动将Today's截断为Today
• 停用词过滤：代码中使用WHERE子句手动过滤，如果你所用的数据库有内置停用词库（比如PostgreSQL的ts_stopwords表），可以直接关联停用词表过滤，效率更高
• 大小写统一：所有单词先统一转小写处理，避免大小写导致的统计误差，输出时用INITCAP转回首字母大写匹配示例效果

MySQL 8.0+适配版本 #

如果你使用的是MySQL，可以用递归CTE实现单词拆分，代码如下：

WITH RECURSIVE split_words AS (
    SELECT 
        ID,
        Headline,
        1 AS pos,
        LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(SUBSTRING_INDEX(Headline, ' ', 1), '''.*$', ''),
                '[^a-zA-Z]', ''
            )
        ) AS word
    FROM News_Articles
    UNION ALL
    SELECT 
        ID,
        Headline,
        pos + 1,
        LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(Headline, ' ', pos + 1), ' ', -1), '''.*$', ''),
                '[^a-zA-Z]', ''
            )
        ) AS word
    FROM split_words
    WHERE SUBSTRING_INDEX(Headline, ' ', pos + 1) != Headline
)
SELECT 
    CONCAT(UPPER(LEFT(word, 1)), LOWER(SUBSTRING(word, 2))) AS Word,
    COUNT(DISTINCT ID) AS Unique_Count,
    COUNT(*) AS Total_Count
FROM split_words
WHERE 
    word != ''
    AND word NOT IN ('the', 'a', 'an', 'to', 'of', 'is', 'in')
GROUP BY word
ORDER BY Total_Count DESC;

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