9.3 9.4 9.5 9.6 10 11 12 13 14 15 Current(16) 17
问题报告 纠错本页面

F.17. fuzzystrmatch — 确定字符串相似性和距离 #

F.17.1. Soundex
F.17.2. Daitch-Mokotoff 音素编码
F.17.3. Levenshtein
F.17.4. Metaphone
F.17.5. 双 Metaphone

fuzzystrmatch模块提供多个函数来判断字符串之间的相似性和距离。

小心

目前,soundexmetaphonedmetaphonedmetaphone_alt 函数在多字节编码 (例如 UTF-8)中效果不佳。对于此类数据,请使用 daitch_mokotofflevenshtein

这个模块被视为trusted,就是说,它可由在当前数据库上拥有CREATE特权的非超级用户安装。

F.17.1. Soundex #

语音表示法系统是一种将相似发音的名字转换成相同的代码来匹配它们的方法。这最初由美国国家统计局在 1880 年、1900 年和 1910 年使用。注意语音表示法对于非英语名称不是很有用。

fuzzystrmatch模块提供了两个函数用于语音表示法代码:

soundex(text) 返回 text
difference(text, text) 返回 int

soundex函数将一个字符串转换成它的语音表示法代码。difference函数将两个字符串转换成它们的语音表示法代码并且接着报告能匹配代码位置的数量。由于语音表示法代码具有四个字符,结果可以从零到四,零表示没有匹配而四表示完全匹配(因此这个函数的命名并不适当 — similarity才是更合适的名称)。

这里有一些例子:

SELECT soundex('hello world!');

SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');

CREATE TABLE s (nm text);

INSERT INTO s VALUES ('john');
INSERT INTO s VALUES ('joan');
INSERT INTO s VALUES ('wobbly');
INSERT INTO s VALUES ('jack');

SELECT * FROM s WHERE soundex(nm) = soundex('john');

SELECT * FROM s WHERE difference(s.nm, 'john') > 2;

F.17.2. Daitch-Mokotoff 音素编码 #

与原始的Soundex系统类似,Daitch-Mokotoff Soundex通过将相似发音的名字 转换为相同的代码来匹配它们。然而,Daitch-Mokotoff Soundex对于非英语 名字比原始系统显著更有用。相较于原始系统的主要改进包括:

  • 代码基于前六个有意义的字母,而不是四个。

  • 一个字母或字母组合映射到十个可能的代码,而不是七个。

  • 当两个连续的字母具有单一的发音时,它们被编码为一个单一的数字。

  • 当一个字母或字母组合可能有不同的发音时,会生成多个代码以涵盖 所有可能性。

此函数为其输入生成Daitch-Mokotoff Soundex代码:

daitch_mokotoff(source text) returns text[]

结果可能包含一个或多个代码,具体取决于有多少种可能的发音, 因此它被表示为一个数组。

由于Daitch-Mokotoff soundex代码仅由6位数字组成, source最好是一个单词或名字。

这里有一些例子:

SELECT daitch_mokotoff('George');
 daitch_mokotoff
-----------------
 {595000}

SELECT daitch_mokotoff('John');
 daitch_mokotoff
-----------------
 {160000,460000}

SELECT daitch_mokotoff('Bierschbach');
                      daitch_mokotoff
-----------------------------------------------------------
 {794575,794574,794750,794740,745750,745740,747500,747400}

SELECT daitch_mokotoff('Schwartzenegger');
 daitch_mokotoff
-----------------
 {479465}

对于单个名称的匹配,返回的文本数组可以直接使用 &&运算符进行匹配:任何重叠都可以 被视为匹配。为了提高效率,可以使用GIN索引, 请参阅第 70 章以及以下示例:

CREATE TABLE s (nm text);
CREATE INDEX ix_s_dm ON s USING gin (daitch_mokotoff(nm)) WITH (fastupdate = off);

INSERT INTO s (nm) VALUES
  ('Schwartzenegger'),
  ('John'),
  ('James'),
  ('Steinman'),
  ('Steinmetz');

SELECT * FROM s WHERE daitch_mokotoff(nm) && daitch_mokotoff('Swartzenegger');
SELECT * FROM s WHERE daitch_mokotoff(nm) && daitch_mokotoff('Jane');
SELECT * FROM s WHERE daitch_mokotoff(nm) && daitch_mokotoff('Jens');

