将字符串相似性与大量记录进行比较

2nbm6dog 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(552)

数据是相机和镜头的产品名称。
我的产品表中有55000条记录，我想将它们与一个干净的3500主记录集进行比较，这样我就知道它们是用来提供额外信息的。
当产品表丢失或获得几千条记录时，它会每天更新，性能很重要。
这是我正在处理的数据的一个例子，这5条记录

Canon 45MM 2.8 TSE    
Canon 45mm F2.8 TS-E
Canon 45mm F/2.8L Tilt-Shift - Boxed
Canon EF TS-E 45mm f/2.8 Tilt-shift Black Lens
Canon TS-e 45mm f2.8 Lens - Unboxed

都应该和主记录匹配吗

Canon TS-E 45mm f/2.8

我尝试了全文搜索来比较字符串，搜索速度非常快，但结果很差。
接下来我尝试了这个levenshtein距离函数https://lucidar.me/en/web-dev/levenshtein-distance-in-mysql/
每次比较（1条记录与3500条主记录比较）可能需要30-60秒，结果更好。一些例子。
佳能85mm 1.2 mk ii l-7号

M_PRODUCTNAME   SCORE
1   Canon EOS 5D Mark II    14
2   Canon EOS 6D Mark II    14
3   Canon EOS-1D Mark II N  14
4   Canon EF 85mm F1.2  14
5   Canon EF 50mm F1.8 II   14
6   Canon EOS 7D Mark II    14
7   Canon EF 85mm F1.2L II USM  14
8   Canon EOS 5D Mark III   14
9   Canon EOS-1D Mark II    14
10  Canon EOS M6 Mark II    14

佳能ef 80-200mm f4-5.6 ii镜头-1号（记录中的实际错误应该是f4.5而不是f4！）

M_PRODUCTNAME   SCORE
1   Canon EF 80-200mm f/4.5-5.6 II  12
2   Canon EF 70-300mm f/4-5.6L IS USM   13
3   Canon EF 70-300mm f/4-5.6 IS USM    13
4   Canon EF 70-200mm F4L IS II USM 14
5   Canon EF 55-200mm f/4.5-5.6 II USM  14
6   Canon EF 70-300 F4-5.6 IS II USM    15
7   Canon EF 70-200mm f/2.8L USM    15
8   Canon EF 70-200mm F4L IS USM    15
9   Canon EF 70-200mm f/2.8L IS USM 15
10  Canon EF 70-200mm F4L USM   15

佳能fit zenitar c lens16毫米f2.8-不匹配

M_PRODUCTNAME   SCORE
1   7artisans 12mm F2.8 22
2   Canon TS-E 45mm f/2.8   22
3   Canon TS-E 90mm f/2.8   22
4   7artisans 25mm F1.8 23
5   Canon TS-E 17mm f/4L    23
6   Canon EF 28mm f/2.8 23
7   Canon Extender EF 1.4x III  23
8   Canon Extender EF 1.4x II   23
9   Canon EF 24mm f/2.8 23
10  Canon EF 35mm F2.0  23

佳能eos ix aps胶片自动对焦和手动单反ef/efs安装相机机身-测试-不匹配

M_PRODUCTNAME   SCORE
1   Minolta Maxxum 7 35mm SLR Camera (Body Only)    60
2   Canon EOS 400D (EOS Digital Rebel XTi / EOS Kiss Digital X) 61
3   Canon EOS 300D (EOS Digital Rebel / EOS Kiss Digital)   61
4   Canon EOS 350D (EOS Digital Rebel XT / EOS Kiss Digital N)  61
5   Holga 120FN Medium Format Plastic Camera with Flash 62
6   Canon EOS 1100D (EOS Rebel T3 / EOS Kiss X50)   62
7   Canon EOS 1200D (EOS Rebel T5 / EOS Kiss X70)   62
8   Canon EF-S 35mm F2.8 Macro IS STM   62
9   Canon EF-M 28mm F3.5 Macro IS STM   62
10  Canon EF-S 60mm f/2.8 Macro USM 62

我相信我可以调整删除/更改/添加字符的评分，但即使这样，仍然需要很长时间才能运行。
e、 g.查询耗时35秒。

SELECT m_productname, levenshtein(m_productname, 'Tamron SP 45mm f/1.8 Di VC USD, Canon EF Fit') AS score FROM m_product ORDER by score

另外，我仍然不确定如何行动的数据时，低分比赛是不正确的，可能需要一些手动输入在某个点-但也许这是一个头痛的另一天。
要么我需要莱文施坦给我更好的得分和更快的速度，要么我需要一个替代方法，有什么想法吗？
我需要能够运行查询55000次在第一个示例，然后每天大约3000次新的条目。所以每次查询30秒是不好的。
我用的是coldfusion，如果这能打开其他的选择。

sql mysql full-text-search levenshtein-distance coldfusion

来源：https://stackoverflow.com/questions/62860975/comparing-string-similarity-with-a-large-amount-of-records

2条答案

按热度按时间

jhdbpxl91#

你的数据似乎有很多信息可以分成更简洁的列。在进一步研究levenshtein方法之前，我会花时间编写一些转换脚本，将数据解析为同一个表中的其他列。换言之，与其有这样一张table：

M_PRODUCTNAME
Canon EF 70-200mm f/2.8L USM

我想要一张这样的table：

M_PRODUCTNAME                   BRAND   APERTURE  FSTOP
Canon EF 70-200mm f/2.8L USM    Canon   70-200    2.8L

你会怎样得到这些数据？就我个人而言，我会把每个完整的产品名称用空格分割成一个数组。然后我将查看数组中的每个项以应用转换逻辑。这件商品有你们的知名品牌吗？然后将该品牌添加到“品牌”列。这根绳子以“mm”结尾吗？然后我会把它加到光圈栏。项目是否以“f/”或“f/”开头？然后我会将该项添加到fstop列。
这种方法无法捕捉m\U productname数据中的所有怪癖。但您可能会对其进行调整，以提取大量有意义的元数据。一旦数据被提取到额外的列中，搜索这些元素就会更快更容易。
此外，将数据提取到多个列中的算法可能与从目标字符串中查找要搜索的元素的算法相同。

赞(0）回复(0）举报 2021-07-26

ergxz8rk2#

我最简单的建议是在两个表中解析出maker的名称，并使用它来限制levenshtein距离的比较空间。代码如下所示：

select p.*, m.*, levenshtein(m.name, p.name)
from product p join
     master m
     on p.maker = m.maker;

这可以在上使用索引 (maker) 在两张table上。
你还可以区分相机和镜头。如果是，请将其作为另一个描述列。
如果有其他属性可以过滤掉，它们也会有帮助。换句话说，您不希望将一个表中的50000条记录与另一个表中的3500条记录进行比较。相反，如果您将50000条记录中的每一条都与——比如说——300条记录进行比较，那么您的代码将要快得多。

赞(0）回复(0）举报 2021-07-26

我来回答

将字符串相似性与大量记录进行比较

2条答案

相关问题

热门标签

最新问答