問題描述

問題:需要 SQL 函數使用 Levenshtein 算法返回最低"匹配值.

代碼:

返回:

我想退貨:

說明:結果是 Levenshtein 算法的最低"值.有兩行 Levenshtein 分數相同 The Apple Farm 和 Ride-a-Long Bikes，在這種情況下，任何值都可以，只要它是一種價值.

參考資料:

SQL 模糊連接 - MSSQL

http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx

如果您按潛在客戶進行分區并使用 ValueLev 對結果進行排序，則可以使用 CTE 來獲得您想要的結果:

;with CTE AS(SELECT RANK() OVER (PARTITION BY a.Customer ORDER BY dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) ASC) AS RowNbr,一個客戶，出價，b.客戶作為客戶，dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) 作為 ValueLev來自#POTENTIALCUSTOMERS aLEFT JOIN #ExistingCustomers b ON dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) <15)選擇客戶，MIN(ID) 作為 ID，MIN(cust) AS cust,價值等級從 CTE哪里 CTE.RowNbr = 1按客戶分組，ValueLev

由于您不介意在重復 ValueLev 的情況下返回哪個結果，請使用 GROUP BY 和 MIN 來縮放結果每個潛在客戶最多一個.

輸出:

Customer ID cust ValueLev糖果店 1004 糖果店 0雜貨鎮 1003 雜貨鎮 0Eds 理發店 1002 Ed 的理發店 1勤雜工 1005 勤雜工 2蘋果農場 1004 Candy Place 9Ride-a-Long Bikes 1003 Candy Place 14

Problem: Need SQL function to return the 'lowest' matching value using the Levenshtein algorithm.

Code:

CREATE FUNCTION ufn_levenshtein(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
 DECLARE @s1_len int, @s2_len int
 DECLARE @i int, @j int, @s1_char nchar, @c int, @c_temp int
 DECLARE @cv0 varbinary(8000), @cv1 varbinary(8000)

 SELECT
  @s1_len = LEN(@s1),
  @s2_len = LEN(@s2),
  @cv1 = 0x0000,
  @j = 1, @i = 1, @c = 0

 WHILE @j <= @s2_len
  SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1

 WHILE @i <= @s1_len
 BEGIN
  SELECT
   @s1_char = SUBSTRING(@s1, @i, 1),
   @c = @i,
   @cv0 = CAST(@i AS binary(2)),
   @j = 1

  WHILE @j <= @s2_len
  BEGIN
   SET @c = @c + 1
   SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
    CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
   IF @c > @c_temp SET @c = @c_temp
   SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
   IF @c > @c_temp SET @c = @c_temp
   SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
 END

 SELECT @cv1 = @cv0, @i = @i + 1
 END

 RETURN @c
END




IF OBJECT_ID('tempdb..#ExistingCustomers') IS NOT NULL
    DROP TABLE #ExistingCustomers;

    CREATE TABLE #ExistingCustomers
(
    Customer VARCHAR(255),
    ID INT
)

INSERT #ExistingCustomers SELECT 'Ed''s Barbershop',  1002
INSERT #ExistingCustomers SELECT 'GroceryTown',  1003
INSERT #ExistingCustomers SELECT 'Candy Place',  1004
INSERT #ExistingCustomers SELECT 'Handy Man',  1005



IF OBJECT_ID('tempdb..#POTENTIALCUSTOMERS') IS NOT NULL
    DROP TABLE #POTENTIALCUSTOMERS;

CREATE TABLE #POTENTIALCUSTOMERS(Customer VARCHAR(255));

INSERT #POTENTIALCUSTOMERS SELECT 'Eds Barbershop'
INSERT #POTENTIALCUSTOMERS SELECT 'Grocery Town'
INSERT #POTENTIALCUSTOMERS SELECT 'Candy Place'
INSERT #POTENTIALCUSTOMERS SELECT 'Handee Man'
INSERT #POTENTIALCUSTOMERS SELECT 'The Apple Farm'
INSERT #POTENTIALCUSTOMERS SELECT 'Ride-a-Long Bikes'


SELECT A.Customer,
       b.ID,
       b.Customer as cust,
       dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) as ValueLev
FROM #POTENTIALCUSTOMERS a
     LEFT JOIN #ExistingCustomers b ON dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) < 15;

This returns:

What I would like to return:

Explanation: The results are the 'lowest' values from the Levenshtein algorithm. There are two rows where the Levenshtein scores are the same The Apple Farm and Ride-a-Long Bikes, in which case any of the values is fine, just as long as it is one value.

References:

SQL Fuzzy Join - MSSQL

http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx

You can use CTE to get the result you want if you partition by the potential customer and use the ValueLev to order the results:

;WITH CTE AS
(
    SELECT  RANK() OVER (PARTITION BY a.Customer ORDER BY dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) ASC) AS RowNbr,
            A.Customer,
            b.ID,
            b.Customer as cust,
            dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) as ValueLev
      FROM  #POTENTIALCUSTOMERS a
        LEFT JOIN #ExistingCustomers b ON dbo.ufn_levenshtein(REPLACE(A.Customer, ' ', ''), REPLACE(B.Customer, ' ', '')) < 15
)
SELECT  Customer,
        MIN(ID) AS ID,
        MIN(cust) AS cust,
        ValueLev
  FROM  CTE
  WHERE CTE.RowNbr = 1
  GROUP BY Customer, ValueLev

As you don't mind which result is returned in the case of duplicate ValueLev, use GROUP BY and MIN to scale the results down to one per potential customer.

Output:

Customer            ID      cust            ValueLev
Candy Place         1004    Candy Place     0
Grocery Town        1003    GroceryTown     0
Eds Barbershop      1002    Ed's Barbershop 1
Handee Man          1005    Handy Man       2
The Apple Farm      1004    Candy Place     9
Ride-a-Long Bikes   1003    Candy Place     14

這篇關于SQL 函數 - 使用 Levenshtein 距離算法進行模糊匹配 - 僅返回最低值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！