MySQL/PostgreSQL统计逗号分隔情感字段值的查询方法

MySQL 实现方式 #

MySQL没有内置的STRING_SPLIT函数，可通过递归CTE（公共表表达式）完成字符串拆分，无需依赖额外数字表：

WITH RECURSIVE split_emotions AS (
    SELECT
        TRIM(SUBSTRING_INDEX(emotions, ',', 1)) AS emotion,
        TRIM(SUBSTRING(emotions, LOCATE(',', emotions) + 1)) AS remaining_emotions
    FROM feedback
    WHERE emotions IS NOT NULL AND emotions != ''
    UNION ALL
    SELECT
        TRIM(SUBSTRING_INDEX(remaining_emotions, ',', 1)) AS emotion,
        TRIM(SUBSTRING(remaining_emotions, LOCATE(',', remaining_emotions) + 1)) AS remaining_emotions
    FROM split_emotions
    WHERE remaining_emotions IS NOT NULL AND remaining_emotions != ''
)
SELECT emotion, COUNT(*) AS occurrence_count
FROM split_emotions
WHERE emotion != ''
GROUP BY emotion
ORDER BY occurrence_count DESC;

关键说明： #

• 递归CTEsplit_emotions先拆分每条记录的首个情感值，再递归处理剩余字符串，直到剩余部分为空。
• TRIM函数用于清理情感值前后的空格，避免 sad或happy 这类带空格的值被统计为不同条目。
• 最后分组统计时过滤空字符串，排除无效拆分结果。

PostgreSQL 实现方式 #

PostgreSQL内置string_to_array和unnest函数，可更简洁地完成拆分统计：

SELECT
    TRIM(emotion) AS emotion,
    COUNT(*) AS occurrence_count
FROM feedback,
     unnest(string_to_array(emotions, ',')) AS emotion
WHERE emotions IS NOT NULL AND emotions != ''
GROUP BY TRIM(emotion)
ORDER BY occurrence_count DESC;

关键说明： #

• string_to_array(emotions, ',')将逗号分隔的字符串转换为数组。
• unnest函数把数组元素拆分为独立行，实现类似STRING_SPLIT的效果。
• 用TRIM统一清理空格，保证统计的准确性。

若使用PostgreSQL 9.3及以上版本，也可通过LATERAL关联写出更清晰的逻辑：

SELECT
    TRIM(e.emotion) AS emotion,
    COUNT(*) AS occurrence_count
FROM feedback
LEFT JOIN LATERAL unnest(string_to_array(emotions, ',')) AS e(emotion) ON true
WHERE emotions IS NOT NULL AND emotions != ''
GROUP BY TRIM(e.emotion)
ORDER BY occurrence_count DESC;

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