PostgreSQL 教程

Appearance

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

F.17.1. Soundex
F.17.2. Daitch-Mokotoff Soundex
F.17.3. Levenshtein
F.17.4. Metaphone
F.17.5. Double Metaphone

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

注意 ​

目前，soundex、metaphone、dmetaphone和dmetaphone_alt函数不能很好地处理多字节编码（例如 UTF-8）。请对此类数据使用daitch_mokotoff或levenshtein。

此模块被认为是“受信任的”，也就是说，它可以由具有当前数据库的CREATE权限的非超级用户安装。

F.17.1. Soundex# ​

Soundex 系统是一种通过将相似发音的名称转换为相同代码来匹配名称的方法。它最初由美国人口普查局在 1880 年、1900 年和 1910 年使用。请注意，Soundex 对非英语名称并不是非常有用。

fuzzystrmatch模块提供两个函数来处理 Soundex 代码

soundex(text) returns text
difference(text, text) returns int

soundex函数将字符串转换为其 Soundex 代码。difference函数将两个字符串转换为其 Soundex 代码，然后报告匹配代码位置的数量。由于 Soundex 代码有四个字符，因此结果范围从 0 到 4，其中 0 表示不匹配，4 表示完全匹配。（因此，函数名称不当——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# ​

与原始 Soundex 系统类似，Daitch-Mokotoff Soundex 通过将相似发音的名称转换为相同代码来匹配名称。但是，与原始系统相比，Daitch-Mokotoff Soundex 对非英语名称更有用。与原始系统相比，主要改进包括

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

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

  • 如果两个连续字母发音相同，则将其编码为一个数字。

  • 如果一个字母或字母组合有多种发音，则会发出多个代码来涵盖所有可能性。

此函数为其输入生成 Daitch-Mokotoff 音码。

daitch_mokotoff(source text) returns text[]

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

由于 Daitch-Mokotoff 音码仅包含 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');

如果希望在索引重新检查期间避免重新计算音码，则可以使用单独列上的索引，而不是表达式上的索引。为此可以使用存储的生成列；请参见第 5.3 节。

F.17.3. Levenshtein# ​

此函数计算两个字符串之间的 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 代码

metaphone(source text, max_output_length int) returns text

source必须是一个不为 null 的字符串，最大长度为 255 个字符。max_output_length设置输出元音转换代码的最大长度；如果更长，则输出将被截断到此长度。

示例

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

F.17.5. 双元音转换# ​

双元音转换系统为给定的输入字符串计算两个“类似于”字符串——一个“主”和一个“备用”。在大多数情况下，它们是相同的，但对于非英语名称，它们可能会有所不同，具体取决于发音。这些函数计算主代码和备用代码

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

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

示例

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