SQL高频考点全拆解：窗口函数、分组聚合与性能优化实战

SQL相关题目。听到这个名字可能有人觉得这是学生时代才需要应付的玩意儿但实际上我工作这么多年不管是招人面试写SQL还是在生产环境排查数据问题发现“SQL相关题目”从来就没离开过一线。它看起来考的是一条SELECT语句背后真正考的却是你对关系模型的理解、对数据形态的判断、以及能不能在脑子里先跑通一遍数据的流向。这篇文章不打算罗列题库我想以一个经常和数据打交道的人的身份聊聊那些“看着会做真上手就出错”的SQL题目到底难在哪顺便把一些高频考点的拆解思路、实操过程和避坑经验完整地捋一遍。如果你正在准备面试或者单纯想把SQL写得心里有底这篇应该能帮到你。1. 题型拆解SQL题目到底在考什么1.1 表面是语法里子是模型很多人在看到“SQL相关题目”这几个字时第一反应是“背语法”。其实大错特错。SQL的语法就那么几十个关键字真正让人翻车的从来不是语法本身而是你拿到一张表之后能不能在脑子里把它“跑起来”。以我面试别人的经验同样一道查“连续登录3天用户”的题新手第一反应是去查“每条记录有没有相邻三行”老手的第一反应是先画出登录日期的序列、再判断怎样把日期之间的间隔折叠掉。差别不在会不会写DATE_SUB而在有没有数据流的概念。所以我建议你把每一道SQL题目都当作一次“手工演算”的机会。拿到表结构之后先别急着写SELECT先在草稿纸上画出来这张表有几行样例数据我要的最终结果长什么样中间需不需要经过一次聚合、一次关联、还是两次去重这三步想明白了语法只是顺手的事。1.2 六大高频场景速查刷过的题和面试问过的题加在一起我发现绝大多数SQL问题可以归纳到六个场景里。整理成表格放在这方便你对照自查场景典型问题核心技巧常见误区分组统计各分类的数量、金额、均值GROUP BY 聚合函数忘掉非聚合列的归属问题去重计数统计某维度下的唯一用户数COUNT(DISTINCT)忽略NULL带来的偏差排序取值每个分组的前N条窗口函数 ROW_NUMBER / RANK不加PARTITION导致全局排序区间判断连续范围、跨天、最新状态日期偏移、自关联边界条件没想清楚行转列把长表变宽表CASE WHEN GROUP BY列数写死不能动态扩展关联匹配两表匹配、补全、排除JOIN ON条件关联键选错导致数据膨胀每个场景的解法都有固定套路但套路背后的“为什么”才是关键。后端我会挑几个翻车率最高的知识点展开讲。2. 几个让很多人翻车的核心知识点2.1 窗口函数一排开不出花但排完再看就很香窗口函数是近些年SQL题目里的常客。它解决的最大痛点是“既要看到明细行又要看到汇总值”。普通GROUP BY会把多行压缩成一行窗口函数不会。我给你一个生活化类比GROUP BY像是把一堆混在一起的水果按品相分了篮每篮只留一张品相标签窗口函数则像是你在按身高排队的队伍里一边排队一边能看到整个队伍的平均身高。你想保持每个人的位置又想算出整体指标窗口函数几乎是唯一解。实操时我最常用的组合是ROW_NUMBER()配合PARTITION BY。比如一个订单表要查“每个用户最近一单”格式长这样SELECT 用户ID, 订单号, 下单时间 FROM ( SELECT 用户ID, 订单号, 下单时间, ROW_NUMBER() OVER (PARTITION BY 用户ID ORDER BY 下单时间 DESC) AS rn FROM 订单表 ) t WHERE rn 1;这里有个细节特别值得说ROW_NUMBER、RANK和DENSE_RANK处理并列的方式完全不同。ROW_NUMBER永远唯一编号即使排序值相同也会硬给个先后RANK会给并列名次留空DENSE_RANK则把并列后紧密排序。面试题里如果出现“并列名次怎么办”十有八九是在考察你有没有分清楚这三个的区别。再提醒一个容易忽略的点窗口函数中的ORDER BY方向直接影响编号顺序。查“最近一笔”就必须DESC“最早一笔”就ASC。方向反了结果完全不对而且这种错误在样例数据少时很难被看出来。2.2 分组聚合与“先看全表再分组”的思维陷阱GROUP BY是SQL里最基础的功能但它有一个隐蔽的思维陷阱绝大多数人会下意识地“先分组再算聚合”真在数据库里执行时优化器反而可能会“先全表扫描再临时分组”。这个差异在日常小数据量下无所谓但一旦表到了百万行级别写法不同性能天差地别。很多SQL题目表面考逻辑其实也考你有没有考虑到数据规模。比如-- 写法A先过滤再分组 SELECT 品类, COUNT(*) FROM 流水表 WHERE 日期 2024-01-01 GROUP BY 品类; -- 写法B先分组再过滤 SELECT 品类, COUNT(*) FROM 流水表 GROUP BY 品类 HAVING 品类 ! 测试数据;写法A通常能利用日期索引减少扫描范围写法B几乎要把整个表都分组完再扔垃圾。数据量一大A可能秒回B会卡到超时。再往根上说GROUP BY之后SELECT里到底能出现哪些列这个规则很多初学者模糊。严格来说SELECT后面出现的列必须是分组列或聚合函数的参数。否则在很多数据库里会直接报错在MySQL宽松模式下则可能返回一个随机值这是特别容易埋雷的地方。2.3 自关联和NULL的暧昧地带自关联就是把一张表和自己JOIN。听着好像没什么了不起但实际处理“层级关系”“相邻记录”“状态流转”这类问题时自关联几乎是必须掌握的套路。举个例子查询每个员工和他的直接主管是否同一个部门。你需要把员工表和自己关联两次一次取员工信息一次取主管信息SELECT a.员工姓名, a.部门ID, b.部门ID AS 主管部门ID FROM 员工表 a LEFT JOIN 员工表 b ON a.主管ID b.员工ID WHERE a.部门ID ! b.部门ID;这个查询本身不难难在NULL的处理上。如果某个员工没有主管b.部门ID会是NULL那么a.部门ID ! b.部门ID的结果既不是TRUE也不是FALSE而是UNKNOWNWHERE条件会把这种“未知”直接过滤掉。这时候你以为结果是“主管在不同部门的人”实际却偷偷漏掉了“没有主管的人”差别很微妙但方向完全不同。排查这类问题有两个习惯值得养成。第一用LEFT JOIN的地方尽量在ON后面把过滤条件写全而不是全部丢给WHERE第二看到结果行数比预期少时马上去想“是不是有NULL字段被判断条件吞掉了”。NULL不是空字符串它代表未知连NULL NULL都返回UNKNOWN更别说NULL ! 某个具体值了。这个点我几乎每次带新人都会强调一遍属于“题目看着不深踩坑踩到怀疑人生”的典型。3. 实操案例拆解老题里挖新东西3.1 案例一连续登录3天用户这是面试中出现频率最高的SQL题没有之一。先给表结构用户登录表login_log字段有user_id、login_date。很多人拿到手就写找每条记录后面的两条记录看日期是否是当前日期加1和加2。这个思路方向对但实现起来会陷入自关联的泥潭一旦某个用户一天内有多次登录记录结果就会膨胀。我的推荐解法是用“等差数列”思想。核心逻辑是如果一名用户在一组连续登录日里登录那么每个登录日期减去它在组内的序号会得到一个相同的日期基准。具体写法SELECT DISTINCT user_id FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY) AS group_date FROM login_log -- 先按天去重避免一天多条记录干扰 WHERE 日期范围条件 ) t GROUP BY user_id, group_date HAVING COUNT(*) 3;踩坑点有两个。第一个是必须先对(user_id, login_date)去重。如果用户一天登录两次不去重的话同样三天的记录数变成6条连续性的判断就失真了。第二个是ROW_NUMBER的排序必须稳定ORDER BY login_date必不可少不然序号乱掉基准日期也会跟着乱掉。这个方案的优雅之处在于它把“连续”问题转化为“分组计数”问题一旦转化成功思路就非常清晰。扩展一下连续7天签到、连续15天活跃都可以套同一套逻辑只需要把最后的HAVING COUNT(*)条件改一下就能通吃。3.2 案例二分组Top N问题“每门课成绩最高的前3名”“每个部门薪资前10的员工”这类分组Top N问题也是必考题。解法套路非常固定窗口函数编号 外层过滤。以课程成绩为例SELECT 学生姓名, 课程ID, 成绩 FROM ( SELECT 学生姓名, 课程ID, 成绩, ROW_NUMBER() OVER (PARTITION BY 课程ID ORDER BY 成绩 DESC) AS num FROM 成绩表 ) ranked WHERE num 3;到这里能写出来的人很多但紧接着面试官通常会追问一个升级问题如果并列第3名是取3个还是全部取如果要全部取并列的人就得把ROW_NUMBER换成RANK或者DENSE_RANK。区别在于RANK在并列后会跳过名次比如两个并列第一下一个名次是第三DENSE_RANK则不会跳下一个是第二。实际工作中我更常用RANK而不是ROW_NUMBER因为业务上很多时候要求“并列的必须同时保留”硬用ROW_NUMBER会随机踢掉某个人结果不够直观。但要注意的是RANK配合HAVING或WHERE条件时取的并不是前3人而是“最多排到第3名的所有人”人数可能超过3个这个预期业务人员是能够接受的。还有一个小技巧如果表的规模很大成绩索引做得不好窗口函数在ORDER BY字段上没有索引会非常吃力。遇到这种情况可以先建一个覆盖索引让排序走索引实测能快一个数量级。3.3 案例三行转列与列转行行转列长表变宽表最常见的场景是原始表里一个维度占多行希望转成每个维度一行、多个属性列展开。比如按月统计销售额SELECT 品类, SUM(CASE WHEN 月份 2024-01 THEN 金额 ELSE 0 END) AS 一月, SUM(CASE WHEN 月份 2024-02 THEN 金额 ELSE 0 END) AS 二月, SUM(CASE WHEN 月份 2024-03 THEN 金额 ELSE 0 END) AS 三月 FROM 销售表 GROUP BY 品类;这个写法原理不复杂CASE WHEN把匹配月份的金额挑出来再用SUM完成累加不匹配的行自动补0。但它有个明显局限——列数是写死的。如果月份动态变多这种静态SQL就撑不住了。真正要动态的行转列一般得靠拼SQL的方式实现-- 先查出所有需要展示的月份 SELECT GROUP_CONCAT(DISTINCT CONCAT(SUM(CASE WHEN 月份 , 月份, THEN 金额 ELSE 0 END) AS , 月份) ) INTO sql_cols FROM 销售表; -- 拼接完整查询 SET sql CONCAT(SELECT 品类, , sql_cols, FROM 销售表 GROUP BY 品类;); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;这段代码在MySQL里可以直接跑通核心是利用GROUP_CONCAT动态生成列表达式再用PREPARE执行。这个技巧第一次见的人会觉得有点绕但它解决的是“列数不确定时能不能通用”的真实问题。数据仓库里做报表的同事几乎每个月都要用类似方案处理动态维度。至于列转行思路正好反过来。把宽表上多个列合并成一个“属性名 属性值”的长表最常见的是用UNION ALLSELECT 学生ID, 语文 AS 科目, 语文成绩 AS 分数 FROM 成绩宽表 UNION ALL SELECT 学生ID, 数学, 数学成绩 FROM 成绩宽表 UNION ALL SELECT 学生ID, 英语, 英语成绩 FROM 成绩宽表;这类转换本身不难但它考察的是对“宽表长表各自适用场景”的理解。长表方便过滤和聚合宽表方便看整体和做横向对比没有谁绝对更好只有这个场景合不合适。4. 常见错误与排查技巧实录4.1 看着对但结果错GROUP BY的隐形雷SQL题目的一个特点是能跑出结果不一定代表结果对。我在实际排查里见过最多的错就是GROUP BY之后SELECT了非聚合非分组的列。在MySQL里默认居然能跑通返回的却是一个没有规则的值。比如SELECT user_id, order_id, MAX(amount) FROM orders GROUP BY user_id;如果用户有多个订单order_id到底取哪个MySQL不报错但返回结果毫无逻辑。很多线上统计出错根子就在这。正确做法是分组后的明细字段要么也用聚合函数包一层要么就先进子查询把每条用户最新的order_id找出来再关联。排查这个问题的习惯也很好养成看到带GROUP BY的查询第一遍检查SELECT列表里的每一个字段问自己“这个字段在分组后还有意义吗”没有就别放进来。4.2 性能相关的坑SQL题目做到后期基本都会涉及“结果对但跑得太慢”的优化问题。我总结三个最常见的原因。第一个是子查询当表用。很多人图方便把一大段查询包在括号里再关联外层查询结果优化器很难把它下推成更高效的执行计划。能用JOIN解决的问题尽量不要用子查询。第二个是索引失效。最常见的失效场景是在WHERE条件里对字段做函数运算比如WHERE DATE(login_date) 2024-01-01。这个写法会让login_date上的索引失效本质上等于全表扫描。正确写法是WHERE login_date 2024-01-01 00:00:00 AND login_date 2024-01-02 00:00:00。第三个是排序临时表。窗口函数、DISTINCT这类操作经常会生成临时表如果排序字段没索引规模一大就慢。排查时可以看执行计划里有没有Using filesort、Using temporary这样的字样一旦出现就要考虑调整索引或改写语句结构。4.3 排查方法从“人工摆数据”到“分段验证”我调试SQL题目时基本上遵循一套自己的流程这套流程救过我很多次。第一步把样例数据缩小到几行手工把预期结果算出来。第二步在每条SELECT前面加“试运行”把中间态的表查出来比如子查询里先不加WHERE条件看中间结果是否符合想象。第三步逐步加上JOIN和分组每加一步就核对一次行数变化。第四步如果预期是10行结果查出来是12行优先怀疑JOIN造成的数据膨胀如果只有8行优先怀疑WHERE把NULL过滤了。这套方法听起来朴素但特别管用。很多人写SQL像写作文一口气写完再跑结果报错了也不知道从哪查。写成“搭积木”的验证方式之后定位问题通常用不了五分钟。5. 我的一些体会绕开具体题目我想说说这些“SQL相关题目”背后我更在意的东西。第一SQL不是背诵语言。真正理解它的人拿到一张陌生的表能迅速判断出数据长什么样、要得到什么结果、中间需要几种变换。这种判断力只能靠不断练习光看不写、光写不验都不行。第二你可以多关注不同数据库之间的差异。MySQL、PostgreSQL、Oracle窗口函数和分页写法各不相同。同一个问题能用标准SQL写出来最好但也要知道手上的数据库有哪些“方言特性”这些往往就是生产环境里真正能派上用场的武器。第三很多看起来很炫的技巧比如动态行转列、递归查询、LATERAL JOIN实际工作中不一定天天用但掌握它们会让你在遇到“常规路线走不通”时多一两个备选方案。我在实际项目中就遇到过报表列数动态变化、数据量爆炸、简单SQL扛不住的时刻当时能解决问题靠的就是这种“边角料”知识的储备。SQL相关的题目就像一面镜子照出的不只是你会不会写查询更是你对数据底层逻辑掌握得扎不扎实。希望你在这条路上越写越顺。