如何通过SQL清洗列数据:过滤关键词次级内容保留主关键词
你想用CASE语句做逻辑判断的思路是可行的,但不建议用CASE硬编码匹配所有关键词的具体值——这种写法只要后续新增一个带次级分类的关键词,就得手动改SQL,维护成本极高。
核心处理逻辑非常简单:对所有带英文冒号的关键词,只保留冒号之前的主分类内容;不带冒号的关键词直接保留原内容。
为什么不推荐硬编码CASE匹配
如果按硬编码思路写,SQL会变成这样:
SELECT CASE WHEN Keywords = 'Policy/Ethics: Employment' THEN 'Policy/Ethics' WHEN Keywords = 'Policy/Ethics: Business' THEN 'Policy/Ethics' -- 后续每新增一个次级分类,就要在这里加一行判断 ELSE Keywords END AS main_keyword, COUNT(*) AS keyword_frequency FROM your_table GROUP BY main_keyword
这种写法扩展性极差,完全可以靠通用的字符串截取逻辑替代。
通用可落地的实现写法
所有写法都遵循同一个逻辑:定位字符串中第一个英文冒号的位置,从开头截断到冒号前;如果字符串里没有冒号,就直接返回原内容。同时搭配TRIM函数清理首尾多余空格,避免因空格差异把同一关键词拆成不同分组。
MySQL 适配写法
直接用内置的SUBSTRING_INDEX函数完成截断,不需要额外写判断:
SELECT TRIM(SUBSTRING_INDEX(Keywords, ':', 1)) AS main_keyword, COUNT(*) AS keyword_frequency FROM your_table_name GROUP BY main_keyword ORDER BY keyword_frequency DESC;
SQL Server 适配写法
这里就可以用到你最初想到的CASE语句,用来做“是否存在冒号”的逻辑兼容,避免空值或报错:
SELECT TRIM( CASE -- 如果字符串里存在冒号,就截取到冒号前面的部分 WHEN CHARINDEX(':', Keywords) > 0 THEN LEFT(Keywords, CHARINDEX(':', Keywords) - 1) -- 没有冒号就保留原内容 ELSE Keywords END ) AS main_keyword, COUNT(*) AS keyword_frequency FROM your_table_name GROUP BY main_keyword ORDER BY keyword_frequency DESC;
PostgreSQL 适配写法
用内置的SPLIT_PART函数直接取分隔符拆分后的第一部分即可:
SELECT TRIM(SPLIT_PART(Keywords, ':', 1)) AS main_keyword, COUNT(*) AS keyword_frequency FROM your_table_name GROUP BY main_keyword ORDER BY keyword_frequency DESC;
Oracle 适配写法
和SQL Server逻辑一致,用CASE判断冒号是否存在,配合字符串截取函数使用:
SELECT TRIM( CASE WHEN INSTR(Keywords, ':') > 0 THEN SUBSTR(Keywords, 1, INSTR(Keywords, ':') - 1) ELSE Keywords END ) AS main_keyword, COUNT(*) AS keyword_frequency FROM your_table_name GROUP BY main_keyword ORDER BY keyword_frequency DESC;
效果对照(基于你提供的样例)
原始Keywords值 → 处理后得到的主关键词
Policy/Ethics → Policy/Ethics
Policy/Ethics → Policy/Ethics
Policy/Ethics: Employment → Policy/Ethics
Policy/Ethics → Policy/Ethics
Policy/Ethics: Business → Policy/Ethics
最终分组统计结果为Policy/Ethics共出现5次,完全符合筛选需求。
内容的提问来源于stack exchange,提问作者Jennylj78

