You need to enable JavaScript to run this app. SQL实现按下划线拆分文本的70%相似度匹配查询构建
优惠活动
大模型
产品
解决方案
定价
更多

SQL实现按下划线拆分文本的70%相似度匹配查询构建

匹配逻辑实现方案

核心规则说明

首先明确可落地的匹配率计算口径(可根据业务灵活调整):

  • 拆分规则:将字符串按下划线_拆分为独立片段,自动过滤连续下划线产生的空片段
  • 匹配率公式:重合片段数 / MAX(目标串总片段数, 当前记录Value总片段数),即两个片段集合的重合度
  • 两类命中条件:
    • 直接匹配:Value与目标字符串完全相等(若需模糊检索,可换成Value LIKE CONCAT('%', 目标串, '%'))
    • 拆分匹配:匹配率≥70%

通用实现思路

  1. 预拆分目标字符串为独立片段集合,统计目标串总片段数
  2. 对每条记录的Value列做相同拆分,统计单条记录的总片段数
  3. 统计目标片段与单条记录片段的重合数量
  4. 按上述公式计算匹配率,筛选符合命中条件的记录

分数据库代码示例

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.20 23:54:10