背景
在日常的生活中，我们可能会经常需要一些像相近、相仿、距离接近、性格接近等等类似这样的需求，对数据进行筛选。
这些需求PostgreSQL居然都支持，是不是很变态。
变态的例子
这些场景都支持索引排序和检索，否则怎么叫变态呢。
按长相相似度排序
比如最近的王宝强和马蓉的事件，估计很多人会拿宋喆的照片进行相似度的搜索，八卦八卦。
说起图像搜索，我前几天才写了一篇这样的文章，是关于在PG数据库中使用图像搜索插件的文章。
《弱水三千,只取一瓢,当图像搜索遇见PostgreSQL(Haar wavelet)》
按喜好重合度排序
比如收集了人群的各种喜好的数据，按喜好进行聚类，或者按喜好的重叠度进行排序，找出目标人群。
按年龄相近程度排序
这个相对简单，比如输入23岁，按接近23岁的输出即可。
例子 https://www.postgresql.org/docs/9.5/static/btree-gist.html
输出与100最接近的10条数据。
postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create table test12(id int);
CREATE TABLE
postgres=# insert into test12 select trunc(random()*1000) from generate_series(1,100000);
INSERT 0 100000
postgres=# create index idx_test12 on test12 using gist(id);
CREATE INDEX
postgres=# select * from test12 order by id <-> 100 limit 10;
id
-----
100
100
100
100
100
100
100
100
100
100
(10 rows)
按距离排序
https://www.postgresql.org/docs/9.5/static/functions-geometry.html
例如取出与某个点最近的10个点。
postgres=# create table test13(c1 point);
CREATE TABLE
postgres=# insert into test13 select ('('||trunc(random()1000)||','||trunc(random()5000)||')')::point from generate_series(1,10000);
INSERT 0 10000
postgres=# create index idx_test13 on test13 using gist(c1);
CREATE INDEX
postgres=# select * from test13 order by c1 <-> point '(1,10000)' limit 10;
c1
------------
(58,4993)
(191,4995)
(48,4991)
(326,4998)
(99,4988)
(205,4991)
(348,4998)
(53,4986)
(174,4988)
(136,4984)
(10 rows)
按文本的相似度排序
https://www.postgresql.org/docs/9.5/static/pgtrgm.html
例如，根据文本的相似程度，排序输出。
postgres=# create extension pg_trgm;
CREATE EXTENSION
postgres=# create table test14(c1 text);
CREATE TABLE
postgres=# insert into test14 values ('hello digoal'), ('china'), ('hello china'), ('nihao digoal');
INSERT 0 4
postgres=# select * from test14;
c1
--------------
hello digoal
china
hello china
nihao digoal
(4 rows)
postgres=# create index idx_test14 on test14 using gist(c1 gist_trgm_ops);
CREATE INDEX
postgres=# explain select *,c1 <-> 'digoal' from test14 order by c1 <-> 'digoal' limit 2;
QUERY PLAN
--------------------------------------------------------------------------------
Limit (cost=0.13..4.17 rows=2 width=36)
-> Index Scan using idx_test14 on test14 (cost=0.13..8.21 rows=4 width=36)
Order By: (c1 <-> 'digoal'::text)
(3 rows)
postgres=# select *,c1 <-> 'digoal' from test14 order by c1 <-> 'digoal' limit 2;
c1 | ?column?
--------------+----------
hello digoal | 0.461538
nihao digoal | 0.461538
(2 rows)
按分词的相似度排序
https://github.com/postgrespro/rum
这个与前面的文本相似度不同，因为它统计的是分词的相似度，而不是文本的相似度。
支持计算相似度的类型分别为tsvector和tsquery。
例如 搜索带有postgresql 或 digoal 或 oracle 或 postgres 关键词的文章，通常来说返回顺序是只要包含就返回，而不会管它的相似度高低来顺序返回。
rum插件则满足按相似度高低来返回的需求。
rum是类GIN的索引访问接口。
export PATH=/home/digoal/pgsql9.6/bin:$PATH
git clone https://github.com/postgrespro/rum
cd rum
make USE_PGXS=1
make USE_PGXS=1 install
//
//
git clone https://github.com/jaiminpan/pg_jieba
cd pg_jieba
make USE_PGXS=1
make USE_PGXS=1 install
//
//
postgres=# create extension rum;
CREATE EXTENSION
postgres=# create extension pg_jieba;
CREATE EXTENSION
// 分词举例
postgres=# select * from to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造');
to_tsvector
----------------------------------------------------------------------------------
'中国科学院':5 '小明':1 '日本京都大学':10 '毕业':3 '深造':11 '硕士':2 '计算所':6
(1 row)
// 有相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') , to_tsquery('计算所'));
rum_ts_distance
-----------------
16.4493
(1 row)
// 没有相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') , to_tsquery('计算'));
rum_ts_distance
-----------------
Infinity
(1 row)
// 或相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') , to_tsquery('计算所 | 硕士'));
rum_ts_distance
-----------------
8.22467
(1 row)
// 与相似度
postgres=# select * from rum_ts_distance(to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') , to_tsquery('计算所 & 硕士'));
rum_ts_distance
-----------------
32.8987
(1 row)
// 排序
postgres=# create table test15(c1 tsvector);
CREATE TABLE
postgres=# insert into test15 values (to_tsvector('jiebacfg', 'hello china, i''m digoal')), (to_tsvector('jiebacfg', 'hello world, i''m postgresql')), (to_tsvector('jiebacfg', 'how are you, i''m digoal'));
INSERT 0 3
postgres=# select * from test15;
c1
-----------------------------------------------------
' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8
' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3
' ':2,4,7,11 'digoal':12 'm':10
(3 rows)
postgres=# create index idx_test15 on test15 using rum(c1 rum_tsvector_ops);
CREATE INDEX
postgres=# select *,c1 <=> to_tsquery('hello') from test15;
c1 | ?column?
-----------------------------------------------------+----------
' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8 | 16.4493
' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3 | 16.4493
' ':2,4,7,11 'digoal':12 'm':10 | Infinity
(3 rows)
postgres=# explain select *,c1 <=> to_tsquery('postgresql') from test15 order by c1 <=> to_tsquery('postgresql');
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using idx_test15 on test15 (cost=3600.25..3609.06 rows=3 width=36)
Order By: (c1 <=> to_tsquery('postgresql'::text))
(2 rows)
不再举例，如果你有更好的想法，PG还不支持的话，可以自己扩展哦。
参考
《找对业务G点, 体验酸爽 - PostgreSQL内核扩展指南》
如果你觉得还不够意思，要来点基于文本集合的深度挖掘，没关系，还有MADlib插件在等你，支持丰富的文本分析和训练接口。
http://madlib.incubator.apache.org/docs/latest/index.html
祝大家玩得开心，欢迎随时来 阿里云促膝长谈业务需求 ，恭候光临。
阿里云的小伙伴们加油，努力 做好内核与服务，打造最贴地气的云数据库 。
时间： 2024-08-04 00:08:49