Postgresql 中文操作指南

F.17. fuzzystrmatch — determine string similarities and distance #

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

The fuzzystrmatch module provides several functions to determine similarities and distance between strings.

Caution

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

At present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do not work well with multibyte encodings (such as UTF-8). Use daitch_mokotoff or levenshtein with such data.

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

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.17.1. Soundex #

Soundex 系统是一种通过将相似发音的名字转换为相同的代码来匹配这些名字的方法。 1880、1900 和 1910 年的美国人口普查最初使用了该方法。请注意，Soundex 对非英语名字没有多大用处。

The Soundex system is a method of matching similar-sounding names by converting them to the same code. It was initially used by the United States Census in 1880, 1900, and 1910. Note that Soundex is not very useful for non-English names.

fuzzystrmatch 模块提供了两个用于处理 Soundex 代码的函数：

The fuzzystrmatch module provides two functions for working with Soundex codes:

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

soundex 函数将字符串转换为其 Soundex 代码。difference 函数将两个字符串转换为其 Soundex 代码，然后报告匹配代码位置的数量。由于 Soundex 代码有四个字符，因此结果的范围从零到四，其中零表示不匹配，四表示精确匹配。（因此，该函数命名不当——similarity 将是一个更好的名称。）

The soundex function converts a string to its Soundex code. The difference function converts two strings to their Soundex codes and then reports the number of matching code positions. Since Soundex codes have four characters, the result ranges from zero to four, with zero being no match and four being an exact match. (Thus, the function is misnamed — similarity would have been a better name.)

以下是一些使用方法示例：

Here are some usage examples:

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 对于非英语名称来说更有用。对于原始系统的一些重大改进包括：

Like the original Soundex system, Daitch-Mokotoff Soundex matches similar-sounding names by converting them to the same code. However, Daitch-Mokotoff Soundex is significantly more useful for non-English names than the original system. Major improvements over the original system include:

此函数为其输入生成 Daitch-Mokotoff soundex 代码：

This function generates the Daitch-Mokotoff soundex codes for its input:

daitch_mokotoff(source text) returns text[]

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

The result may contain one or more codes depending on how many plausible pronunciations there are, so it is represented as an array.

因为一个 Daitch-Mokotoff soundex 代码仅包含 6 位，因此 source 最好是单个单词或名称。

Since a Daitch-Mokotoff soundex code consists of only 6 digits, source should be preferably a single word or name.

这里一些示例：

Here are some examples:

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 索引来提高效率，请参见 Chapter 70 和此示例：

For matching of single names, returned text arrays can be matched directly using the && operator: any overlap can be considered a match. A GIN index may be used for efficiency, see Chapter 70 and this example:

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');

对于按任何顺序对任意数量的名称进行索引和匹配，可以使用全文搜索功能。请参见 Chapter 12 和此示例：

For indexing and matching of any number of names in any order, Full Text Search features can be used. See Chapter 12 and this example:

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 代码，则可以使用单独列上的索引而不是表达式上的索引。可以使用存储的生成列来实现此目的；请参见 Section 5.3 。

If it is desired to avoid recalculation of soundex codes during index rechecks, an index on a separate column can be used instead of an index on an expression. A stored generated column can be used for this; see Section 5.3.

F.17.3. Levenshtein #

此函数计算两个字符串之间的莱文斯坦距离：

This function calculates the Levenshtein distance between two strings:

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。

Both source and target can be any non-null string, with a maximum of 255 characters. The cost parameters specify how much to charge for a character insertion, deletion, or substitution, respectively. You can omit the cost parameters, as in the second version of the function; in that case they all default to 1.

levenshtein_less_equal 是莱文斯坦函数的加速版本，用于在仅关注较小距离的情况下使用。如果实际距离小于或等于 max_d，那么 levenshtein_less_equal 返回正确的距离；否则，它会返回大于 max_d 的某个值。如果 max_d 为负数，那么它的行为与 levenshtein 相同。

levenshtein_less_equal is an accelerated version of the Levenshtein function for use when only small distances are of interest. If the actual distance is less than or equal to max_d, then levenshtein_less_equal returns the correct distance; otherwise it returns some value greater than max_d. If max_d is negative then the behavior is the same as levenshtein.

示例：

Examples:

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, like Soundex, is based on the idea of constructing a representative code for an input string. Two strings are then deemed similar if they have the same codes.

此函数计算输入字符串的元音变换代码：

This function calculates the metaphone code of an input string:

metaphone(source text, max_output_length int) returns text

source 必须是非空字符串，最长为 255 个字符。max_output_length 设置输出元音变换代码的最大长度；如果长于此长度，输出将被截断至该长度。

source has to be a non-null string with a maximum of 255 characters. max_output_length sets the maximum length of the output metaphone code; if longer, the output is truncated to this length.

示例：

Example:

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

F.17.5. Double Metaphone #

双元音变换算法为给定的输入字符串计算两个“听起来像”的字符串——“主音”和“辅音”。在大多数情况下，它们是相同的，但对于非英语名称来说，它们可能根据读音有所不同。这些函数计算主音代码和辅音代码：

The Double Metaphone system computes two “sounds like” strings for a given input string — a “primary” and an “alternate”. In most cases they are the same, but for non-English names especially they can be a bit different, depending on pronunciation. These functions compute the primary and alternate codes:

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

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

There is no length limit on the input strings.

示例：

Example:

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