做数据分析的人应该对“按区间分桶统计”这类需求不陌生。拿到一张用户会话表要统计每个时长区间里有多少记录还要把没数据的区间也补成0在SQL里确实能绊倒不少人。LeetCode的Database第1435题“制作会话柱状图”就是专门考这个点的经典题目。这道题的场景是这样的有一张会话表Sessions里面记录每次会话的唯一ID和持续时长duration单位是秒。现在需要把会话时长分成四个档次统计数量[0-5、[5-10、[10-15、15 or more分别对应不足5分钟、5到10分钟、10到15分钟、15分钟及以上。重点在于如果某一档一个会话都没有这一档依然要出现在结果里数量记0。听起来不复杂但你上手一写就会发现直接把duration做CASE WHEN再GROUP BY大概率会丢行。具体为什么会丢用什么写法才能保证四行齐全就是这篇文章要聊的重点。无论你是准备数据分析岗位的笔试题还是工作中经常要做“按区间看分布”的统计这个套路都值得收藏。1. 题目解读与核心考点这道题到底在考什么1.1 先把表结构和分桶规则吃透Sessions表结构非常简单就两个字段。字段名类型含义session_idint会话ID主键唯一标识一次会话durationint会话持续时长单位是秒示例数据长这样session_idduration11202180330044505900612007158720按分钟看120秒是2分钟300秒是5分钟720秒是12分钟。分桶规则是左闭右开的区间[0-5大于等于0分钟小于5分钟[5-10大于等于5分钟小于10分钟[10-15大于等于10分钟小于15分钟15 or more大于等于15分钟注意一个很容易看漏的细节正好5分钟也就是duration等于300秒时属于第二档[5-10而不是第一档10分钟、15分钟同理分别落进第三档、第四档。左闭右开这个规则一旦踩错整个统计表跟着错。期望输出是这样的bintotal[0-53[5-102[10-15115 or more2这四行是对着上面8条示例数据手工统计出来的15秒、120秒、180秒落在[0-5300秒、450秒落在[5-10720秒落在[10-15900秒、1200秒落在15 or more。如果某一档没有会话比如删掉720秒那条[10-15这一档依然要输出数量为0。这就是题里埋的最大陷阱。1.2 考点拆解四个关键点这道题在LeetCode数据库题库里难度不算高但它同时考了四个非常基础又实用的点组合起来就变精致了。第一个是CASE WHEN条件分支。要把duration从秒换算成分钟再映射到桶名称同时必须处理好区间边界。第二个是GROUP BY聚合与计数。根据桶名分组数出每个桶里有多少条会话记录。只做到这一步远远不够这是大多数人的第一反应也是翻车的起点。第三个是固定维度补全。桶列表是题目写死的四个区间不是从数据里自然出现的值。只要数据里某个桶没有记录纯GROUP BY就不会输出那一行。你必须额外造一张“桶全量表”再通过连接把它带出来。这是题目真正想考的东西。第四个是结果排序与NULL处理。输出四行的顺序是指定的不是字典序LEFT JOIN之后没有数据的桶会得到NULL还要安全地变成0。这四个点单独拎出来都不难但合到同一道题里对SQL的综合能力就有要求了。尤其是第三个“固定维度补全”在真实业务里极其常见——报表要求无论有没有数据都保留固定维度没处理过这种需求的人很容易被测试用例打回。2. 解题思路对比为什么直接GROUP BY会翻车2.1 第一直觉CASE WHEN GROUP BY大部分人上手的第一版代码长这样SELECT CASE WHEN duration / 60 5 THEN [0-5 WHEN duration / 60 10 THEN [5-10 WHEN duration / 60 15 THEN [10-15 ELSE 15 or more END AS bin, COUNT(*) AS total FROM Sessions GROUP BY bin;这个写法把duration换算成分钟按区间打标再按标签聚合。逻辑没毛病跑起来也确实能算出数。问题在于它返回的行数完全取决于数据里有几种桶标签。假如[10-15这个区间恰好一个会话都没有结果里就不会出现这一行。还是用上面的8条示例数据做实验把720秒那条删掉数据库里只剩7条记录。上述SQL的输出会变成三行bintotal[0-53[5-10215 or more2期望输出是四行缺的正是[10-15 0这一行。题目要求所有桶为0也要输出直接GROUP BY做不到。2.2 为什么GROUP BY天然不适合这种场景要理解这个问题先说清楚GROUP BY的工作机制。SQL执行SELECT ... FROM Sessions GROUP BY bin时会先按bin把真实存在的行分成一组一组再对每组做聚合。分组键的取值来自数据本身数据里没有的值就不存在对应的组。这和“固定维度报表”的需求正好相反。报表要求先有一张完整的维度表比如四个桶的名称再拿它和事实表碰撞碰撞不到的地方填0。本质是“维度外连接事实”而GROUP BY是“基于事实做切片”。后者天然会丢掉稀疏维度。这个道理放到业务里很好懂你有一张销售表要按12个月统计销售额。如果6月一单都没有GROUP BY月份的结果就只有11行。但报表必须显示12行6月写0。做数据看板的人几乎每个月都会遇到这件事。2.3 三种补全方案对比既然纯GROUP BY不行那就想办法把缺失的桶补上。常见思路有三种。第一种是LEFT JOIN固定桶表。先用UNION ALL构造包含四个桶名的临时表再把它作为左表与分组统计结果做左连接。优点是逻辑清晰、性能好、通用性强缺点是需要多写几行造桶SQL。第二种是UNION ALL手动补零。先按GROUP BY统计找出结果里缺了哪些桶再用SELECT [10-15 AS bin, 0 AS total UNION ALL ...补进去。优点是直观缺点是桶一多就变成体力活而且你得提前知道数据里缺了哪些桶程序化处理很别扭。第三种是递归CTE生成桶。如果桶的规则是连续的数值区间可以用递归CTE从0生成到某个上限再拼接标签。优点是灵活可扩展缺点是语法复杂老版本MySQL还不支持递归。从可维护性、通用性以及面试官的偏好来看第一种都是首选。下面给出完整实现这也是我认为最值得记住的一套模板。3. 核心SQL实现与细节剖析完整可跑的参考代码3.1 参考SQLMySQL 8.0先把测试环境准备好CREATE TABLE Sessions ( session_id INT PRIMARY KEY, duration INT NOT NULL ); INSERT INTO Sessions (session_id, duration) VALUES (1, 120), (2, 180), (3, 300), (4, 450), (5, 900), (6, 1200), (7, 15), (8, 720);然后是这个解题SQLWITH bin_table AS ( SELECT [0-5 AS bin UNION ALL SELECT [5-10 UNION ALL SELECT [10-15 UNION ALL SELECT 15 or more ), sessions_with_bin AS ( SELECT session_id, CASE WHEN duration / 60 5 THEN [0-5 WHEN duration / 60 10 THEN [5-10 WHEN duration / 60 15 THEN [10-15 ELSE 15 or more END AS bin FROM Sessions ) SELECT b.bin, COUNT(s.session_id) AS total FROM bin_table b LEFT JOIN sessions_with_bin s ON b.bin s.bin GROUP BY b.bin ORDER BY FIELD(b.bin, [0-5, [5-10, [10-15, 15 or more);执行结果bintotal[0-53[5-102[10-15115 or more2如果把720秒那条删掉也就是[10-15区间没有会话结果是这样bintotal[0-53[5-102[10-15015 or more2四行齐全空桶正确补0这就是我们要的效果。3.2 逐段拆解每段代码为什么这么写bin_table这段是“维度表”。它用四个UNION ALL把题目要求的桶名一行行列出来。UNION ALL和UNION的区别在于前者不去重这里四个字符串本身就互不相同用哪个都一样但UNION ALL性能略好也是大家更习惯的写法。sessions_with_bin这段是“事实表打标”。CASE WHEN从最小的区间开始判断满足条件就返回桶名否则继续向下走。由于CASE WHEN是短路求值判断顺序绝对不能乱把 5和 10写反了会让5分钟的会话错误落桶。为什么用duration / 60 5而不是duration 300两者数学上完全等价但除以60更直白读者一眼就能看出是在按分钟比较。如果换成duration 300建议加注释说明“300秒5分钟”否则过两个月再回来看代码容易懵。主查询这段是核心。bin_table作为左表LEFT JOIN打标结果左边四行无论如何都会保留右边能匹配上就带出session_id匹配不上就是NULL。COUNT(s.session_id)统计的是非NULL的session_id个数所以空桶得到的结果不是1而是0。这是整条SQL里最精妙也最容易写错的一步——如果手滑写成COUNT(*)空桶会输出1因为LEFT JOIN之后那一行在物理上依然存在只是右表字段全是NULL。初次接触这个写法的人十个有九个会在这个地方踩坑。最后的ORDER BY FIELD指定输出顺序。FIELD的第一个参数是待排序字段后面按优先级列出一串候选值MySQL会把排在前面的值放在结果集前面。它保证了输出永远是[0-5、[5-10、[10-15、15 or more这个顺序。别小看这一步如果不加排序数据库可能返回多种顺序很可能被判错。3.3 其他数据库的写法适配如果你用的是PostgreSQL整体思路完全一样只有两个小差异一是把ORDER BY FIELD(...)改成ORDER BY CASE b.bin WHEN [0-5 THEN 1 WHEN [5-10 THEN 2 WHEN [10-15 THEN 3 ELSE 4 END二是如果需要处理NULL应使用COALESCE而不是IFNULL。但因为这里用的是COUNT(session_id)其实用不到NULL转换函数。其余CTE、UNION ALL、LEFT JOIN的语法完全通用。如果是SQL Server写法也几乎一致把IFNULL换成ISNULL、FIELD换成CASE排序即可。如果数据库不支持CTE例如老版本MySQL 5.6可以把两个CTE整体改写成子查询逻辑不变SELECT bins.bin, COUNT(s.session_id) AS total FROM ( SELECT [0-5 AS bin UNION ALL SELECT [5-10 UNION ALL SELECT [10-15 UNION ALL SELECT 15 or more ) bins LEFT JOIN ( SELECT session_id, CASE WHEN duration / 60 5 THEN [0-5 WHEN duration / 60 10 THEN [5-10 WHEN duration / 60 15 THEN [10-15 ELSE 15 or more END AS bin FROM Sessions ) s ON s.bin bins.bin GROUP BY bins.bin ORDER BY FIELD(bins.bin, [0-5, [5-10, [10-15, 15 or more);这一版的兼容性最好也最容易迁移到不同SQL方言。如果你需要在一个老项目里跑类似逻辑直接用这个就行。4. 实操中的坑与排查技巧这些细节最容易出错4.1 边界值5分钟到底属于哪个桶题目给出的桶是左闭右开区间[0-5包含0分钟但排除5分钟。duration恰好等于300秒时应该落在[5-10而不是[0-5。对应CASE WHEN的写法必须是连续小于WHEN duration / 60 5 THEN [0-5 WHEN duration / 60 10 THEN [5-10 WHEN duration / 60 15 THEN [10-15 ELSE 15 or more如果手抖写成比如第一句变成duration / 60 5那么5分钟会被分进第一个桶后面的判断根本轮不到它。这种错误在数据上看起来只是“这里多个1、那里少个1”但口径一旦错了下游不管怎么用都是错的。排查建议构造一批正好落在300秒、600秒、900秒的测试数据肉眼核对分桶结果。这三个点是三处边界每个都必须单独验证一遍。4.2 空桶的NULL与0陷阱LEFT JOIN造出的空桶行用COUNT(s.session_id)得到0这是正确结果。但如果有人把函数改成COUNT(*)结果立刻变成1。这个细节我在实际排查中见过不止一次最后定位到的问题都是COUNT写错。顺带说一句如果需求改成“没有会话的桶显示NULL而不是0”SQL反而更简单直接把COUNT(s.session_id)去掉改成SUM(CASE WHEN s.session_id IS NULL THEN 0 ELSE 1 END)甚至直接不聚合、用字段把NULL带出来。但本题明确要求total为0所以必须用COUNT来聚合并清掉NULL。另外如果duration字段本身允许NULL需要在CASE WHEN里先处理。因为SQL里NULL参与比较会得到NULL而不是TRUE每条duration为NULL的记录都会落入ELSE被算成15 or more。稳妥的做法先单独过滤CASE WHEN duration IS NULL THEN 未知 ...。这属于扩展思考面试官追问时用得上。4.3 分组与排序的兼容性MySQL对GROUP BY的宽容度很高允许select列表里出现没有参与分组或聚合的字段。但其他主流数据库的默认配置会直接报错比如PostgreSQL。最严谨的写法是分组字段、排序字段、select字段尽量保持一致。我在上面的CTE版本里用GROUP BY b.bin排序用ORDER BY FIELD(b.bin, ...)字段来源一致就不会有歧义。另一个容易忽略的坑是字符串排序。如果忘了ORDER BY数据库很可能按字典序输出[0-5、[10-15、[5-10、15 or more和题目要求的顺序完全不一致。排序这一步绝不是可选项。5. 从题目到业务会话分桶统计模式化5.1 一套模板打天下固定标签表 LEFT JOIN把这道题的解法抽象一下就是一套极其通用的SQL模式。第一步构造维度表列出所有期望输出的分组标签。第二步构造事实表把原始行映射到某个标签上。第三步LEFT JOIN加分组计数。第四步按业务顺序排序。这套模式可以套到大量需求上你有一张用户表要统计各年龄段的用户数量有一张订单表要统计各价格区间的订单笔数有一张接口日志表要统计各耗时区间的请求次数。全都长一个样子差别只在第二步的CASE WHEN条件和第一步的标签枚举。5.2 我实际用过的几个场景有一回做产品埋点分析要统计用户单次会话时长分布判断用户在App里停留多久会出现明显流失。当时的表结构和这道题几乎一模一样只是桶的边界改成了“0-1分钟、1-3分钟、3-5分钟、5-10分钟、10分钟以上”。我直接套模板不到二十分钟就把结果交出去了。在此之前我第一次做同类统计时用的是UNION ALL手动补零只有五六个桶还能忍受后面桶数增加到十几个手动补零真的写到怀疑人生。还有一次统计接口响应耗时的档位分布要输出固定几个档位的请求量作为性能看板的数据源。需求方明确要求某个档位即使0请求也必须保留因为前端柱状图的x轴不能缺柱子。这就属于产品视角的固定维度数据侧必须配合。固定标签表加LEFT JOIN是唯一干净的做法。5.3 如果桶多了怎么扩展实际工作里的分桶规则往往不止四个可能是十个、二十个。UNION ALL一长串看起来特别笨重。如果桶的起点和终点是连续的可以用递归CTE或数字表生成标签。比如生成0到15分钟、每分钟一个桶WITH RECURSIVE minute_range AS ( SELECT 0 AS minute UNION ALL SELECT minute 1 FROM minute_range WHERE minute 14 ) SELECT CONCAT([, minute, -, minute 1, ) AS bin FROM minute_range;再和事实表LEFT JOIN后面的逻辑完全不变。这就是前面提到的第三种方案平时用不上但当你需要快速扩展桶数量时它会省下大量重复的UNION ALL代码。另外如果业务上还要按日期或渠道等第二维度同时分组只需要把第二维度字段加进GROUP BY和SELECT模板一样成立。唯一要注意的是维度表也要跟着扩展成“日期×桶”的笛卡尔积别漏了组合。最后分享一点个人体会。这类问题我在实际项目里遇到的频率远高于LeetCode上很多所谓的难题。关键不在于背下这道题的答案而是理解“固定维度必须显式造出来再LEFT JOIN上去”这个思维模型。用熟之后不管是四档、十二档还是几十档不管是在MySQL还是PostgreSQL里都能在几分钟之内把架子搭好。建议看到这里的读者把示例数据复制到本地环境里跑一遍然后故意删掉一个区间的数据看看输出是不是依然四行齐全。做完这个实验这个知识点才算真正长在身上。