1. 题目拆解与业务场景还原1.1 一段话看懂这道题在问什么力扣的 1934 题确认率是 SQL 入门到进阶之间一道非常典型的聚合 连接综合题。题目给了两张表一张是用户注册表Signups记录每个用户什么时间注册另一张是确认记录表Confirmations记录用户每次请求确认动作时系统给出的回应结果是confirmed已确认、timeout超时还是expired已过期。要算的东西很朴素每个用户的确认率。确认率的定义是该用户所有确认请求中状态为confirmed的请求数除以总请求数。如果某个用户一条确认请求都没有那他的确认率记为 0保留两位小数输出。我第一次刷这道题的时候第一反应是这不就是一个LEFT JOIN加AVG(CASE WHEN)吗但真正动手写了之后才发现里面有几个细节如果不注意很容易写出看起来对、跑起来错的 SQL。比如用户没有请求记录时怎么办confirmed之外的状态要不要计入分母保留两位小数用ROUND还是用FORMAT这些问题在真实业务里可一点都不多余。1.2 为什么这道题值得单独拿出来讲这道题表面上是一个 LeetCode 中等难度的数据库题但它覆盖了几个在真实数据分析工作中天天要用的能力表连接的多对一关系处理。Signups和Confirmations是一对多的关系一个用户可以有多条确认请求。这种主表 明细表的结构在任何业务系统里都很常见比如订单表和订单明细表、用户表和登录日志表。聚合时对条件分支的处理。不是所有记录都要参与分子计算用CASE WHEN把布尔条件变成 0/1 再求平均是 SQL 里最高频的技巧之一。空值的语义理解。LEFT JOIN之后没有匹配行相关字段会是NULL怎么把NULL转成0用COALESCE还是IFNULL背后是对 SQL 三值逻辑的理解。输出格式的控制。保留几位小数用什么函数在不同数据库引擎里写法还不一样这也是实际开发中容易被坑的地方。所以我说这道题是一道小而全的题小在表结构和数据规模全在知识点覆盖。把这道题吃透了举一反三的能力会有实打实的提升。2. 核心思路与两种主流解法对比2.1 解法一LEFT JOINCASE WHEN条件聚合先来看最直白的一版写法。思路是先以Signups为主表LEFT JOIN明细表让每个用户带着自己的全部确认请求行参与查询然后按用户分组用AVG配合CASE WHEN对状态为confirmed的请求计 1其余计 0求平均后自然得到 confirmed 请求的比例。SELECT s.user_id, ROUND(AVG(CASE WHEN c.action confirmed THEN 1 ELSE 0 END), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id GROUP BY s.user_id;这段 SQL 巧妙的地方在于AVG(CASE...END)本身就能处理分母的问题。因为LEFT JOIN后没有请求记录的用户只有一行且c.action是NULLCASE走ELSE 0分支平均值得 0。有请求记录的用户分母是该用户的总行数分子是confirmed的行数比例自然正确。2.2 解法二拆分分子分母用COUNT分别统计另一种常见的写法是把分子和分母分开算最后做除法。这种写法的可读性更强也更贴近我到底要算什么的思维过程SELECT s.user_id, ROUND( COUNT(CASE WHEN c.action confirmed THEN 1 END) / COUNT(c.action), 2 ) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id GROUP BY s.user_id;这里用COUNT(CASE WHEN action confirmed THEN 1 END)统计确认次数COUNT(c.action)统计所有有实际动作的请求次数。注意COUNT只统计非NULL值所以CASE不满足条件时返回NULL不影响统计。LEFT JOIN后无请求记录的用户分子分母都是 00 / NULL在 SQL 里结果是NULL这一步其实埋了个隐患下面我们会专门讨论。2.3 两种解法的取舍没有绝对的好坏只有合适的场景两种解法大部分情况下都能得到相同结果但侧重点不同AVG(CASE...)写法代码更短含义更数学化——把布尔值直接当 0/1 求均值。劣势是逻辑不如第二种直观初学者读起来需要转个弯。COUNT分子分母分离写法逻辑显式便于在分组维度更多时做扩展。劣势是代码略长且对NULL的敏感度更高容易踩坑。我个人在实际工作中更倾向用第二种原因是真实业务里确认率的定义经常会被业务方追问你的分母到底包含哪些状态timeout 要不要算expired 算不算分子分母分开写你可以在代码里直接看出统计口径排查问题的成本更低。但是在 LeetCode 这类场景下用第一种写法刷题更干净利落。提示两种解法中表连接都要用LEFT JOIN而不是JOIN。如果用INNER JOIN那些一条确认请求都没有的用户会直接被过滤掉结果表里直接缺行达不到题目确认率为 0 也要输出的要求。3. 实操过程与关键环节实现3.1 建表与造数据先把测试环境搭起来看题做题是纸上谈兵真正要验证 SQL 写得对不对还是得把数据落到本地跑一遍。我在本地用 MySQL 8.0 环境验证了这道题的所有写法建表和造数据的语句如下-- 用户注册表 CREATE TABLE Signups ( user_id INT, signup_time DATETIME, PRIMARY KEY (user_id) ); -- 确认记录表 CREATE TABLE Confirmations ( user_id INT, time DATETIME, action VARCHAR(20) ); INSERT INTO Signups (user_id, signup_time) VALUES (1, 2025-01-01 10:00:00), (2, 2025-01-02 11:00:00), (3, 2025-01-03 12:00:00); INSERT INTO Confirmations (user_id, time, action) VALUES (1, 2025-01-02 10:00:00, confirmed), (1, 2025-01-02 11:00:00, timeout), (2, 2025-01-03 10:00:00, confirmed), (2, 2025-01-03 11:00:00, confirmed), (2, 2025-01-03 12:00:00, timeout), (3, 2025-01-04 10:00:00, expired);这里我故意构造了三种典型情况用户 1 有确认有超时用户 2 确认占多数用户 3 只有一条过期请求。这样能覆盖绝大多数测试用例。3.2 逐层拆解 SQL 的执行过程很多人学 SQL 只记语法不理解执行顺序导致出了问题不知道从哪查起。这道题的 SQL 看似只有三行实际内部执行逻辑值得逐层拆开看。第一步连接阶段SELECT s.user_id, c.action FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id;这一步的结果是一张宽表Signups 里每个用户至少保留一行多出的行数取决于 Confirmations 里匹配的条数。上面的测试数据跑完你会得到 6 行结果。最关键的一点是用户 3 虽然没有confirmed或timeout记录但他有一条expired记录所以LEFT JOIN之后他并不是空行而是带着action expired的那一行参与后续聚合。第二步分组与条件判断GROUP BY s.user_id把 6 行聚合成 3 组。此时CASE WHEN c.action confirmed THEN 1 ELSE 0 END会在组内逐行判断形成一个 0/1 的隐藏列表。以用户 2 为例三行数据的隐藏列表是[1, 1, 0]AVG得到0.6667ROUND后是0.67。第三步输出格式化最后ROUND(..., 2)把结果统一为两位小数。MySQL 的ROUND遵循四舍五入规则0.6667会变成0.67。这三步走完结果应该是user_idconfirmation_rate10.5020.6730.003.3 关于expired状态的一个重要细节这里是很多人在评论区反复讨论、也是我最想强调的一个点为什么expired不算有效确认但要算进分母再看一遍题目原文对确认率的定义confirmed的请求数 / 总请求数。关键是总请求数到底指什么。从 LeetCode 的预期输出来看expired虽然不属于confirmed但它也是一次有效的请求动作所以分母必须包含它否则用户 3 这种只有一条expired记录的人分子分母都为空就无法得到确认率 0这个合理的输出。这跟真实业务里的统计口径思维完全一致。比如你在拉一个支付转化率报表分子是支付成功人数分母是进入支付页人数。支付页用户取消了支付这个状态既不等于支付成功但它确实算一次进入支付页的行为必须进分母。反过来用户根本没打开支付页就不算。这就是为什么LEFT JOIN之后不是所有NULL都无脑补零你得先想清楚业务口径。3.4 推荐的标准答案与可读性更好的变体如果要我给出一个在 LeetCode 上能直接通过的完整答案我会用AVG(CASE...)的写法简洁且少出幺蛾子SELECT s.user_id, ROUND(AVG(CASE WHEN c.action confirmed THEN 1 ELSE 0 END), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id GROUP BY s.user_id;如果是在实际工程项目里我会额外加一句ORDER BY s.user_id保证输出顺序稳定。这个排序在 LeetCode 判题时不是必需的但真实报表场景里用户 ID 不排序会导致每次导出结果顺序随机给下游核对数据造成困扰。4. 常见问题与排查技巧实录4.1 为什么我的结果里少了没有确认记录的用户这是初学者最容易犯的错误根源在于连接类型选错。用INNER JOIN时Signups里找不到匹配记录的用户会被整行丢弃自然就消失了。排查方法很简单先单独跑一遍LEFT JOIN不加聚合的查询看看每个用户是否至少出现一次。如果某个用户压根没出现在结果里那一定是你用了JOIN或者WHERE条件里误加了对右表字段的过滤。比如SELECT s.user_id, c.action FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id WHERE c.action confirmed;这种写法相当于把LEFT JOIN变成了INNER JOIN——WHERE子句在连接完成后过滤把action为NULL或非confirmed的行全删掉了。你要加条件过滤右表字段时必须把它放在连接条件ON里而不是WHERE里这点非常容易踩坑。4.2 除零问题确认率是 0/0 怎么办我前面提到第二种COUNT写法在用户没有任何请求记录时分子分母都是 0也就是0 / 0。在标准 SQL 里这个结果是NULL而不是报错。ROUND(NULL, 2)依然是NULL最终输出会是NULL与题目要求的0.00不符。处理方式有两种。第一种是在除法外面套COALESCE或IFNULLROUND( COALESCE( COUNT(CASE WHEN c.action confirmed THEN 1 END) / COUNT(c.action), 0 ), 2 ) AS confirmation_rate第二种是干脆用AVG(CASE...)写法天然规避除零问题。我在本地验证过AVG对空组返回NULL但LEFT JOIN保证了每个用户至少有 1 行只是那行右表字段是NULL。所以CASE走ELSE 0AVG不会遇到空集稳妥得很。这也是我推荐第一种写法的原因之一。4.3 保留两位小数ROUND、FORMAT、CAST的差异不少人在保留两位小数这一步翻车因为不同数据库的函数行为不一样MySQLROUND(x, 2)直接四舍五入返回数值类型。这是最常用的。SQL Server同样用ROUND但要注意它返回的类型仍是数值不会自动补齐末尾的 0。PostgreSQLROUND(x, 2)可用但x必须是numeric类型如果是double precision类型会报错需要先CAST。Oracle用ROUND也可以但很多场景下你会看到TO_CHAR(x, FM999.00)这种格式化写法返回的是字符串。FORMAT(x, 2)在 MySQL 里也能保留两位小数但它返回的是字符串类型而且会带千位分隔符。比如FORMAT(1234.5, 2)会得到1,234.50这在 LeetCode 判题系统里会直接导致答案错误。所以尽量用ROUND不要图省事用FORMAT。注意在力扣的环境里输出0.50而不是0.5才符合预期。ROUND对 0.5 这类恰好一位小数的值返回的是0.50不会自动去掉末尾零。如果最终结果显示0.50被显示成0.5那多半是前端展示层处理了精度不是 SQL 的问题。4.4 分组后要不要加ORDER BY的讨论LeetCode 对这道题的输出没有强制排序要求但实际开发中几乎一定会加。我的习惯是聚合查询后除了GROUP BY的字段输出的每个字段都必须语义明确再根据业务需求决定排序字段。这道题里加不加ORDER BY user_id对结果没有影响但加了之后用diff工具对比两次查询结果会方便很多。4.5 一个容易忽略的索引优化点虽然这道题的数据量很小但把思路延伸到真实生产环境里你就得考虑Confirmations表如果几十万行这个LEFT JOIN的性能怎么样一个很现实的建议是在Confirmations表的user_id字段上建索引。原因在于LEFT JOIN的语义是以左表为驱动表逐行去右表匹配如果在右表的连接键上没有索引每次匹配都得全表扫描左表多大扫描次数就有多大。建索引后匹配就能走索引查找查询耗时会大幅下降。CREATE INDEX idx_confirmations_user_id ON Confirmations(user_id);如果你用的是 MySQL还可以用EXPLAIN看看执行计划确认是否走了index或ref级别的访问而不是ALL全表扫描。这道题本身不需要考虑优化但这个习惯一旦养成以后处理千万级数据时会受益无穷。5. 从力扣题到真实业务的思路迁移5.1 确认率在真实业务里的多维度复刻把确认率这个概念抽象一下它本质上是一个比率型指标分子是满足特定条件的事件数分母是整个事件总数。这个框架在真实业务里到处都是消息推送到达率分子是delivered的消息数分母是sent的消息数。状态可能有failed、pending、delivered。支付转化率分子是paid的订单数分母是created的订单数。状态可能有pending、paid、refunded、cancelled。客服响应率分子是被客服回复过的工单数分母是全部工单数。状态可能有open、resolved、pending。一旦遇到这类需求你就可以套用这道题的模板找到主表用户、订单、工单找到明细状态表用LEFT JOIN保底维度用CASE WHEN定义分子用COUNT或AVG完成聚合最后统一格式。5.2 口径管理问题同一个指标不同部门算出来不一样做数据分析的人最怕听到一句话为什么你俩做的是同一个指标数字却对不上原因就是口径不一致。比如注册转化率市场部定义的分母是落地页访问用户数运营部定义的分母是注册页到达用户数分子都是注册成功用户数但最后算出来的百分比差一大截。这个问题在代码层面解决不了必须在需求评审阶段就确认好分母的事件范围是什么分子的事件定义是什么没有事件记录的对象如何处理这道力扣题其实已经隐含了答案分母包含所有有请求动作的用户包括expired没有请求记录的用户确认率按 0 算。如果业务方对分母的定义是只包含 confirmed 和 timeout那expired就需要从分母剔除SQL 要改成WHERE action IN (confirmed, timeout)。别小看这一行WHERE它背后是业务口径的决策。遇到这种问题建议写进数据字典或指标文档留个可追溯的记录。5.3 用窗口函数扩展同时看每个用户的确认率和整体均值如果你觉得这道题已经做完了不妨再往前走一步如何在同一个查询里同时输出每个用户的确认率和全站平均确认率这可以用窗口函数轻松实现SELECT s.user_id, ROUND(AVG(CASE WHEN c.action confirmed THEN 1 ELSE 0 END), 2) AS user_rate, ROUND(AVG(AVG(CASE WHEN c.action confirmed THEN 1 ELSE 0 END)) OVER (), 2) AS global_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id c.user_id GROUP BY s.user_id;这里用到了聚合窗口函数的技巧内层AVG(CASE...)是组内聚合外层AVG(...) OVER ()是对所有组的聚合结果再做一次整体平均。这种个体占比 整体基准的对比视图在做异常检测、用户分层时非常实用。比如电商场景里你可以一眼看出哪些用户的支付转化率显著低于全站平均值从而圈出需要跟进干预的用户群。这个扩展思路的价值在于不要把力扣题当成刷题任务而是当成一个可迁移的思维模型。每做完一道题问自己三个问题这个模型在业务里怎么用换个维度怎么套多个模型怎么组合想明白这三个问题刷题和业务能力才会真正打通。6. 踩坑记录与做题之外的三点建议6.1 我刷这道题时踩过的三个坑第一个坑是把expired直接忽略。我第一次写的时候用WHERE c.action confirmed OR c.action timeout把expired过滤掉了结果发现用户 3 的确认率变成了NULL。后来意识到题目里的分母是所有请求动作expired也是请求的一部分不能想当然地过滤掉。这个坑提醒我读题要先确认指标口径不能凭直觉做假设。第二个坑是在LEFT JOIN的WHERE里加了右表条件。前面讲过这会让LEFT JOIN降级成INNER JOIN。我排查了十几分钟才发现问题后来养成习惯只要在LEFT JOIN查询里需要对右表字段做过滤一律尝试挪到ON子句里。第三个坑是把ROUND(0.5, 2)和前端显示搞混。一开始我以为输出0.5也算对结果发现预期值是0.50再一查才知道 LeetCode 判题系统是按字符串比对的。虽然ROUND返回的字段类型不是字符串但数值 0.5 和 0.50 的底层存储是一致的判题系统实际是按数值比较这一步我一开始多虑了但也因此把ROUND和FORMAT的区别彻底搞明白了不算白踩。6.2 给刚开始刷题的人的三点建议第一不要直接看题解。先自己写哪怕写得稀烂只要跑通了就算赢。写不出来就看题目下面的讨论区但要带着问题去讨论区别人为什么用AVG而不用COUNT评论区里经常藏着比题解更有价值的发言。第二一道题尽量掌握两种写法以上。SQL 的灵活之处在于同一结果可以通过不同方式实现熟练之后你在面对真实业务时才能根据场景选择合适的方案。只会一种写法换个数据库环境就可能抓瞎。第三每道题做完后主动加一个指标。比如这道题你可以试着额外输出每个用户的请求总数、确认请求数、超时请求数把它们放在同一个结果集里。这个动作能帮你把会做这道题变成理解这个数据模型价值完全不同。6.3 最后分享一个我自己的测试习惯写完 SQL 后我会在测试数据里刻意构造几条最容易出问题的记录一条完全没有明细数据的记录、一条只有expired状态的记录、一条所有明细都是confirmed的记录。如果这三条记录的输出都符合预期那这道题基本稳了。这个习惯帮我挡掉了大量没必要的提交失败也让我在面试手写 SQL 时更有底气。数据工作的本质就是跟边界条件打交道谁能更快想到NULL、空值、异常状态这些边界谁就能少踩坑。