★ FEATURED ARTICLE 拿到“系统设计与数据库系统 作业二 Advanced SQL LAB”这种题目第一眼可能觉得就是几个SQL练习真正动手才发现它把数据库课里最容易被忽略、却最贴近真实业务分析的硬骨头全塞进来了。我当初做完这份作业只有一个感受基础SQL和高级SQL之间隔着的不是语法量而是思维方式的切换。这篇就把我拆题、写题、调优、踩坑的完整过程整理出来给正在写类似作业、或者想系统补一补SQL分析能力的人做参考。先说清楚这个LAB是什么。它不是让你背命令也不是考那种SELECT * FROM 表的入门操作而是围绕复杂查询来出题——窗口函数、公共表表达式、嵌套子查询、条件聚合、日期分组、排名和去重这些才是考察重点。换句话说它模拟的是真实业务中给我按月销售额同期对比找出连续三个月下单的客户统计每个城市消费前3的会员这类分析需求。你平时会基础增删改查不够用这类题才是分水岭。适合谁来读正在赶数据库作业的学生、刚入职写报表但SQL不熟的新人、以及想用一份系统清单巩固SQL分析能力的人这篇应该都能帮上忙。1. 作业题面的真实意图不只是写好SQL而是建立分析型查询思维1.1 从课程作业结构反推考核点数据库系统设计这门课前面一半在讲关系代数、ER模型、范式、事务这些偏理论的东西一到作业里就变成纸上谈兵到了Advanced SQL LAB终于落地到写查询。但作业不会直接告诉你我要考你窗口函数它通常给你几张业务表让你完成几个分析任务。我仔细复盘后发现所有题目可以用一句话概括能不能把一个复杂的业务问题拆解成清晰的SQL逻辑步骤并保证结果正确、可解释、还能抗住数据变化。这就涉及三个层次的能力。第一层是写出来用SELECT、JOIN、GROUP BY能出结果第二层是写得对处理了NULL、重复值、过滤条件、窗口边界第三层是写得稳换成更大的数据量、更复杂的过滤条件逻辑依然成立。作业里的题目看起来是在考语法实际上是在逼你从第二层往第三层走。1.2 高级SQL在真实业务里的位置为什么课程要把这些内容单独拎出来做LAB因为真实的数据分析、报表、后台统计大量依赖这些高级特性。举几个身边常见的场景电商后台要每个品类销量前10的商品这需要窗口函数报表系统要本月累计到今天的销售额这需要滑动窗口或条件聚合会员运营要过去30天内连续活跃7天以上的用户这需要日期处理和子查询的配合。这些需求如果全用基础SQL硬写往往能写出来但语句极其臃肿、维护性差、性能也不行。高级SQL的价值就是给这些问题提供标准化的、声明式的表达方式。1.3 把“会SQL”和“用SQL思考”区分开我见过很多同学单看每条语法都懂一遇到综合题就卡住。原因在于他们习惯从语法出发思考而不是从结果集出发思考。高级SQL LAB真正锻炼的是倒过来的思路先问自己最终要输出几列哪些是明细、哪些是聚合行是按什么粒度展开的再反推需要哪些表、哪些JOIN、哪些分组条件、哪个窗口范围。这个思维方式一旦建立后面写任何分析SQL都会顺手很多。整个作业本质上就是一场思维训练。2. 核心SQL特性拆解把每个高级语法讲透2.1 窗口函数在保留明细的同时做分组计算窗口函数是这份作业里最核心的内容没有之一。它的作用通俗讲就是不合并行的情况下在每一行旁边贴一个基于某个分组范围计算出来的结果。对比一下就清楚了。对比项GROUP BY窗口函数输出行数每个分组只有一行每一行都保留能访问明细字段不能直接访问可以访问能排序后计算不行可以按PARTITION和ORDER BY典型用途汇总报表排名、移动平均、同比环比窗口函数的骨架是函数名(参数) OVER (PARTITION BY 分组字段 ORDER BY 排序字段 [ROWS 范围])。PARTITION BY相当于把窗口划分成小组ORDER BY决定组内的计算顺序ROWS/RANGE则控制只看当前行附近哪些行。作业里最常见的用法是排名类的ROW_NUMBER()、RANK()、DENSE_RANK()以及滚动计算类的SUM()、AVG()配OVER。我给你一个最典型的例子SELECT customer_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn FROM orders;这段SQL对每个客户分别按金额从大到小编号编号1就是该客户金额最大的订单。注意它并没有合并客户的多个订单而是把它们全部保留下来多了一列rank。这种“保留上下文”的能力正是GROUP BY做不到的。2.2 公共表表达式给复杂查询搭脚手架公共表表达式就是WITH子句作用是给一段子查询起个名字后面可以反复引用。作用有点像写代码时先定义中间变量再拿中间变量去拼主逻辑。高级SQL里很多题目不是一步能算出来的这时候硬塞在一个SELECT里嵌套五六层子查询写的人晕看的人更晕。用WITH拆开每一步干一件事整体逻辑一目了然。举例来说要算有购买记录的客户里消费总额超过5000的客户在当月的订单数如果不用CTE你得把客户汇总、订单明细、月份过滤全部揉在一起用CTE就可以写成WITH customer_spending AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), active_customers AS ( SELECT customer_id FROM customer_spending WHERE total 5000 ) SELECT * FROM orders o WHERE o.customer_id IN (SELECT customer_id FROM active_customers) AND EXTRACT(MONTH FROM o.order_date) 6;看起来步骤多了但每一步都是独立的逻辑单元。在实际工程里几乎所有复杂的分析SQL都会先用CTE把清洗步骤汇总步骤业务口径步骤分开Debug的时候也能单独跑每一段。这个习惯从作业阶段就该养起来。2.3 子查询的三个安放位置SELECT、FROM、WHERE子查询本身不是新东西但很多作业题的坑恰恰藏在这个子查询应该放在哪个子句里。我总结一个判断规则如果子查询返回一个标量值可以放在SELECT处做计算字段如果返回一个多行多列的结果集就放FROM处当临时表如果返回一个集合或判断存在性就放WHERE处配合IN、EXISTS、ANY、ALL使用。子查询位置返回类型典型写法注意事项SELECT子句单个标量每个客户的最近订单金额必须保证每个返回行只对应一行结果FROM子句多行多列按月的汇总表再接明细必须写别名否则无法引用WHERE子句集合或布尔消费过3次以上的客户IN注意NULLEXISTS更稳我实际在作业里见得最多的错误是初学者喜欢把一个标量子查询放到FROM里然后忘记给结果加别名直接报错。另外WHERE里IN子查询如果结果集中有NULL判断结果可能不符合直觉。经验之谈判断存在场景优先用EXISTS它不会踩NULL的坑也更容易走索引。2.4 条件逻辑与行转列CASE WHEN是万金油CASE WHEN在高级SQL里不算难但它几乎是所有不等于简单汇总的题目的基础零件。它的作用相当于SQL里的if-else可以做字段级别的逻辑判断、条件计数、分组重定义。比如统计订单状态构成SELECT customer_id, SUM(CASE WHEN status completed THEN amount ELSE 0 END) AS completed_amount, SUM(CASE WHEN status cancelled THEN amount ELSE 0 END) AS cancelled_amount FROM orders GROUP BY customer_id;这不是什么炫技但掌握了CASE WHEN很多语义复杂的业务指标都能翻译成SQL。作业里遇到有效订单金额剔除了某类状态的统计区间划分A/B/C类客户这类需求基本都是CASE WHEN的变体。还有一类比较难的行转列题目——把某个枚举值变成多列也是CASE WHEN配合GROUP BY实现的。2.5 日期处理分析型SQL里绕不开的硬骨头日期是作业和质量检查里最容易出问题的部分因为不同数据库的日期函数差异太大。以PostgreSQL为例常用的是DATE_TRUNC()把时间截断到月、周、年EXTRACT()提取年月日。MySQL里则常用DATE_FORMAT()。写的时候务必先确认作业指定的是哪个数据库。按月分组的标准写法在PostgreSQL里是SELECT DATE_TRUNC(month, order_date) AS order_month, SUM(amount) AS total_amount FROM orders GROUP BY DATE_TRUNC(month, order_date) ORDER BY order_month;这种写法有几个隐蔽的坑。第一DATE_TRUNC返回的是时间戳直接输出会出现2024-01-01 00:00:00如果只想要年月字符串记得格式化第二GROUP BY后面写函数表达式不要在SELECT里用了别名但GROUP BY里写别名不同数据库行为不一样容易报错第三涉及环比、同比这种跟上一期比的运算窗口函数LAG()比自连接优雅得多这一点后面实操部分会展开。3. 整份作业怎么打四道模拟题完整走一遍3.1 先造一张演示表和测试数据做题之前先准备数据。我不建议直接等作业环境自己在本地建个库、插点数据把所有解法跑通再迁移是最稳的路径。为了演示我用一张简化的订单表CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL, amount NUMERIC(10, 2) NOT NULL, status VARCHAR(16) NOT NULL ); INSERT INTO orders VALUES (1, 1, DATE 2024-01-05, 120.00, completed), (2, 1, DATE 2024-01-18, 200.00, cancelled), (3, 2, DATE 2024-01-12, 80.00, completed), (4, 1, DATE 2024-02-02, 150.00, completed), (5, 2, DATE 2024-02-22, 90.00, completed), (6, 3, DATE 2024-02-10, 60.00, completed), (7, 1, DATE 2024-03-01, 300.00, completed), (8, 2, DATE 2024-05-28, 110.00, completed), (9, 3, DATE 2024-03-15, 75.00, completed), (10, 3, DATE 2024-04-05, 95.00, completed);这张表结构简单但够用有客户、有日期、有金额、有状态足以把窗口函数、CTE、子查询、条件逻辑全部串起来。需要说明的是实际作业里表名可能叫Sales、Orders、Transactions字段也可能有出入下面的思路照搬即可。3.2 题目一按月统计有效销售额并计算环比增长率这个需求几乎在每个分析类作业里都会出现。先说口径题目里如果写了有效订单或者排除取消状态那在汇总之前必须先过滤status。我自己的习惯是先把数据做一层清洗、再做聚合而不是在GROUP BY之后再过滤逻辑更清晰。WITH valid_orders AS ( SELECT * FROM orders WHERE status completed ), monthly AS ( SELECT DATE_TRUNC(month, order_date) AS month, SUM(amount) AS total_amount FROM valid_orders GROUP BY DATE_TRUNC(month, order_date) ) SELECT month, total_amount, LAG(total_amount) OVER (ORDER BY month) AS prev_amount, CASE WHEN LAG(total_amount) OVER (ORDER BY month) IS NULL THEN NULL ELSE ROUND( (total_amount - LAG(total_amount) OVER (ORDER BY month)) / LAG(total_amount) OVER (ORDER BY month) * 100, 2 ) END AS growth_pct FROM monthly ORDER BY month;这段代码里我第一层用CTE过滤出有效订单第二层做月度汇总第三层在汇总结果上算环比。为什么不在第一层直接汇总因为第一层CTE可以复用给后面的题目而且语义清晰。环比的本质是本月跟上月比窗口函数LAG(字段, 1)能直接取到上一行的值省掉了自连接那一大段。如果数据库是MySQL 8.0以下不支持窗口函数改成自连接也能算但代码长度会翻倍而且容易漏月份。所以能在作业里用窗口函数就尽量用这也是考察点本身。3.3 题目二找出每个客户金额最高的订单这道题的经典错误是只想到GROUP BY然后MAX(amount)结果虽然得到了最大金额但拿不到这个金额对应的订单ID、下单日期。这就是我前面说的GROUP BY会丢明细的问题。正确做法是用窗口函数生成排名再在外面过滤排名为1的行WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY amount DESC, order_date ASC ) AS rn FROM orders ) SELECT * FROM ranked WHERE rn 1;为什么用ROW_NUMBER而不是RANK或DENSE_RANK这是作业里经常考的微妙区别。ROW_NUMBER会给每一行一个唯一序号即使金额相同也按order_date分先后RANK和DENSE_RANK则会让并列的订单拿到相同的排名。在取每个客户某维度最大的那一条时我们需要的是一个唯一标识所以ROW_NUMBER最合适。如果题目的语义是找出所有达到最大金额的订单那才应该用RANK。做题时一定要先弄清楚题意再选函数。另外我踩过一个坑排序字段有并列时如果不指定次级排序同一个客户的排名可能不稳定同样一条数据跑两次得到的明细会不一样。所以我加了order_date ASC作为次级排序这在实际开发里也很关键。3.4 题目三找出连续三个月都有下单的客户这是一道典型的连续性问题在会员分析、登录行为分析里都非常常见。思路可以拆成三步先把每个客户的下单月份去重再判断相邻两个月是否连续最后筛选出三个月连续存在的客户。WITH monthly AS ( SELECT DISTINCT customer_id, DATE_TRUNC(month, order_date) AS month FROM orders ), with_prev AS ( SELECT customer_id, month, LAG(month) OVER (PARTITION BY customer_id ORDER BY month) AS prev_1, LAG(month, 2) OVER (PARTITION BY customer_id ORDER BY month) AS prev_2 FROM monthly ) SELECT DISTINCT customer_id FROM with_prev WHERE prev_2 IS NOT NULL AND prev_1 month - INTERVAL 1 month AND prev_2 prev_1 - INTERVAL 1 month ORDER BY customer_id;重点在第一层CTE的DISTINCT一个客户在同一个自然月里可能下多单如果不先去重后面判断连续月份会产生重复干扰。第二层用LAG取当前行之前的一个月和前两个月然后比较日期差值是否为一个月。如果数据库中月份差处理复杂也可以先提取成年*12月的整数形式再比较整数差是否等于1这样能绕开边界问题WITH monthly AS ( SELECT DISTINCT customer_id, EXTRACT(YEAR FROM order_date) * 12 EXTRACT(MONTH FROM order_date) AS month_num FROM orders ), with_prev AS ( SELECT customer_id, month_num, LAG(month_num) OVER (PARTITION BY customer_id ORDER BY month_num) AS prev_1, LAG(month_num, 2) OVER (PARTITION BY customer_id ORDER BY month_num) AS prev_2 FROM monthly ) SELECT customer_id FROM with_prev WHERE prev_2 IS NOT NULL AND prev_1 month_num - 1 AND prev_2 month_num - 2;这个写法思路更简单而且不受跨年问题影响。在我的演示数据里客户1连续1、2、3月满足条件客户3连续2、3、4月满足客户2因为4月到5月之间断了两个月不满足所以结果就非常明确。3.5 题目四金额第二高的订单以及每个客户的第二高订单第二高这种需求很容易掉进一个陷阱直接ORDER BY amount DESC LIMIT 1 OFFSET 1。这个写法的问题是当有多条订单金额相同并列第一时OFFSET 1取到的是金额最高的一行而不是真正的第二名。作业里如果只要求金额第二高的订单严谨的做法是先对金额去重排名。如果只看全表金额第二名SELECT * FROM orders WHERE amount ( SELECT DISTINCT amount FROM orders ORDER BY amount DESC LIMIT 1 OFFSET 1 );这里用子查询先取出第二大的金额值再用它匹配所有订单。这样如果有并列的也能被完整选出。如果说的是每个客户的第二高订单那就再把窗口函数拿进来WITH ranked AS ( SELECT *, DENSE_RANK() OVER ( PARTITION BY customer_id ORDER BY amount DESC ) AS dr FROM orders ) SELECT * FROM ranked WHERE dr 2;用DENSE_RANK而不是RANK是因为DENSE_RANK在并列之后不会跳号第二组名次一定是2如果只用RANK遇到第一名并列时第二名会变成3结果就漏了。这种细微差异作业里不考则已一考就是整道题的命门。4. 常见问题与排查技巧这些坑我基本都踩过4.1 窗口函数不能在WHERE里用这是窗口函数使用频率最高的报错点。很多人辛辛苦苦写出来SELECT customer_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) rn FROM orders WHERE rn 1;马上就会收到类似window function are not allowed in WHERE的错误。原因在于窗口函数是在WHERE过滤之后、SELECT计算阶段才执行的WHERE压根看不到这列。解决办法就是外面再套一层先算排名再在外面过滤。这也解释了为什么高级SQL里CTE或者说嵌套子查询几乎是必需品因为它能严格按执行阶段分层。4.2 GROUP BY导致的重复行与“隐藏列”问题做月度汇总时如果SELECT里同时出现聚合列和非聚合列而GROUP BY没有包含所有非聚合列数据库的行为有两种要么报错要么在宽松模式下随机取值。我建议把严格模式打开让数据库报错倒逼自己修正逻辑。另外多个表JOIN后再GROUP BY很容易因为一对多关系产生重复计数。比如订单表和明细表JOIN订单金额被明细行撑大了好几倍这时候SUM出来的结果偏大。排查方法也简单先不聚合直接看JOIN后的明细行数确认没有重复展开再聚合。4.3 日期函数和NULL值带来的隐形错误日期处理的坑一是边界二是时区。作业数据一般是DATE类型如果用的是TIMESTAMP WITH TIME ZONE做DATE_TRUNC之前得先明确统计口径基于哪个时区。NULL值的问题主要发生在排序和比较上ORDER BY时NULL在升序里通常排最前、降序里排最后不同数据库默认不一样最好显式写NULLS LAST。做环比时如果上月没有数据LAG返回NULL直接套公式会出现结果为NULL而不是0需要用COALESCE/IFNULL兜底。这些细节单独看都不大但组合起来就是报表对不对的关键。4.4 性能自查子查询和JOIN的选择题作业数据量小性能问题不明显但养成性能意识很重要。我的经验是这样能用JOIN表达的关系就别用WHERE IN大子查询判断存在性优先EXISTS子查询里尽量只取需要的字段不要SELECT *大表JOIN之前先在子查询里把数据缩到最小粒度。再看索引WHERE条件和JOIN条件里的字段如果没建索引数据量一大就会全表扫描。很多同学觉得性能问题离作业很远但其实数据库课程之所以安排LAB就是让这些小习惯在真实工程到来之前先成型。下面把踩过的坑整理成一个速查表方便写作业时对照。典型症状可能原因解决方向窗口函数放WHERE报错执行顺序问题用CTE先算外层再过滤GROUP BY结果有重复JOIN膨胀或缺少DISTINCT先跑明细核对行数聚合结果偏大一对多JOIN导致重复计数先聚合再JOIN或使用子查询环比结果是全NULL缺少LAG窗口数据或未兜底用COALESCE处理NULL第二名查询取错并列排名未处理用DENSE_RANK或先取金额日期按月分组报错数据库函数语法差异确认数据库用DATE_FORMAT或DATE_TRUNC写在最后的一个实操习惯整个LAB做下来我最大的收获不是多会了几个函数而是养成了一个先画结果集再写SQL的习惯。拿到任何分析题我先在纸上画一个输出表格列是什么、行是什么、每一行代表什么粒度、需要哪些过滤条件然后再去想用什么语法能拼出这张表。大多数时候写着写着思路就通了卡壳往往是因为结果集没想清楚。另外一个实用技巧是写完SQL之后主动做三个自问这个查询可能产生重复行吗中间步骤如果数据为空结果会怎样如果我把过滤条件从completed改成cancelled逻辑还成立吗这三个问题能揪出一大半的隐性错误。这份作业真正留给我的是这套方法论而不是那几个题的答案后来写任何分析报表我都还在用。
阅读完成 · 觉得有帮助？
