One Vendor, Four Spellings: How Deterministic Stages Beat Similarity Scores

一个供应商，四种拼写：确定性阶段如何胜过相似度评分

My agency buys editorial placements from independent publishers. The supplier list is a little over 10,000 websites, every one of them a company that sends us invoices, and keeping that list clean is the least glamorous problem we have. It is also the one that costs money when it goes wrong. A duplicate supplier record is how you pay the same invoice twice, and split records mean split price history, so the rate you negotiated last year is filed under a spelling nobody searches for.

我的代理机构从独立出版商那里购买编辑版位。我们的供应商名单上有超过 10,000 个网站，每一家都是会给我们发送发票的公司。保持这份名单的整洁是我们面临的最枯燥的问题，但一旦出错，它也是最费钱的。重复的供应商记录会导致你为同一张发票支付两次，而分散的记录意味着价格历史被割裂，导致你去年谈好的费率被归档在一个没人搜索的拼写下。

Since January we have been syncing an external marketplace catalog into our internal system through its API, on top of a decade of manual entry by account managers and spreadsheets that arrive by email. The same site now shows up as solartravelmag.com, https://www.solartravelmag.com/, blog.solartravelmag.com, and on a bad day SolarTravelMag.COM with a tracking parameter attached. One marketplace we sync is upfront that its listed prices match reality about 90% of the time, which is honest of them, and which means cross-checking prices between sources is mandatory. Cross-checking only works if you know two rows describe the same site. That is entity resolution, a problem with formal theory going back to Fellegi and Sunter in 1969 [1], and it has a property the tutorials rarely lead with: the string matching is the easy part. Deciding what to do with the scores is the job.

自一月以来，我们一直在通过 API 将外部市场目录同步到我们的内部系统中，此外还有客户经理十年来手动录入的数据以及通过电子邮件发送的电子表格。同一个网站现在可能显示为 solartravelmag.com、https://www.solartravelmag.com/、blog.solartravelmag.com，在糟糕的情况下，还可能带有跟踪参数显示为 SolarTravelMag.COM。我们同步的一个市场明确表示其列出的价格与实际情况有约 90% 的匹配度，这很诚实，但也意味着必须在不同来源之间交叉核对价格。只有当你确定两行数据描述的是同一个网站时，交叉核对才有效。这就是实体解析（Entity Resolution），这是一个理论基础可以追溯到 1969 年 Fellegi 和 Sunter [1] 的问题，它有一个教程中很少提及的特性：字符串匹配只是简单部分，决定如何处理这些评分才是真正的工作。

This article walks through the pipeline that cleaned our list, run end to end on a synthetic rebuild of it, with ground truth labels so every precision claim below is checkable. The punchline, in one sentence: two boring deterministic stages removed 76% of the duplicates for free, the fuzzy matcher scored 33.7 million pairs in two seconds, and no threshold existed that could safely merge what remained. The matcher’s real output is a ranked review queue, not a set of merges.

本文将介绍清理我们名单的流水线，该流水线是在一个合成重建的名单上端到端运行的，并带有真实标签，因此下面提到的每一个精度声明都是可验证的。一句话总结核心结论：两个枯燥的确定性阶段免费移除了 76% 的重复项，模糊匹配器在两秒内对 3370 万对数据进行了评分，且不存在一个阈值可以安全地合并剩余部分。匹配器的真正输出是一个排好序的审核队列，而不是一组自动合并的结果。

A synthetic copy of a real mess

一个真实混乱的合成副本

I cannot publish our vendor list, so I rebuilt one with the same diseases. Seven thousand invented publisher brands received domains across a realistic spread of endings, and then I corrupted the list the way ours gets corrupted: 2,429 surface variants (schemes, www prefixes, random casing, paths, tracking parameters), 888 subdomain rows like blog. and m., 529 country-domain siblings such as brand.com sitting next to brand.de, and 505 one-keystroke entry typos. I also planted 180 traps: domains one edit away from an existing brand that belong to a different vendor entirely, because the real web is full of near-name neighbors and any honest test set needs them.

我无法发布我们的供应商名单，所以我重建了一个带有同样“病症”的名单。七千个虚构的出版商品牌获得了分布在现实后缀中的域名，然后我按照我们名单被破坏的方式对其进行了破坏：2,429 个表面变体（协议、www 前缀、随机大小写、路径、跟踪参数），888 个类似 blog. 和 m. 的子域名行，529 个类似 brand.com 和 brand.de 并存的国家域名兄弟，以及 505 个单键输入错误。我还埋下了 180 个陷阱：与现有品牌仅差一个字符但属于完全不同供应商的域名，因为真实的互联网充满了名称相近的邻居，任何诚实的测试集都需要它们。

The result is 11,531 rows describing 7,180 actual vendors. The generator is fifty lines of word lists and deliberate vandalism; the corruption recipe above is enough to rebuild it. Everything ran on Python 3.12 with RapidFuzz 3.14.5 [2] and tldextract 5.3.2 [3] on a single-vCPU cloud sandbox, so none of the timings below required serious hardware.

结果是 11,531 行数据描述了 7,180 个实际供应商。生成器由五十行词表和刻意的破坏代码组成；上述破坏配方足以重建它。所有程序都在 Python 3.12 环境下，配合 RapidFuzz 3.14.5 [2] 和 tldextract 5.3.2 [3]，在单 vCPU 云沙箱中运行，因此下面的时间测试都不需要高性能硬件。

