如何用PostgreSQL解决一个人工智能语义去重的小问题

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 在云栖社区的问答区,有一位网友提到有一个问题: 表里相似数据太多,想删除相似度高的数据,有什么办法能实现吗? 例如: 银屑病怎么治? 银屑病怎么治疗? 银屑病怎么治疗好? 银屑病怎么能治疗好? 等等 解这个问题的思路.1. 首先如何判断内容的相似度,PostgreSQL中

在云栖社区的问答区,有一位网友提到有一个问题:

表里相似数据太多,想删除相似度高的数据,有什么办法能实现吗?
例如:
银屑病怎么治?
银屑病怎么治疗?
银屑病怎么治疗好?
银屑病怎么能治疗好?
等等

解这个问题的思路
.1. 首先如何判断内容的相似度,PostgreSQL中提供了中文分词,pg_trgm(将字符串切成多个不重复的token,计算两个字符串的相似度) .
对于本题,我建议采取中文分词的方式,首先将内容拆分成词组。
.2. 在拆分成词组后,首先分组聚合,去除完全重复的数据。
.3. 然后自关联生成笛卡尔(矩阵),计算出每条记录和其他记录的相似度。相似度的算法很简单,重叠的token数量除以集合的token去重后的数量。
.4. 根据相似度,去除不需要的数据。
这里如果数据量非常庞大,使用专业的分析编程语言会更好例如 PL/R。

实操的例子:
首先要安装PostgreSQL 中文分词插件
(阿里云AliCloudDB PostgreSQL已包含zhparser插件,用法参考阿里云官方手册)
https://yq.aliyun.com/articles/7730

下面是PostgreSQL社区版的用法:

git clone https://github.com/jaiminpan/pg_jieba.git
mv pg_jieba $PGSRC/contrib/
export PATH=/home/digoal/pgsql9.5/bin:$PATH
cd $PGSRC/contrib/pg_jieba
make clean;make;make install

git clone https://github.com/jaiminpan/pg_scws.git
mv pg_jieba $PGSRC/contrib/
export PATH=/home/digoal/pgsql9.5/bin:$PATH
cd $PGSRC/contrib/pg_scws
make clean;make;make install

创建插件

psql
# create extension pg_jieba;
# create extension pg_scws;

创建测试CASE

create table tdup1 (id int primary key, info text);
create extension pg_trgm;
insert into tdup1 values (1, '银屑病怎么治?');
insert into tdup1 values (2, '银屑病怎么治疗?');
insert into tdup1 values (3, '银屑病怎么治疗好?');
insert into tdup1 values (4, '银屑病怎么能治疗好?');

这两种分词插件,可以任选一种。

postgres=# select to_tsvector('jiebacfg', info),* from tdup1 ;
     to_tsvector     | id |         info         
---------------------+----+----------------------
 '治':3 '银屑病':1   |  1 | 银屑病怎么治?
 '治疗':3 '银屑病':1 |  2 | 银屑病怎么治疗?
 '治疗':3 '银屑病':1 |  3 | 银屑病怎么治疗好?
 '治疗':4 '银屑病':1 |  4 | 银屑病怎么能治疗好?
(4 rows)

postgres=# select to_tsvector('scwscfg', info),* from tdup1 ;
            to_tsvector            | id |         info         
-----------------------------------+----+----------------------
 '治':2 '银屑病':1                 |  1 | 银屑病怎么治?
 '治疗':2 '银屑病':1               |  2 | 银屑病怎么治疗?
 '好':3 '治疗':2 '银屑病':1        |  3 | 银屑病怎么治疗好?
 '好':4 '治疗':3 '能':2 '银屑病':1 |  4 | 银屑病怎么能治疗好?
(4 rows)

创建三个函数,
计算2个数组的集合(去重后的集合)

postgres=# create or replace function array_union(text[], text[]) returns text[] as 
$$

  select array_agg(c1) from (select c1 from unnest($1||$2) t(c1) group by c1) t;

