如何使用Snowflake SQL找到单列中字符串的变化？

Question

说我有这样的桌子：

Person1	Person2
Dave	Fred
Dave Dave	Dave
Dave Dave	Dave Dave Dave Dave
Dave	Dave Dave
Dave	Dave Dave Dave Dave
Dave Dave	Jeff

在专栏中“ Person1”中显然是最受欢迎的输入，所以我想在SQL（Snowflake）中产生“相似性得分”或“列中的变化”分数。

相反，对于列“ Person2”，字符串之间存在更大的变化，因此相似度得分将较低，或列内列内的变化。因此，您可能最终会以相似的分数输出为：'person1'：0.9，'person2'：0.4。

如果这只是行明确的Levenshtein距离（LD），我该如何在这些方面推出编辑距离以获得每列的分数？目前，我只能看到如何在'person1'和'person2'之间获得LD，而不是在“ Person1”和“ Person2”之间获得LD。

非常感谢

Answer 1

您提出的0.9和0.4的值似乎是相同性的比率，因此可以通过计数和比率_OF_REPORT像这样计算：

with data(person1, person2) as (
    select * from values
        ('Dave','Fred'),
        ('Dave','Dave'),
        ('Dave','Mike'),
        ('Fred','Dave'),
        ('Dave','Mike'),
        ('Dave','Jeff')
), p1 as (
    select 
      person1 
      ,count(*) as c_p1
      ,ratio_to_report(c_p1) over () as q
    from data
    group by 1
    qualify row_number() over(order by c_p1 desc) = 1
), p2 as (
    select 
      person2 
      ,count(*) as c_p2
      ,ratio_to_report(c_p2) over () as q
    from data
    group by 1
    qualify row_number() over(order by c_p2 desc) = 1
)
select 
    p1.q as p1_same,
    p2.q as p2_same
from p1
cross join p2
;

给予：

p1_same	p2_same
0.8333333 0.3333333	0.333333 0.333333

EditDistance：

因此，使用完整的交叉接口，我们可以计算 editdistance 所有值的，并找到总数的比率：

with data(person1, person2) as (
    select * from values
        ('Dave','Fred'),
        ('Dave','Dave'),
        ('Dave','Mike'),
        ('Fred','Dave'),
        ('Dave','Mike'),
        ('Dave','Jeff')
), combo as (
select 
    editdistance(da.person1, db.person1) as p1_dist    
    ,editdistance(da.person2, db.person2) as p2_dist
from data as da
cross join data as db
)
select count(*) as c
    ,sum(p1_dist) as s_p1_dist
    ,sum(p2_dist) as s_p2_dist
    ,c / s_p1_dist as p1_same
    ,c / s_p2_dist as p2_same 
from combo
 ;

但是 所需的结果不符。

与

给定的editDistance给出了相同的零的结果，差的差异为差异，这些比例 之间，能够平均如此。

select 
     avg(JAROWINKLER_SIMILARITY(da.person1, db.person1)/100) as p1_dist    
    ,avg(JAROWINKLER_SIMILARITY(da.person2, db.person2)/100) as p2_dist
from data as da
cross join data as db;

sql -reference/functions/jarowinkler_similarity.html“ rel =“ nofollow noreferrer	”> jarowinklet相似
性结果已经在0-100	之间缩放在0-100