对于任意数量的名称进行索引和匹配,无论顺序如何,都可以使用全文 检索功能。请参阅第 12 章以及以下示例:

CREATE FUNCTION soundex_tsvector(v_name text) RETURNS tsvector
BEGIN ATOMIC
  SELECT to_tsvector('simple',
                     string_agg(array_to_string(daitch_mokotoff(n), ' '), ' '))
  FROM regexp_split_to_table(v_name, '\s+') AS n;
END;

CREATE FUNCTION soundex_tsquery(v_name text) RETURNS tsquery
BEGIN ATOMIC
  SELECT string_agg('(' || array_to_string(daitch_mokotoff(n), '|') || ')', '&')::tsquery
  FROM regexp_split_to_table(v_name, '\s+') AS n;
END;

CREATE TABLE s (nm text);
CREATE INDEX ix_s_txt ON s USING gin (soundex_tsvector(nm)) WITH (fastupdate = off);

INSERT INTO s (nm) VALUES
  ('John Doe'),
  ('Jane Roe'),
  ('Public John Q.'),
  ('George Best'),
  ('John Yamson');

SELECT * FROM s WHERE soundex_tsvector(nm) @@ soundex_tsquery('john');
SELECT * FROM s WHERE soundex_tsvector(nm) @@ soundex_tsquery('jane doe');
SELECT * FROM s WHERE soundex_tsvector(nm) @@ soundex_tsquery('john public');
SELECT * FROM s WHERE soundex_tsvector(nm) @@ soundex_tsquery('besst, giorgio');
SELECT * FROM s WHERE soundex_tsvector(nm) @@ soundex_tsquery('Jameson John');

如果希望在索引重新检查期间避免重新计算soundex代码,可以使用单独列上的索引, 而不是表达式上的索引。可以使用存储生成的列;请参阅 第 5.3 节

F.17.3. Levenshtein #

这个函数计算两个字符串之间的编辑距离。

levenshtein(source text, target text, ins_cost int, del_cost int, sub_cost int) returns int
levenshtein(source text, target text) returns int
levenshtein_less_equal(source text, target text, ins_cost int, del_cost int, sub_cost int, max_d int) returns int
levenshtein_less_equal(source text, target text, max_d int) returns int

source以及target都可以是任何非空字符串, 最长为 255 个字符。代价参数分别指定一个字符插入、删除或替换的开销。 你可以像这个函数的第二种版本那样忽略代价参数,那样它们都会默认为 1。

levenshtein_less_equal是 Levenshtein 函数的速度更快 的版本,它被用于只对小距离感兴趣的情况。如果实际距离小于等于max_d, 那么levenshtein_less_equal返回正确的距离。否则它返回某个 大于max_d的值。如果max_d是负值,那么其行为等同于 levenshtein

例子:

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)

test=# SELECT levenshtein('GUMBO', 'GAMBOL', 2,1,1);
 levenshtein
-------------
           3
(1 row)

test=# SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
 levenshtein_less_equal
------------------------
                      3
(1 row)

test=# SELECT levenshtein_less_equal('extensive', 'exhaustive',4);
 levenshtein_less_equal
------------------------
                      4
(1 row)

F.17.4. Metaphone #

和 Soundex 相似,Metaphone 的思想是构建一个输入字符串的一种代码。如果两个字符串具有相同的代码则认为它们相似。

这个函数计算一个输入字符串的变音位代码:

metaphone(source text, max_output_length int) returns text

source必须是一个非空字符串,最大长度为 255 个字符。max_output_length设置输出的变音位代码的最大长度,如果超长,输出会被截断到这个长度。

例子:

test=# SELECT metaphone('GUMBO', 4);
 metaphone
-----------
 KM
(1 row)

F.17.5. 双 Metaphone #

双变音位系统为一个给定输入字符串计算两个听起来像的字符串 — 一个主要代码和一个次要代码。在大部分情况下它们是相同的,但是对于非英语名称它们可能有一点不同,这取决于发音。这些函数计算主要和次要代码:

dmetaphone(source text) returns text
dmetaphone_alt(source text) returns text

对输入字符串没有长度限制。

例子:

test=# SELECT dmetaphone('gumbo');
 dmetaphone
------------
 KMP
(1 row)