Oracle中实现两表列间80%字符匹配的查询方法求助

Got it, let's break down how to solve this problem where you need to match records from two tables based on an 80% character similarity threshold between colA (tableA) and colB (tableB). Here's how to approach it depending on your database system:

Core Idea #

We'll calculate the similarity score between pairs of strings, then filter for scores ≥ 80%. The most common way to measure string similarity is using the Levenshtein Distance (edit distance) or trigram-based similarity. The formula for similarity using Levenshtein is:

Similarity (%) = (1 - (Levenshtein Distance / Length of Longest String)) * 100

1. MySQL/MariaDB #

MySQL doesn't have a built-in Levenshtein function, so we'll first define one, then run our query.

Step 1: Create the Levenshtein Distance Function

DELIMITER //
CREATE FUNCTION levenshtein(s1 VARCHAR(255), s2 VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INT;
    DECLARE s1_char CHAR;
    DECLARE cv0, cv1 VARBINARY(256);
    
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2);
    SET cv1 = 0x00;
    FOR i FROM 1 TO s2_len DO
        SET cv1 = CONCAT(cv1, UNHEX(HEX(i)));
    END FOR;
    
    FOR i FROM 1 TO s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1);
        SET c = i;
        SET cv0 = UNHEX(HEX(i));
        FOR j FROM 1 TO s2_len DO
            SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10);
            IF s1_char = SUBSTRING(s2, j, 1) THEN
                SET c = c_temp;
            ELSE
                SET c = 1 + LEAST(c, c_temp, CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10));
            END IF;
            SET cv0 = CONCAT(cv0, UNHEX(HEX(c)));
        END FOR;
        SET cv1 = cv0;
    END FOR;
    
    RETURN CONV(HEX(SUBSTRING(cv1, s2_len+1, 1)), 16, 10);
END//
DELIMITER ;

Step 2: Run the Similarity Query

SELECT a.colA, b.colB
FROM tableA a
JOIN tableB b 
  ON (1 - levenshtein(a.colA, b.colB) / GREATEST(CHAR_LENGTH(a.colA), CHAR_LENGTH(b.colB))) * 100 >= 80;

2. PostgreSQL #

PostgreSQL makes this easier with the pg_trgm extension, which includes a built-in similarity() function that returns a score between 0 and 1.

Step 1: Enable the pg_trgm Extension

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Step 2: Run the Query

SELECT a.colA, b.colB
FROM tableA a
JOIN tableB b 
  ON similarity(a.colA, b.colB) >= 0.8; -- 0.8 = 80% similarity

Pro tip: For large datasets, add a trigram index to speed up matches:

CREATE INDEX idx_colA_trgm ON tableA USING gin (colA gin_trgm_ops);
CREATE INDEX idx_colB_trgm ON tableB USING gin (colB gin_trgm_ops);

3. SQL Server #

SQL Server 2017+ has a STRING_SIMILARITY function (part of Machine Learning Services), but if that's unavailable, you can implement Levenshtein manually.

Option A: Using STRING_SIMILARITY

SELECT a.colA, b.colB
FROM tableA a
JOIN tableB b 
  ON STRING_SIMILARITY(a.colA, b.colB) >= 0.8;

Option B: Custom Levenshtein Function

CREATE FUNCTION dbo.Levenshtein(@s1 NVARCHAR(4000), @s2 NVARCHAR(4000))
RETURNS INT
AS
BEGIN
    DECLARE @len1 INT = LEN(@s1), @len2 INT = LEN(@s2);
    DECLARE @d TABLE (i INT, j INT, val INT);
    
    INSERT INTO @d VALUES (0, 0, 0);
    DECLARE @i INT = 1;
    WHILE @i <= @len1 BEGIN
        INSERT INTO @d VALUES (@i, 0, @i);
        SET @i = @i + 1;
    END;
    
    DECLARE @j INT = 1;
    WHILE @j <= @len2 BEGIN
        INSERT INTO @d VALUES (0, @j, @j);
        SET @j = @j + 1;
    END;
    
    SET @i = 1;
    WHILE @i <= @len1 BEGIN
        SET @j = 1;
        WHILE @j <= @len2 BEGIN
            INSERT INTO @d VALUES (@i, @j, 
                CASE WHEN SUBSTRING(@s1, @i, 1) = SUBSTRING(@s2, @j, 1)
                     THEN (SELECT val FROM @d WHERE i = @i-1 AND j = @j-1)
                     ELSE 1 + (SELECT MIN(val) FROM (VALUES 
                         ((SELECT val FROM @d WHERE i = @i-1 AND j = @j)),
                         ((SELECT val FROM @d WHERE i = @i AND j = @j-1)),
                         ((SELECT val FROM @d WHERE i = @i-1 AND j = @j-1))
                     ) AS temp(v))
                END);
            SET @j = @j + 1;
        END;
        SET @i = @i + 1;
    END;
    
    RETURN (SELECT val FROM @d WHERE i = @len1 AND j = @len2);
END;

Then run the query:

SELECT a.colA, b.colB
FROM tableA a
JOIN tableB b 
  ON (1.0 - dbo.Levenshtein(a.colA, b.colB) / GREATEST(LEN(a.colA), LEN(b.colB))) * 100 >= 80;

Important Notes #

• Performance: If your tables are large, a full cartesian join will be slow. Add filters first (e.g., only match strings where length differs by ≤20%) to reduce the number of comparisons.
• Special Characters: If colB has wildcards like * (as in your example), clean the strings first using REPLACE(b.colB, '*', '') before calculating similarity.

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