前阵子帮团队里的同学做 SQL 面试复盘聊到一道数据库题名字叫做“制作会话柱状图”。题号在某个在线题库里排在第 1435 位表面上看是个统计聚合题结果不少人一上来就卡住。问题拆开来看其实非常典型一张保存会话时长的表要求按时间区间分桶输出每个区间的会话数量。区间是[0-5)、[5-30)、[30-60)、[60)而且哪怕某个区间一条数据都没有也得输出 0。这个“空桶也输出”的要求就是整道题的分水岭。说实话这道题我在面试候选人和帮忙查业务 SQL 时都碰到过同款问题。很多人第一反应就是CASE WHEN然后GROUP BY一跑发现少了行才知道 SQL 里“把连续值切成离散标签”和“保证标签维度完整”是两码事。这篇文章就围绕这个题目展开从表结构、边界定义、三种解法到真实业务里的分桶实践完整拆一遍。给准备数据库面试的朋友也给平时天天写报表的兄弟做个参考。1. 读懂题目Sessions 表和四个时间桶1.1 表结构、字段与业务含义这道题给的表结构很简洁一张Sessions表主要字段就是session_id和duration。用表格看就是这样列名类型含义session_idint会话唯一编号durationint会话持续时长单位通常是分钟题目要求的输出很简单把会话按照持续时间分成四个区间然后统计每个区间里有多少条会话记录。最终结果要有两列一列是区间名一列是数量。现实中这张表对应的场景非常常见一个 App 记录用户每次登录的时长或者在线客服系统记录每次会话的接待时长。在做产品分析的时候我们很少关心“平均时长是 7.3 分钟”这种数字反而更想知道“有多少用户连 5 分钟都没待够”“有多少用户待了超过半小时”。把连续时长切成几个区间再数每个区间的人数这个过程就是分桶统计也就是柱状图背后的数据基础。这里先提醒一句题目里duration给的是分钟数但如果你在真实业务里遇到的数据源给的是秒、毫秒或者直接给了开始时间和结束时间两个字段那就得先换算单位。别拿起 SQL 就写先确认字段语义这是做数据查询的第一步也是很多人容易忽略的一步。1.2 分桶边界半开区间和“等于边界归谁”的问题四个桶的划分规则如下桶名条件含义[0-5duration 0 且 duration 5短会话不足 5 分钟[5-30duration 5 且 duration 30中等会话5 到 30 分钟之间[30-60duration 30 且 duration 60较长会话30 到 60 分钟之间[60duration 60长会话超过 1 小时这种写法叫半开区间左边闭右边开。为什么业界都这么设计因为可以保证每一个值只有一个归属不会出现“duration 等于 5 到底算上一个桶还是下一个桶”的争执。柱状图的横轴刻度也不会重叠数据不会重复计算。我在真实项目里见过有人用闭区间写结果[0-5]和[5-30]都包含 5同一个数被算进两个区间报表里的总数和明细对不上查了半天才发现是边界条件写重了。从那以后我给自己定了个规矩凡是分桶一律用半开区间。这道题里[0-5这个写法其实就是半开区间的标准表示左边闭右边开数学上很严谨写 SQL 的时候也方便。另外有一个细节在CASE WHEN里写条件时建议按照从小到大的顺序判断写成duration 5、duration 30、duration 60这样逻辑上最不容易出错。因为CASE WHEN是顺序匹配的只要命中了第一个条件就会进入对应桶后面的条件根本不会判断。如果你写成duration 0 AND duration 5这种完整写法也没问题但顺序判断更简洁也不容易漏条件。2. 核心知识点拆解这道题不是简单 GROUP BY2.1 条件聚合的两种打开方式这道题看起来简单但它实际上考了三个容易被忽略的 SQL 知识点条件聚合、结果集完整性和排序控制。第一个知识点是条件聚合。条件聚合有两种写法。第一种是在SELECT后面用CASE WHEN给每条记录打上桶标签然后GROUP BY这个标签第二种是在SELECT里用SUM(CASE WHEN ... THEN 1 ELSE 0 END)直接把符合条件的记录数加出来不需要GROUP BY。两种写法都能算出结果但适用场景不太一样。第一种写法的问题是GROUP BY能不能直接引用SELECT里定义的别名这个在不同数据库里行为不一样。MySQL 和部分分支版本允许GROUP BY引用别名这是对标准 SQL 的扩展但标准 SQL 的执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY理论上在GROUP BY阶段还看不到SELECT里的别名。所以有些严格模式或者数仓引擎会直接报错比如我们把CASE WHEN的结果命名为bin然后写GROUP BY bin在严格模式下是过不去的。稳妥的做法是先写个子查询在内层把分桶标签用CASE WHEN算好直接作为一列输出外层再对那一列做GROUP BY。这个习惯建议直接刻进脑子里能避免很多莫名其妙的兼容性问题。我自己过去就吃过这个亏本地 MySQL 写得好好的换到某云数据仓库一跑就报错找半天原因才发现是GROUP BY引用别名的问题。2.2 空桶的哲学柱状图的横轴必须有刻度第二个知识点是结果集完整性也就是空桶问题。直接用GROUP BY出来的结果只有那些出现过记录的分组。如果测试数据里恰好没有 30 到 60 分钟之间的会话那么查询结果就只有三行[30-60这个桶直接消失不见。但题目要求很明确四个区间都必须输出缺失的区间要补 0。这个需求背后是有实际意义的柱状图的横轴是一个固定的刻度体系你不能让柱子凭空少一根否则看图的人会以为系统出错或者数据被截断了。哪怕某个区间一根柱子都没有刻度也得留在那里数值显示为 0。在 SQL 里实现“维度补齐”套路几乎是固定的先构造一个包含全部区间的维度表再拿它去LEFT JOIN统计数据。这是做 BI 报表的人每天都要用的基本功。行业里也管这个叫“日期维度补齐”或者“类别维度补齐”本质就是让结果集的行数不受数据本身影响始终和定义好的维度保持一致。拿生活里的例子类比一个月有 30 天就算某天完全没有订单日报上也要把这天列出来销售额写 0。否则到了月底做汇总天数对不上数据完整性就崩了。LeetCode 这道题其实就是这个场景的简化版只不过把日期换成了会话时长区间。2.3 时间函数不是重点单位才是重点第三个知识点也是很多人容易误解的地方这道题虽然是数据库分类下的题目但它考的不是时间差计算函数。duration字段已经是一个算好的数字不需要你用DATEDIFF或者TIMESTAMPDIFF去推导。真正该留意的是数据单位的一致性。如果某天你的数据源给的不是分钟而是秒、毫秒或者直接给了start_time和end_time两个DATETIME字段你需要先做换算。比如用TIMESTAMPDIFF(SECOND, start_time, end_time) / 60.0得出带小数的分钟数再参与分桶逻辑。换算完了之后所有条件都基于同一个单位去判断避免一个 SQL 里一会儿秒一会儿分钟把自己都绕晕。顺带提一下两个常见时间函数的区别DATEDIFF只能返回两个日期之间相差的天数精度太低TIMESTAMPDIFF可以指定单位SECOND、MINUTE、HOUR都行。做时长类分析的时候优先用TIMESTAMPDIFF。3. 三种可落地的解法从简到繁3.1 解法 A先统计、后补维度推荐先给出我认为最标准的解法。核心思路分两步第一步在子查询里把每条记录分到对应桶然后按桶统计数量第二步用UNION ALL构造一个包含全部桶名的维度表左连接统计结果缺失的桶用 0 补齐。SELECT b.bin, IFNULL(t.total, 0) AS total FROM ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ) b LEFT JOIN ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin, COUNT(*) AS total FROM Sessions GROUP BY bin ) t ON b.bin t.bin ORDER BY CASE b.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;拆开来看这个 SQL 的每一部分。最内层的子查询t做的事情是扫描Sessions表用CASE WHEN把每条记录归入对应桶然后GROUP BY bin统计每个桶的会话数量。这里有一个问题需要注意内层子查询里的GROUP BY bin引用了CASE WHEN表达式产生的别名。在 MySQL 里这样写没问题但如果想做到严格兼容建议再包一层子查询把CASE WHEN的结果先作为独立的列算出来外层再分组比如FROM ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) s GROUP BY s.bin外面的b子查询就是标准的“维度构造”用UNION ALL把四个桶名竖排成四行。之所以用UNION ALL而不是UNION是因为不会出现重复行没必要去重UNION ALL性能也更好。左连接之后没有数据的桶对应的t.total就是NULL包一层IFNULL转成 0。最后是排序。因为bin是文本如果直接ORDER BY bin会按照字典序排成[0-5、[30-60、[5-30、[60这不是柱状图从左到右的自然顺序。所以要用CASE再映射一次手动指定顺序。这个细节在题目测试用例里可能不会触发严格检查但实际做报表一定会被产品经理问“为什么顺序是乱的”。3.2 解法 B四个 UNION ALL逻辑最朴素第二种解法思路完全不同不分组直接用四个SELECT分别统计每个桶的数量再用UNION ALL拼起来SELECT [0-5 AS bin, SUM(CASE WHEN duration 5 THEN 1 ELSE 0 END) AS total FROM Sessions UNION ALL SELECT [5-30, SUM(CASE WHEN duration 5 AND duration 30 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [30-60, SUM(CASE WHEN duration 30 AND duration 60 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [60, SUM(CASE WHEN duration 60 THEN 1 ELSE 0 END) FROM Sessions;这个方案的优点非常明显完全没有GROUP BY不存在别名引用问题也不存在空桶丢失问题。四个区间的条件写得很明确任何一条数据只会命中其中一个SUM条件互斥性有保证。缺点也相当突出表会被全表扫描四次。在这个题目里数据量很小完全无所谓但在生产环境里如果Sessions表有几百万甚至上亿行这个写法会让数据库多扛三倍的压力。所以我的建议是这个解法适合作为理解题意的入门方案不适合作为生产环境的最终方案。如果面试官问你“还有什么优化空间”你最好能主动提到这一点。3.3 解法 CCTE 把分桶和补齐拆开第三种解法是解法 A 的现代写法用公共表表达式CTE把“分桶”和“维度补齐”两个步骤拆得清清楚楚。在可读性上是最好的适合支持 CTE 的现代数据库环境WITH dim AS ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ), bucketed AS ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) SELECT d.bin, COUNT(b.bin) AS total FROM dim d LEFT JOIN bucketed b ON d.bin b.bin GROUP BY d.bin ORDER BY CASE d.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;这里有一个小技巧值得专门讲外层用的是COUNT(b.bin)而不是IFNULL(SUM(...), 0)。因为LEFT JOIN之后如果bucketed里没有匹配的行b.bin的值就是NULL而COUNT本身会忽略NULL、只数非空值所以COUNT(b.bin)正好可以实现“没有数据就输出 0”的效果。这个写法比先JOIN再包IFNULL要简洁而且执行效率也不差。三种解法说到底核心思想是一样的先把维度补全再把数据对齐到维度上。区别只在于“补维度”用的是显式的UNION ALL构造还是隐式的分组聚合。笔试做题用哪种都能过但如果我们站在工程角度选解法 A 和解法 C 明显比解法 B 更值得推广。4. 实操中容易踩的坑和排查心得4.1 边界值到底归哪个桶这是最典型的问题。duration 5到底算[0-5还是[5-30按照半开区间定义左边的闭区间包含边界值所以 5 算进[5-30。在CASE WHEN里如果写的是duration 5那么等于 5 的行会自然落到下一个条件结果完全正确。怕的是有人写成duration 5这样 5 就会被算进第一个桶两个桶的语义就被破坏了。排查这个问题的技巧很简单构造边界测试数据。在测试环境里插入duration分别为 4、5、29、30、59、60 的会话记录然后跑一遍查询检查每个值落在哪个桶。如果 4 落在[0-5、5 落在[5-30、29 落在[5-30、30 落在[30-60、59 落在[30-60、60 落在[60那分桶逻辑就是对的。这一步能帮你把边界值的隐患提前掐死在摇篮里。4.2 GROUP BY 别名在不同数据库的行为差异我在前面已经提过一次GROUP BY别名的问题这里再展开说透。标准 SQL 的执行顺序里GROUP BY在SELECT之前所以理论上不能引用SELECT里的别名。但 MySQL 做了扩展允许这样写这就导致很多人在本地开发环境没问题一换到严格要求标准的数仓引擎就报错。实际工作中我遇到过不止一次同样一条 SQL在 A 平台跑得好好的到了 B 平台直接抛Unknown column异常。排查方法很简单把GROUP BY后面的别名替换成完整的CASE WHEN表达式问题立刻消失。最稳妥的写法永远是在子查询里先算好分桶列外层再分组不要依赖任何数据库的扩展特性。4.3 性能隐患条件聚合里的子查询和大表全扫有些人在真实业务里会写出类似SUM(CASE WHEN duration (SELECT AVG(duration) FROM Sessions) THEN 1 ELSE 0 END)这种 SQL。问题在于CASE WHEN里如果嵌入了标量子查询每一行都要执行一次子查询性能会非常差。这道题虽然没有复杂到那个程度但它引出了一个原则分桶条件必须是简单表达式不能带子查询不能带自定义函数否则大表场景下一定会拖垮数据库。另外解法 B 里提到的“全表扫描四次”也是一个隐患。如果Sessions表数据量大建议优先使用只扫描一次表的写法。判断标准很简单看执行计划里Sessions被访问了几次。出现多次大表扫描就值得重新设计查询结构了。4.4 排序和 NULL 的隐藏坑排序的问题前面已经讲过了文本序不是我们想要的自然序必须手动映射。这里再补充一个容易忽略的坑duration字段如果允许为NULL那么在CASE WHEN里所有比较条件都会返回FALSE最终会被ELSE分支捕获被算进[60。这在语义上是错的未知时长怎么能算作“超过一小时”呢处理方式取决于业务定义。如果NULL表示数据缺失比较合理的做法是在子查询最前面加WHERE duration IS NOT NULL把这些记录排除在统计之外或者用COALESCE(duration, 0)给个默认值让它们落到最短桶。不管选哪种都要显式处理而不是任由ELSE兜底。下面把常见的坑整理成一张速查表现象可能原因解决思路输出的桶缺少某一行直接GROUP BY导致空桶消失先构造维度表再LEFT JOIN统计数据5 分钟被算进[0-5分桶条件写成duration 5统一用半开区间边界值归入右区间排序结果变成[0-5、[30-60文本字典序不符合预期用CASE手动映射桶的顺序在数仓引擎里GROUP BY报错引用了SELECT别名标准 SQL 不允许子查询先算列外层再分组NULL时长被算进最长桶ELSE兜底捕获了NULLWHERE过滤或COALESCE显式处理5. 从这道题延展出去分桶统计的真实应用场景5.1 用户活跃时长分析LeetCode 这道题简化了很多真实业务细节但分桶统计这个思路在实际数据分析里是绕不开的。我举个例子。某个内容类产品要分析用户单次访问时长原始表里有login_time和logout_time两个字段。我们先用TIMESTAMPDIFF(MINUTE, login_time, logout_time)算出访问时长然后分桶统计[0-1、[1-5、[5-30、[30。这样一看就能知道自己是“流量型产品”大量用户待几秒钟就走还是“黏性产品”大量用户停留超过半小时。后续的产品策略完全不同。这里的关键点和题目一模一样先把时间差换算成统一单位再分桶最后补齐维度。不管字段多复杂这个套路是固定的。5.2 订单金额区间与人群结构分析分桶统计在电商场景里同样常见。订单金额是典型的连续值分析时通常切成0-50、50-100、100-500、500-2000、2000几个区间。同样是半开区间边界写法和语义要提前定义好。在这个基础上还可以再加一维统计每个金额桶里的下单人数用COUNT(DISTINCT user_id)。这样就可以看出高价值金额区间的人群密度运营团队据此决定往哪个价位段倾斜资源。和 LeetCode 原题相比只是多了一个去重计数的维度底层的 SQL 结构没有本质区别。5.3 分桶设计原则总结做了这么多年数据分析我总结了几条分桶设计的经验写在这里供参考区间互斥且穷尽行话叫 MECE。任何一条数据必须且只能落进一个桶不允许有漏网之鱼也不允许有重复计入。区间数量控制在 5 到 8 个。太少会掩盖分布规律太多则人眼没法快速看出结构。超过十几个桶的柱状图基本只能靠程序读人已经看不过来了。边界值必须在文档里写清楚。比如“5 分钟归入 5 到 30 分钟桶”或者在下发 SQL 时用注释写明白避免同事之间理解不一致。桶标签尽量无歧义。[0-5这种写法数学上很严谨但业务同事可能看不懂。实际报表里可以写成“0 到 5 分钟不含 5”或者直接用“不足 5 分钟”“5 到 30 分钟”。原则是让看图的人不需要额外解释就明白含义。回到 LeetCode 这道题本身它的价值不在于难度而在于它把分桶统计里最容易翻车的几个坑全部暴露出来了边界值定义、空桶维度的保留、结果排序、单位换算。如果能在不看答案的情况下独立写出满足全部测试用例的 SQL那说明你真的理解了聚合查询的底层逻辑。我在实际带人的时候经常用这道题做入职摸底测试。不是因为它的难度而是因为它真的能反映一个人是不是有做数据报表的底层素养。最后送大家一句话分桶容易桶全难。把这道题吃透以后遇到各类“补齐维度”的统计需求你会感谢自己现在花的时间。