SQL实现按下划线拆分文本的70%相似度匹配查询构建
匹配逻辑实现方案
核心规则说明
首先明确可落地的匹配率计算口径(可根据业务灵活调整):
- 拆分规则:将字符串按下划线
_拆分为独立片段,自动过滤连续下划线产生的空片段 - 匹配率公式:重合片段数 / MAX(目标串总片段数, 当前记录Value总片段数),即两个片段集合的重合度
- 两类命中条件:
- 直接匹配:Value与目标字符串完全相等(若需模糊检索,可换成
Value LIKE CONCAT('%', 目标串, '%')) - 拆分匹配:匹配率≥70%
- 直接匹配:Value与目标字符串完全相等(若需模糊检索,可换成
通用实现思路
- 预拆分目标字符串为独立片段集合,统计目标串总片段数
- 对每条记录的Value列做相同拆分,统计单条记录的总片段数
- 统计目标片段与单条记录片段的重合数量
- 按上述公式计算匹配率,筛选符合命中条件的记录
分数据库代码示例
MySQL 8.0+ 实现
依赖递归CTE做字符串拆分,直接替换表名、目标字符串后即可运行
-- 定义目标字符串 SET @target_str = '008_ADL_A0_S81_PCIE_L2_B2B_Cycling_Failure_Phystatus_PP'; WITH RECURSIVE target_parts AS ( -- 拆分目标串首个片段 SELECT SUBSTRING_INDEX(@target_str, '_', 1) AS part, SUBSTRING(@target_str, LENGTH(SUBSTRING_INDEX(@target_str, '_', 1)) + 2) AS remaining_str UNION ALL -- 递归拆分剩余片段 SELECT SUBSTRING_INDEX(remaining_str, '_', 1), SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, '_', 1)) + 2) FROM target_parts WHERE remaining_str != '' ), target_meta AS ( -- 过滤空片段,统计目标串总片段数 SELECT COUNT(*) AS total_target_parts FROM target_parts WHERE part != '' ), table_parts AS ( -- 拆分表中所有记录的Value字段 SELECT t.ID, t.Value, SUBSTRING_INDEX(t.Value, '_', 1) AS part, SUBSTRING(t.Value, LENGTH(SUBSTRING_INDEX(t.Value, '_', 1)) + 2) AS remaining_str FROM your_table t UNION ALL SELECT ID, Value, SUBSTRING_INDEX(remaining_str, '_', 1), SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, '_', 1)) + 2) FROM table_parts WHERE remaining_str != '' ), table_meta AS ( -- 过滤空片段,统计单条记录总片段数 SELECT ID, COUNT(*) AS total_row_parts FROM table_parts WHERE part != '' GROUP BY ID ), match_stats AS ( SELECT tp.ID, tp.Value, COUNT(DISTINCT tp.part) AS matched_parts, tm.total_target_parts, rm.total_row_parts, -- 计算匹配率,保留2位小数 ROUND(COUNT(DISTINCT tp.part) / GREATEST(tm.total_target_parts, rm.total_row_parts), 2) AS match_rate FROM table_parts tp CROSS JOIN target_meta tm -- 关联统计重合片段 INNER JOIN target_parts tg ON tp.part = tg.part AND tg.part != '' INNER JOIN table_meta rm ON tp.ID = rm.ID GROUP BY tp.ID, tp.Value, tm.total_target_parts, rm.total_row_parts ) -- 输出命中的记录 SELECT DISTINCT m.ID, m.Value, m.match_rate FROM match_stats m WHERE -- 直接匹配条件 m.Value = @target_str -- 拆分匹配条件 OR m.match_rate >= 0.7;
PostgreSQL 实现
原生支持字符串数组拆分,语法更简洁
-- 定义目标字符串 WITH target_config AS ( SELECT '008_ADL_A0_S81_PCIE_L2_B2B_Cycling_Failure_Phystatus_PP' AS target_str ), target_meta AS ( SELECT -- 拆分目标串,过滤空片段 ARRAY_REMOVE(STRING_TO_ARRAY((SELECT target_str FROM target_config), '_'), NULL) AS target_parts, ARRAY_LENGTH(ARRAY_REMOVE(STRING_TO_ARRAY((SELECT target_str FROM target_config), '_'), NULL), 1) AS total_target_parts ), match_stats AS ( SELECT t.ID, t.Value, -- 拆分当前记录Value,过滤空片段 ARRAY_REMOVE(STRING_TO_ARRAY(t.Value, '_'), NULL) AS row_parts, ARRAY_LENGTH(ARRAY_REMOVE(STRING_TO_ARRAY(t.Value, '_'), NULL), 1) AS total_row_parts, -- 统计重合片段数 ( SELECT COUNT(*) FROM UNNEST(ARRAY_REMOVE(STRING_TO_ARRAY(t.Value, '_'), NULL)) row_part INNER JOIN UNNEST((SELECT target_parts FROM target_meta)) target_part ON row_part = target_part ) AS matched_parts, -- 计算匹配率 ROUND( ( SELECT COUNT(*) FROM UNNEST(ARRAY_REMOVE(STRING_TO_ARRAY(t.Value, '_'), NULL)) row_part INNER JOIN UNNEST((SELECT target_parts FROM target_meta)) target_part ON row_part = target_part )::NUMERIC / GREATEST( ARRAY_LENGTH(ARRAY_REMOVE(STRING_TO_ARRAY(t.Value, '_'), NULL), 1), (SELECT total_target_parts FROM target_meta) ), 2 ) AS match_rate FROM your_table t ) SELECT ID, Value, match_rate FROM match_stats WHERE Value = (SELECT target_str FROM target_config) OR match_rate >= 0.7;
注意事项
- 性能优化:如果表数据量超过10万行,实时拆分全表会产生较大性能开销,建议提前将Value的拆分片段存入关联表(表结构:ID、part),并对part字段建索引,查询时直接关联统计重合数即可
- 匹配率调整:如果业务需要以目标串的片段为基准计算占比,可将匹配率公式的分母改为
total_target_parts;如果需要考虑两边片段总数的平均值,可改为(total_target_parts + total_row_parts)/2 - 大小写兼容:如果需要不区分大小写匹配片段,可在拆分片段时统一转小写,比如MySQL的
LOWER(part)、PostgreSQL的LOWER(row_part)
内容的提问来源于stack exchange,提问作者Michael Groiser
相关产品推荐
相关产品推荐

