我正在努力找出在5列csv数据中查找重复项的最佳方法。真实数据中有超过百万行。
以下是上述6栏的内容。
Name, address, city, post-code, phone number, machine number
数据没有固定的长度,某些列中的数据可能在某些情况下丢失。
我正在考虑使用perl首先规范名称、城市和地址中使用的所有缩写形式。stackoverflow的perl爱好者帮了我很多忙
但仍有大量数据难以匹配。因此,我想知道是否有可能匹配基于"LIKELINES/SIMILARITY"的内容(例如,类似于gugl的谷歌),即克服收集数据时出现的错误所需的可能性。
关于数据,我手头有两项任务。
- 标记具有特定标识符的重复行
- 提及相似行之间的匹配百分比
如果我能得到建议,说明可以采用哪些所有可能的方法,哪些方法可能是最好的,因为它们有一定的优点,我将不胜感激。
您可以编写一个Perl程序来实现这一点,但将其放入SQL数据库并使用它会更容易、更快。
大多数SQL数据库都有导入CSV的方法。对于这个答案,我建议PostgreSQL,因为它有非常强大的字符串函数,您需要这些函数来查找模糊重复项。如果CSV数据还没有唯一的ID,请使用自动递增的ID列创建表。
导入完成后,在要检查重复项的列上添加索引。
CREATE INDEX name ON whatever (name);
你可以做一个自联接,以任何你喜欢的方式寻找重复项。下面是一个查找重复名称的示例。
SELECT id
FROM whatever t1
JOIN whatever t2 ON t1.id < t2.id
WHERE t1.name = t2.name
PostgreSQL有强大的字符串函数,包括用于进行比较的正则表达式。
索引将很难处理lower(t1.name)
之类的内容。根据您想要处理的重复项的种类,您可以为这些转换添加索引(这是PostgreSQL的一个功能)。例如,如果您想不区分大小写地搜索,可以在小写名称上添加索引。(感谢@asjo指出这一点)
CREATE INDEX ON whatever ((lower(name)));
// This will be muuuuuch faster
SELECT id
FROM whatever t1
JOIN whatever t2 ON t1.id < t2.id
WHERE lower(t1.name) = lower(t2.name)
"相似性"匹配可以通过多种方式实现,一种简单的方式是使用像metaphone()
这样的模糊匹配函数。和以前一样的技巧,用转换后的行添加一列并对其进行索引
在添加索引和查找重复项之前,最好先对数据本身进行其他简单的操作,如数据规范化。例如,修剪并挤压多余的空白。
UPDATE whatever SET name = trim(both from name);
UPDATE whatever SET name = regexp_replace(name, '[[:space:]]+', ' ');
最后,您可以使用Postgres Trigram模块为您的表添加模糊索引(再次感谢@asjo)。