FDE Times 数据在招 FDE 职位 924▲33 近 7 天在招职位数的变化(同口径比较):不计入本周新开始追踪的公司的职位近 7 天新增 80在招公司 196支持远程 25%美国薪资中位数 $200k招聘最多 Databricks 107
ENVI

前线部署工程师(FDE)的专业媒体

技能指南 · 指南

CRM 重复记录,让 FDE 的第一个数字就算错

ROW_NUMBER 取 rn = 1 能保留每位客户最新的完整记录,前提是处理好并列,结果才不会随运行或引擎变化。

分享微博

要点速览

  • 窗口函数在一组行上计算,但不把这些行合并,因此完整记录得以保留。
  • 标准写法:按键 PARTITION BY,ORDER BY updated_at DESC NULLS LAST 再加一个决胜列,然后筛选 rn = 1。
  • Snowflake 和 DuckDB 支持 QUALIFY。PostgreSQL 不允许在 WHERE 中使用窗口函数,需要多套一层 CTE 或子查询。
斜率图,展示客户 An 的四条记录在 PostgreSQL 和 DuckDB 上的 rn 排名。记录 104(updated_at 为 NULL)在 PostgreSQL 上排第 1,在 DuckDB 上排第 4;记录 103、102、101 在 PostgreSQL 上排第 2、3、4,在 DuckDB 上排第 1、2、3。
使用 ORDER BY updated_at DESC, id DESC 时,PostgreSQL 把没有时间戳的第 104 行排在 rn = 1,DuckDB 保留的是第 103 行。加上 NULLS LAST 后,两个引擎都选 103。来源:文中 customers_raw 表示例,在 PostgreSQL 和 DuckDB 上运行。

驻场第二天,你拿到一份 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 处理,以及去重前后的键数核对。

下次有人问“我们有多少客户”,你给出的数字应该是明天重跑、无论在笔记本上还是客户服务器上,结果都一模一样的那个。

这篇文章对你有帮助吗?

7 个信源
路线图上的下一篇 · 阶段 2: 全栈工程能力写代码之前,先用 curl 调一遍客户的 API文档写的是 API 应该怎样工作,最初几次请求,尤其是不带 token 的那次,才说明它实际怎样工作。