具有匹配百分比的 SQL Server 模糊搜索 [英] SQL Server Fuzzy Search with Percentage of match
本文介绍了具有匹配百分比的 SQL Server 模糊搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用的是 SQL Server 2008 R2 SP1.
我有一个包含大约 36034 条客户记录的表.我正在尝试对客户名称字段实施模糊搜索.
这里是模糊搜索函数
ALTER FUNCTION [Party].[FuzySearch](@Reference VARCHAR(200) ,@目标 VARCHAR(200))返回十进制(5, 2)使用架构绑定作为开始声明 @score DECIMAL(5, 2)SELECT @score = CASE WHEN @Reference = @Target然后 CAST(100 AS NUMERIC(5, 2))当@Reference 为空时或 @Target 为空然后 CAST(0 作为数字 (5, 2))ELSE ( SELECT [Score %] = CAST(SUM(LetterScore)* 100.0/MAX(字长* 字长)作为数字(5，2))发件人 ( -- 做选择 seq = t1.n ,参考信，v.WordLength ,LetterScore = v.WordLength- ISNULL(MIN(tgt.n),v.WordLength)发件人 ( -- v选择参考 = LEFT(@Reference+ 复制('_'，字长),字长) ,目标 = 左(@目标+ 复制('_'，字长),字长) ,字长 = 字长从(——迪选择字长 = MAX(字长)从( 值(数据长度(@Reference))，( DATALENGTH(@Target)) ) d ( WordLength )) 迪) v交叉应用 ( -- t1选择顶部(字长)n从( 值 ( 1),( 2), ( 3), ( 4),( 5), ( 6), ( 7),( 8), ( 9),( 10), ( 11),( 12), ( 13),( 14), ( 15),( 16), ( 17),( 18), ( 19),( 20), ( 21),( 22), ( 23),( 24), ( 25),( 26), ( 27),( 28), ( 29),( 30), ( 31),( 32), ( 33),( 34), ( 35),( 36), ( 37),( 38), ( 39),( 40), ( 41),( 42), ( 43),( 44), ( 45),( 46), ( 47),( 48), ( 49),( 50), ( 51),( 52), ( 53),( 54), ( 55),( 56), ( 57),( 58), ( 59),( 60), ( 61),(62)、(63)、( 64), ( 65),(66)、(67)、( 68), ( 69),( 70), ( 71),( 72), ( 73),( 74), ( 75),( 76), ( 77),( 78), ( 79),( 80), ( 81),(82)、(83)、(84)、(85)、(86)、(87)、(88)、(89)、( 90), ( 91),( 92), ( 93),( 94), ( 95),( 96), ( 97),( 98), ( 99),( 100), ( 101),(102)、(103)、(104)、(105)、(106)、(107)、(108)、(109)、(110)、(111)、(112)、(113)、(114)、(115)、(116)、(117)、(118)、(119)、(120)、(121)、(122)、(123)、(124)、(125)、(126)、(127)、(128)、(129)、(130)、(131)、(132)、(133)、(134)、(135)、(136)、(137)、(138)、(139)、(140)、(141)、(142)、(143)、(144)、(145)、(146)、(147)、(148)、(149)、(150)、(151)、(152)、(153)、(154)、(155)、(156)、(157)、(158)、(159)、(160)、(161)、(162)、(163)、(164)、(165)、(166)、(167)、(168)、(169)、(170)、(171)、(172)、(173)、(174)、(175)、(176)、(177)、(178)、(179)、(180)、(181)、(182)、(183)、(184)、(185)、(186)、(187)、(188)、(189)、(190)、(191)、(192)、(193)、(194)、(195)、(196)、(197)、(198)、(199)、( 200)) t2 ( n )) t1交叉申请(选择字母 = SUBSTRING(参考，t1.n, 1)) 参考外部应用 ( -- tgt选择顶部(字长)n = ABS(t1.n- t2.n)从( 值 ( 1),( 2), ( 3), ( 4),( 5), ( 6), ( 7),( 8), ( 9),( 10), ( 11),( 12), ( 13),( 14), ( 15),( 16), ( 17),( 18), ( 19),( 20), ( 21),( 22), ( 23),( 24), ( 25),( 26), ( 27),( 28), ( 29),( 30), ( 31),( 32), ( 33),( 34), ( 35),( 36), ( 37),( 38), ( 39),( 40), ( 41),( 42), ( 43),( 44), ( 45),( 46), ( 47),( 48), ( 49),( 50), ( 51),( 52), ( 53),( 54), ( 55),( 56), ( 57),( 58), ( 59),( 60), ( 61),(62)、(63)、( 64), ( 65),(66)、(67)、( 68), ( 69),( 70), ( 71),( 72), ( 73),( 74), ( 75),( 76), ( 77),( 78), ( 79),( 80), ( 81),(82)、(83)、(84)、(85)、(86)、(87)、(88)、(89)、( 90), ( 91),( 92), ( 93),( 94), ( 95),( 96), ( 97),( 98), ( 99),( 100), ( 101),(102)、(103)、(104)、(105)、(106)、(107)、(108)、(109)、(110)、(111)、(112)、(113)、(114)、(115)、(116)、(117)、(118)、(119)、(120)、(121)、(122)、(123)、(124)、(125)、(126)、(127)、(128)、(129)、(130)、(131)、(132)、(133)、(134)、(135)、(136)、(137)、(138)、(139)、(140)、(141)、(142)、(143)、(144)、(145)、(146)、(147)、(148)、(149)、(150)、(151)、(152)、(153)、(154)、(155)、(156)、(157)、(158)、(159)、(160)、(161)、(162)、(163)、(164)、(165)、(166)、(167)、(168)、(169)、(170)、(171)、(172)、(173)、(174)、(175)、(176)、(177)、(178)、(179)、(180)、(181)、(182)、(183)、(184)、(185)、(186)、(187)、(188)、(189)、(190)、(191)、(192)、(193)、(194)、(195)、(196)、(197)、(198)、(199)、( 200 ) ) t2 ( n )在哪里子字符串(@目标，t2.n, 1) = ref.Letter) tgt按 t1.n 分组，参考信，v.字长) 做)结尾返回@score结尾这是调用函数的查询
select [Party].[FuzySearch]('First Name Middle Name Last Name', C.FirstName) from dbo.Customer C这大约需要 2 分 22 秒才能给我所有模糊匹配的百分比
我怎样才能在不到一秒的时间内解决这个问题.关于我的函数的任何建议以使其更健壮.
预期输出为 45.34、40.00、100.00、23.00、81.23.....
解决方案
这就是我可以做到的:
进一步解释@SQL Server模糊搜索-Levenshtein算法
使用您选择的任何编辑器创建以下文件:
使用系统；使用 System.Data;使用 System.Data.SqlClient;使用 System.Data.SqlTypes;使用 Microsoft.SqlServer.Server;公共部分类 StoredFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]公共静态 SqlDouble Levenshtein(SqlString stringOne, SqlString stringTwo){#region 空值句柄如果(stringOne.IsNull)stringOne = new SqlString("");如果(stringTwo.IsNull)stringTwo = new SqlString("");#endregion#region 转换为大写string strOneUppercase = stringOne.Value.ToUpper();string strTwoUppercase = stringTwo.Value.ToUpper();#endregion#region 快速检查和快速匹配分数int strOneLength = strOneUppercase.Length;int strTwoLength = strTwoUppercase.Length;int[,] 维度 = new int[strOneLength + 1, strTwoLength + 1];int matchCost = 0;如果(strOneLength + strTwoLength == 0){返回 100；}否则如果(strOneLength == 0){返回0；}否则如果(strTwoLength == 0){返回0；}#endregion#region Levenshtein 公式for (int i = 0; i <= strOneLength; i++)维度[i, 0] = i;for (int j = 0; j <= strTwoLength; j++)维度[0, j] = j;for (int i = 1; i <= strOneLength; i++){for (int j = 1; j <= strTwoLength; j++){如果(strOneUppercase[i - 1] == strTwoUppercase[j - 1])匹配成本 = 0;别的匹配成本 = 1;dimention[i, j] = System.Math.Min(System.Math.Min(dimention[i - 1, j] + 1, dimention[i, j - 1] + 1), dimention[i - 1, j -1] + 匹配成本)；}}#endregion//计算匹配百分比双倍百分比 = System.Math.Round((1.0 - ((double)dimion[strOneLength, strTwoLength]/(double)System.Math.Max(strOneLength, strTwoLength))) * 100.0, 2);退货率；}};将其命名为 levenshtein.cs
转到命令提示符.转到 levenshtein.cs 的文件目录，然后调用 csc.exe/t: library/out: UserFunctions.dll levenshtein.cs 您可能需要提供 NETFrameWork 2.0 中 csc.exe 的完整路径.
一旦您的 DLL 准备就绪.将其添加到程序集数据库>>可编程性>>程序集>>新程序集.
在您的数据库中创建函数:
创建函数 dbo.LevenshteinSVF(@S1 NVARCHAR(200) ,@S2 NVARCHAR(200))回报浮动作为外名UserFunctions.StoredFunctions.Levenshtein走就我而言，我必须启用 clr:
sp_configure 'clr enabled', 1走重新配置走测试功能:
SELECT dbo.LevenshteinSVF('James','James Bond')<块引用>
结果:50% 匹配
I am using SQL Server 2008 R2 SP1.
I have a table with about 36034 records of customers. I am trying to implement Fuzy search on Customer Name field.
Here is Function for Fuzzy Search
ALTER FUNCTION [Party].[FuzySearch]
(
@Reference VARCHAR(200) ,
@Target VARCHAR(200)
)
RETURNS DECIMAL(5, 2)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @score DECIMAL(5, 2)
SELECT @score = CASE WHEN @Reference = @Target
THEN CAST(100 AS NUMERIC(5, 2))
WHEN @Reference IS NULL
OR @Target IS NULL
THEN CAST(0 AS NUMERIC(5, 2))
ELSE ( SELECT [Score %] = CAST(SUM(LetterScore)
* 100.0 / MAX(WordLength
* WordLength) AS NUMERIC(5,
2))
FROM ( -- do
SELECT seq = t1.n ,
ref.Letter ,
v.WordLength ,
LetterScore = v.WordLength
- ISNULL(MIN(tgt.n),
v.WordLength)
FROM ( -- v
SELECT
Reference = LEFT(@Reference
+ REPLICATE('_',
WordLength),
WordLength) ,
Target = LEFT(@Target
+ REPLICATE('_',
WordLength),
WordLength) ,
WordLength = WordLength
FROM
( -- di
SELECT
WordLength = MAX(WordLength)
FROM
( VALUES
( DATALENGTH(@Reference)),
( DATALENGTH(@Target)) ) d ( WordLength )
) di
) v
CROSS APPLY ( -- t1
SELECT TOP ( WordLength )
n
FROM
( VALUES ( 1),
( 2), ( 3), ( 4),
( 5), ( 6), ( 7),
( 8), ( 9),
( 10), ( 11),
( 12), ( 13),
( 14), ( 15),
( 16), ( 17),
( 18), ( 19),
( 20), ( 21),
( 22), ( 23),
( 24), ( 25),
( 26), ( 27),
( 28), ( 29),
( 30), ( 31),
( 32), ( 33),
( 34), ( 35),
( 36), ( 37),
( 38), ( 39),
( 40), ( 41),
( 42), ( 43),
( 44), ( 45),
( 46), ( 47),
( 48), ( 49),
( 50), ( 51),
( 52), ( 53),
( 54), ( 55),
( 56), ( 57),
( 58), ( 59),
( 60), ( 61),
( 62), ( 63),
( 64), ( 65),
( 66), ( 67),
( 68), ( 69),
( 70), ( 71),
( 72), ( 73),
( 74), ( 75),
( 76), ( 77),
( 78), ( 79),
( 80), ( 81),
( 82), ( 83),
( 84), ( 85),
( 86), ( 87),
( 88), ( 89),
( 90), ( 91),
( 92), ( 93),
( 94), ( 95),
( 96), ( 97),
( 98), ( 99),
( 100), ( 101),
( 102), ( 103),
( 104), ( 105),
( 106), ( 107),
( 108), ( 109),
( 110), ( 111),
( 112), ( 113),
( 114), ( 115),
( 116), ( 117),
( 118), ( 119),
( 120), ( 121),
( 122), ( 123),
( 124), ( 125),
( 126), ( 127),
( 128), ( 129),
( 130), ( 131),
( 132), ( 133),
( 134), ( 135),
( 136), ( 137),
( 138), ( 139),
( 140), ( 141),
( 142), ( 143),
( 144), ( 145),
( 146), ( 147),
( 148), ( 149),
( 150), ( 151),
( 152), ( 153),
( 154), ( 155),
( 156), ( 157),
( 158), ( 159),
( 160), ( 161),
( 162), ( 163),
( 164), ( 165),
( 166), ( 167),
( 168), ( 169),
( 170), ( 171),
( 172), ( 173),
( 174), ( 175),
( 176), ( 177),
( 178), ( 179),
( 180), ( 181),
( 182), ( 183),
( 184), ( 185),
( 186), ( 187),
( 188), ( 189),
( 190), ( 191),
( 192), ( 193),
( 194), ( 195),
( 196), ( 197),
( 198), ( 199),
( 200)
) t2 ( n )
) t1
CROSS APPLY ( SELECT
Letter = SUBSTRING(Reference,
t1.n, 1)
) ref
OUTER APPLY ( -- tgt
SELECT TOP ( WordLength )
n = ABS(t1.n
- t2.n)
FROM
( VALUES ( 1),
( 2), ( 3), ( 4),
( 5), ( 6), ( 7),
( 8), ( 9),
( 10), ( 11),
( 12), ( 13),
( 14), ( 15),
( 16), ( 17),
( 18), ( 19),
( 20), ( 21),
( 22), ( 23),
( 24), ( 25),
( 26), ( 27),
( 28), ( 29),
( 30), ( 31),
( 32), ( 33),
( 34), ( 35),
( 36), ( 37),
( 38), ( 39),
( 40), ( 41),
( 42), ( 43),
( 44), ( 45),
( 46), ( 47),
( 48), ( 49),
( 50), ( 51),
( 52), ( 53),
( 54), ( 55),
( 56), ( 57),
( 58), ( 59),
( 60), ( 61),
( 62), ( 63),
( 64), ( 65),
( 66), ( 67),
( 68), ( 69),
( 70), ( 71),
( 72), ( 73),
( 74), ( 75),
( 76), ( 77),
( 78), ( 79),
( 80), ( 81),
( 82), ( 83),
( 84), ( 85),
( 86), ( 87),
( 88), ( 89),
( 90), ( 91),
( 92), ( 93),
( 94), ( 95),
( 96), ( 97),
( 98), ( 99),
( 100), ( 101),
( 102), ( 103),
( 104), ( 105),
( 106), ( 107),
( 108), ( 109),
( 110), ( 111),
( 112), ( 113),
( 114), ( 115),
( 116), ( 117),
( 118), ( 119),
( 120), ( 121),
( 122), ( 123),
( 124), ( 125),
( 126), ( 127),
( 128), ( 129),
( 130), ( 131),
( 132), ( 133),
( 134), ( 135),
( 136), ( 137),
( 138), ( 139),
( 140), ( 141),
( 142), ( 143),
( 144), ( 145),
( 146), ( 147),
( 148), ( 149),
( 150), ( 151),
( 152), ( 153),
( 154), ( 155),
( 156), ( 157),
( 158), ( 159),
( 160), ( 161),
( 162), ( 163),
( 164), ( 165),
( 166), ( 167),
( 168), ( 169),
( 170), ( 171),
( 172), ( 173),
( 174), ( 175),
( 176), ( 177),
( 178), ( 179),
( 180), ( 181),
( 182), ( 183),
( 184), ( 185),
( 186), ( 187),
( 188), ( 189),
( 190), ( 191),
( 192), ( 193),
( 194), ( 195),
( 196), ( 197),
( 198), ( 199),
( 200) ) t2 ( n )
WHERE
SUBSTRING(@Target,
t2.n, 1) = ref.Letter
) tgt
GROUP BY t1.n ,
ref.Letter ,
v.WordLength
) do
)
END
RETURN @score
END
Here is the query to call the function
select [Party].[FuzySearch]('First Name Middle Name Last Name', C.FirstName) from dbo.Customer C
This is taking about 2 minutes 22 seconds to give me the percentage of fuzzy match for all
How can I fix this to run in lessthan a second. Any suggestions on my function to make it more robust.
Expected ouput is 45.34, 40.00, 100.00, 23.00, 81.23.....
解决方案
This is how I could accomplish this:
Explained further @ SQL Server Fuzzy Search - Levenshtein Algorithm
Create below file using any editor of your choice:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
public static SqlDouble Levenshtein(SqlString stringOne, SqlString stringTwo)
{
#region Handle for Null value
if (stringOne.IsNull)
stringOne = new SqlString("");
if (stringTwo.IsNull)
stringTwo = new SqlString("");
#endregion
#region Convert to Uppercase
string strOneUppercase = stringOne.Value.ToUpper();
string strTwoUppercase = stringTwo.Value.ToUpper();
#endregion
#region Quick Check and quick match score
int strOneLength = strOneUppercase.Length;
int strTwoLength = strTwoUppercase.Length;
int[,] dimention = new int[strOneLength + 1, strTwoLength + 1];
int matchCost = 0;
if (strOneLength + strTwoLength == 0)
{
return 100;
}
else if (strOneLength == 0)
{
return 0;
}
else if (strTwoLength == 0)
{
return 0;
}
#endregion
#region Levenshtein Formula
for (int i = 0; i <= strOneLength; i++)
dimention[i, 0] = i;
for (int j = 0; j <= strTwoLength; j++)
dimention[0, j] = j;
for (int i = 1; i <= strOneLength; i++)
{
for (int j = 1; j <= strTwoLength; j++)
{
if (strOneUppercase[i - 1] == strTwoUppercase[j - 1])
matchCost = 0;
else
matchCost = 1;
dimention[i, j] = System.Math.Min(System.Math.Min(dimention[i - 1, j] + 1, dimention[i, j - 1] + 1), dimention[i - 1, j - 1] + matchCost);
}
}
#endregion
// Calculate Percentage of match
double percentage = System.Math.Round((1.0 - ((double)dimention[strOneLength, strTwoLength] / (double)System.Math.Max(strOneLength, strTwoLength))) * 100.0, 2);
return percentage;
}
};
Name it levenshtein.cs
Go to Command Prompt. Go to the file directory of levenshtein.cs then call csc.exe /t: library /out: UserFunctions.dll levenshtein.cs you may have to give the full path of csc.exe from NETFrameWork 2.0.
Once your DLL is ready. Add it to the assemblies Database>>Programmability>>Assemblies>> New Assembly.
Create function in your database:
CREATE FUNCTION dbo.LevenshteinSVF
(
@S1 NVARCHAR(200) ,
@S2 NVARCHAR(200)
)
RETURNS FLOAT
AS EXTERNAL NAME
UserFunctions.StoredFunctions.Levenshtein
GO
In my case I had to enable clr:
sp_configure 'clr enabled', 1
GO
reconfigure
GO
Test the function:
SELECT dbo.LevenshteinSVF('James','James Bond')
Result: 50 % match
这篇关于具有匹配百分比的 SQL Server 模糊搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
