第1章全文检索语法
1.1 概述
查询引擎为文本数据类型提供~,~*,LIKE和ILIKE操作符，并提供全文检索以识别自然语言文档，并通过相关性查询进行排序。查询引擎提供两种数据类型用于支持全文检索，即tsvector类型与tsquery类型。
1.2 文档(tsvector)类型
对于tsvector类型，表示一个检索单元，通常是一个数据库表中一行的文本字段，或者这些字段的可能组合（级联），也可能存储在多个表中或者动态地获得，它的值是一个无重复值的lexemes排序列表，即一些同一个词的不同变种的标准化，在输入的同时会自动排序和消除重复。to_tsvector函数通常用于解析和标准化文档字符串。
一个tsvector的值是唯一分词的分类列表，把一话一句词格式化为不同的词条，在进行分词处理的时候tsvector会自动去掉分词中重复的词条，按照一定的顺序装入。例如
SELECT 'a fat cat sat on a mat and atea fat rat'::tsvector;
tsvector
----------------------------------------------------
'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat''sat'
从上面的例子可以看出 ，通过tsvector把一个字符串按照空格进行分词，分词的顺序是按照长短和字母来排序的。但是某些时候，为了让词条中包含空格或者符号，就需要对其使用引号。
SELECT $$the lexeme ' ' contains spaces$$::tsvector;
tsvector
-------------------------------------------
' ''contains' 'lexeme' 'spaces' 'the'
为了使用引号，可以使用双$$符号来避免混淆。并且词条位置常量可以附属于每个词条,例如：
SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
tsvector
-------------------------------------------------------------------------------
'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11'mat':7 'on':5 'rat':12 'sat':4
理解tsvector类型是很重要的,不能只关注标准的应用.例如
select 'The Fat Rats'::tsvector;
tsvector
--------------------
'Fat' 'Rats' 'The'
但是对于英文全文检索应用来说,上面的句子就是非标准化的,但是tsvector是不会知道的,为处理加工的文本应该通过使用to_tsvector函数来是之规格化,标注化的应用于搜索.
SELECT to_tsvector('english','The Fat Rats');
to_tsvector
-----------------
'fat':2 'rat':3
1.3查询(tsquery)类型
对于tsquery类型，表示一个检索条件，存储用于检索的词汇，并且使用布尔操作符&(AND)，|(OR)和!(NOT) 来组合它们，括号用来强调操作符的分组。与tsvector一样，任何单词必须在转换为tsquery类型前规范化。to_tsquery函数及plainto_tsquery函数可以方便的用来执行规范化。
SELECT 'fat & rat'::tsquery;
tsquery
---------------
'fat' & 'rat'
SELECT 'fat & (rat | cat)'::tsquery;
tsquery
---------------------------
'fat' & ( 'rat' | 'cat' )
SELECT to_tsquery('english','fat & rat');
to_tsquery
---------------
'fat' & 'rat'
to_tsquery函数在处理查询文本的时候，查询文本的单个词之间要使用逻辑操作符（& (AND),| (OR) and ! (NOT)）连接（或者使用括号）。例如
SELECT to_tsquery('english','FatRats');
如果要使执行上面的操作，就会报语法错误。然而plainto_tsquery函数却可以提供一个标准的tsquery，如上面的例子，plainto_tsquery会自动加上逻辑&操作符。
SELECT plainto_tsquery('english','Fat Rats');
plainto_tsquery
-----------------
'fat' & 'rat'
但是plainto_tsquery函数不能够识别逻辑操作符和权重标记。
SELECTplainto_tsquery('english','The Fat & Rats:C');
plainto_tsquery
---------------------
'fat'& 'rat' & 'c'
1.4检索表
查询引擎的全文检索基于匹配算子@@，如果一个tsvector与一个tsquery匹配，则返回true。在不使用索引的情况下也是可以进行全文检索的,一个简单查询,显示出title从所有body字段中包含friend的每一行：
SELECT title
FROM web
WHERE to_tsvector('english',body) @@ to_tsquery('english','friend');
其中to_tsvector和to_tsquery中第一个参数用于指定全文检索的分词语言设置，一般可省略，语句如下：
SELECT title
FROM web
WHERE to_tsvector(body) @@ to_tsquery('friend');
1.5创建索引
gist 和 gin的索引类型，这两种索引都能用在提高全文检索的速度，注意全文检索不一定非要使用索引，但是当一个字段被固定规律搜索时，使用索引将会有很好的效果。创建gist 索引字段的类型可以是 tsvector 或者 tsquery。创建gin 索引字段的类型必须是tsvector
CREATE INDEX web_idx ON web USING gin(to_tsvector('english',body));
创建索引可以有多种方式.索引的创建甚至可以连接两个列:
CREATE INDEX web_idx ON web USING gin(to_tsvector('english',title ||body));
另外的一种方式是创建一个单独的 tsvector列,然后使用to_tsvector函数把需要索引字段的数据联合在一起，比如列title和body，并且使用函数coalesce来确保字段为NULL的可以建立索引。如下：
ALTER TABLE web ADD COLUMN textsearchable_index_col tsvector;
UPDATE web SET textsearchable_index_col =
to_tsvector('english',coalesce(title,'') ||coalesce(body,''));
然后，就可以创建倒排的索引
CREATE INDEX textsearch_idx ON web USING gin(textsearchable_index_col);
索引创建完毕，就可以使用全文检索了。
SELECT title
FROM web
WHERE textsearchable_index_col @@ to_tsquery('create& table')
ORDER BY last_mod_date DESC LIMIT 10;
1.6权重匹配(Weight)
提供一个函数setweight，使用这个函数要引入一个概念，这个概念就是权重weight，什么是权重，字面上解释就是权衡一下哪个更重要，也就是说哪个更侧重一些。可以通过函数setweight来设置权重，switf提供了四个权重级别A，B，C，D，级别类型用来标记他们来自于文档中的不同部分，例如title和body。查询结果的关注度可以使用这个权重级别。如：
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');
在搜索中tsquery中可以使用权重（weight）,在搜索词条中附加权重，查询的结果就是在这个权重范围的了。
SELECT to_tsquery('english','Fat | Rats:AB');
to_tsquery
------------------
'fat' | 'rat':AB
1.7相关(Ranking)查询
相关度试图衡量哪一个文档是检索中最关注的，所以当有很多匹配时，最相关的一个则最先显示。查询引擎提供了两个预定义的相关函数（ts_rank和rs_rank_cd），考虑了查询词在文档中出现的频率，术语在文档中的紧密程度，以及它们在文档中的部分的重要性。
这两个函数的语法是
ts_rank([ weights float4[],] vectortsvector,query tsquery [,normalization integer ]) returns float4
ts_rank_cd([ weights float4[],normalization integer ]) returns float4
两个函数的第一个参数都是 权重(weight)，在前面已经讲了权重的概念。
参数的格式为 {D-weight,C-weight,B-weight,A-weight} ，在使用函数的时候没有指定这个参数，默认指定参数为：{0.1,0.2,0.4,1.0}
vector tsvector表示分词的位置
query tsquery 表示查询关键词的位置
因为一个长文档有更大的几率包含检索的关键词，我们认为一个包含100词的文档有5个关键词，比一个包含1000个词的文档有五个关键词更相关。所以这里用最后一个参数来表示文档长度对得分的影响，你可以指定一个或者多个例如（2|4）。这些参数的定义
0 (the default) ignores the documentlength
表示跟长度大小没有关系
1 divides the rank by 1 + the logarithmof the document length
表示参数 关注度（rank）除以 文档长度的对数+1
2 divides the rank by the documentlength
表示 关注度 除以 文档的长度
4 divides the rank by the mean harmonicdistance between extents (this is implemented only by ts_rank_cd)
表示 关注度 除以 文档长度的平均值，只能使用函数ts_rank_cd.
8 divides the rank by the number ofunique words in document
表示 关注度 除以 文档中 唯一分词的数量
16 divides the rank by 1 + thelogarithm of the number of unique words in document
表示关注度 除以 唯一分词数量的对数+1
32 divides the rank by itself + 1
表示 关注度 除以 本身+1
下面是返回得分最高的前10项的例子
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 NeutrinoDetector |2.4
A MACHO View of Galactic DarkMatter | 2.01317
Hot Gas and Dark Matter | 1.91171
The Virgo Cluster: Hot Plasmaand Dark Matter | 1.90953
Rafting for SolarNeutrinos |1.9
NGC 4650A: Strange Galaxy andDark Matter | 1.85774
Hot Gas and Dark Matter | 1.6123
Ice Fishing for CosmicNeutrinos |1.6
Weak Lensing Distorts theUniverse | 0.818218
这是相同的例子使用规范化的排名
SELECT title,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
1.8索引统计函数
ts_stat(sqlquery text,[ weights text,] OUT word text,OUT ndoc integer,OUT nentry integer)
返回的是统计的纪录
word text — 索引中的词条
ndoc integer — 词条在索引中出现的次数
nentry integer — 词条在文档中出现的总次数
例如：
SELECT * FROMts_stat('SELECT tsv FROM messages')
ORDER BY nentry DESC,ndoc DESC,word
LIMIT 10;
查询的结果为
wordndoc nentry
test2 3
title2 2
test1 2
body1 1
上面可以看到，通过ts_stat函数就可以看到索引列中的分词的情况。
1.9 屏蔽词(Stop Words)
stop words 是一个很普遍并且在每个文档中几乎都能出现的的词，并且这个词没有实际的意义，因此在全文检索的文档中这些词将被忽略。例如英文文本内容中单词 像 a 和like，他们不需要存储在索引中，但是他会影响词所在文档的位置。
SELECT to_tsvector('english','in the list of stopwords');
to_tsvector
----------------------------
'list':3'stop':5 'word':6
并且相关度的计算与是否存在stopwords是十分不同的,如:
SELECT ts_rank_cd (to_tsvector('english','in thelist of stop words'),to_tsquery('list & stop'));
ts_rank_cd
------------
0.05
SELECT ts_rank_cd (to_tsvector('english','list stopwords'),to_tsquery('list & stop'));
ts_rank_cd
------------
0.1
原文链接：https://www.f2er.com/postgresql/195546.html