1. 窗口函数：从“是什么”到“为什么用它”
如果你写过SQL，尤其是处理过需要“既看整体，又看局部”的数据分析任务，那你大概率已经和窗口函数打过交道，或者至少听说过它的大名。窗口函数，也叫分析函数，是SQL中一个强大到令人惊叹的特性。它允许你在不改变原始行数的情况下，对数据的“窗口”进行计算，比如计算累计和、移动平均、排名、前后行差值等。听起来有点抽象？我们换个说法：普通的聚合函数（如SUM、AVG）是把多行数据“揉”成一团，输出一个汇总结果；而窗口函数则是给每一行数据都“戴上一副特殊的眼镜”，透过这副眼镜，每一行都能看到它所在分组（窗口）内的其他行，并基于这些“看到”的信息计算出属于自己的新值。
为什么这个特性如此重要？因为在真实的数据分析场景中，我们经常面临这样的困境：我需要知道每个员工的销售额，同时也需要知道他在部门内的排名；我需要计算每个产品每天的销售额，同时也需要看它过去7天的移动平均趋势；我需要为每一笔订单标记，看它是否是该客户的首单。这些需求，如果用传统的GROUP BY子查询去实现，SQL会变得异常复杂、难以阅读，且性能往往不佳。窗口函数正是为了解决这类“行内上下文计算”问题而生的，它让复杂的逻辑变得清晰、高效。
这篇文章，我不会只给你罗列语法。我会从一个有多年数据处理经验的从业者角度，带你深入理解几个最常用、也最核心的窗口函数。我们会拆解它们背后的计算逻辑，探讨在不同场景下的应用技巧，并分享一些从实际坑里爬出来的经验。无论你是刚开始接触窗口函数，还是想更系统地掌握它，相信接下来的内容都能让你有所收获。
2. 排名三剑客：ROW_NUMBER, RANK, DENSE_RANK 的细微差别与实战选择
排名需求大概是窗口函数最经典的应用场景了。SQL提供了三个功能相似的排名函数：ROW_NUMBER()、RANK()和DENSE_RANK()。它们看起来很像，但在处理并列情况时，行为有微妙的差异，而正是这些差异决定了你在不同业务场景下该选谁。
2.1 核心机制拆解与并列处理逻辑
我们先通过一个最简单的例子来直观感受它们的区别。假设我们有一张学生成绩表scores：
| student_id | name | score |
|---|---|---|
| 1 | 张三 | 95 |
| 2 | 李四 | 92 |
| 3 | 王五 | 92 |
| 4 | 赵六 | 88 |
现在，我们分别用三个函数按分数降序排名：
SELECT student_id, name, score, ROW_NUMBER() OVER (ORDER BY score DESC) as row_num, RANK() OVER (ORDER BY score DESC) as rank, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank FROM scores;结果会是：
| student_id | name | score | row_num | rank | dense_rank |
|---|---|---|---|---|---|
| 1 | 张三 | 95 | 1 | 1 | 1 |
| 2 | 李四 | 92 | 2 | 2 | 2 |
| 3 | 王五 | 92 | 3 | 2 | 2 |
| 4 | 赵六 | 88 | 4 | 4 | 3 |
看出区别了吗？
ROW_NUMBER()：严格的行号。即使分数相同（李四和王五都是92分），它也会赋予不同的、连续递增的序号（2和3）。它的逻辑很简单：按ORDER BY的顺序，一行一行地数过去，每行一个唯一号。RANK()：跳跃排名。当出现并列时（92分并列），它们会获得相同的名次（都是第2名），但下一个名次会“跳跃”。赵六的88分在92分之后，但因为92分占用了第2和第3名（从ROW_NUMBER角度看），所以赵六的名次是第4名。你可以把它想象成奥运会的奖牌榜：如果有两个并列金牌，那么下一名就是铜牌（第三名），银牌（第二名）位置空出来了。DENSE_RANK()：密集排名。同样处理并列，但它不会让名次“跳跃”。李四和王五并列第2名后，赵六紧跟着就是第3名。名次数字是连续、密集的。
注意：
ROW_NUMBER()在排序字段完全相同时，其分配的具体数字（谁2谁3）是不确定的，取决于数据库的实现和当时的数据物理存储顺序。除非你在ORDER BY中加入了能唯一确定顺序的列（如主键student_id），否则不要依赖其顺序做关键业务逻辑。
2.2 业务场景下的选型策略与避坑指南
理解了机制，我们来看怎么选。这个选择往往取决于你的业务逻辑和后续处理需求。
场景一：需要绝对唯一的标识或分页当你需要为查询结果的每一行生成一个绝对唯一的、连续的序号时，ROW_NUMBER()是唯一选择。例如，在Web应用中进行数据分页展示：
-- 获取第二页的数据（每页10条） SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY create_time DESC) as rn FROM orders ) AS tmp WHERE rn BETWEEN 11 AND 20;这里必须用ROW_NUMBER()，因为RANK或DENSE_RANK可能产生的并列会导致分页数据重复或丢失。
场景二：竞赛排名或成绩榜单典型的“金牌、银牌、铜牌”场景。如果业务上允许并列，并且认同并列后名次跳跃的规则（即并列第二，则下一个是第四），就用RANK()。这符合大多数体育比赛和学校成绩排名的直观认知。
场景三：等级划分或梯队分析如果你在进行客户分层（如VIP1, VIP2...）、产品等级划分，或者分析“前N%”的数据时，DENSE_RANK()通常更合适。因为它能保证等级编号是连续的，便于后续按等级分组统计。例如，找出销售额排名前10%的销售员，使用DENSE_RANK()可以更容易地计算出具体的排名阈值。
一个实战中的大坑：PARTITION BY与ORDER BY的组合窗口函数的核心是OVER()子句，其中PARTITION BY和ORDER BY决定了数据的“窗口”如何划分和排序。
RANK() OVER (PARTITION BY department_id ORDER BY sales DESC) as dept_sales_rank这句的意思是：先按department_id分区，在每个部门内部独立形成一个数据窗口；然后在每个窗口内部，按sales降序排序；最后在每个窗口内计算RANK。
这里最容易出错的地方是混淆了“窗口内排序”和“最终结果集排序”。OVER()里的ORDER BY只服务于窗口函数的计算逻辑（比如排名依据），它不保证最终查询结果的输出顺序！如果你希望结果也按某个顺序排列，必须在查询的最外层再使用一个ORDER BY子句。
-- 错误示范：你以为结果会按部门、销售额排好序？ SELECT employee_id, department_id, sales, RANK() OVER (PARTITION BY department_id ORDER BY sales DESC) as rank FROM sales_table; -- 结果集的顺序可能是杂乱无章的。 -- 正确做法：外层再加ORDER BY SELECT employee_id, department_id, sales, RANK() OVER (PARTITION BY department_id ORDER BY sales DESC) as rank FROM sales_table ORDER BY department_id, sales DESC; -- 确保输出顺序3. 聚合窗口：SUM, AVG, COUNT 的进阶玩法与性能考量
当我们把普通的聚合函数SUM(),AVG(),COUNT(),MIN(),MAX()放进OVER()窗口里，它们就获得了“透视”的能力。这可能是窗口函数中应用最广泛的一类，用于计算累计值、移动平均值、占比等。
3.1 累计计算与移动窗口的语法奥秘
最基本的用法是计算累计和（Running Total）：
SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) as cumulative_revenue FROM daily_sales;这会给每一天都计算从第一天到当天的总收入累计。
但窗口函数的强大之处在于其灵活的“窗口框架”定义。通过ROWS BETWEEN ... AND ...或RANGE BETWEEN ... AND ...子句，我们可以精确控制参与计算的行范围。
ROWS与RANGE的关键区别：
ROWS：基于行的物理位置。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW意思是“当前行及它前面的两行”，总共三行参与计算。RANGE：基于排序字段的数值范围。RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW意思是“排序字段（比如date）的值在（当前行日期 - 7天）到当前日期之间的所有行”。
对于日期连续、没有间隔的数据，两者结果可能一样。但如果日期有缺失，RANGE会包含所有在时间窗口内的行，而ROWS只认固定的行数偏移，可能导致窗口大小不一致。在涉及日期的移动窗口计算时，务必想清楚业务逻辑是需要固定的行数，还是固定的时间范围。
经典场景：计算最近N天的移动平均
-- 方法1：使用RANGE (基于日期值，更符合业务直觉) SELECT date, revenue, AVG(revenue) OVER ( ORDER BY date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW -- 过去7天（含当天） ) as ma_7days FROM daily_sales; -- 方法2：使用ROWS (基于行数，计算效率通常更高，但要求日期连续) SELECT date, revenue, AVG(revenue) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 过去7行（含当前行） ) as ma_7rows FROM daily_sales;3.2 分区聚合与占比分析的实战应用
结合PARTITION BY，我们可以在每个分组内进行独立的窗口计算。一个非常实用的场景是计算“组内占比”。
-- 计算每个员工销售额占其部门总销售额的比例 SELECT employee_id, department_id, sales, SUM(sales) OVER (PARTITION BY department_id) as dept_total_sales, sales * 1.0 / SUM(sales) OVER (PARTITION BY department_id) as sales_ratio_in_dept FROM employee_sales;这里，SUM(sales) OVER (PARTITION BY department_id)为每个部门的每一行都计算了该部门的总销售额。注意，这个窗口没有ORDER BY，也没有ROWS/RANGE子句，这意味着窗口默认是“从分区第一行到最后一行”（即整个分区）。这种用法非常高效。
性能经验谈：窗口函数 vs. 自连接或子查询对于上面的占比计算，传统的写法可能是先子查询查出部门总额，再关联回去：
-- 传统写法（可能低效） SELECT a.employee_id, a.department_id, a.sales, b.dept_total, a.sales * 1.0 / b.dept_total as ratio FROM employee_sales a JOIN ( SELECT department_id, SUM(sales) as dept_total FROM employee_sales GROUP BY department_id ) b ON a.department_id = b.department_id;窗口函数的写法通常更简洁，而且在大多数现代数据库优化器中，性能更好。因为数据库只需要扫描一次employee_sales表，就可以在扫描过程中同时计算每行的原始值和其窗口聚合值。而传统写法可能需要多次扫描或创建临时表。在处理大数据集时，这种性能差异会非常明显。当然，具体还是要看执行计划，但优先尝试窗口函数写法是一个好习惯。
4. 前后行导航：LAG 与 LEAD 在时序分析中的核心作用
在分析时间序列数据时，我们经常需要将当前行与它的“前一行”或“后一行”进行比较，比如计算日环比、周同比、判断状态是否连续变化等。LAG()和LEAD()函数就是为此而生。
4.1 函数原型与边界条件处理
LAG(column, offset, default)：获取当前行之前第offset行的column值。offset默认为1，default是当没有前一行（比如窗口的第一行）时返回的默认值，默认为NULL。LEAD(column, offset, default)：获取当前行之后第offset行的column值。参数含义同上。
一个计算日销售额环比增长的例子：
SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) as revenue_prev_day, revenue - LAG(revenue, 1) OVER (ORDER BY date) as revenue_diff, (revenue - LAG(revenue, 1) OVER (ORDER BY date)) * 1.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY date), 0) as revenue_growth_rate -- 处理除零 FROM daily_sales ORDER BY date;这里，LAG(revenue, 1)为每一行找到了前一天的销售额。对于第一天，没有前一天，所以revenue_prev_day是 NULL，导致revenue_diff和growth_rate也是 NULL。这是符合逻辑的。
关键点：如何处理窗口边界的NULL值？这是使用LAG/LEAD时最常见的痛点。上面的例子中，我们使用了NULLIF函数来防止除以零，但第一天的NULL增长率的业务含义可能是“无前期数据”。你必须和业务方明确：
- 这些NULL值在最终报表或看板中应该如何显示？（显示为“-”、0，还是“N/A”？）
- 是否需要在计算前用默认值填充？这时就可以用到第三个参数
default。
-- 用0填充没有前一天数据的情况 LAG(revenue, 1, 0) OVER (ORDER BY date) as revenue_prev_day但要非常小心：用0填充可能会扭曲后续计算（比如增长率从无穷大变成了一个具体值）。更常见的做法是让结果保持NULL，在应用层或BI工具中做格式化处理。
4.2 复杂场景：跨分区导航与状态变化检测
LAG/LEAD同样可以和PARTITION BY结合，在每个分组内独立地进行前后导航。这是一个更强大的模式。
场景：检测用户连续登录状态假设有用户登录日志表user_logins，我们想找出哪些用户是连续两天都登录的。
SELECT user_id, login_date, LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) as prev_login_date, -- 判断当前登录日期是否比前一天登录日期正好多一天 CASE WHEN login_date - LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) = 1 THEN '连续登录' ELSE '非连续或首日' END as login_status FROM user_logins;在这个查询中，PARTITION BY user_id确保了每个用户的计算是独立的。LAG函数在每个用户的时间线里寻找上一次登录日期。
进阶场景：计算会话（Session）结合LAG和条件判断，我们可以做更复杂的事情，比如将用户的行为流切割成不同的会话（假设超过30分钟不活动就视为新会话）。
WITH user_actions AS ( SELECT user_id, action_time, LAG(action_time, 1) OVER (PARTITION BY user_id ORDER BY action_time) as prev_action_time FROM action_logs ), session_flags AS ( SELECT *, -- 如果当前动作与上一个动作间隔超过30分钟，则标记为新会话开始 CASE WHEN EXTRACT(EPOCH FROM (action_time - prev_action_time)) > 1800 -- 30分钟=1800秒 OR prev_action_time IS NULL THEN 1 ELSE 0 END as is_new_session FROM user_actions ) SELECT user_id, action_time, -- 对is_new_session进行累计求和，生成会话ID SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY action_time) as session_id FROM session_flags;这个例子展示了如何组合使用LAG、窗口函数SUM以及公共表表达式（CTE）来解决一个经典的流式数据处理问题。SUM(is_new_session) OVER (... ORDER BY action_time)这个技巧值得牢记，它通过累计标记位的方式，生成了递增的会话ID。
5. 分布函数：NTILE 与统计窗口的实践精要
最后，我们来看两个在数据分箱、统计计算中非常有用的窗口函数：NTILE()和统计窗口函数（如FIRST_VALUE(),LAST_VALUE(),NTH_VALUE()）。
5.1 使用 NTILE 进行数据分箱与等频分桶
NTILE(n)函数将有序分区内的行尽可能平均地分配到n个桶（或分位数组）中，并为每一行分配其所属的桶编号（从1开始）。这是进行数据等频分箱的利器。
场景：将客户按消费金额分为高、中、低三档
SELECT customer_id, total_spent, NTILE(3) OVER (ORDER BY total_spent) as spending_tier FROM customers;NTILE(3)会尝试把客户分成三组，每组人数尽可能相等。如果总客户数不能被3整除，那么多出来的行会依次分配到前面的桶里（例如，10个人分3桶，桶的大小可能是4, 3, 3）。
这里有一个重要的细节：NTILE的分桶是基于行数的，而不是基于值的范围。也就是说，它保证的是每个桶里的记录数大致相等（等频），而不是保证每个桶的数值区间大小一致。如果你需要基于数值区间的分箱，可能需要使用WIDTH_BUCKET函数（如果数据库支持）或者用CASE WHEN手动定义阈值。
实战技巧：与聚合函数结合进行分层分析NTILE的结果常常用于后续的聚合分析，比如计算每个消费层级客户的客单价分布。
WITH customer_tiers AS ( SELECT customer_id, total_spent, NTILE(5) OVER (ORDER BY total_spent) as quintile -- 分为5档 FROM customers ) SELECT quintile, COUNT(*) as customer_count, AVG(total_spent) as avg_spent, MIN(total_spent) as min_spent_in_tier, MAX(total_spent) as max_spent_in_tier FROM customer_tiers GROUP BY quintile ORDER BY quintile;这个查询能清晰地展示出，消费最高的20%客户（第5档）和最低的20%客户（第1档）的平均消费额差异有多大。
5.2 FIRST_VALUE, LAST_VALUE 与 NTH_VALUE 的窗口框架陷阱
这三个函数用于获取窗口内第一行、最后一行或第N行的值。
FIRST_VALUE(column) OVER (...)：返回窗口内第一行的column值。LAST_VALUE(column) OVER (...)：返回窗口内最后一行的column值。NTH_VALUE(column, N) OVER (...)：返回窗口内第N行的column值。
听起来很简单，但LAST_VALUE和NTH_VALUE有一个非常容易踩坑的地方：默认窗口框架。
看这个例子：
SELECT date, revenue, FIRST_VALUE(revenue) OVER (ORDER BY date) as first_rev, LAST_VALUE(revenue) OVER (ORDER BY date) as last_rev_bug, -- 有问题的写法 LAST_VALUE(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_rev_correct -- 正确的写法 FROM daily_sales;对于last_rev_bug，你期望它返回整个时间窗口的最后一天的销售额，但实际上，在缺少明确ROWS/RANGE子句时，LAST_VALUE和NTH_VALUE的默认窗口框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着，对于每一行，窗口的“最后一行”就是当前行自己！所以last_rev_bug列的值会和revenue列一模一样，这显然不是我们想要的。
要获得整个分区的最后一个值，必须显式地将窗口框架扩展到“从第一行到最后一行”：
LAST_VALUE(revenue) OVER ( ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )而FIRST_VALUE的默认窗口框架就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，对于第一行来说，这个窗口就是第一行本身，所以它能正确工作。但为了代码清晰和避免混淆，我个人的习惯是，只要用到LAST_VALUE或NTH_VALUE，就显式地写出完整的窗口框架子句。这是一个能节省大量调试时间的经验。
窗口函数的世界远不止这几种，还有像PERCENT_RANK(),CUME_DIST()等用于统计分析的函数，但上面这四大类——排名、聚合、导航、分布——已经覆盖了90%以上的日常应用场景。掌握它们的核心机制、差异和组合用法，你的SQL数据处理能力会提升一个巨大的台阶。最关键的是，多写多练，在真实的业务数据上尝试解决实际问题，你会越来越深刻地体会到窗口函数那种“四两拨千斤”的巧妙与高效。
