ClickHouse自然语言处理技术之文本相似度计算

文本相似度的计算场景比较复杂，有时不能直接使用SQL的JOIN关键字进行关联。但需要关联的表中均有一列文本，可通过其文本相似度进行关联。比如从互联网上抓取的公共资源交易的招标公告和中标公告，如果要把同一个项目的招标公告和中标公告关联起来，就需要用到两个公告的公告标题进行文本关联。

文本相似度计算测试

（1）源表结构如下，其中str、str1分别为两个文本字段，需要在目标表中增加一个str、str1文本相似度标签；

（2）文本相似度计算公式：

例如“北京万山数据科技”与“北京万山数据科技公司”的相似度为

（3）测试数据条数：1000000条。

文本相似度计算测试

（1）SQL方案

通过正则提取，将str和str1转换成数组，求出两个数组的交集数组，然后用交集数组的长度除以str和str1不重复长度之和减去交集数组长度，再开3次方，SQL实现如下：

extractAll(ifNull(str,’’),’.’) as arr,

extractAll(ifNull(str1,’’),’.’) as arr1,

cbrt(length(arrayIntersect(arr,arr1))/(length(arrayDistinct(arr))+length(arrayDistinct(arr1))-length(arrayIntersect(arr,arr1))))as correlation

（2）ETL方案（Talend）

使用Talned Open Studion作为ETL工具，需要扩展一个JAVA函数，以实现相似度的计算。实现的代码如下：

文本相似度计算测试结果

测试环境为笔记本电脑，CPU为Intel is-8265U，内存为8G，硬盘为256G的固态硬盘；数据库为clickhouse，安装于WSL2（ubuntu18.04）。

ETL为Talend Open Studio的Windows版本。

（1）SQL批处理

本方案运行时间为3.7秒，平均每秒处理数据超过27万。

运行截图：

处理前的数据截图：

处理后的数据截图：

（2）ETL处理

ETL处理运行的时间为12.86秒，平均每秒处理不到8万条数据。

运行过程截图：

处理后的数据截图：

（3）测评结果对比