企业官网建设流程全解析

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 JOIN+CASE 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是NULL，CASE走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后无请求记录的用户，分子分母都是 0，0 / 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.6667，ROUND后是0.67。

第三步：输出格式化

最后ROUND(..., 2)把结果统一为两位小数。MySQL 的ROUND遵循四舍五入规则，0.6667会变成0.67。

这三步走完，结果应该是：

3.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或IFNULL：

ROUND( 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 0，AVG不会遇到空集，稳妥得很。这也是我推荐第一种写法的原因之一。

4.3 保留两位小数：ROUND、FORMAT、CAST的差异

不少人在"保留两位小数"这一步翻车，因为不同数据库的函数行为不一样：

• MySQL：ROUND(x, 2)直接四舍五入，返回数值类型。这是最常用的。
• SQL Server：同样用ROUND，但要注意它返回的类型仍是数值，不会自动补齐末尾的 0。
• PostgreSQL：ROUND(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、空值、异常状态这些边界，谁就能少踩坑。