Hitta ungefärliga matchningar med fuzzy-strängfunktioner

12 minuter

Verkliga data matchar sällan perfekt. Kundnamn kan vara felstavade, adresser förkortade på olika sätt eller produktbeskrivningar som anges inkonsekvent. Fuzzy-strängmatchningsfunktioner hjälper dig att hitta poster som är liknande men inte identiska, vilket möjliggör förbättringar av datakvaliteten, dubblettidentifiering och mer flexibla sökfunktioner.

Förstå begrepp om stränglikhet

Fuzzy-matchningsalgoritmer mäter hur lika två strängar är genom att beräkna skillnaderna mellan dem. Två primära metoder används ofta:

Redigeringsavstånd (Levenshtein-avstånd) räknar det minsta antalet åtgärder med en tecken (infogningar, borttagningar, ersättningar) som krävs för att omvandla en sträng till en annan. Lägre värden anger fler liknande strängar.

Likhetspoäng uttrycker relationen mellan strängar som en procentandel eller ett förhållande, där högre värden indikerar större likhet.

Tänk på följande exempel:

"färg" → "färg": redigeringsavstånd = 0 (ingen ändring)
"database" → "databaes": edit distance = 2 (swap 'e' and 's')
"Microsoft" → "Microsft": redigeringsavstånd = 1 (ta bort bokstaven 'o')

Anmärkning

Fuzzy-matchning är beräkningsmässigt dyrt jämfört med exakt matchning. Använd det strategiskt, vanligtvis på kandidatuppsättningar som har förfiltreerats med hjälp av andra kriterier.

Beräkna redigeringsavstånd med `EDIT_DISTANCE`

Funktionen EDIT_DISTANCE returnerar Levenshtein-avståndet mellan två strängar, vilket är det minsta antalet ändringar som krävs för att omvandla en sträng till den andra. Målet är att hitta strängar som är liknande baserat på ett definierat tröskelvärde.

I följande exempel visas hur du använder EDIT_DISTANCE:

SELECT 
    EDIT_DISTANCE('color', 'colour') AS ColorVariant,     -- Returns 1
    EDIT_DISTANCE('database', 'databaes') AS Typo,        -- Returns 2
    EDIT_DISTANCE('SQL Server', 'SQL Server') AS Exact,   -- Returns 0
    EDIT_DISTANCE('hello', 'world') AS Different;         -- Returns 4

Du kan använda EDIT_DISTANCE för att hitta poster som kan vara dubbletter eller matchningar trots små variationer:

-- Find customers with similar names to a search term
DECLARE @searchName NVARCHAR(100) = 'Jon Smith';

SELECT 
    CustomerID,
    FirstName,
    LastName,
    FirstName + ' ' + LastName AS FullName,
    EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName) AS EditDistance
FROM SalesLT.Customer
WHERE EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName) <= 3
ORDER BY EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName);

Du kan också hitta potentiella duplicerade produkter:

-- Find product pairs with similar names
SELECT 
    p1.ProductID AS Product1ID,
    p1.Name AS Product1Name,
    p2.ProductID AS Product2ID,
    p2.Name AS Product2Name,
    EDIT_DISTANCE(p1.Name, p2.Name) AS EditDistance
FROM SalesLT.Product AS p1
INNER JOIN SalesLT.Product AS p2
    ON p1.ProductID < p2.ProductID
WHERE EDIT_DISTANCE(p1.Name, p2.Name) <= 5
ORDER BY EDIT_DISTANCE(p1.Name, p2.Name);

Tips/Råd

Det maximala meningsfulla redigeringsavståndet beror på stränglängden. För korta strängar (5–10 tecken) anger ett redigeringsavstånd på 1–2 likheter. För längre strängar kan du tillåta avstånd på 3–5.

Mät likheten med `EDIT_DISTANCE_SIMILARITY`

EDIT_DISTANCE_SIMILARITY returnerar en normaliserad likhetspoäng mellan 0 och 100, där 100 representerar identiska strängar. Det här procentbaserade måttet är lättare att tolka än råredigeringsavstånd, särskilt när du jämför strängar med olika längder:

SELECT 
    EDIT_DISTANCE_SIMILARITY('color', 'colour') AS ColorSimilarity,     -- ~85
    EDIT_DISTANCE_SIMILARITY('database', 'databaes') AS TypoSimilarity, -- ~75
    EDIT_DISTANCE_SIMILARITY('SQL', 'SQL Server') AS PartialMatch,      -- ~30
    EDIT_DISTANCE_SIMILARITY('hello', 'hello') AS Exact;                -- 100

Du kan använda likhetspoäng för att hitta ungefärliga matchningar med ett tröskelvärde som i följande exempel:

