标签
PostgreSQL , 全文检索 , ranking
背景
《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
《排序算法》这个章节实际上介绍了PostgreSQL的ranking算法。
tsvector将文档分为4层结构：标题、作者、摘要、内容。对这四个层级，用户可以设定对应的weight，用于ranking的计算。同时用户可以设定ranking的修正掩码。
但是只有四个层级，远远不能满足业务需求，那么PostgreSQL实现更多层级，更精细的ranking算法呢？
如何自定义ranking呢？
例如在电商行业中，我们可能存储了每个店铺的标签，每个标签旁边可能是一个系数，这个系数可能是动态调整的。在搜索的时候，商店的某些标签被命中了，可能搜索到了几百万个店铺，但是最后要根据权重算出应该如何排序，并得到其中的1万个店铺。
这种情况，如何精细化排序就体现出来了。
例子1 - tsvector向量
1、店铺标签表：
create table tbl (
shop_id int8 primary key, -- 店铺 ID
tags text -- 多值类型，标签1:评分1,标签2:评分2,.....
);
对tags字段，使用UDF索引，存储标签的数组或tsvector索引。
如果使用tsvector，主要是便于使用PostgreSQL的全文检索的语法，包含、不包含、距离等。
tags例如
国民_足浴:0.99,国民_餐饮:0.1,娱乐_KTV:0.45
2、标签权值表：
create table tbl_weight (
tagid int primary key, -- 标签ID
tagname name, -- 标签名
desc text, -- 标签描述
weight float8 -- 标签权值
);
create index idx_tbl_weight_1 on tbl_weight (tagname);
3、文本转标签数组、tsvector的UDF
create or replace function text_to_tsvector(text) returns tsvector as $$
select array_to_tsvector(array_agg(substring(id,'(.+):'))) from unnest(regexp_split_to_array($1, ',')) as t(id);
$$ language sql strict immutable;
postgres=# select text_to_tsvector('abc:1.1,bc:100,c:293');
text_to_tsvector
------------------
'abc' 'bc' 'c'
(1 row)
创建TSVECTOR表达式索引
create index idx_tbl_1 on tbl using gin (text_to_tsvector(tags));
4、取出命中标签权值的UDF
postgres=# select substring('bc:1.1,abc:100,c:293','[^,]?abc:([\d\.]+)') ;
substring
-----------
100
(1 row)
postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?abc:([\d\.]+)') ;
substring
-----------
1.1
(1 row)
未命中则返回NULL
postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') ;
substring
-----------
(1 row)
postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') is null;
?column?
----------
t
(1 row)
5、全文检索
参考 《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
select
6、精排
计算ranking可能是结合 “命中的标签、评分、标签本身的权值” 根据算法得到一个ranking值。
根据4得到命中标签的评分，根据命中标签从tbl_weight得到对应的权值。
将算法封装到UDF，最后得到RANKING。
ranking算法的UDF函数内容略，请根据业务的需要编写对应算法，伪代码如下。
create or replace function cat_ranking(tsquery) returns float8 as $$
declare
begin
for each x in array (contains_element) loop
search hit element's score.
search hit element's weight.
cat ranking and increment
end loop;
return res;
end;
$$ language plpgsql strict;
7、
7.1 删除标签与对应的评分：
regexp_replace 函数。
7.2 追加标签与对应的评分：
concat函数。
7.3 修改元素评分：
regexp_replace 函数。
以上都可以使用正则表达式来操作。
例子2 - 多维数组
使用数组来存储标签和权值，实际上在编程上会比使用tsvector更简单。
首先需要介绍一些用到的数组函数
根据元素求位置，根据标签，求它的位置，根据这个位置从score[]得到它的SCORE。
postgres=# select array_position(array[1,2,null,null,2,2,3,1],null);
array_position
----------------
3
(1 row)
postgres=# select array_positions(array[1,2,null,null,2,2,3,1],null);
array_positions
-----------------
{3,4}
(1 row)
postgres=# select array_positions(array[1,2,null,null,2,2,3,1],2);
array_positions
-----------------
{2,5,6}
(1 row)
求某个位置的元素
array[i]
postgres=# select (array[1,2,null,null,2,2,3,1])[1];
array
-------
1
(1 row)
postgres=# select (array[1,2,null,null,2,2,3,1])[3];
array
-------
(1 row)
postgres=# select (array[1,2,null,null,2,2,3,1])[5];
array
-------
2
(1 row)
追加元素
array_append
替换元素
array_replace
删除某个元素
array_remove，注意如果有一样的元素，都会被删掉（如果有一样的score，就的注意，需要用删除位置来删除元素）
postgres=# select array_remove(array[1,2,null,null,2,2,3,1],2);
array_remove
-------------------
{1,NULL,NULL,3,1}
(1 row)
删除某个位置的元素，
postgres=# create or replace function array_remove(anyarray,int[]) returns anyarray as $$
select array(select $1[i] from (select id from generate_series(1,array_length($1,1)) t(id) where id <> all( $2) ) t(i))
$$ language sql strict;
CREATE FUNCTION
postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[1,2]);
array_remove
---------------------
{NULL,NULL,2,2,3,1}
(1 row)
postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[3,5]);
array_remove
------------------
{1,2,NULL,2,3,1}
(1 row)
1、店铺标签表：
create table tbl (
shop_id int8 primary key, -- 店铺 ID
tags text[], -- 数组，标签1,标签2,.....
scores float8[] -- 数组，评分1,评分2,.....
);
create index idx_tbl_1 on tbl using gin(tags);
国民_足浴,国民_餐饮,娱乐_KTV
0.99,0.1,0.45
2、标签权值表：
create table tbl_weight (
tagid int primary key, -- 标签ID
tagname name, -- 标签名
desc text, -- 标签描述
weight float8 -- 标签权值
);
create index idx_tbl_weight_1 on tbl_weight (tagname);
3、包含、不包含、相交的数组查询。
https://www.postgresql.org/docs/10/static/functions-array.html
4、精排算法，与例子1类似。自定义UDF即可。
使用array简化了开发工作量，不需要使用正则表达式，效率也会提高。
参考
https://www.postgresql.org/docs/10/static/functions-matching.html
