PostgreSQL中统计字符串列内热门Hashtag的出现次数

我的数据集有一个varchar类型的hashtags列，数据格式如下：

hashtags
1 [#newyears, #christmas, #christmas]
2 [#easter, #newyears, #fourthofjuly]
3 [#valentines, #christmas, #easter]

我已经用以下SQL统计每行的Hashtag数量：

SELECT hashtags, (LENGTH(hashtags) - LENGTH(REPLACE(hashtags, ',', '')) + 1) AS hashtag_count
FROM full_data
ORDER BY hashtag_count DESC NULLS LAST

现在需要统计每个Hashtag的出现次数，返回如下格式的结果：

hashtags     count
christmas     3
newyears      2

请问该如何实现？

核心思路是先把每行的hashtag列表拆分成单独的行，再分组统计每个hashtag的出现次数。以下是不同主流数据库的实现方式：

PostgreSQL #

利用regexp_split_to_table函数拆分字符串，同时清理多余符号：

SELECT
    TRIM(BOTH '[]# ' FROM hashtag) AS hashtags,
    COUNT(*) AS count
FROM full_data,
     regexp_split_to_table(hashtags, ',') AS hashtag
GROUP BY hashtags
ORDER BY count DESC;

• regexp_split_to_table(hashtags, ',')将每行的hashtag按逗号拆分成多行
• TRIM(BOTH '[]# ' FROM hashtag)去除每个hashtag前后的方括号、#号和空格
• 最后分组统计计数并按次数降序排列

MySQL（8.0+） #

通过JSON_TABLE拆分（需先把字符串转成合法JSON格式）：

SELECT
    TRIM(BOTH '#' FROM hashtag) AS hashtags,
    COUNT(*) AS count
FROM full_data,
     JSON_TABLE(
         REPLACE(REPLACE(hashtags, '[', '["'), ']', '"]'),
         '$[*]' COLUMNS (hashtag VARCHAR(255) PATH '$')
     ) AS jt
GROUP BY hashtags
ORDER BY count DESC;

• REPLACE(REPLACE(hashtags, '[', '["'), ']', '"]')把原字符串转换为合法JSON数组
• JSON_TABLE将JSON数组拆分成多行
• 去除#号后分组统计

SQL Server（2016+） #

用STRING_SPLIT函数配合清理操作：

SELECT
    TRIM(BOTH '[]# ' FROM value) AS hashtags,
    COUNT(*) AS count
FROM full_data
CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(hashtags, '[', ''), ']', ''), ',')
GROUP BY hashtags
ORDER BY count DESC;

• 先去除原字符串的方括号，再用STRING_SPLIT按逗号拆分
• 清理拆分后内容的#号和空格，再分组统计

通用兼容方案（适用于多数数据库） #

如果数据库不支持上述拆分函数，可使用递归CTE实现：

WITH recursive_hashtags AS (
    SELECT
        1 AS pos,
        SUBSTRING(hashtags, CHARINDEX('#', hashtags), CHARINDEX(',', hashtags + ',', CHARINDEX('#', hashtags)) - CHARINDEX('#', hashtags)) AS hashtag,
        SUBSTRING(hashtags, CHARINDEX(',', hashtags) + 1, LEN(hashtags)) AS remaining
    FROM full_data
    WHERE hashtags IS NOT NULL AND hashtags != ''
    UNION ALL
    SELECT
        pos + 1,
        SUBSTRING(remaining, CHARINDEX('#', remaining), CHARINDEX(',', remaining + ',', CHARINDEX('#', remaining)) - CHARINDEX('#', remaining)) AS hashtag,
        SUBSTRING(remaining, CHARINDEX(',', remaining) + 1, LEN(remaining)) AS remaining
    FROM recursive_hashtags
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT
    TRIM(hashtag) AS hashtags,
    COUNT(*) AS count
FROM recursive_hashtags
WHERE hashtag IS NOT NULL
GROUP BY hashtags
ORDER BY count DESC;

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