LeetCode第1934题《确认率》，属于那种一读题干感觉是白送分、一提交就发现被暗坑放倒的SQL题。我第一次做的时候信心满满写完LEFT JOIN和GROUP BY，结果用例直接红一片。排查了半天，问题出在“把没有确认记录的用户排除掉了”“除数为零时没兜底”“COUNT把0也数进去了”这三个点上。后来我在公司做短信发送回执统计时，发现这套逻辑简直原封不动被复刻：注册用户就是Signups，回执表就是Confirmations，用户收到短信后的成功回执率就是这么算的。这篇就把这道题拆开讲透：题目模型、三种解法、常见错误、性能写法、面试追问，一次讲完。
1. 先把题目的业务模型看清楚
1.1 两张表到底在描述什么
这道题给了两张表，第一张叫 Signups，记录了用户的注册信息，核心字段就是 user_id 和注册时间。第二张叫 Confirmations， recording 的是用户的“确认动作”，核心字段是 user_id、确认时间，以及 action，action 只有两种取值：confirmed和timeout。
我用身边最常见的例子帮你翻译一下：你注册了一个App，为了验证手机号是你的，系统给你发了一条短信验证码。这条短信发出去之后，后台会记一条记录。如果你最终填对了验证码，这条记录的动作就是confirmed；如果你一直没填或者超时了，这条记录的动作就是timeout。Confirmations表里的一行数据，代表着一次确认请求的处理结果，而不是用户信息本身。
所以要算的“确认率”，用大白话说就是：在你发出的所有确认请求里，最终成功确认的比例。分子是动作等于confirmed的记录数，分母是你在 Confirmations 表里总共被记录了多少次。
这里有个非常关键的业务前提：并不是所有注册用户都会出现在 Confirmations 表里。有些用户注册完就走了，根本没触发确认流程；有些用户可能是老数据迁移进来的，压根没有走过这套机制。这意味着 Signups 表里的用户数量，通常会大于等于 Confirmations 表里出现的去重用户数量。
1.2 确认率在真实业务里到底怎么用
确认率从来不是一道纯刷题概念，它背后对应着一整套运营和风控逻辑。比如电商平台计算优惠券的核销率、支付系统计算一笔交易从下单到完成支付的转化率、IM系统计算消息推送后的送达率，本质上都是同一个数学公式：成功事件数除以总事件数。
这类统计里最敏感的问题就是“一个人没有事件发生怎么办”。数学上，0除以0是未定义的，但业务上通常会把分母为0的情况按0处理。原因很朴素：系统压根没给他发过确认请求，你总不能说他确认率是无穷大或者百分之百吧。把这个逻辑映射到SQL里，就是要处理空值兜底，也就是后面会讲到的 IFNULL 和 NULLIF。
我在实际业务里见过不少报表因此翻车。比如运营要看“新用户短信验证成功率”，开发直接 inner join 回执表，结果注册了但没收到短信的用户全部消失了，报表人数少了一大截，最后CEO拿着人数对不上账的报表来问，才发现是 join 方式选错了。这道题考的其实就是这种真实场景里的基础素养。
2. 审题时必须注意的三个细节
2.1 用户范围是全量注册用户，不是有确认记录的用户
这是整道题最大的坑，也可能是面试官最想看到的细节。题目要求计算每个注册用户的确认率，注意主语是“每个注册用户”，不是“每个有确认记录的用户”。
如果你写成INNER JOIN Confirmations ON ...，那么那些在 Confirmations 表中没有任何记录的用户，会直接从结果里消失。这在业务上意味着什么？意味着你丢掉了一批“确认率为0”的用户，同时把整体的确认率数值人为拉高了。
正确做法是以 Signups 作为左表，用LEFT JOIN关联 Confirmations。这样即使某个用户一条确认记录都没有，他也会出现在最终结果里，只是右边所有字段都是 NULL，需要后续把这种状态翻译成确认率0。
我在LeetCode讨论区看过不少解法，确实有人用 INNER JOIN 也通过了一部分用例，因为题目在边界条件上只给了一条无记录用户的数据。一旦测试数据里多塞几个空记录用户，INNER JOIN 的解法立刻暴露。所以练习时必须把这个逻辑刻在脑子里：left join 起手，先保证用户不丢。
2.2 无匹配记录时，COUNT(*)和COUNT(右表字段)的结果不一样
这个细节很少有人主动讲，但它直接决定了你的分母到底对不对。
在LEFT JOIN之后按 user_id 分组，如果某个用户没有匹配的确认记录，那么这一组里其实有一行“右表全为 NULL”的虚拟行。这时候：
COUNT(*)会统计这一行，结果是1。COUNT(c.user_id)会忽略这一行里的 NULL，结果是0。
用COUNT(*)做分母，0个确认记录的用户会被计算成 0/1=0，虽然最终输出的数值恰好也是0.00，看起来好像没毛病，但计算语义是错的。因为这1不是真实存在的确认请求，而是 LEFT JOIN 补出来的一行空数据。
严谨的写法是用COUNT(c.user_id)或者COUNT(c.action)作为分母，这样无匹配用户的分母就是0，再配合 NULLIF 转成 NULL，最后用 IFNULL 兜底成0。我用“碰巧能过”和“语义正确”来区分这两种写法，面试时如果能把这一点讲清楚，会比单纯背答案亮眼很多。
2.3 action字段取值范围和筛选方式
题目里 action 只可能是confirmed或timeout，所以统计成功数只需要判断c.action = 'confirmed'的行数。但怎么把这种“条件计数”写对，非常考验对 SQL 聚合函数行为的理解。
一个最常见的错误是：
COUNT(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END)这行 SQL 看起来是在数“确认成功的次数”，实际上它把每一行都数进去了，因为COUNT统计的是非 NULL 值，而ELSE 0里的0显然非NULL。正确姿势是让不满足条件的返回 NULL，因为COUNT会自动忽略 NULL：
COUNT(CASE WHEN c.action = 'confirmed' THEN 1 END)或者用 MySQL 里的简写：
COUNT(IF(c.action = 'confirmed', 1, NULL))这个理解一旦到位，后面很多变种题都能顺手解出来。
3. 一步一步写出标准答案
3.1 第一版：用LEFT JOIN + GROUP BY + COUNT解决
明确了业务模型和细节，第一版正确的SQL其实很容易写出来。以 Signups 为主表，LEFT JOIN Confirmations，按 user_id 分组，然后用条件统计计算分子分母。我最早提交通过的版本长这样：
SELECT s.user_id, ROUND( IFNULL( COUNT(IF(c.action = 'confirmed', 1, NULL)) / NULLIF(COUNT(c.user_id), 0), 0 ), 2 ) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id GROUP BY s.user_id;拆开来看每个关键点：
COUNT(IF(c.action = 'confirmed', 1, NULL))负责统计分子。IF 函数在 action 等于 confirmed 时返回1，否则返回 NULL，COUNT 忽略 NULL，所以这个聚合值正好是成功确认次数。COUNT(c.user_id)负责统计分母。作用在前面已经说过，它只统计右表真实存在的确认记录数。NULLIF(COUNT(c.user_id), 0)负责把分母为0的情况转成 NULL。因为0除以0在 MySQL 中会得到 NULL，倒也不会直接报错，但如果不处理，后面 IFNULL 的兜底逻辑就接不上。IFNULL(... , 0)负责把分母为0、除出来是 NULL 的情况统一改成0。ROUND(..., 2)负责保留两位小数，满足题目输出格式要求。
这套写法是三种方式里最“正统”的，逻辑链路完整，每一个环节都能单独解释清楚，特别适合在面试时一步一步讲给面试官听。
3.2 第二版：用AVG函数简化，思路完全不一样
如果你用的是 MySQL（LeetCode 的 SQL 运行环境就是 MySQL），其实还有一个更优雅的写法，核心思路从“先数数再除”变成了“求平均值”。
LEFT JOIN 之后，每个用户的确认记录被摊平成多行。对于某一行来说，如果 action 是confirmed，布尔表达式c.action = 'confirmed'的值是1；否则是0。那么确认率就等于这一组1和0的平均值。SQL写出来就是：
SELECT s.user_id, ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id GROUP BY s.user_id;这个写法非常短，但背后的模型转换很有意思：原来需要两个聚合字段相除，现在变成了一个聚合函数。AVG 在处理只有 NULL 的情况时返回 NULL，所以 IFFNULL 的兜底照样需要。
担心一点：AVG(c.action = 'confirmed')依赖 MySQL 的布尔运算返回0/1这个行为。如果你平时用的是 PostgreSQL，布尔表达式不会隐式转成整数，需要显式AVG(CASE WHEN ... THEN 1 ELSE 0 END)。如果是 SQL Server，那更是写不了一点。我在跨数据库迁移脚本时吃过这种亏，所以你要心里有数：AVG版简洁，但在 MySQL 之外的地方得改。
3.3 第三版：工程上更推荐“先聚合再JOIN”
前两种写法有一个共同的工程隐患：先把 Signups 和 Confirmations 做全量连接，再分组聚合。如果两张表数据量都很大，特别是 Confirmations 表里一个用户有多条记录，中间结果集会被撑大很多，查询性能不太好。
我在处理线上数据时养成了另一个习惯：先在 Confirmations 表里按 user_id 做聚合，把每个用户的总请求数和成功数算出来，让子查询输出“一个用户一行”的紧凑结果，然后再和 Signups 做 LEFT JOIN。SQL如下：
SELECT s.user_id, ROUND(IFNULL(c.cnt_confirmed / c.cnt_total, 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN ( SELECT user_id, COUNT(IF(action = 'confirmed', 1, NULL)) AS cnt_confirmed, COUNT(*) AS cnt_total FROM Confirmations GROUP BY user_id ) c ON s.user_id = c.user_id;在这个写法里，子查询内部的COUNT(*)是可以放心用的，因为子查询已经限定在 Confirmations 内部，每一行都是真实的确认请求记录。子查询外部，只有无匹配记录用户的 cnt_confirmed 和 cnt_total 会同时为 NULL，IFNULL 兜底成0。
从执行计划来看，这种“先聚合缩数据，再关联大表”的方式，能够有效减少连接阶段的数据膨胀，在很多业务报表场景里我都是这样优化SQL的。LeetCode 的数据量根本体现不出性能差异，但真实业务里的百万级数据，这种方式会稳很多。
4. 实战中踩过的坑
4.1 COUNT的ELSE 0陷阱
我在第2.3节提到过 COUNT(CASE WHEN ... THEN 1 ELSE 0 END) 的问题，这里单独拿出来说，是因为我现实中真的见过生产代码这么写。那是一条统计“支付成功订单占比”的SQL，运行了好久没人发现数据不对，直到某次和人工核算比对，发现分子永远等于分母，订单的成功率要么是100%，要么是0%。
问题就出在 COUNT 不会忽略0，只要 CASE 走了 ELSE 0 分支，这一行就被 COUNT 数进去了。改成THEN 1 END（不写 ELSE）或SUM(CASE WHEN ... THEN 1 ELSE 0 END)都可以。记一条规律：COUNT配合条件计数时，不满足条件要返回 NULL，永远不要返回0。
4.2 除数为0的正确处理顺序
很多新手第一次写除法，会在算完结果后用 IFNULL 兜底，但这个顺序是有讲究的。如果分母是0，分子也是0，0除以0在 MySQL 中得到 NULL，直接用 IFNULL(NULL, 0) 是可以的。但有些数据库对除零会直接抛异常，或者产生正负无穷值，逻辑就乱了。
所以我的习惯是：先用NULLIF(分母, 0)把0安全转换成 NULL，然后再除法。因为任意数除以 NULL 的结果是 NULL，整个表达式从源头避开除零异常，最后统一用 IFNULL 兜底。这条经验可以推广到任何SQL统计场景里，是我最常用的防御性写法。
4.3 用COUNT(c.action)还是COUNT(c.user_id)
在 LEFT JOIN 场景中，c.action 和 c.user_id 在处理 NULL 上表现一致：无匹配记录时，两者都是 NULL，COUNT 都会得到0。所以这题里两者等效。但如果哪天另一条SQL在关联时不是从主表出发，或右表字段本身可能存在空值，就需要认真想一下到底哪个字段更“可靠”。
我的判断标准很简单：分母统计的是“业务事件发生次数”，就选业务事件表里最不可能为空的字段。这题里 Confirmations 表的主键是(user_id, confirmation_date)，action 虽然是枚举，但必然有值。选 c.user_id 或者 c.action 都没有问题，但 c.user_id 作为外键关联字段，语义上更干净。
4.4 保留两位小数到底怎么写
MySQL 里ROUND(x, 2)是最直接的，对数值类型有效。要注意的是ROUND返回的还是数值类型，只是数值本身被四舍五入到两位小数，至于显示成0.50还是0.5，取决于客户端和结果集的展示方式。LeetCode 的评测只看数值是否相等，0.50和0.5都会被判定正确。
如果有人用FORMAT(x, 2)，返回的是字符串，加了千分位分隔符，比如1234.57会显示成1,234.57，这就完全不符合题目需求了。所以这里锁定 ROUND。
5. 变体扩展：面试官喜欢这样追问
5.1 如果限定“最近30天内发起的确认请求”怎么办
有些业务统计会要求只看近30天行为。这题的扩展写法在于把过滤条件放在JOIN时带上日期窗口：
SELECT s.user_id, ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id AND c.confirmation_date >= DATE_SUB('2024-01-01', INTERVAL 30 DAY) WHERE s.signup_time < '2024-01-01' GROUP BY s.user_id;把时间过滤放在 ON 条件里，而不是 WHERE 里，是很关键的差别。放在 ON 子句中，可以保留那些在时间窗口内没有确认记录的用户，他们依然会出现在结果里，确认率为0。放在 WHERE 里则会把这些用户直接过滤掉，语义完全变了。
5.2 如果action有三种状态怎么定义确认率
比如把failed也加进来，action 变成confirmed、timeout、failed三种。确认率仍然可以定义为confirmed次数除以总次数，分子条件不变，分母还是总记录数。但这时候前端报表可能会有另一种口径：把failed和timeouted视为“未确认”，确认率依旧是成功数除以总数，只是分母里包含了失败状态的记录。
如果在面试里被问到，建议先反问一句：“确认率在你们业务里的定义是什么？成功数除以总数，还是成功数除以移除失败后的剩余数？”这既体现对业务理解，也能避免写错公式。
5.3 如果还要查“注册超过30天但从没确认过的用户”
可以把上面的SQL再加一个 HAVING 条件：
SELECT s.user_id, ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id WHERE s.signup_time < DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY s.user_id HAVING COUNT(c.user_id) = 0;HAVING 后面接的是聚合结果，COUNT(c.user_id)=0 恰好筛出那些一条确认记录都没有的注册用户。这个变体在工作里太常用了，运营经常会要求拉“注册后沉寂”的用户名单做召回。
6. 这道题给我的启发
刷完这题之后，我在自己的笔记里写了一句：SQL入门时如果能把 LEFT JOIN 和聚合函数之间的交互关系彻底搞懂，起码能避开工作中一半的 SQL 事故。这道题恰好把这类交互关系浓缩在了一起。
还有一个小技巧想分享：每次写完一条SQL，我都会手动找一条“边界数据”做验证，在这题里就是找一个在 Confirmations 表里完全没记录的用户，确认他的结果是0.00。如果只拿常规数据测试，很多边界问题根本暴露不出来。这个小习惯帮我撑过了后面好几年的数据开发工作。
如果这题你已经掌握得很熟练了，我建议再挑战一下同类题型，比如“计算每个卖家的成交率”“统计每个渠道的活动参与率”，逻辑框架几乎一模一样。把这套处理LEFT JOIN、空值兜底、条件统计的思路吃透，刷题能稳住，工作也能稳住。
