力扣SQL高频50题进阶：窗口函数与查询优化实战-北京尧图网络

1. 力扣高频 SQL 50 题阶段总结二概述作为一名长期奋战在数据领域的老兵我深知SQL技能对程序员的重要性。力扣LeetCode作为技术面试的练兵场其SQL题库的质量和实用性在业内是有口皆碑的。这次我将继续分享高频SQL 50题的第二部分实战总结重点聚焦那些让无数面试者又爱又恨的中高级查询场景。与基础篇不同这部分题目更注重考察对SQL特性的深入理解和灵活运用能力。窗口函数、复杂子查询、多表连接优化等核心知识点频繁出现很多题目看似简单实则暗藏玄机。我在实际刷题过程中发现即使是工作多年的开发者也常在这些题目上翻车。2. 核心题型与解题思路拆解2.1 窗口函数的进阶应用窗口函数是SQL高级查询的瑞士军刀在力扣高频题中占比超过30%。与基础篇介绍的ROW_NUMBER()不同这部分更侧重LEAD/LAG的时间序列分析典型如第178题分数排名需要计算当前行与前后行的差值。关键点在于理解FRAME子句的默认行为LAG(salary, 1, 0) OVER(PARTITION BY department ORDER BY hire_date) -- 第三个参数0表示默认值DENSE_RANK与RANK的微妙差异第185题部门工资前三高的员工完美展示了这个区别。当存在并列时RANK会产生间隔而DENSE_RANK不会/* 错误示范 */ SELECT * FROM ( SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employee ) t WHERE rnk 3 -- 可能漏掉实际需要的数据 /* 正确方案 */ SELECT * FROM ( SELECT *, DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS drnk FROM employee ) t WHERE drnk 3提示窗口函数性能陷阱 - 当OVER子句中的PARTITION BY列基数很高时可能导致内存溢出。我曾在一个500万行的表上使用PARTITION BY user_id直接导致OOM。解决方案是先用WHERE缩小数据范围。2.2 复杂子查询的优化策略力扣第262题行程和用户是典型的子查询难题要求计算取消率。常见误区包括在WHERE中使用相关子查询导致Nested Loop性能灾难/* 低效写法 */ SELECT request_at, COUNT(IF(status LIKE cancelled%, 1, NULL)) / COUNT(*) FROM trips WHERE client_id IN (SELECT users_id FROM users WHERE banned No) AND driver_id IN (SELECT users_id FROM users WHERE banned No) GROUP BY request_at /* 优化方案 */ WITH valid_users AS ( SELECT users_id FROM users WHERE banned No ) SELECT request_at, ROUND(SUM(status LIKE cancelled%) / COUNT(*), 2) AS cancellation_rate FROM trips t JOIN valid_users v1 ON t.client_id v1.users_id JOIN valid_users v2 ON t.driver_id v2.users_id GROUP BY request_at忽略NULL值处理当除数为0时MySQL返回NULL而非错误。安全写法应加入IF(COUNT(*) 0, SUM(...)/COUNT(*), 0)2.3 递归CTE解决层次查询第1270题所有人的会议展示了递归CTE的强大之处。关键步骤基础查询确定起始点递归部分通过JOIN扩展关系终止条件避免循环引用WITH RECURSIVE meeting_path AS ( -- 基础查询找出所有直接向CEO汇报的人 SELECT employee_id FROM Employees WHERE manager_id 1 AND employee_id ! 1 UNION ALL -- 递归查询找出下属的下属 SELECT e.employee_id FROM Employees e JOIN meeting_path mp ON e.manager_id mp.employee_id ) SELECT * FROM meeting_path;踩坑记录MySQL 8.0之前不支持递归CTE面试时若遇到旧版本环境需要用存储过程模拟。我曾用临时表循环实现代码量暴涨且性能下降明显。3. 高频题型实战解析3.1 第184题部门最高工资题干找出每个部门工资最高的员工。典型错误-- 错误方案1GROUP BY后直接SELECT非聚合列 SELECT departmentId, name, MAX(salary) FROM Employee GROUP BY departmentId; -- MySQL可能不报错但结果随机 -- 错误方案2先GROUP再JOIN可能重复 WITH max_sal AS ( SELECT departmentId, MAX(salary) AS max_salary FROM Employee GROUP BY departmentId ) SELECT e.* FROM Employee e JOIN max_sal m ON e.departmentId m.departmentId WHERE e.salary m.max_salary; -- 当多人同薪时会重复最优解SELECT d.name AS Department, e.name AS Employee, e.salary FROM Employee e JOIN Department d ON e.departmentId d.id WHERE (e.departmentId, e.salary) IN ( SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId );执行计划分析MySQL 8.0对IN子查询有优化会先执行子查询物化比窗口函数方案节省了排序开销3.2 第180题连续出现的数字题干找出所有至少连续出现三次的数字。解决方案对比方案代码复杂度性能可读性自连接高O(n³)差窗口函数中O(nlogn)良变量计数低O(n)优推荐方案SELECT DISTINCT num AS ConsecutiveNums FROM ( SELECT num, counter : IF(prev num, counter 1, 1) AS cnt, prev : num FROM Logs, (SELECT prev : NULL, counter : 1) AS init ) AS t WHERE cnt 3;注意事项变量初始化必须在同一语句中完成执行顺序FROM → WHERE → SELECT因此变量赋值要在SELECT完成MySQL 8.0建议改用窗口函数变量方案在复杂查询中可能产生意外结果4. 性能优化专项4.1 索引使用黄金法则通过第197题上升的温度日期差值计算分析索引失效场景-- 题目找出温度比前一天高的日期 SELECT w1.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) 1 WHERE w1.Temperature w2.Temperature;问题诊断DATEDIFF函数导致无法使用recordDate索引自连接产生N²中间结果优化方案-- 方案1利用日期连续性假设无缺失日期 SELECT w1.id FROM Weather w1 JOIN Weather w2 ON w1.recordDate DATE_ADD(w2.recordDate, INTERVAL 1 DAY) WHERE w1.Temperature w2.Temperature; -- 方案2窗口函数MySQL 8.0 SELECT id FROM ( SELECT id, Temperature - LAG(Temperature) OVER(ORDER BY recordDate) AS diff FROM Weather ) t WHERE diff 0;4.2 执行计划解读技巧以第601题体育馆的人流量为例分析EXPLAIN关键指标-- 查询人流量连续三天≥100的记录 WITH consecutive AS ( SELECT *, id - ROW_NUMBER() OVER(ORDER BY id) AS grp FROM Stadium WHERE people 100 ) SELECT id, visit_date, people FROM consecutive WHERE grp IN ( SELECT grp FROM consecutive GROUP BY grp HAVING COUNT(*) 3 );EXPLAIN输出关键点Using temporary出现临时表可能成为瓶颈Using filesort排序操作考虑添加合适索引rows列估算扫描行数与实际差距大时需要ANALYZE TABLE5. 面试实战技巧5.1 白板编码注意事项明确需求边界处理NULL的规则比较/聚合时重复数据的处理逻辑DISTINCT/GROUP BY选择结果排序要求即使题目未明确说明代码风格建议CTE优先于嵌套子查询列显式命名AS别名适当添加注释解释复杂逻辑常见Follow-up问题如果数据量扩大100倍会怎样如何验证查询结果的正确性请解释你选择的JOIN类型5.2 高频考点速查表题型代表题号核心考点易错点排名问题178,185窗口函数区别RANK vs DENSE_RANK连续问题180,601差值分组法边界条件处理分层查询1270递归CTE循环引用检测占比计算262NULL处理除数可能为0极值查询184GROUP BY陷阱多值对应问题6. 刷题路线建议根据面试岗位调整侧重点数据分析岗强化窗口函数70%熟悉日期处理20%了解PIVOT等高级特性10%后端开发岗深入JOIN优化50%掌握索引设计30%理解事务隔离级别20%全栈工程师平衡简单查询与复杂查询各50%注意SQL注入防御方案了解ORM转换原理我个人的刷题节奏是每天3-5题每道题至少尝试两种解法。对于特别复杂的题目会用真实数据在本地MySQL环境验证往往能发现理论分析时忽略的性能问题。

