1. 首页
  2. 问题
  3. SQL Server模糊搜索（匹配百分比）
小编典典

SQL Server模糊搜索（匹配百分比）

sql

我正在使用SQL Server 2008 R2 SP1。

我有一张约有36034个客户记录的表。我正在尝试在“客户名称”字段上实施Fuzy搜索。

这是模糊搜索的功能

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

这是调用该函数的查询

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 .....


阅读 448

收藏
2021-03-23

共1个答案

小编典典

这就是我可以实现的方式：

进一步解释@ SQL Server模糊搜索-Levenshtein算法

使用您选择的任何编辑器创建以下文件：

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;
    }
};

命名为 levenshtein.cs

转到命令提示符。转到levenshtein.cs的文件目录，然后调用 csc.exe / t：库/ out：UserFunctions.dll
levenshtein.cs， 您可能必须提供 NETFrameWork 2.0中csc.exe的完整路径。

DLL准备好后。将其添加到程序集 数据库 >>可编程性>>程序集 > >新建程序集。

在数据库中创建函数：

CREATE FUNCTION dbo.LevenshteinSVF
    (
      @S1 NVARCHAR(200) ,
      @S2 NVARCHAR(200)
    )
RETURNS FLOAT
AS EXTERNAL NAME
    UserFunctions.StoredFunctions.Levenshtein
GO

就我而言，我必须启用clr：

sp_configure 'clr enabled', 1
GO
reconfigure
GO

测试功能：

SELECT  dbo.LevenshteinSVF('James','James Bond')

结果：50％匹配

2021-03-23