-- Find products similar to a search term (at least 70% similar)
DECLARE @searchTerm NVARCHAR(100) = 'Mountain Bike Frame';

SELECT 
    ProductID,
    Name,
    EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) AS SimilarityScore
FROM SalesLT.Product
WHERE EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) >= 70
ORDER BY EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) DESC;

Beräkna fonetisk likhet med `JARO_WINKLER_DISTANCE`

Algoritmen Jaro-Winkler är särskilt utformad för att jämföra namn och korta strängar. Det ger högre poäng till strängar som matchar från början, vilket gör det särskilt effektivt för personnamn där prefix är mer betydande:

SELECT 
    JARO_WINKLER_DISTANCE('MARTHA', 'MARHTA') AS NameTypo,      -- ~0.96
    JARO_WINKLER_DISTANCE('JONES', 'JOHNSON') AS SimilarNames,  -- ~0.83
    JARO_WINKLER_DISTANCE('JOHN', 'JON') AS NameVariant,        -- ~0.93
    JARO_WINKLER_DISTANCE('SMITH', 'SMYTH') AS SpellingVar;     -- ~0.96

Jaro-Winkler poäng varierar från 0 till 1, där 1 anger identiska strängar. En poäng över 0,9 indikerar vanligtvis en stark matchning för namn.

I följande exempel hittas kunder med namn som liknar en sökinmatning:

-- Find customers with names similar to a search
DECLARE @searchFirst NVARCHAR(50) = 'John';
DECLARE @searchLast NVARCHAR(50) = 'Smythe';

SELECT 
    CustomerID,
    FirstName,
    LastName,
    JARO_WINKLER_DISTANCE(@searchFirst, FirstName) AS FirstNameScore,
    JARO_WINKLER_DISTANCE(@searchLast, LastName) AS LastNameScore,
    (JARO_WINKLER_DISTANCE(@searchFirst, FirstName) + 
     JARO_WINKLER_DISTANCE(@searchLast, LastName)) / 2 AS CombinedScore
FROM SalesLT.Customer
WHERE JARO_WINKLER_DISTANCE(@searchFirst, FirstName) > 0.85
  AND JARO_WINKLER_DISTANCE(@searchLast, LastName) > 0.85
ORDER BY CombinedScore DESC;

Anmärkning

Jaro-Winkler är optimerad för korta strängar som namn. För längre strängar som adresser eller beskrivningar ger EDIT_DISTANCE_SIMILARITY det ofta bättre resultat.

Prestandaöverväganden

Fuzzy-matchningsfunktioner undersöker varje tecken i båda strängarna, vilket gör dem beräkningsintensiva. Exakta strängjämförelser kan stoppas så snart tecknen skiljer sig åt, och indexerade sökningar använder effektiv B-trädbläddering. Däremot måste fuzzy-algoritmer beräkna likhetspoäng tecken för tecken. För en tabell med en miljon rader kan en ooptimerad fuzzy-sökning utföra en miljon likhetsberäkningar, var och en med dussintals teckenjämförelser.

Nyckeln till effektiv fuzzy-matchning är att minska kandidatuppsättningen innan du tillämpar de dyra fuzzy-funktionerna. Använd indexerade kolumner med LIKE mönster, exakta matchningar på relaterade fält eller intervallfilter för att begränsa resultaten först. Använd fuzzy-matchning först när du har den mindre uppsättningen av kandidater.

Följande exempel visar den här progressiva filtreringsmetoden:

-- Not good: Fuzzy match against entire table
SELECT * FROM LargeCustomerTable
WHERE EDIT_DISTANCE_SIMILARITY('John Smith', FullName) > 70;

-- Better: Pre-filter before fuzzy matching
SELECT * FROM LargeCustomerTable
WHERE FullName LIKE 'J%'  -- First letter filter
  AND EDIT_DISTANCE_SIMILARITY('John Smith', FullName) > 70;

-- Best: Use multiple pre-filters
SELECT * FROM LargeCustomerTable
WHERE FirstName LIKE 'Jo%'
  AND LastName LIKE 'Sm%'
  AND JARO_WINKLER_DISTANCE('John', FirstName) > 0.85
  AND JARO_WINKLER_DISTANCE('Smith', LastName) > 0.85;

Viktigt!

Fuzzy-strängmatchningsfunktioner som EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITYoch JARO_WINKLER_DISTANCE är tillgängliga i SQL Server 2025 och senare, Azure SQL Database och SQL-databaser i Microsoft Fabric. Kontrollera din plattforms dokumentation för specifik information om funktionens tillgänglighet.

Mer information om fuzzy-strängmatchning finns i Strängfunktioner.

Feedback

Var den här sidan till hjälp?