The boring stages do most of the work

枯燥的阶段完成了大部分工作

Stage one is normalization, and it is deliberately dumb: lowercase, strip the scheme, cut the path and query string, drop a leading www.

第一阶段是标准化，它被刻意设计得很简单：转为小写、去除协议、切掉路径和查询字符串、去掉开头的 www。

import re
def norm_host(raw: str) -> str:
    s = raw.strip().lower()
    s = re.sub(r"^[a-z]+://", "", s)
    s = s.split("/")[0].split("?")[0]
    if s.startswith("www."):
        s = s[4:]
    return s.rstrip(".")

That took the list from 11,531 rows to 9,080 unique hosts in 0.03 seconds. No model, no scores, 2,451 duplicates gone.

这使得名单在 0.03 秒内从 11,531 行减少到 9,080 个唯一主机。没有模型，没有评分，2,451 个重复项被移除。

Stage two collapses subdomains, and this is where people who split on dots get hurt. The registered domain of blog.solartravelmag.com is solartravelmag.com, but the registered domain of solartravelmag.co.uk is not co.uk, and the only way to know that is the Public Suffix List [4], a maintained catalog of every suffix under which the public can register names. The tldextract library wraps it:

第二阶段是折叠子域名，这是那些仅通过点号分割字符串的人容易栽跟头的地方。blog.solartravelmag.com 的注册域名是 solartravelmag.com，但 solartravelmag.co.uk 的注册域名并不是 co.uk，了解这一点的唯一方法是使用公共后缀列表（Public Suffix List）[4]，这是一个维护了公众可以注册名称的所有后缀的目录。tldextract 库封装了它：

import tldextract
# pin to the bundled suffix snapshot so runs are reproducible
extract = tldextract.TLDExtract(suffix_list_urls=())
registered = {}
for host, row in hosts.items():
    ext = extract(host)
    reg = ext.domain + "." + ext.suffix if ext.suffix else host
    registered.setdefault(reg, row)

Another 873 hosts collapsed, leaving 8,207 registered domains. Two deterministic stages, zero judgment calls, and 3,324 of the 4,351 duplicate rows are already gone. If you take one number from this article, take that one: 76% of the problem never needed a similarity score at all.

又折叠了 873 个主机，剩下 8,207 个注册域名。两个确定性阶段，零次主观判断，4,351 个重复行中已经有 3,324 个被移除了。如果你要从本文记住一个数字，请记住这个：76% 的问题根本不需要相似度评分。

What is left is the hard quarter. The ground truth says 1,135 true duplicate pairs are still hiding in those 8,207 domains, split records that only a cross-domain comparison can find: the entry typos and the country-domain siblings.

剩下的就是那困难的四分之一。根据真实标签，在那 8,207 个域名中还隐藏着 1,135 对真正的重复项，这些分散的记录只有通过跨域比较才能发现：输入错误和国家域名兄弟。

Brute force is fine, and blocking still earns its keep

暴力破解是可以的，但分块依然有其价值

8,207 domains make 33.7 million possible pairs, which sounds like the moment to reach for clever indexing. It is not, at least not for speed. RapidFuzz computes the full similarity matrix in one call:

8,207 个域名会产生 3,370 万个可能的配对，这听起来像是该使用巧妙索引的时候了。其实不然，至少在速度上不是。RapidFuzz 可以通过一次调用计算出完整的相似度矩阵：

import numpy as np
from rapidfuzz import fuzz, process
M = process.cdist(labels, labels, scorer=fuzz.ratio, dtype=np.uint8, workers=-1)

Two seconds, 67 MB. On a list this size, brute force is a coffee sip.

两秒钟，67 MB。对于这个规模的列表，暴力破解只需要喝口咖啡的时间。

Blocking matters for two other reasons. First, scale: at 100,000 vendors the same matrix is 10 billion cells and 10 GB, and the coffee sip becomes an outage. Second, and underrated: a scored matrix is not a to-do list. You want candidate pairs you can rank and route, not a wall of numbers. So I blocked anyway, comparing only domains that share a key:

分块（Blocking）在另外两个方面很重要。首先是规模：在 10 万个供应商的情况下，同样的矩阵将包含 100 亿个单元格和 10 GB 数据，喝咖啡的时间就会变成系统宕机。其次，且常被低估的一点是：评分矩阵不是待办事项列表。你需要的是可以排序和分发的候选配对，而不是一堵数字墙。所以我还是进行了分块，只比较共享同一个键的域名：

from itertools import combinations
blocks = {}
for reg in domains:
    label = label_of[reg] # the part left of the suffix
    for key in ("P" + label[:2], "S" + label[-3:]):
        blocks.setdefault(key, set()).add(reg)
pairs = set()
for members in blocks.values():
    pairs.update(combinations(sorted(members), 2))

Blocking has a price, and you should measure it instead of hoping. With only the two-character prefix key, the candidate set caught 93.0% of the true duplicate pairs; typos that hit the first two letters escaped their block. Adding the last-three-characters key raised that to 99.8%, at the cost of growing…

分块是有代价的，你应该去衡量它而不是心存侥幸。仅使用双字符前缀键时，候选集捕获了 93.0% 的真实重复对；前两个字母出错的拼写逃脱了分块。增加最后三个字符作为键将捕获率提高到了 99.8%，代价是增加了……