Postgres中的模糊名称匹配

应用程序开发和分析中的令人惊讶的常见问题是：给定输入名称，找到它最有可能是指的数据库记录。它＆＃39;常见的，因为名称和人的数据库很常见，它＆＃39; s一个问题，因为名称是一个非常不规则的识别令牌。

页面＆＃34;谎言程序员相信名称＆＃34;涵盖名称难以在编程中处理的一些方式。此帖子将忽略大部分复杂性，并处理将松散用户输入与名称数据库匹配的问题。

此帖子的数据是假名生成器创建的50,000名西方化名称，然后使用Copy命令加载到PostgreSQL中。

创建表名（数字整数主键，给定名称文本，中兆文本，姓氏文本）; \拷贝名称来自＆＃39; fakenamegenerator.csv＆＃39;使用（格式化CSV，标题TRUE）;

选择*来自名称限制10;号码|伯克名|中数体|姓氏-------- + ---------- + -------------- + --------------- 1 |海伦| s | Godinez 2 | Sabrina | l |格兰兹3 |蒂芙尼| P | Hernandez 4 |威利| C |威廉姆斯5 |迈克尔| s |琼斯6 | jaime | l |桑德森7 |路易斯| l | Broderick 8 | Debby | r |索普9 |辛西娅| n | Figueroa 10 |阿曼达| k | Schnieders.

假设我们正在使用表单输入字段，并且希望返回实时匹配用户击键匹配的名称。如果用户类型＆＃34;返回＆＃34;什么查询可以找到以＆＃34开头的所有姓氏;返回＆＃34 ;??

在50,000个名称的测试数据上，查询返回8个名称，约为11ms。

编号|给定名称|中间字母|姓氏-------- + ----------- + ----------------- ++ ------------ 6788 |米尔顿| E |支持者7748 |伯爵L | Backlund 11787 | Estela | J | Backlund 28516 |莉莲| J |巴科斯31087 |约翰| F |支持者35760 |乔伊斯| D | Backman 43301 |罗纳德| S | Backman 44967 |丽莎| J |贝克曼

到目前为止，我们还没有索引，因此，如果我们索引＆＃34;柱子？

现在，当我们运行＆＃34; LIKE＆＃34;查询，结果是在大约11毫秒内有8个名称。等一下索引并没有使查询更快！

问题是＆＃34; text＆＃34;的默认运算符类数据类型不支持模式匹配。如果要进行前缀匹配，则需要使用＆＃34; text_pattern_ops＆＃34;创建索引。操作员类别。

您不能指望用户对输入中的大写和小写字符应用任何特定的规则。因此，唯一明智的做法是将所有输入强制转换为单个大小写。

PostgreSQL支持＆＃34;功能＆＃34;索引，其中索引建立在存储数据的功能转换之上。只要查询使用与函数相同的转换，就可以索引数据的转换，而不必直接将其存储在表中。

现在，查询又回到了在大约1毫秒内返回8个名称的过程，但完全不区分大小写。

到目前为止，我们的虚构用户非常擅长输入与数据库匹配的数据。但如果他们正在寻找和＃34;罗伯特·哈灵顿＆＃34;并输入＆＃34; Robert Harington＆＃34; （一个＆＃34; R＆＃34;）......有没有办法仍然可以找到他们正在寻找的名称，而不会牺牲性能？

输入PostgreSQL FuzzyStrmatch扩展！此扩展提供了一些用于匹配类似 - 但不相同的字符串的实用程序函数。

我们将使用的第一个功能计算两个字符串之间的Levenshtein距离。 Levenshtein距离是字符转换数量的总和和字符插入/删除的数量。

我们可以使用Levenshtein距离来写一个查询，该查询找到输入字符串的一个字符内的所有数据库条目（＆＃34;罗伯特Harington＆＃34;）。为了保持更简单的事情，我们比较＆＃34;全名＆＃34;姓氏和给定名称的串联。

用q为（选择＆＃39; robert＆＃39;作为qgn，＆＃39; arington＆＃39; qsn）选择levenshtein（较低（concat（姓氏，给定名称）），更低（concat（qsn，qgn）））作为leven，名字。 *来自名称，q其中levenshtein（较低（姓名，给定名称）），更低（concat（qsn，qgn）））））＆lt; leven 2订购;

leven |号码|伯克名|中数体|姓氏------- + -------- + ----------- + -------------- + ---- -------- 1 | 1186 |罗伯特| H | Harrington 1 | 21256 |罗伯特| B |哈灵顿

唯一的麻烦是，它＆＃39非常慢（超过100ms），因为它正在计算我们候选字符串和数据库中的每个名称之间的Levenshtein距离。没有一些索引帮助，这种方法不会扩展。

我们要做的是使用索引过滤器将候选记录的数量减少到可管理的大小，然后仅对那些记录执行昂贵的Levenshtein计算。

Soundex算法将一个单词简化为一个“语音代码”，基本上是一个简短的字符串，近似于初始音节的发音。这使我们避免了各种常见的拼写错误，因为Soundex只要错误的发音相似就相同。

我们的完整数据库有50,000条记录：与姓氏的soundex匹配的记录集有多少？

因此，我们可以添加soundex功能索引，然后重新编写Levenshtein查询以将soundex用作预过滤器，从而以完全索引的速度获得levenshtein计算。

使用q AS（SELECT＆＃39; Robert＆＃39; AS qgn，＆＃39; Harington＆＃39; AS qsn）SELECT levenshtein（lower（concat（姓氏，姓氏）），lower（concat（qsn，qgn）））作为名字，名字。 *从名称中，q WoundE（Soundex（surname）= soundex（qsn）和levenshtein（lower（concat（姓氏，给定名称）），lower（concat（qsn，qgn）））＆lt; 2

现在我们得到与以前相同的答案，但仅1毫秒，快了一百倍。

使用正确的索引和Fuzzystrmatch工具包，可以构建非常快速的松散文本匹配查询。

将soundex（）上的索引与levenshtein（）等昂贵的测试结合起来，以快速搜索模糊查询。