CRM 重复记录,让 FDE 的第一个数字就算错
ROW_NUMBER 取 rn = 1 能保留每位客户最新的完整记录,前提是处理好并列,结果才不会随运行或引擎变化。
要点速览
- 窗口函数在一组行上计算,但不把这些行合并,因此完整记录得以保留。
- 标准写法:按键 PARTITION BY,ORDER BY updated_at DESC NULLS LAST 再加一个决胜列,然后筛选 rn = 1。
- Snowflake 和 DuckDB 支持 QUALIFY。PostgreSQL 不允许在 WHERE 中使用窗口函数,需要多套一层 CTE 或子查询。
驻场第二天,你拿到一份 CRM 导出数据,还有一个听上去很简单的问题:现在有多少活跃客户?
你数出一个数,销售负责人却摇头,说比实际多。原因并不新鲜:同一个人出现了好几次,两次导入、一次电话号码修正、一次旧系统迁移,各留下一份。
对前线部署工程师(FDE)来说,这不是小杂活。之后搭建的一切,从看板、数据管道到查询客户信息的 Agent,都建立在这张表上。去重做错了,下游每个数字都跟着错,而人们通常是在演示时发现,而不是在你写查询的时候。
这项技能可以归结为一个 SQL 模式:在每个客户分组内给记录编号,只保留第 1 行。下面用一个具体例子演示这个模式,并说明哪些陷阱会让它在两次运行之间、或两个引擎之间返回不同结果。
为什么 GROUP BY 不够?
很多工程师首先想到的是 GROUP BY email 加 MAX(updated_at)。这样能拿到最近一次更新时间,却拿不到同一行上的电话号码、地址或状态。
要取回这些列,就得再回连源表。如果两行时间戳相同,两行都会匹配,这位客户又重复了。
窗口函数正好解决这个问题。PostgreSQL 文档指出,窗口函数不会像普通聚合那样把多行合并成一行输出。每一行在结果中保持独立,因此可以在组内计算排名,同时手里仍握着完整记录供挑选。
这个模式由三部分组成。PARTITION BY 把键值相同的行分到一组,比如同一个 email。ROW_NUMBER() 为每组内的行连续编号。MotherDuck 术语表提到,与 RANK() 或 DENSE_RANK() 不同,它从不产生并列。
OVER 里的 ORDER BY 决定哪一行得到编号 1。在 SQL Server 上,Microsoft 文档规定 ROW_NUMBER 必须带这个子句。
一张脏表,逐行编号
设想一张 customers_raw 表,email 已经转成小写并去掉了空白。客户 An 有四条记录:其中两条在同一秒内创建,因为导入任务跑了两次;第 104 行来自旧系统,没有 updated_at。
rn 列是 ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, id DESC) 的结果,原样在 PostgreSQL 和 DuckDB 上运行。rnk 列使用 RANK(),排序相同但去掉了 id,在 DuckDB 上运行。
| id | 客户 | 电话 | updated_at | rn(PostgreSQL) | rn(DuckDB) | rnk(DuckDB) |
|---|---|---|---|---|---|---|
| 104 | An | 0912 000 099 | NULL | 1 | 4 | 4 |
| 103 | An | 0912 000 222 | 2026-05-10 14:30 | 2 | 1 | 1 |
| 102 | An | 0912 000 222 | 2026-05-10 14:30 | 3 | 2 | 1 |
| 101 | An | 0912 000 111 | 2026-03-01 09:00 | 4 | 3 | 3 |
| 201 | Binh | 0987 000 333 | 2026-04-02 08:00 | 1 | 1 | 1 |
在 DuckDB 上筛选 rn = 1,恰好返回两行:An 的 103 和 Binh 的 201。筛选 rnk = 1 则返回三行,因为 An 仍有两份。
Microsoft 文档把区别讲得很清楚:ROW_NUMBER 按顺序编号,1、2、3、4、5;RANK 给并列行相同的编号,例如 1、2、2、4、5。
想要每组恰好一行,就得用 ROW_NUMBER。
PostgreSQL 那一列则是另一回事:没有时间戳的第 104 行拿到了编号 1。同一条查询,两个引擎,选出了两个不同版本的 An。原因在最后一节解释。
PostgreSQL 只允许在 SELECT 列表和 ORDER BY 中使用窗口函数,禁止在 WHERE 中使用,所以不能写 WHERE ROW_NUMBER() ... = 1。文档建议,需要在窗口计算之后再筛选时,使用子查询。
在 PostgreSQL 上,可读性较好的做法是把编号放进 CTE,在外层筛选。下面的 NULLS LAST 就是为了堵住第 104 行那个陷阱:
WITH ranked AS (
SELECT
c.*,
ROW_NUMBER() OVER (
PARTITION BY lower(trim(email))
ORDER BY updated_at DESC NULLS LAST, id DESC
) AS rn
FROM customers_raw c
)
SELECT *
FROM ranked
WHERE rn = 1;
在 Snowflake 上,QUALIFY 子句写起来更简洁。Snowflake 文档说明,QUALIFY 之于窗口函数,相当于 HAVING 之于聚合函数,官方示例用的正是 ROW_NUMBER(),只返回每个分区的第一行。DuckDB 同样支持用 QUALIFY 筛选窗口函数结果。
SELECT *
FROM customers_raw
QUALIFY ROW_NUMBER() OVER (
PARTITION BY lower(trim(email))
ORDER BY updated_at DESC, id DESC
) = 1;
ORDER BY 末尾的 id 有什么用?
这个细节能区分出谁被脏数据坑过、谁没有。只写 ORDER BY updated_at DESC 时,第 102 行和第 103 行并列。PostgreSQL 文档说得很直白:并列行的编号顺序不确定。它自己的示例也在 ORDER BY 中加了员工编号列来打破并列。
Microsoft 文档也有类似警告:除非每个分区内 ORDER BY 的值唯一,否则无法保证 ROW_NUMBER() 返回的行在每次执行时顺序相同。
上面的例子里,102 和 103 电话号码相同,所以暂时看不出后果。但假设两行并列记录的状态列不同:今天报表显示客户活跃,明天却显示已取消,而没人动过数据。这种 bug 当着客户的面几乎无法排查。
解决办法是始终在 ORDER BY 末尾加一个唯一的决胜列,通常是主键或源系统的自增 id。id DESC 表示并列时,最晚创建的记录胜出。如果客户有更合适的列,比如事件序号或摄入时间戳,就用那个。
写 PARTITION BY 之前要回答的三个问题
第一个问题和 SQL 无关:什么才算“同一个客户”?email、税号,还是 ERP 系统里的 ID?答案就是 PARTITION BY 中的表达式,而且应当就地规范化,用 lower、trim,或去掉电话号码里的多余字符。
第二个问题是“哪一份是对的”。按 updated_at 取最新是合理的默认值,但有些客户希望保留销售团队录入的记录,而不是网页表单生成的那条。这时 ORDER BY 可以先用 CASE 表达式按来源排优先级,再按时间戳,最后按决胜列。
第三个问题是怎么确认结果可信。统计源表中不同键的数量,与筛选后的行数比较:两者必须相等。再挑几个记录最多的分组,完整打印出来,给客户的数据负责人看你保留了哪一行。
让你留错行的陷阱
最难发现的陷阱就在第 104 行。根据 PostgreSQL 文档,NULL 排序时被视为大于任何其他值,所以在 DESC 下排在最前。最旧、没有时间戳的那条记录,悄无声息地拿到了 rn = 1。
DuckDB 正好相反:其文档说明,与 PostgreSQL 不同,即使在 DESC 排序中 NULL 也留在末尾,因此同一条查询选出的是第 103 行。于是,笔记本电脑上的 DuckDB notebook 和客户现场的 PostgreSQL 数据管道可能给出两个不同答案,正如表中两列 rn 所示。
防御办法是不依赖默认行为。在支持该语法的引擎上,显式写出 ORDER BY updated_at DESC NULLS LAST, id DESC。如果同一条查询还要在不支持 NULLS LAST 的引擎上运行,就把 NULL 的排序写进数据本身:ORDER BY CASE WHEN updated_at IS NULL THEN 1 ELSE 0 END, updated_at DESC, id DESC。
这个 CASE 表达式给有时间戳的行赋 0、空值行赋 1,再升序排列,因此无论引擎默认如何,空值行总在最后。用上面的表,两个引擎都选出 103。
另一个陷阱是按未规范化的列分区,把 “[email protected] “ 和 “[email protected]” 变成两个客户。还有一个是以为 RANK 或 DENSE_RANK 与之等价,结果“去重后”的表恰恰在时间戳并列的分组里仍有重复。
在简历和面试中,与其写“精通 SQL”,不如具体说明你如何为一张多来源的客户表搭建确定性的去重步骤,包括决胜列、NULL 处理,以及去重前后的键数核对。
下次有人问“我们有多少客户”,你给出的数字应该是明天重跑、无论在笔记本上还是客户服务器上,结果都一模一样的那个。
这篇文章对你有帮助吗?
感谢你的反馈!