SQL Server Recherche Floue avec un Pourcentage de match

Je suis à l'aide de SQL Server 2008 R2 SP1.

J'ai une table avec environ 36034 dossiers de clients.
Je suis en train de mettre en œuvre Fuzy de recherche sur le champ Nom du Client.

Ici est la Fonction de Recherche Floue

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

Voici la requête d'appel de la fonction

select [Party].[FuzySearch]('First Name Middle Name Last Name', C.FirstName) from dbo.Customer C

C'est de prendre environ 2 minutes 22 secondes pour me donner le pourcentage de correspondance floue pour tous

Comment puis-je résoudre ce problème à exécuter dans lessthan une seconde. Toutes les suggestions sur ma fonction pour la rendre plus solide.

Sortie est prévue 45.34, 40.00, 100.00, 23.00, 81.23.....

Par curiosité, quel algorithme de mettez-vous en oeuvre ici?
SQL Server fournit les Recherches Floues et Regroupement probable dans le cadre de la SSIS - ce qui signifie que vous ne pouvez les utiliser dans les opérations de traitement. Pour améliorer ad-hoc sur les performances de la requête que vous deviez écrire une fonction SQLCLR la mise en œuvre de l'algorithme que vous souhaitez. La chaîne de fonctions que vous utilisez le prévenir de SQL Server à partir de l'utilisation d'index. Regex matches en C# serait un meilleur rendement et le résultat en beaucoup moins de code
Vous pouvez aussi tricher et d'utiliser le la Similitude des fonctions SQLCLR dans SQL Server Master Data Services
Voir aussi À la Demande de Recherche Floue dans dba.stackexchange.com pour une Recherche Floue SQLCLR mise en œuvre, ou Floue Chaînes de caractères qui correspondent à l'aide de l'algorithme de Levenshtein sur SQL Server pour la vitesse des comparaisons entre les T-SQL et SQLCLR (30x mieux dans SQLCLR)
Levenshtein

InformationsquelleAutor HaBo | 2014-10-08

Le meilleur que j'ai pu faire est de simplifier une partie de la requête, et de le modifier pour une fonction à valeur de table. Les fonctions scalaires sont notoirement pauvres artistes interprètes ou exécutants, et les avantages d'un en ligne TVF est que la définition de la requête est développée dans la requête principale, à l'instar de vue.

Cela réduit le temps d'exécution de manière significative sur les tests que j'ai fait.

ALTER FUNCTION dbo.FuzySearchTVF (@Reference VARCHAR(200), @Target VARCHAR(200))
RETURNS TABLE
AS
RETURN
(   WITH N (n) AS 
(   SELECT  TOP (ISNULL(CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) 
THEN DATALENGTH(@Reference) 
ELSE DATALENGTH(@Target) 
END, 0))    
ROW_NUMBER() OVER(ORDER BY n1.n)
FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N1 (n)
CROSS JOIN (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N2 (n)
CROSS JOIN (VALUES (1), (1)) AS N3 (n)
WHERE   @Reference IS NOT NULL AND @Target IS NOT NULL
), Src AS
(   SELECT  Reference = CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) THEN @Reference
ELSE @Reference + REPLICATE('_', DATALENGTH(@Target) - DATALENGTH(@Reference))
END,
Target = CASE WHEN DATALENGTH(@Target) > DATALENGTH(@Reference) THEN @Target
ELSE @Target + REPLICATE('_', DATALENGTH(@Target) - DATALENGTH(@Reference))
END,
WordLength = CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) THEN DATALENGTH(@Reference) ELSE DATALENGTH(@Target) END
WHERE   @Reference IS NOT NULL 
AND     @Target IS NOT NULL
AND     @Reference != @Target
), Scores AS
(   SELECT  seq = t1.n ,
Letter = SUBSTRING(s.Reference, t1.n, 1),
s.WordLength ,
LetterScore = s.WordLength - ISNULL(MIN(ABS(t1.n - t2.n)), s.WordLength)
FROM    Src AS s
CROSS JOIN N AS t1
INNER JOIN N AS t2
ON SUBSTRING(@Target, t2.n, 1) = SUBSTRING(s.Reference, t1.n, 1)
WHERE   @Reference IS NOT NULL 
AND     @Target IS NOT NULL
AND     @Reference != @Target
GROUP BY t1.n, SUBSTRING(s.Reference, t1.n, 1), s.WordLength
)
SELECT  [Score] = 100 
WHERE   @Reference = @Target
UNION ALL
SELECT  0
WHERE   @Reference IS NULL OR @Target IS NULL
UNION ALL
SELECT  CAST(SUM(LetterScore) * 100.0 / MAX(WordLength * WordLength) AS NUMERIC(5, 2))
FROM    Scores
WHERE   @Reference IS NOT NULL 
AND     @Target IS NOT NULL
AND     @Reference != @Target
GROUP BY WordLength
);

Et ce serait appelé comme:

SELECT  f.Score
FROM    dbo.Customer AS c
CROSS APPLY [dbo].[FuzySearch]('First Name Middle Name Last Name', c.FirstName) AS f

