Transact SQL中Jaro Winkler距离算法的实现

靘場蒗秄

我一直想知道如何在Transact
- J) }( A9 ^  \4 a8 E# YSQL中实现此算法，https：//en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance
5 \  ^, ?3 E/ r$ |9 ^怎么做到呢？
' u, q+ E1 ?( ?& ?- ?- x' C) d9 y( O               
9 E( S. ~% q0 L解决方案:
               


6 B$ |7 a, G6 y! Q0 Q% \& M                今天，我终于偶然发现了leebickmtu的这个Stack
Overflow-answer，它显示了最初从Java移植的C＃实现。我自由地将其移植到Transact SQL函数，请尽情享受！
( ]$ v; K/ r3 Y* b: `* L; n8 BIF OBJECT_ID (N'dbo.InlineMax', N'FN') IS NOT NULL
3 D3 I3 m2 X/ J3 D9 E) F' }+ K    DROP FUNCTION dbo.InlineMax;
' e2 E) c  D* T' t! ?$ UGO
8 Z4 X" y2 ?1 P! l% ]6 W( iCREATE FUNCTION dbo.InlineMax(@valueOne int, @valueTwo int)
) o8 Y9 S# }/ K' Y    RETURNS FLOAT
1 s. d! ~( y% y; i5 tAS
# u- M; U( R0 G! o+ T' F! b- iBEGIN
+ a; F3 j" Z- O4 w- _2 m    IF @valueOne > @valueTwo
    BEGIN
        RETURN @valueOne
- q7 X' R/ D2 S" [9 C3 M/ e    END
    RETURN ISNULL(@valueTwo, @valueOne)
8 A7 B! l5 [3 C! c" PEND;
" I' C9 Q, U0 ~/ U0 fGO
IF OBJECT_ID (N'dbo.InlineMin', N'FN') IS NOT NULL
    DROP FUNCTION dbo.InlineMin;
& e* l9 e, B( d( f* gGO
CREATE FUNCTION dbo.InlineMin(@valueOne int, @valueTwo int)
9 x; b* ?6 x' I3 D2 H; A: z$ i1 L    RETURNS FLOAT
% c6 t, o* @6 y9 HAS
2 i& N% n$ I1 F) Z7 w' B) j+ k/ _) {( mBEGIN
    IF @valueOne  (SELECT SUBSTRING(@stringTwo, @j, 1))
            BEGIN
                SET @j = @j + 1
                CONTINUE
            END
" ^4 S: K# h3 e; m3 ~( T; `2 h            INSERT INTO @lMatched1 (position, [status]) VALUES(@i, 1)
/ Y; A# x  E# Y# {& {( _            INSERT INTO @lMatched2 (position, [status]) VALUES(@j, 1)
            SET @lNumCommon = @lNumCommon + 1
            BREAK
. R; K) z/ U& \7 I        END
' f% U: R: C4 R' q2 O' B% t. W# D0 z        SET @i = @i + 1
    END
0 S3 F( T! P% @: v3 y0 V    IF @lNumCommon = 0
    BEGIN
, v0 c& }1 N0 ]1 z7 w/ s8 _        RETURN 0.0;
    END
8 c1 y/ D# B: v' ]    DECLARE @lNumHalfTransposed int; SET @lNumHalfTransposed = 0
( p+ d7 S4 S: _3 r: R- `    DECLARE @k INT; SET @k = 1;
    DECLARE @stopLoop bit; SET @stopLoop = 0;
    SET @i = 1; WHILE(@i  (SELECT SUBSTRING(@stringTwo, @k, 1)))
) e% M+ A  F) v& A  f& S+ `+ n  a                SET @lNumHalfTransposed = @lNumHalfTransposed + 1
$ ^+ b- Q' M+ }3 r2 b- e            SET @k = @k + 1
1 T7 T1 B& ]" o% o2 l% }' d3 l        END
2 B* Y. s" c  D2 @: x; [        SET @i = @i + 1
    END
    DECLARE @lNumTransposed INT; SET @lNumTransposed = @lNumHalfTransposed/2;
    DECLARE @lNumCommonD FLOAT; SET @lNumCommonD = @lNumCommon
    DECLARE @lWeight FLOAT; SET @lWeight = (@lNumCommonD / @lLen1 + @lNumCommonD / @lLen2 + (@lNumCommon - @lNumTransposed) / @lNumCommonD) / 3.0;
    IF(@lWeight <= @mWeightThreshold)
$ Y6 m! e; W  @, W# z3 ^        RETURN @lWeight
    DECLARE @lMax INT; SET @lMax = dbo.InlineMin(@mNumChars, dbo.InlineMin(@lLen1, @lLen2))
    DECLARE @lPos INT; SET @lPos = 0
    WHILE(@lPos < @lMax AND (SELECT SUBSTRING(@stringOne, @lPos, 1)) = (SELECT SUBSTRING(@stringTwo, @lPos, 1)))
7 L2 _" o; c7 N' R2 V, \% G    BEGIN
        SET @lPos = @lPos + 1
3 Y' e) C! ~( s6 E& v    END
; o) Q& `( T" p; ^9 X    IF @lPos = 0
        RETURN @lWeight
" k' ~: _8 N: a    RETURN @lWeight + 0.1 * @lPos * (1.0 - @lWeight)
END;
GO