PostgreSQL 10.1 手册_部分 II. SQL 语言_第 12 章 全文搜索_12.3. 空值文本搜索

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 12.3. 空值文本搜索 12.3.1. 解析文档 12.3.2. 解析查询 12.3.3. 排名搜索结果 12.3.4. 加亮结果 要实现全文搜索必须要有一个从文档创建tsvector以及从用户查询创建tsquery的函数。

12.3. 空值文本搜索

要实现全文搜索必须要有一个从文档创建tsvector以及从用户查询创建tsquery的函数。而且我们需要一种有用的顺序返回结果,因此我们需要一个函数能够根据文档与查询的相关性比较文档。还有一点重要的是要能够很好地显示结果。PostgreSQL对所有这些函数都提供了支持。

12.3.1. 解析文档

PostgreSQL提供了函数to_tsvector将一个文档转换成tsvector数据类型。

to_tsvector([ config regconfig, ] document text) returns tsvector

to_tsvector把一个文本文档解析成记号,把记号缩减成词位,并且返回一个tsvector,它列出了词位以及词位在文档中的位置。文档被根据指定的或默认的文本搜索配置来处理。下面是一个简单例子:

SELECT to_tsvector('english', 'a fat  cat sat on a mat - it ate a fat rats');
                  to_tsvector
-----------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

在上面这个例子中我们看到,作为结果的tsvector不包含词aonit,词rats变成了rat,并且标点符号-被忽略了。

to_tsvector函数在内部调用了一个解析器,它把文档文本分解成记号并且为每一种记号分配一个类型。对于每一个记号,会去查询一个词典列表(第 12.6 节),该列表会根据记号的类型而变化。第一个识别记号的词典产生一个或多个正规化的词位来表示该记号。例如,rats变成rat是因为一个词典识别到该词ratsrat的复数形式。一些词会被识别为停用词第 12.6.1 节),这将导致它们被忽略,因为它们出现得太频繁以至于在搜索中起不到作用。在我们的例子中有aonit是停用词。如果在列表中没有词典能识别该记号,那它将也会被忽略。在这个例子中标点符号-就属于这种情况,因为事实上没有词典会给它分配记号类型(空间符号),即空间记号不会被索引。对于解析器、词典以及要索引哪些记号类型是由所选择的文本搜索配置(第 12.7 节)决定的。可以在同一个数据库中有多种不同的配置,并且有用于很多种语言的预定义配置。在我们的例子中,我们使用用于英语的默认配置english

函数setweight可以被用来对tsvector中的项标注一个给定的权重,这里一个权重可以是四个字母之一:ABCD。这通常被用来标记来自文档不同部分的项,例如标题对正文。稍后,这种信息可以被用来排名搜索结果。

因为to_tsvector(NULL) 将返回NULL,不论何时一个域可能为空时,我们推荐使用coalesce。下面是我们推荐的从一个结构化文档创建一个tsvector的方法:

UPDATE tt SET ti =
    setweight(to_tsvector(coalesce(title,'')), 'A')    ||
    setweight(to_tsvector(coalesce(keyword,'')), 'B')  ||
    setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
    setweight(to_tsvector(coalesce(body,'')), 'D');

这里我们已经使用了setweight在完成的tsvector标注每一个词位的来源,并且接着将标注过的tsvector值用tsvector连接操作符||合并在一起(第 12.4.1 节给出了关于这些操作符的细节)。

12.3.2. 解析查询

PostgreSQL提供了函数to_tsqueryplainto_tsqueryphraseto_tsquery用来把一个查询转换成tsquery数据类型。to_tsquery提供了比plainto_tsqueryphraseto_tsquery更多的特性,但是它对其输入要求更加严格。

to_tsquery([ config regconfig, ] querytext text) returns tsquery

to_tsqueryquerytext创建一个tsquery值,该值由被tsquery操作符&(AND)、|(OR)、!(NOT)和<->(FOLLOWED BY)分隔的单个记号组成。 这些操作符可以使用圆括号分组。换句话说,to_tsquery的输入必须已经遵循tsquery输入的一般规则,如第 8.11.2 节所述。区别在于基本的tsquery输入把记号当作表面值,而to_tsquery 会使用指定的或者默认的配置把每一个记号正规化成一个词位,并且丢弃掉任何根据配置是停用词的记号。例如:

SELECT to_tsquery('english', 'The & Fat & Rats');
  to_tsquery   
---------------
 'fat' & 'rat'