$$
 language sql strict;
CREATE FUNCTION

数组去重

postgres=# create or replace function array_dist(text[]) returns text[] as 
$$
         
  select array_agg(c1) from (select c1 from unnest($1) t(c1) group by c1) t;    

$$
 language sql strict;
CREATE FUNCTION

计算两个数组的重叠部分(去重后的重叠部分)

postgres=# create or replace function array_share(text[], text[]) returns text[] as 
$$

  select array_agg(unnest) from (select unnest($1) intersect select unnest($2) group by 1) t;

$$
 language sql strict;
CREATE FUNCTION

笛卡尔结果是这样的:
regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:d+)', '', 'g')),' ') 用于将info转换成数组。

postgres=# with t(c1,c2,c3) as 
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select * from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2) t;
 t1c1 | t2c1 |         t1c2         |         t2c2         |       t1c3        |       t2c3        | simulate 
------+------+----------------------+----------------------+-------------------+-------------------+----------
    1 |    1 | 银屑病怎么治?       | 银屑病怎么治?       | {'银屑病','治'}   | {'银屑病','治'}   |     1.00
    1 |    2 | 银屑病怎么治?       | 银屑病怎么治疗?     | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    1 |    3 | 银屑病怎么治?       | 银屑病怎么治疗好?   | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    1 |    4 | 银屑病怎么治?       | 银屑病怎么能治疗好? | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    2 |    1 | 银屑病怎么治疗?     | 银屑病怎么治?       | {'银屑病','治疗'} | {'银屑病','治'}   |     0.33
    2 |    2 | 银屑病怎么治疗?     | 银屑病怎么治疗?     | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    2 |    3 | 银屑病怎么治疗?     | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    2 |    4 | 银屑病怎么治疗?     | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    1 | 银屑病怎么治疗好?   | 银屑病怎么治?       | {'银屑病','治疗'} | {'银屑病','治'}   |     0.33
    3 |    2 | 银屑病怎么治疗好?   | 银屑病怎么治疗?     | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    3 | 银屑病怎么治疗好?   | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    4 | 银屑病怎么治疗好?   | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    4 |    1 | 银屑病怎么能治疗好? | 银屑病怎么治?       | {'银屑病','治疗'} | {'银屑病','治'}   |     0.33
    4 |    2 | 银屑病怎么能治疗好? | 银屑病怎么治疗?     | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    4 |    3 | 银屑病怎么能治疗好? | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    4 |    4 | 银屑病怎么能治疗好? | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
(16 rows)

以上生成的实际上是一个矩阵,simulate就是矩阵中我们需要计算的相似度:
1
我们在去重计算时不需要所有的笛卡尔积,只需要这个矩阵对角线的上部分或下部分数据即可。
所以加个条件就能完成。

postgres=# with t(c1,c2,c3) as 
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select * from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t;
 t1c1 | t2c1 |        t1c2        |         t2c2         |       t1c3        |       t2c3        | simulate 
------+------+--------------------+----------------------+-------------------+-------------------+----------
    1 |    2 | 银屑病怎么治?     | 银屑病怎么治疗?     | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    1 |    3 | 银屑病怎么治?     | 银屑病怎么治疗好?   | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    1 |    4 | 银屑病怎么治?     | 银屑病怎么能治疗好? | {'银屑病','治'}   | {'银屑病','治疗'} |     0.33
    2 |    3 | 银屑病怎么治疗?   | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    2 |    4 | 银屑病怎么治疗?   | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    4 | 银屑病怎么治疗好? | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
(6 rows)

开始对这些数据去重,去重的第一步,明确simulate, 例如相似度大于0.5的,需要去重。

postgres=# with t(c1,c2,c3) as 
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select * from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t where simulate>0.5;
 t1c1 | t2c1 |        t1c2        |         t2c2         |       t1c3        |       t2c3        | simulate 