Il est encore assez complexe de la fonction si, et, en fonction du nombre d'enregistrements dans une table client, je pense arriver vers le bas à 1 seconde va être un peu un défi.

Merci ce vraiment amélioré le rendement. Mon SVF prenait environ 1 min 15 secondes. Votre TABLE est en 8 secondes. Mais Toujours à la recherche pour savoir si je peux le faire dans lessthan un deuxième
Est-il anyalternate pour le Cross s'Appliquent. C'est tuer la performance.
Ne pas appeler la fonction. De façon réaliste, je pense que le fractionnement 40000 chaînes est le réel de la performance de tueur, et je ne vois aucun moyen possible d'obtenir cette baisse à moins d'une seconde. Je vous suggère la recherche d'un CLR solution, car cela est susceptible d'offrir les meilleures performances. Il est un exemple CLR dans cet article.
Merci pour le bon démarrage. CLR solution a fonctionné parfaitement. il est maintenant en cours d'exécution dans ~1 seconde
Merci beaucoup pour votre recherche floue

InformationsquelleAutor GarethD

2

C'est de cette façon que je pourrais accomplir ceci:

Expliqué @ SQL Server Recherche Floue - l'Algorithme de Levenshtein

Créer fichier ci-dessous à l'aide de n'importe quel éditeur de votre choix:
```
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;
}
};
```
Nom il levenshtein.cs

Aller à l'Invite de Commande. Allez dans le fichier de répertoire de levenshtein.cs ensuite appeler csc.exe /t: library /out: UserFunctions.dll levenshtein.cs vous pourriez avoir à donner le chemin d'accès complet csc.exe de NETFrameWork 2.0.

Une fois votre fichier DLL est prêt. Ajouter aux assemblées Base de données>>Programmation>>Ensembles>> Nouvelle Assemblée.

Créer la fonction dans votre base de données:
```
CREATE FUNCTION dbo.LevenshteinSVF
(
@S1 NVARCHAR(200) ,
@S2 NVARCHAR(200)
)
RETURNS FLOAT
AS EXTERNAL NAME
UserFunctions.StoredFunctions.Levenshtein
GO
```
Dans mon cas, j'ai dû activer clr:
```
sp_configure 'clr enabled', 1
GO
reconfigure
GO
```
Test de la fonction:
```
SELECT  dbo.LevenshteinSVF('James','James Bond')
```
Résultat: 50 % de bonus
- Je suis incapable d'ajouter à l'assemblée de SQL Server 2008 R2... Erreur se lit comme suit: "Créer a échoué pour SqlAssembly 'UserFunctions'. (Microsoft.SqlServer.Smo) | CRÉER de l'ASSEMBLÉE l'assemblée 'UserFunctions' a échoué parce que l'assemblée est conçu pour une version non prise en charge du Common Language Runtime. (Microsoft SQL Server, Erro: 6257)". Je suis en cours d'exécution .NET 4.6?
- vous avez besoin pour correspondre à la .NET framework version. Vérifiez quelle est la .Version NET sur SQL Server 2008 R2 et de la compilation de l'assemblée avec cette version.
- À partir de la racine du lecteur C: Ouvrez l'invite de commandes et saisies: C:\>C:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:UserFunctions.dll Levenshtein.cs (NOTE: mon Levenshtein.cs fichier a été collé à la racine du lecteur C avant d'exécuter cette commande). Ensuite, j'ai couru la requête suivante dans Server Management Studio: USE MyDatabase GO CREATE ASSEMBLY UserFunctions from 'c:\UserFunctions.dll' WITH PERMISSION_SET = SAFE. C'est ce que j'avais à faire pour que tout cela fonctionne sur mon Serveur SQL 2008 R2 boîte... Merci @HaBo! Vous avez m'a aidé beaucoup de temps!
- Maintenant je vois "impossible de trouver la méthode 'Levenshtein,' type 'StoredFunctions" dans l'assembly 'UserFunctions'" quand j'essaie de créer de la dbo.Levenshtein fonction comme vous l'avez demandé... j'ai fait en sorte d'essayer de l'activer clr (il était déjà activée). Des idées?
- La fonction CLR est appelé HaBoLevenshtein. Je pense qu'il y avait une erreur dans la réponse originale à cette question. Renommer Levenshtein pour HaBoLevenshtein dans la fonction SQL et cela devrait fonctionner.
- Qu'est-ce exactement cette fonction retourne? Qu'est-ce pourcentage signifie?
- elle renvoie à la prononciation match en pourcentage
- Comment comptez-vous ce pourcentage? Qu'est-ce que dans le numérateur et le dénominateur de la fraction? Nombre de caractère suppression, insertion? Je voudrais savoir de formule précise.
- vous avez besoin de lire le code pour le comprendre. Quelques références docs.microsoft.com/en-us/sql/t-sql/functions/... docs.microsoft.com/en-us/sql/t-sql/functions/...
InformationsquelleAutor HaBo

Vous devez vous connecter pour publier un commentaire.