和在基本tsquery输入中一样,权重可以被附加到每一个词位来限制它只匹配属于那些权重的tsvector词位。例如:

SELECT to_tsquery('english', 'Fat | Rats:AB');
    to_tsquery    
------------------
 'fat' | 'rat':AB

同样,*可以被附加到一个词位来指定前缀匹配:

SELECT to_tsquery('supern:*A & star:A*B');
        to_tsquery        
--------------------------
 'supern':*A & 'star':*AB

这样一个词位将匹配一个tsvector中的任意以给定字符串开头的词。

to_tsquery也能够接受单引号短语。当配置包括一个会在这种短语上触发的分类词典时就是它的主要用处。在下面的例子中,一个分类词典含规则supernovae stars : sn

SELECT to_tsquery('''supernovae stars'' & !crab');
  to_tsquery
---------------
 'sn' & !'crab'

在没有引号时,to_tsquery将为那些没有被 AND、OR 或者 FOLLOWED BY 操作符分隔的记号产生一个语法错误。

plainto_tsquery([ config regconfig, ] querytext text) returns tsquery

plainto_tsquery将未格式化的文本querytext转换成一个tsquery值。该文本被解析并被正规化,很像to_tsvector,然后&(AND)布尔操作符被插入到留下来的词之间。

例子:

SELECT plainto_tsquery('english', 'The Fat Rats');
 plainto_tsquery 
-----------------
 'fat' & 'rat'

注意plainto_tsquery不会识其输入中的tsquery操作符、权重标签或前缀匹配标签:

SELECT plainto_tsquery('english', 'The Fat & Rats:C');
   plainto_tsquery   
---------------------
 'fat' & 'rat' & 'c'

这里,所有输入的标点都被作为空间符号并且丢弃。

phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery

phraseto_tsquery的行为很像plainto_tsquery,不过前者会在留下来的词之间插入<->(FOLLOWED BY)操作符而不是&(AND)操作符。还有,停用词也不是简单地丢弃掉,而是通过插入<N>操作符(而不是<->操作符)来解释。在搜索准确的词位序列时这个函数很有用,因为 FOLLOWED BY 操作符不只是检查所有词位的存在性,还会检查词位的顺序。

例子:

SELECT phraseto_tsquery('english', 'The Fat Rats');
 phraseto_tsquery
------------------
 'fat' <-> 'rat'

plainto_tsquery相似,phraseto_tsquery函数不会识别其输入中的tsquery操作符、权重标签或者前缀匹配标签:

SELECT phraseto_tsquery('english', 'The Fat & Rats:C');
      phraseto_tsquery
-----------------------------
 'fat' <-> 'rat' <-> 'c'

12.3.3. 排名搜索结果

排名处理尝试度量文档和一个特定查询的接近程度,这样当有很多匹配时最相关的那些可以被先显示。PostgreSQL提供了两种预定义的排名函数,它们考虑词法、临近性和结构信息;即,它们考虑查询词在文档中出现得有多频繁,文档中的词有多接近,以及词出现的文档部分有多重要。不过,相关性的概念是模糊的并且与应用非常相关。不同的应用可能要求额外的信息用于排名,例如,文档修改时间。内建的排名函数只是例子。你可以编写你自己的排名函数和/或把它们的结果与附加因素整合在一起来适应你的特定需求。

目前可用的两种排名函数是:

ts_rank([ weights float4[]vector tsvectorquery tsquery [normalization integer ]) returns float4

基于向量的匹配词位的频率来排名向量。

ts_rank_cd([ weights float4[]vector tsvectorquery tsquery [normalization integer ]) returns float4

这个函数为给定文档向量和查询计算覆盖密度排名,该方法在 Clarke、Cormack 和 Tudhope 于 1999 年在期刊 "Information Processing and Management" 上的文章 "Relevance Ranking for One to Three Term Queries" 文章中有描述。覆盖密度类似于ts_rank排名,不过它会考虑匹配词位相互之间的接近度。

这个函数要求词位的位置信息来执行其计算。因此它会忽略tsvector中任何被剥离的词位。如果在输入中有未被剥离的词位,结果将会是零(strip函数和tsvector中的位置信息的更多内容请见第 12.4.1 节)。

对这两个函数,可选的权重参数提供了为词实例赋予更多或更少权重的能力,这种能力是依据它们被标注的情况的。权重数组指定每一类词应该得到多重的权重,按照如下的顺序:

{D-权重, C-权重, B-权重, A-权重}

如果没有提供权重,那么将使用这些默认值:

{0.1, 0.2, 0.4, 1.0}

通常权重被用来标记来自文档特别区域的词,如标题或一个初始的摘要,这样它们可以被认为比来自文档正文的词更重要或更不重要。

由于一个较长的文档有更多的机会包含一个查询术语,因此考虑文档的尺寸是合理的,例如一个一百个词的文档中有一个搜索词的五个实例而零一个一千个词的文档中有该搜索词的五个实例,则前者比后者更相关。两种排名函数都采用一个整数正规化选项,它指定文档长度是否影响其排名以及如何影响。该整数选项控制多个行为,因此它是一个位掩码:你可以使用|指定一个或多个行为(例如,2|4)。

  • 0(默认值)忽略文档长度

  • 1 用 1 + 文档长度的对数除排名

  • 2 用文档长度除排名

  • 4 用长度之间的平均调和距离除排名(只被ts_rank_cd实现)

  • 8 用文档中唯一词的数量除排名

  • 16 用 1 + 文档中唯一词数量的对数除排名

  • 32 用排名 + 1 除排名

如果多于一个标志位被指定,转换将根据列出的顺序被应用。

值得注意的是排名函数并不使用任何全局信息,因此它不可能按照某些时候期望地产生一个公平的正规化,从 1% 或 100%。正规化选项 32 (rank/(rank+1))可以被应用来缩放所有的排名到范围零到一,但是当然这只是一个外观上的改变;它不会影响搜索结果的顺序。

这里是一个例子,它只选择十个最高排名的匹配:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
                     title                     |   rank
-----------------------------------------------+----------
 Neutrinos in the Sun                          |      3.1
 The Sudbury Neutrino Detector                 |      2.4
 A MACHO View of Galactic Dark Matter          |  2.01317
 Hot Gas and Dark Matter                       |  1.91171
 The Virgo Cluster: Hot Plasma and Dark Matter |  1.90953
 Rafting for Solar Neutrinos                   |      1.9
 NGC 4650A: Strange Galaxy and Dark Matter     |  1.85774
 Hot Gas and Dark Matter                       |   1.6123
 Ice Fishing for Cosmic Neutrinos              |      1.6
 Weak Lensing Distorts the Universe            | 0.818218

这是相同的例子使用正规化的排名:

SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE  query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
                     title                     |        rank
-----------------------------------------------+-------------------
 Neutrinos in the Sun                          | 0.756097569485493
 The Sudbury Neutrino Detector                 | 0.705882361190954
 A MACHO View of Galactic Dark Matter          | 0.668123210574724
 Hot Gas and Dark Matter                       |  0.65655958650282
 The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
 Rafting for Solar Neutrinos                   | 0.655172410958162
 NGC 4650A: Strange Galaxy and Dark Matter     | 0.650072921219637
 Hot Gas and Dark Matter                       | 0.617195790024749
 Ice Fishing for Cosmic Neutrinos              | 0.615384618911517
 Weak Lensing Distorts the Universe            | 0.450010798361481

排名可能会非常昂贵,因为它要求查询每一个匹配文档的tsvector,这可能会涉及很多I/O因而很慢。不幸的是,这几乎不可能避免,因为实际查询常常导致巨大数目的匹配。

12.3.4. 加亮结果

要表示搜索结果,理想的方式是显示每一个文档的一个部分并且显示它是怎样与查询相关的。通常,搜索引擎显示文档片段时会对其中的搜索术语进行标记。PostgreSQL提供了一个函数ts_headline来实现这个功能。

ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) returns text

ts_headline接受一个文档和一个查询,并且从该文档返回一个引用,在其中来自查询的术语会被加亮。被用来解析该文档的配置可以用config指定;如果config被忽略,将会使用default_text_search_config配置。

如果一个options字符串被指定,它必须由一个逗号分隔的列表组成,列表中是一个或多个option=value对。可用的选项是:

  • StartSelStopSel:用来定界文档中出现的查询词的字符串,这用来把它们与其他被引用的词区分开。如果这些字符串包含空格或逗号,你必须把它们加上双引号。

  • MaxWordsMinWords:这些数字决定要输出的最长和最短 headline。

  • ShortWord:长度小于等于这个值的词将被从一个 headline 的开头或结尾处丢掉。默认值三消除普通英语文章。

  • HighlightAll:布尔标志,如果为true整个文档将被用作 headline,并忽略前面的三个参数。

  • MaxFragments:要显示的文本引用或片段的最大数量。默认值零选择一种非片段倾向的 headline 生成方法。一个大于零的值选择基于片段的 headline 生成。这种方法找到有尽可能多查询词的文本片段并且展开查询词周围的那些片段。结果是查询词会靠近每个片段的中间并且在其两侧都有词。每一个片段将是最多MaxWords并且长度小于等于ShortWord的词被从每个片段的开头或结尾丢弃。如果不是所有的查询词都在该文档中找到,文档中第一个MinWords的单一片段将被显示。

  • FragmentDelimiter:当多于一个片段被显示时,片段将被这个字符串所分隔。

任何未指定的选项将收到这些默认值:

StartSel=<b>, StopSel=</b>,
MaxWords=35, MinWords=15, ShortWord=3, HighlightAll=FALSE,
MaxFragments=0, FragmentDelimiter=" ... "

例如:

SELECT ts_headline('english',
  'The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.',
  to_tsquery('query & similarity'));
                        ts_headline                         
------------------------------------------------------------
 containing given <b>query</b> terms
 and return them in order of their <b>similarity</b> to the
 <b>query</b>.

SELECT ts_headline('english',
  'The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.',
  to_tsquery('query & similarity'),
  'StartSel = <, StopSel = >');
                      ts_headline                      
-------------------------------------------------------
 containing given <query> terms
 and return them in order of their <similarity> to the
 <query>.

ts_headline使用原始文档,而不是一个tsvector摘要,因此它可能很慢并且应该被小心使用。

本文转自PostgreSQL中文社区,原文链接: 12.3. 空值文本搜索
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
6月前
|
SQL Oracle 关系型数据库
SQL 通配符:用于模糊搜索和匹配的 SQL 关键技巧
通配符字符用于替代字符串中的一个或多个字符。通配符字符与LIKE运算符一起使用。LIKE运算符用于在WHERE子句中搜索列中的指定模式。
84 0
|
负载均衡 监控 关系型数据库
百度搜索:蓝易云【PostgreSQL 主从复制方案】
请注意,上述仅为一种主从复制方案的概述,实际实施时可能需要根据特定环境和需求进行调整。建议参考PostgreSQL官方文档和其他可靠资源获取更详细的指南和说明。
109 1
|
Ubuntu 关系型数据库 数据库
百度搜索:蓝易云【Ubuntu系统安装 PostgreSQL详细教程。】
现在,你已经成功在Ubuntu系统上安装了PostgreSQL,并创建了一个新的数据库和用户。你可以使用所创建的用户凭据连接到数据库并开始使用。记得根据你的具体需求进行进一步的配置和安全性调整。
287 2
|
3月前
|
SQL 关系型数据库 MySQL
|
3月前
|
SQL 存储 关系型数据库
COALESCE 函数:SQL中的空值处理利器
【8月更文挑战第31天】
281 0
|
3月前
|
SQL Oracle 关系型数据库
NVL() 函数:SQL中的空值处理利器
【8月更文挑战第31天】
315 0
|
3月前
|
SQL 数据处理 数据库
SQL正则表达式应用:文本数据处理的强大工具——深入探讨数据验证、模式搜索、字符替换等核心功能及性能优化和兼容性问题
【8月更文挑战第31天】SQL正则表达式是数据库管理和应用开发中处理文本数据的强大工具,支持数据验证、模式搜索和字符替换等功能。本文通过问答形式介绍了其基本概念、使用方法及注意事项,帮助读者掌握这一重要技能,提升文本数据处理效率。尽管功能强大,但在不同数据库系统中可能存在兼容性问题,需谨慎使用以优化性能。
44 0
|
5月前
|
SQL 监控 安全
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
|
6月前
|
SQL 数据库 开发者
百度搜索:蓝易云【SQL高级之慢查询日志?】
慢查询日志是数据库性能优化中一个重要的工具,通过记录执行时间较长的查询语句,帮助识别和解决数据库中的性能问题。它提供了有价值的信息,帮助开发者和管理员优化查询和提升数据库的性能。
68 6
|
6月前
|
SQL 存储
百度搜索:蓝易云【高级SQL语句】
这些是SQL中一些常见的高级语句示例,它们可以帮助你处理更复杂的查询和数据操作需求。请根据具体情况选择适当的语句,并参考相关的SQL文档和教程来进一步了解和学习。
64 0