------+------+--------------------+----------------------+-------------------+-------------------+----------
    2 |    3 | 银屑病怎么治疗?   | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    2 |    4 | 银屑病怎么治疗?   | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    4 | 银屑病怎么治疗好? | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
(3 rows)

去重第二步,将t2c1列的ID对应的记录删掉即可。

delete from tdup1 where id in (with t(c1,c2,c3) as 
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select t2c1 from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t where simulate>0.5);
例如 : 
postgres=# insert into tdup1 values (11, '白血病怎么治?');
INSERT 0 1
postgres=# insert into tdup1 values (22, '白血病怎么治疗?');
INSERT 0 1
postgres=# insert into tdup1 values (13, '白血病怎么治疗好?');
INSERT 0 1
postgres=# insert into tdup1 values (24, '白血病怎么能治疗好?');
INSERT 0 1
postgres=# 
postgres=# with t(c1,c2,c3) as                             
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select * from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t where simulate>0.5;
 t1c1 | t2c1 |        t1c2        |         t2c2         |       t1c3        |       t2c3        | simulate 
------+------+--------------------+----------------------+-------------------+-------------------+----------
    2 |    3 | 银屑病怎么治疗?   | 银屑病怎么治疗好?   | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    2 |    4 | 银屑病怎么治疗?   | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
    3 |    4 | 银屑病怎么治疗好? | 银屑病怎么能治疗好? | {'银屑病','治疗'} | {'银屑病','治疗'} |     1.00
   22 |   24 | 白血病怎么治疗?   | 白血病怎么能治疗好? | {'治疗','白血病'} | {'治疗','白血病'} |     1.00
   13 |   22 | 白血病怎么治疗好? | 白血病怎么治疗?     | {'治疗','白血病'} | {'治疗','白血病'} |     1.00
   13 |   24 | 白血病怎么治疗好? | 白血病怎么能治疗好? | {'治疗','白血病'} | {'治疗','白血病'} |     1.00
(6 rows)

