【转】PostgreSQL 文本数据分析实践之 - 相似度分析
背景
在日常的生活中,我们可能会经常需要一些像相近、相仿、距离接近、性格接近等等类似这样的需求,对数据进行筛选。
这些需求PostgreSQL居然都支持,是不是很变态。
变态的例子
这些场景都支持索引排序和检索,否则怎么叫变态呢。
按长相相似度排序
比如最近的王宝强和马蓉的事件,估计很多人会拿宋喆的照片进行相似度的搜索,八卦八卦。
说起图像搜索,我前几天才写了一篇这样的文章,是关于在PG数据库中使用图像搜索插件的文章。
《弱水三千,只取一瓢,当图像搜索遇见PostgreSQL(Haar wavelet)》
https://yq.aliyun.com/articles/58246
按喜好重合度排序
比如收集了人群的各种喜好的数据,按喜好进行聚类,或者按喜好的重叠度进行排序,找出目标人群。
按年龄相近程度排序
这个相对简单,比如输入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内核扩展指南》
https://yq.aliyun.com/articles/55981
如果你觉得还不够意思,要来点基于文本集合的深度挖掘,没关系,还有MADlib插件在等你,支持丰富的文本分析和训练接口。