LeetCode第1934题《确认率》属于那种一读题干感觉是白送分、一提交就发现被暗坑放倒的SQL题。我第一次做的时候信心满满写完LEFT JOIN和GROUP BY结果用例直接红一片。排查了半天问题出在“把没有确认记录的用户排除掉了”“除数为零时没兜底”“COUNT把0也数进去了”这三个点上。后来我在公司做短信发送回执统计时发现这套逻辑简直原封不动被复刻注册用户就是Signups回执表就是Confirmations用户收到短信后的成功回执率就是这么算的。这篇就把这道题拆开讲透题目模型、三种解法、常见错误、性能写法、面试追问一次讲完。1. 先把题目的业务模型看清楚1.1 两张表到底在描述什么这道题给了两张表第一张叫 Signups记录了用户的注册信息核心字段就是 user_id 和注册时间。第二张叫 Confirmations recording 的是用户的“确认动作”核心字段是 user_id、确认时间以及 actionaction 只有两种取值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/10虽然最终输出的数值恰好也是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会自动忽略 NULLCOUNT(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否则返回 NULLCOUNT 忽略 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函数简化思路完全不一样如果你用的是 MySQLLeetCode 的 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 会同时为 NULLIFNULL 兜底成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分子也是00除以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 上表现一致无匹配记录时两者都是 NULLCOUNT 都会得到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、空值兜底、条件统计的思路吃透刷题能稳住工作也能稳住。
LeetCode 1934确认率：SQL聚合与LEFT JOIN实战详解
LeetCode第1934题《确认率》属于那种一读题干感觉是白送分、一提交就发现被暗坑放倒的SQL题。我第一次做的时候信心满满写完LEFT JOIN和GROUP BY结果用例直接红一片。排查了半天问题出在“把没有确认记录的用户排除掉了”“除数为零时没兜底”“COUNT把0也数进去了”这三个点上。后来我在公司做短信发送回执统计时发现这套逻辑简直原封不动被复刻注册用户就是Signups回执表就是Confirmations用户收到短信后的成功回执率就是这么算的。这篇就把这道题拆开讲透题目模型、三种解法、常见错误、性能写法、面试追问一次讲完。1. 先把题目的业务模型看清楚1.1 两张表到底在描述什么这道题给了两张表第一张叫 Signups记录了用户的注册信息核心字段就是 user_id 和注册时间。第二张叫 Confirmations recording 的是用户的“确认动作”核心字段是 user_id、确认时间以及 actionaction 只有两种取值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/10虽然最终输出的数值恰好也是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会自动忽略 NULLCOUNT(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否则返回 NULLCOUNT 忽略 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函数简化思路完全不一样如果你用的是 MySQLLeetCode 的 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 会同时为 NULLIFNULL 兜底成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分子也是00除以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 上表现一致无匹配记录时两者都是 NULLCOUNT 都会得到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、空值兜底、条件统计的思路吃透刷题能稳住工作也能稳住。
