SQL窗口函数入门：像Excel一样写高效数据库查询
简介：这是一份专为数据库从业者设计的《SQL窗口函数速查表》PDF文档，面向数据库管理员、数据分析师、开发工程师及SQL进阶学习者，旨在解决复杂数据分析场景下窗口函数选型难、语法易混淆、实际应用无参照等痛点。文档系统梳理了排名函数（ROW_NUMBER/RANK/DENSE_RANK）、偏移函数（LEAD/LAG）与聚合类窗口函数（SUM/AVG/COUNT等）三类核心用法，详解OVER子句中PARTITION BY、ORDER BY与FRAME CLAUSE的组合逻辑，并配以典型业务场景示例（如分组排序、同比环比、滚动统计），便于即查即用。资源为单文件PDF格式，共1个841KB文件，内容排版紧凑、分类清晰、示例可直接复现，适合作为开发调试时的桌面参考或学习过程中的记忆锚点。目前已有255人下载学习，是兼顾准确性、实用性与便携性的SQL高阶技能提效工具。
1. SQL窗口函数到底是什么？——别被“窗口”俩字吓住，它其实就是你手边的Excel透视表+公式下拉键
你有没有试过在Excel里做这样的事：
- 给销售数据按地区分组，然后给每个地区的每条记录自动编个序号（从1开始连续排）；
- 算出每个员工当月工资，再顺手标出“比上个月涨了还是跌了”，直接用上一行的数据；
- 不仅要算出每个部门的平均薪资，还要把这平均值“贴”到每个人那行里，方便对比——而不是只返回一个数字、把原始名单全吞掉。
这些操作，你在Excel里点几下就能搞定：选中区域 → 插入透视表 → 写个=OFFSET或=INDEX+MATCH，甚至直接拖填充柄。但一旦数据量上百万、要跑在数据库里、还得嵌进报表系统自动刷新……Excel就歇菜了。这时候，SQL窗口函数就是你的「数据库版Excel公式下拉键」——它不改变原始行数，不合并数据，也不丢细节，而是一边保留每一行的完整信息，一边给每行“悄悄算好一个新值”。
它不是什么黑匣子玄学，也不是只有DBA才配碰的高阶技巧。你每天用的“TOP 10销量商品”“近30天日活环比”“用户首次下单后第7天是否复购”……背后八成靠它撑着。这份《SQL窗口函数速查表.pdf》，就是一张印满“常用公式写法+参数说明+抄了就能跑”的便携卡片——没有长篇大论讲执行计划，不扯底层B+树索引，就干一件事：让你今天下午下班前，写出第一个能跑通的ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC)。适合完全没听过“窗口”、连OVER()括号都打不全的新手；也适合写了三年SQL却总在RANK()和DENSE_RANK()之间反复横跳、每次上线都提心吊胆的半熟手。
2. 为什么非得学窗口函数？——告别三层嵌套子查询、临时表和“先查再拼”的土办法
2.1 传统SQL处理“组内计算”的三大翻车现场
我们先看一个真实场景：某电商平台想统计“每个品类下销量最高的前3款商品”。老办法怎么写？
-- ❌ 方法一：用子查询 + 相关子查询（慢到哭） SELECT category, product_name, sales FROM products p1 WHERE ( SELECT COUNT(*) FROM products p2 WHERE p2.category = p1.category AND p2.sales > p1.sales ) < 3;问题在哪？
- 每查一行p1，都要全表扫一遍p2找比它销量高的——10万行数据，实际执行10万×10万次比较；
COUNT(*)里不能加索引，纯CPU硬扛；- 代码像俄罗斯套娃，改个排序字段得翻三页。
-- ❌ 方法二：用临时表 + JOIN（维护噩梦） CREATE TEMP TABLE top3_per_cat AS SELECT category, product_name, sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn FROM products; SELECT category, product_name, sales FROM top3_per_cat WHERE rn <= 3;问题在哪？
- 多建一张表，就得管它的生命周期（忘删？占空间；删早了？下游报错）；
- 如果要加“同销量并列第2”，还得换
RANK()，临时表逻辑全重写； - 每次需求变，都得改DDL+DML两套脚本。
提示：这两种写法在小数据量（<1万行）时还能凑合，但只要业务增长、报表并发一上来，数据库负载立刻飙升——这不是你SQL写得差，是工具没选对。
2.2 窗口函数怎么一招破局？——三行代码，一次扫描，结果自带“上下文”
现在用窗口函数重写：
-- ✅ 一行核心逻辑，清晰、高效、可读 SELECT category, product_name, sales FROM ( SELECT category, product_name, sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn FROM products ) t WHERE rn <= 3;它背后的执行逻辑，就像Excel里你选中整列→点“排序”→再点“填充序列”：
PARTITION BY category：相当于“按品类分组”，每组独立编号；ORDER BY sales DESC：组内按销量从高到低排；ROW_NUMBER()：从1开始，挨个发号（1,2,3…），绝不重复、绝不跳空；- 最外层
WHERE rn <= 3：只取每组前3名——注意！这里过滤的是“计算后的结果列”，不是原始数据。
关键优势：
✅单次扫描：数据库引擎遍历products表1次，边读边算序号，不用回表、不建临时结构；
✅语义直白：看到PARTITION BY就知道分组，看到ORDER BY就知道排序依据，看到ROW_NUMBER()就知道编号规则；
✅灵活切换：想改成“销量并列第2也算第2名”？把ROW_NUMBER()换成RANK()就行，其他代码不动；
✅零学习成本迁移：你已经会写SELECT ... FROM ... WHERE ...，窗口函数只是在SELECT里多加了个带OVER()的表达式。
2.3 它真能替代90%的“复杂报表逻辑”吗？——看这几个高频场景你就信了
| 场景 | 以前怎么搞 | 现在怎么写 | 省了多少事 |
|---|---|---|---|
| 同比/环比：本月销售额 vs 上月 | 先查本月，再查上月，用LEFT JOIN拼日期，手动算差值 | LAG(sales, 1) OVER (ORDER BY month)直接取上月值 | 不用写两个SELECT，不用JOIN，日期错位风险归零 |
| 移动平均：过去7天日均订单量 | 建7张临时表，每天一张，再UNION ALL后AVG() | AVG(order_cnt) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | 一行搞定，滑动窗口自动伸缩，不用管“哪天没数据” |
| 分组累计：每个客户从注册到现在的总消费额 | 用自关联a.id >= b.id暴力求和，性能崩盘 | SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) | 索引友好，千万级数据秒出 |
| Top N per Group：每个城市最贵的3套房 | 嵌套3层子查询，加GROUP_CONCAT拼字符串再解析 | ROW_NUMBER() OVER (PARTITION BY city ORDER BY price DESC) | 代码长度缩短2/3，执行时间从分钟级降到毫秒级 |
你会发现：这些不是“炫技场景”，而是日报、周报、BI看板里天天出现的刚需。而窗口函数，就是SQL世界里那个“本该存在、却晚来了十年”的基础能力——它不取代GROUP BY，而是补上GROUP BY做不到的事：既要分组聚合，又要保留明细。
3. 第一步怎么迈？——从安装环境到跑通第一个ROW_NUMBER()，5分钟实操指南
3.1 你不需要装任何新软件，用现有数据库就能开练
很多新手一看到“SQL高级功能”就默认要升级数据库、装插件、改配置……其实大可不必。窗口函数是SQL:2003标准的一部分，主流数据库早已原生支持：
- ✅PostgreSQL 8.4+（2008年就支持，现在用的都是12/14/15）
- ✅MySQL 8.0+（2018年发布，目前企业主力版本基本全覆盖）
- ✅SQL Server 2005+（
ROW_NUMBER()最早就是它带火的） - ✅Oracle 8i+（更早，但语法略有差异）
- ✅SQLite 3.25+（2018年加入，轻量级项目够用）
注意：如果你用的是MySQL 5.7或更早版本，确实不支持——但别急着重装！先确认你连接的是哪个实例：公司测试库、本地Docker镜像、还是云服务后台？很多云厂商（如阿里云RDS、腾讯云CynosDB）已默认开启MySQL 8.0，你可能早就“有而不自知”。
3.2 准备一个超简单的测试表（复制粘贴就能跑）
我们不用导入真实业务数据，自己建一张5行的模拟表，足够验证所有逻辑：
-- 创建测试表（MySQL 8.0+ / PostgreSQL / SQL Server 通用写法） CREATE TABLE sales_demo ( id INT PRIMARY KEY, region VARCHAR(20), product VARCHAR(30), amount DECIMAL(10,2) ); -- 插入5条测试数据（故意让region有重复，product有同名） INSERT INTO sales_demo VALUES (1, '华东', '手机A', 5999.00), (2, '华东', '手机B', 3999.00), (3, '华北', '手机A', 5899.00), (4, '华北', '耳机X', 299.00), (5, '华南', '手机B', 4199.00);执行完后，用SELECT * FROM sales_demo;确认数据长这样：
| id | region | product | amount |
|---|---|---|---|
| 1 | 华东 | 手机A | 5999.00 |
| 2 | 华东 | 手机B | 3999.00 |
| 3 | 华北 | 手机A | 5899.00 |
| 4 | 华北 | 耳机X | 299.00 |
| 5 | 华南 | 手机B | 4199.00 |
这就是我们的“练功沙盒”——5行数据，3个地区，3款产品，金额有高低。所有窗口函数实验，都从这张表出发。
3.3 动手写第一个窗口函数：给每个地区销量最高的商品标序号
目标：按地区分组，组内按销量降序排，给每行标个rank_in_region（地区内排名）。
-- ✅ 第一个能跑通的窗口函数（复制即用） SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region FROM sales_demo;预期结果：
| region | product | amount | rank_in_region |
|---|---|---|---|
| 华东 | 手机A | 5999.00 | 1 |
| 华东 | 手机B | 3999.00 | 2 |
| 华北 | 手机A | 5899.00 | 1 |
| 华北 | 耳机X | 299.00 | 2 |
| 华南 | 手机B | 4199.00 | 1 |
逐部分拆解，确保你真正看懂：
ROW_NUMBER()：函数名，意思是“给我一个连续不重复的序号”；OVER (...)：这是窗口函数的“身份证”，告诉数据库：“接下来我要定义一个计算范围”；PARTITION BY region：把数据按region字段切成几块（华东一块、华北一块、华南一块），每块独立计算；ORDER BY amount DESC：在每块内部，按amount从大到小排——所以华东里手机A（5999）排第1，手机B（3999）排第2；AS rank_in_region：给这个新算出来的列起个名字，方便后续用或展示。
关键理解：
ROW_NUMBER()不是对整张表排序后编号，而是“先分块（PARTITION），再在块里排序（ORDER BY），最后块内编号”。这和Excel里“按地区筛选→排序→填序号→取消筛选”逻辑完全一致。
3.4 验证你真的懂了：快速切换三种排名逻辑
现在把ROW_NUMBER()换成另外两个常见函数，观察结果变化——这是检验理解的黄金练习：
-- 对比1：ROW_NUMBER() —— 连续编号，同销量也不同号 SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales_demo; -- 对比2：RANK() —— 同销量同名次，但跳过后续名次（比如两个第1，下一个就是第3） -- （当前数据没同销量，我们手动改一条：UPDATE sales_demo SET amount=5999.00 WHERE id=3; 再运行） SELECT region, product, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rk FROM sales_demo; -- 对比3：DENSE_RANK() —— 同销量同名次，不跳号（两个第1，下一个就是第2） SELECT region, product, amount, DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drk FROM sales_demo;为什么必须亲手试这三次？
因为文档里写的“RANK跳号、DENSE_RANK不跳号”是结论，而你脑子里形成的画面应该是：
RANK()像运动会颁奖：金牌1人，银牌空缺（没人拿），铜牌直接第3名；DENSE_RANK()像班级排名：考100分的3个同学并列第1，下一个98分就是第2名；ROW_NUMBER()像军训报数：不管成绩，按站队顺序喊1、2、3……
只有看到结果差异，你才能条件反射地选对函数——而不是每次写代码前，还得翻文档查区别。
4. 新手必踩的5个坑：现象、原因、一招解决（附错误截图联想）
4.1 坑1：OVER()括号里啥都不写，直接报错“syntax error near OVER”
- 现象：写
SELECT name, ROW_NUMBER() OVER () FROM table;，数据库报错，提示语法错误。 - 原因：
OVER()是窗口函数的强制语法，括号里至少得有ORDER BY（排序依据）。ROW_NUMBER()要求明确“按什么顺序排”，哪怕你只想按主键排，也得写ORDER BY id。 - 解决：永远记住口诀——“
OVER后面不空着，ORDER BY是底线”。如果真不想排序（极少见），用ORDER BY (SELECT NULL)兜底（PostgreSQL/SQL Server支持），但强烈建议显式写ORDER BY id。
4.2 坑2：PARTITION BY字段拼错，结果全乱套却没报错
- 现象：想按
region分组，但误写成PARTITION BY reigon（少个n），查询能跑通，但rank_in_region全是1。 - 原因：数据库把
reigon当成一个不存在的列，返回NULL；PARTITION BY NULL意味着“所有行分到同一组”，所以ROW_NUMBER()在整个表里连续编号，但因为你没写ORDER BY，顺序不确定，看起来像随机。 - 解决：执行前先用
SELECT DISTINCT reigon FROM sales_demo;检查字段名是否存在；或者，在写完PARTITION BY后，立刻补上ORDER BY，双重保险。
4.3 坑3：在WHERE里直接引用窗口函数列，报错“invalid reference to FROM-clause entry”
- 现象：写
SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t WHERE rn <= 3;，报错说rn找不到。 - 原因：SQL执行顺序是
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而窗口函数在SELECT阶段才计算，WHERE阶段它还没出生！ - 解决：必须用子查询或CTE（公用表表达式）包裹。正确写法：
-- 子查询方式（兼容性最好） SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales_demo ) t WHERE t.rn <= 3; -- CTE方式（更易读，MySQL 8.0+/PostgreSQL支持） WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales_demo ) SELECT * FROM ranked WHERE rn <= 3;
4.4 坑4：LAG()取上一行，第一行返回NULL，但业务要求填0或默认值
- 现象：
SELECT amount, LAG(amount) OVER (ORDER BY id) AS prev_amount FROM sales_demo;，第一行prev_amount是NULL，下游程序报空指针。 - 原因：
LAG()默认行为就是“没上一行就返回NULL”，这是设计使然，不是bug。 - 解决：用
LAG(..., offset, default_value)第三个参数指定默认值：
或者用SELECT amount, LAG(amount, 1, 0) OVER (ORDER BY id) AS prev_amount -- 第一行prev_amount=0 FROM sales_demo;COALESCE兜底：COALESCE(LAG(amount) OVER (...), 0)。
4.5 坑5：移动平均ROWS BETWEEN写错方向，结果完全反了
- 现象：想算“截止当前行的累计和”，写了
SUM(amount) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)，结果却是下两行的和。 - 原因：
ROWS BETWEEN X AND Y的X必须是起点，Y是终点，且X不能比Y“靠后”。CURRENT ROW AND 1 FOLLOWING是对的（当前+下1行），但1 PRECEDING AND CURRENT ROW才是“上1行+当前行”。初学者常把PRECEDING（之前）和FOLLOWING（之后）记混。 - 解决：牢记口诀——“
PRECEDING是回头看，FOLLOWING是往前瞅”。需要“过去N天”就用N PRECEDING；需要“未来N天”才用N FOLLOWING。调试时，先用ROWS UNBOUNDED PRECEDING（从开头到当前）看累计效果，再逐步收紧范围。
5. 下一步学什么？——从速查表出发的3条渐进路径与我的血泪经验
5.1 路径一：吃透速查表里的“核心函数族”，用2小时建立肌肉记忆
这份PDF不是用来“收藏吃灰”的，而是当作随身词典+错题本用。我建议你按这个顺序精读并动手验证：
| 阶段 | 重点函数 | 必做练习（基于sales_demo表） | 验证要点 |
|---|---|---|---|
| Day 1 上午 | ROW_NUMBER(),RANK(),DENSE_RANK() | ① 按region分组排名；② 按amount全局排名；③ 故意插入两条相同amount，对比三者输出 | 看清“同值时序号是否相同”“后续序号是否跳空” |
| Day 1 下午 | LAG(),LEAD() | ①LAG(amount)取上一行；②LEAD(amount, 2)取下两行；③LAG(amount, 1, 0)设默认值 | 确认NULL出现位置，验证默认值生效 |
| Day 2 上午 | SUM(),AVG(),COUNT()+OVER | ①SUM(amount) OVER (PARTITION BY region)；②AVG(amount) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) | 区分“分组聚合”和“滑动窗口聚合”结果形态 |
我第一次学时，把PDF打印出来，在每类函数旁手写3行自己的例子（不是抄文档的），然后在数据库里一条条跑。遇到报错不查百度，先看错误提示里哪个单词拼错了——两周后，看到
OVER就条件反射想PARTITION和ORDER BY，这才是真正的入门。
5.2 路径二：攻克“帧（frame）”概念——别怕ROWS和RANGE，它们就是Excel的“相对引用”
很多人卡在frame_clause（帧子句），觉得ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING像天书。其实它就是Excel里AVERAGE(B2:B4)的数据库表达：
B2:B4=ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING（当前行±1行）；B1:B10=ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从第一行到当前）；B5:B5=ROWS BETWEEN CURRENT ROW AND CURRENT ROW（就当前这一行）。
速查表里最关键的帧用法对比表：
| 场景 | 推荐写法 | 为什么这么写 | 实际效果（以sales_demo按id排序为例） |
|---|---|---|---|
| 累计求和（从第一行加到当前行） | SUM(amount) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) | UNBOUNDED PRECEDING= 从头开始，CURRENT ROW默认省略 | id=1时sum=5999；id=2时sum=5999+3999=9998… |
| 移动平均（最近3天） | AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) | 2 PRECEDING= 往前推2行，CURRENT ROW= 包含自己，共3行 | 当前行+前两行的平均值 |
| 同比计算（今年vs去年） | amount - LAG(amount, 12) OVER (ORDER BY year_month) | 用LAG比ROWS更精准，避免月份缺失导致错位 | 直接取12行前的值相减，不依赖日期连续性 |
血泪经验：别死记
ROWS和RANGE区别（RANGE按值范围，ROWS按物理行数）。95%的业务场景用ROWS就够了。RANGE只在“按金额分段”这类特殊需求才用，新手先跳过。
5.3 路径三：把速查表变成你的“SQL急救包”——3个真实工作流改造案例
学完函数，下一步是用它替换你正在写的低效SQL。我整理了三个高频改造场景，照着做，当天就能感受到性能提升：
案例1：替换“自关联求Top N”的慢查询
- 原SQL（10万行数据，执行23秒）：
SELECT a.region, a.product, a.amount FROM sales_demo a WHERE 3 > ( SELECT COUNT(*) FROM sales_demo b WHERE b.region = a.region AND b.amount > a.amount ); - 改造后（同一数据，执行0.08秒）：
SELECT region, product, amount FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales_demo ) t WHERE rn <= 3;
案例2：替换“临时表存中间结果”的维护噩梦
- 原流程：每天凌晨跑脚本 →
CREATE TEMP TABLE tmp1 AS SELECT ...→CREATE TEMP TABLE tmp2 AS SELECT ... FROM tmp1→INSERT INTO report_table SELECT ... FROM tmp2 - 改造后：单条SQL搞定，无临时表，无DDL风险：
INSERT INTO report_table SELECT region, AVG(amount) AS avg_amount, SUM(amount) OVER (PARTITION BY region ORDER BY id) AS cumsum_by_region FROM sales_demo GROUP BY region;
案例3：替换“应用层拼接”的脆弱逻辑
- 原做法：Java代码查出全部销售数据 →
for(Product p : list){ p.setRank(getRank(p)); }→ 再传给前端 - 改造后：数据库直接算好，应用层零逻辑：
SELECT id, region, product, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank, DENSE_RANK() OVER (ORDER BY amount DESC) AS global_rank FROM sales_demo;
从那以后我每次写SQL，只要看到GROUP BY后还想保留明细，或者WHERE里要引用聚合结果，就强制走一遍“能不能用窗口函数重构”的 checklist。不是为了炫技，而是因为——它让代码更短、执行更快、出错更少、交接更容易。希望帮到你。
本文还有配套的精品资源，点击获取