PostgreSQL 文本数据分析实践之 - 相似度分析

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 背景 在日常的生活中,我们可能会经常需要一些像相近、相仿、距离接近、性格接近等等类似这样的需求,对数据进行筛选。 这些需求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插件在等你,支持丰富的文本分析和训练接口。

http://madlib.incubator.apache.org/docs/latest/index.html

祝大家玩得开心,欢迎随时来 阿里云促膝长谈业务需求 ,恭候光临

阿里云的小伙伴们加油,努力 做好内核与服务,打造最贴地气的云数据库

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
数据可视化 数据挖掘 数据处理
Python在数据分析中的应用实践
【2月更文挑战第13天】 本文旨在探讨Python语言在当前数据驱动时代的核心应用之一——数据分析领域的实践方法和技术。Python,作为一种高级编程语言,因其简洁的语法、强大的库支持以及广泛的社区资源,已成为数据科学家和分析师首选的工具之一。文章首先简要介绍Python及其在数据分析中的优势,随后深入讲解使用Python进行数据处理、分析、可视化的关键技术,包括但不限于Pandas库的数据处理、Matplotlib和Seaborn库的数据可视化技术,以及SciPy和Scikit-learn库在数据分析中的应用。通过具体案例,展示Python如何有效地解决实际数据分析问题,最终旨在为读者提供一
20 2
|
1月前
|
数据采集 数据挖掘 大数据
Python在数据分析中的应用及实践
【2月更文挑战第13天】 本文旨在探讨Python语言在数据分析领域的广泛应用及其实践方法。通过深入浅出的方式,介绍Python在处理、分析大数据时的核心库和工具,如Pandas、NumPy、Matplotlib等,并通过一个实际案例来展示这些工具如何协同工作,解决数据分析中遇到的常见问题。文章不仅为读者提供了一个学习和应用Python进行数据分析的起点,也通过案例分析,展示了Python在数据处理能力上的强大与灵活性,旨在激发读者对Python数据分析深入学习和研究的兴趣。
|
2月前
|
数据采集 存储 数据可视化
Python数据分析从入门到实践
Python数据分析从入门到实践
|
2月前
|
数据可视化 搜索推荐 数据挖掘
数据分析案例-顾客购物数据可视化分析
数据分析案例-顾客购物数据可视化分析
97 0
|
4天前
|
数据采集 数据可视化 数据挖掘
Seaborn在数据分析中的应用:案例分析与实践
【4月更文挑战第17天】本文介绍了Seaborn在数据分析中的应用,它是一个基于Python的可视化库,简化了复杂数据的图表创建。通过一个销售数据分析的案例,展示了数据加载、描述性统计、相关性分析、多变量分析及高级可视化步骤。实践技巧包括数据清洗、图表选择、颜色使用、注释标签和交互性。Seaborn助力高效数据探索和理解,提升分析效率。注意,实际使用需根据数据集和目标调整,并参考最新文档。
|
17天前
|
存储 机器学习/深度学习 数据采集
数据分析师如何处理数据以进行分析?
【4月更文挑战第4天】数据分析师如何处理数据以进行分析?
19 9
|
1月前
|
存储 关系型数据库 MySQL
TiDB与MySQL、PostgreSQL等数据库的比较分析
【2月更文挑战第25天】本文将对TiDB、MySQL和PostgreSQL等数据库进行详细的比较分析,探讨它们各自的优势和劣势。TiDB作为一款分布式关系型数据库,在扩展性、并发性能等方面表现突出;MySQL以其易用性和成熟性受到广泛应用;PostgreSQL则在数据完整性、扩展性等方面具有优势。通过对比这些数据库的特点和适用场景,帮助企业更好地选择适合自己业务需求的数据库系统。
|
1月前
|
机器学习/深度学习 数据可视化 算法
python数据分析——在面对各种问题时,因如何做分析的分类汇总
Python数据分析是指使用Python编程语言对数据进行收集、处理、分析和可视化的过程。Python是一种非常流行的编程语言,具有简单易学、代码可读性高、生态系统强大的特点,因此在数据科学领域得到广泛应用。
82 0
|
1月前
|
监控 搜索推荐 数据挖掘
python数据分析——业务指标分析
业务指标分析是企业运营中不可或缺的一环,通过对各项关键指标的深入剖析,我们能够更好地了解企业的运营状况,发现潜在问题,进而制定相应的策略来优化业务流程、提升经营效率。 在业务指标分析中,我们首先要明确分析的目的和范围。是为了评估整体业务健康状况,还是针对某一具体环节进行优化?明确了目的后,我们需要收集相关的数据,这些数据可能来源于不同的业务系统和数据库,因此数据的整合和清洗也是分析过程中的重要步骤。 接下来,我们要选择合适的分析方法。比如,对于销售数据,我们可以采用时间序列分析来观察销售趋势;对于用户行为数据,我们可以使用用户画像和路径分析来洞察用户需求和行为习惯。
100 1
|
1月前
|
数据采集 数据挖掘 数据处理
Python在数据分析中的应用实践
【2月更文挑战第12天】 本文深入探讨了Python语言在数据分析领域的应用,通过介绍Python的几个关键数据分析库(Pandas、NumPy、Matplotlib)的基本使用方法和案例实践,展示了Python处理数据的强大能力。不同于传统的摘要,本文旨在通过实际操作案例,让读者能够直观感受到Python在数据分析中的实际应用价值,从而激发读者进一步探索Python数据分析能力的兴趣。

相关产品

  • 云原生数据库 PolarDB