力扣SQL高频50题进阶：窗口函数与查询优化实战

相关推荐

Raspberry Pi Pico + MicroPython 入门实战：从点灯到温湿度监测器

Proteus 8.10中STM32F103精准PWM舵机控制实战

三维超声辅助激光熔覆技术的COMSOL多物理场仿真

更多资讯

Archify 语义化 Story 载体（Semantic Story Carrier）设计与实现解析：让五类语义流 Token 沿精确关系流动

光模块固晶机高精度贴装技术：三菱伺服动态刚性与磁极补偿解析

cognee 如何用最小 docker-compose 启动 API 服务并完成首次 add、cognify、search 请求

用事件驱动编程从零构建打字速度游戏：Web-Dev-For-Beginners 实战指南

嵌入式开发必备：从环境搭建到调试的实用命令指南

混合精度省下60%显存，VPC配错延迟翻三倍——补了机器学习基础课才止血

今日更新

SpacetimeDB C++ 模块库实战：用 C++20 在数据库内构建 WebAssembly 模块

别踩雷！不是所有 AI 写作工具都靠谱，2026 高校认可工具精选

SSM+MySQL幼儿园管理系统源码解析：从部署到优化

本周精选

超人会飞不算本事：系统稳定依赖清晰规则与边界设计

超人VS蜘蛛侠：拆解超级IP的影响力与传播方法论

基于CNN的调制信号识别：MATLAB实现时频图分类实战

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？