postgres=# begin;
BEGIN
postgres=# delete from tdup1 where id in (with t(c1,c2,c3) as 
postgres(# (select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
postgres(# select t2c1 from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
postgres(# simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t where simulate>0.5);
DELETE 4
postgres=# select * from tdup1 ;
 id |        info        
----+--------------------
  1 | 银屑病怎么治?
  2 | 银屑病怎么治疗?
 11 | 白血病怎么治?
 13 | 白血病怎么治疗好?
(4 rows)

用数据库解会遇到的问题, 因为我们的JOIN filter是<>和<,用不上hashjoin。
数据量比较大的情况下,耗时会非常的长。

postgres=# explain delete from tdup1 where id in (with t(c1,c2,c3) as 
(select id,info,array_dist(regexp_split_to_array((regexp_replace(to_tsvector('jiebacfg',info)::text,'(:\d+)', '', 'g')),' ')) from tdup1) 
select t2c1 from (select t1.c1 t1c1,t2.c1 t2c1,t1.c2 t1c2,t2.c2 t2c2,t1.c3 t1c3,t2.c3 t2c3,round(array_length(array_share(t1.c3,t2.c3),1)::numeric/array_length(array_union(t1.c3,t2.c3),1),2) 
simulate from t t1,t t2 where t1.c1<>t2.c1 and t1.c1<t2.c1) t where simulate>0.5);
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Delete on tdup1  (cost=10005260133.58..10005260215.84 rows=2555 width=34)
   ->  Hash Join  (cost=10005260133.58..10005260215.84 rows=2555 width=34)
         Hash Cond: (tdup1.id = "ANY_subquery".t2c1)
         ->  Seq Scan on tdup1  (cost=0.00..61.10 rows=5110 width=10)
         ->  Hash  (cost=10005260131.08..10005260131.08 rows=200 width=32)
               ->  HashAggregate  (cost=10005260129.08..10005260131.08 rows=200 width=32)
                     Group Key: "ANY_subquery".t2c1
                     ->  Subquery Scan on "ANY_subquery"  (cost=10000002667.20..10005252911.99 rows=2886838 width=32)
                           ->  Subquery Scan on t  (cost=10000002667.20..10005224043.61 rows=2886838 width=4)
                                 Filter: (t.simulate > 0.5)
                                 CTE t
                                   ->  Seq Scan on tdup1 tdup1_1  (cost=0.00..2667.20 rows=5110 width=36)
                                 ->  Nested Loop  (cost=10000000000.00..10005113119.99 rows=8660513 width=68)
                                       Join Filter: ((t1.c1 <> t2.c1) AND (t1.c1 < t2.c1))
                                       ->  CTE Scan on t t1  (cost=0.00..102.20 rows=5110 width=36)
                                       ->  CTE Scan on t t2  (cost=0.00..102.20 rows=5110 width=36)
(16 rows)

其他更优雅的方法,使用PLR或者R进行矩阵运算,得出结果后再进行筛选。
PLR
R
或者使用MPP数据库例如Greenplum加上R和madlib可以对非常庞大的数据进行处理。
MADLIB
MPP

小结
这里用到了PG的什么特性?
.1. 中文分词
.2. 窗口查询功能
(本例中没有用到,但是如果你的数据没有主键时,则需要用ctid和row_number来定位到一条唯一记录)

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
3月前
|
SQL 存储 人工智能
探索语义解析技术和AI人工智能大模型的关系
探索语义解析技术和AI人工智能大模型的关系
72 1
|
人工智能 自然语言处理 API
调用百度api实现人工智能文本语义识别
调用百度api实现人工智能文本语义识别
134 0
调用百度api实现人工智能文本语义识别
|
人工智能 语音技术 物联网
深思考人工智能 CEO 杨志明:多模态语义理解,是机器能否实现智能的关键 | 2019 WISE 超级进化者大会
未来随着5G、物联网以及产业互联网的发展,要深植边缘计算和中央智能,成为万物互联里面的语义理解的大脑。未来的设备不光能听到、看到,而且能理解你、听懂你、看懂你,更好地为你服务。
576 0
|
人工智能 自然语言处理 大数据
NLPIR智能语义:大数据挖掘助力人工智能快速发展
NLPIR能够全方位多角度满足应用者对大数据文本的处理需求,包括大数据完整的技术链条:网络采集、正文提取、中英文分词、词性标注、实体抽取、词频统计、关键词提取、语义信息抽取、文本分类、情感分析、语义深度扩展、繁简编码转换、自动注音、文本聚类等。
1748 0
|
人工智能 算法 定位技术
《中国人工智能学会通讯》——7.28 利用 PrAGMATiC 算法构建语义地 图集
本节书摘来自CCAI《中国人工智能学会通讯》一书中的第7章,第7.28节, 更多章节内容可以访问云栖社区“CCAI”公众号查看。
1375 0
|
人工智能 数据可视化 数据挖掘
《中国人工智能学会通讯》——7.27 大脑皮层中语义表示的定位
本节书摘来自CCAI《中国人工智能学会通讯》一书中的第7章,第7.27节, 更多章节内容可以访问云栖社区“CCAI”公众号查看。
1219 0
|
人工智能 自然语言处理 定位技术
《中国人工智能学会通讯》——7.25 利用自然语言语音绘制人体大脑皮层的语义地图
本节书摘来自CCAI《中国人工智能学会通讯》一书中的第7章,第7.25节, 更多章节内容可以访问云栖社区“CCAI”公众号查看。
1270 0
|
机器学习/深度学习 人工智能 自然语言处理
《中国人工智能学会通讯》——7.4 语义组合
本节书摘来自CCAI《中国人工智能学会通讯》一书中的第7章,第7.4节, 更多章节内容可以访问云栖社区“CCAI”公众号查看。
1515 0

相关产品

  • 云原生数据库 PolarDB