PostgreSQL,三元组和相似性

SELECT set_limit(0.5);

SELECT winery,similarity(winery,'chateau chevla blanc') AS similarity 
FROM usr_wines 
WHERE status=1 AND winery % 'chateau chevla blanc'  
ORDER BY similarity DESC;

Chateau ChevL Blanc 0,85
Chateau Blanc   0,736842
Chateau Blanc   0,736842
Chateau Blanc,736842
Chateau Cheval Blanc    0,727273
Chateau Cheval Blanc    0,727273
Chateau Cheval Blanc (7)    0,666667
Chateau Cheval Blanc Cbo    0,64
Chateau Du Cheval Blanc 0,64

三元组相似性的概念依赖于将任何句子分为“三元组”(三个连续字母的序列),并将结果视为一个SET(即：顺序无关紧要,并且您没有重复的值).在考虑句子之前,在开头添加两个空格,在末尾添加一个空格,单个空格用双精度空格替换.

Trigrams是N-grams的特例.

对应于“Chateau blanc”的三元组通过查找出现在其上的三个字母的所有序列来找到：

chateau  blanc
---                 => '  c'
 ---                => ' ch'
  ---               => 'cha'
   ---              => 'hat'
    ---             => 'ate'
     ---            => 'tea'
      ---           => 'eau'
       ---          => 'au '
        ---         => 'u  '
         ---        => '  b'
          ---       => ' bl'
           ---      => 'bla'
            ---     => 'lan'
             ---    => 'anc'
              ---   => 'nc '

对它们进行排序,并重复进行可以获得：

'  b'
'  c'
' bl'
' ch'
'anc'
'ate'
'au '
'bla'
'cha'
'eau'
'hat'
'lan'
'nc '
'tea'

这可以通过PostgreSQL通过函数show_trgm来计算：

SELECT show_trgm('Chateau blanc') AS A

A = [  b,c,bl,ch,anc,ate,au,bla,cha,eau,hat,lan,nc,tea]

……有14个三卦. (检查pg_trgm).

对应于“Chateau Cheval Blanc”的三元组是：

SELECT show_trgm('Chateau Cheval Blanc') AS B 

B = [  b,che,evl,hev,la,tea,vla]

……有19个三卦

如果计算三个三元组共有多少个三元组,你会发现它们有以下几个：

A intersect B = 
    [  b,tea]

他们总共拥有的是：

A union B = 
    [  b,vla]

也就是说,两个句子共有14个三元组,总共19个.
相似度计算如下：

similarity = 14 / 19

您可以查看：

SELECT 
    cast(14.0/19.0 as real) AS computed_result,similarity('Chateau blanc','chateau chevla blanc') AS function_in_pg

你会看到你得到：0.736842

…解释了如何计算相似度,以及为什么得到你得到的值.

注意：您可以通过以下方式计算交集和并集：

SELECT 
   array_agg(t) AS in_common
FROM
(
    SELECT unnest(show_trgm('Chateau blanc')) AS t 
    INTERSECT 
    SELECT unnest(show_trgm('chateau chevla blanc')) AS t
    ORDER BY t
) AS trigrams_in_common ;

SELECT 
   array_agg(t) AS in_total
FROM
(
    SELECT unnest(show_trgm('Chateau blanc')) AS t 
    UNION 
    SELECT unnest(show_trgm('chateau chevla blanc')) AS t
) AS trigrams_in_total ;

这是一种探索不同句子对的相似性的方法：

WITH p AS
(
    SELECT 
      'This is just a sentence I''ve invented'::text AS f1,'This is just a sentence I''ve also invented'::text AS f2
),t1 AS
(
    SELECT unnest(show_trgm(f1)) FROM p
),t2 AS
(
    SELECT unnest(show_trgm(f2)) FROM p
),x AS
(
    SELECT
        (SELECT count(*) FROM 
            (SELECT * FROM t1 INTERSECT SELECT * FROM t2) AS s0)::integer AS same,(SELECT count(*) FROM 
            (SELECT * FROM t1 UNION     SELECT * FROM t2) AS s0)::integer AS total,similarity(f1,f2) AS sim_2
FROM
    p 
)
SELECT
    same,total,same::real/total::real AS sim_1,sim_2
FROM
    x ;

你可以在Rextester查看