在报表类查询中，我们经常需要既展示明细行，又展示该行对应的分组汇总值，例如每位用户的每笔订单金额以及该用户的总消费额。过去这类需求大多依靠子查询或自连接实现，而窗口函数提供了一条更直接的路径。
一、为什么子查询在这里显得笨重
假设有一张订单表 orders，字段包括 order_id、user_id、amount、order_date。现在要查出每个用户的所有订单，以及每个用户的总消费金额。使用子查询的常见写法是在 select 列表里放一个相关性子查询：
select o.order_id, o.user_id, o.amount, (select sum(i.amount) from orders i where i.user_id = o.user_id) as user_total from orders o;
这种写法逻辑上没问题，但数据库对外部查询的每一行都会执行一次内部子查询。若 orders 有十万行，内部 sum 就可能被执行十万次，即便 user_id 上有索引，频繁回表也会带来明显开销。更重要的是，SQL 语义被拆成了主查询和嵌套块，后续维护的人要来回对照才能理解“user_total 是怎么算出来的”。
从执行计划角度看，相关性子查询往往表现为循环嵌套，无法利用批量聚合的优势。当过滤条件变多、分组维度变复杂时，子查询层数会继续叠加，SQL 长度和维护成本呈非线性增长。
二、窗口函数的基本替代思路
窗口函数的核心是 over 子句，它定义一个“窗口”来描述函数要计算的行范围。把上面的需求改用窗口函数，只需要一次扫描：
select order_id, user_id, amount, sum(amount) over (partition by user_id) as user_total from orders;
这里 partition by user_id 告诉数据库按用户分组开窗口，sum(amount) 在窗口内聚合，但不会像 group by 那样折叠行，而是把汇总值写回每一行。数据库通常只需对数据做一次分区排序，然后流式聚合，扫描次数从 N 次降为 1 次。
要注意的是，窗口函数发生在 where 和 group by 之后、order by 之前，因此它操作的是“已筛选、已分组后的结果集”。如果你需要先过滤再开窗口，就应把过滤写在 where 里，而不是把窗口结果再套一层子查询去过滤，那样就又回到了旧模式。
三、三类典型场景的改写示例
1. 排名需求替代子查询
找出每个用户消费最高的前两笔订单，子查询写法常配合 count 或 exists 实现，而窗口函数直接用 row_number：
select *
from (
select
order_id,
user_id,
amount,
row_number() over (
partition by user_id
order by amount desc
) as rn
from orders
) t
where rn <= 2;
内部查询给每个用户的订单按金额打序号，外部查询只取前两名。相比用子查询比较“是否存在比当前金额更大的两笔”，窗口写法直观且性能稳定。row_number 在并列时不会跳号，若业务要求并列同分则可用 rank 或 dense_rank 替换。
2. 累计求和替代自连接
按时间顺序算每个用户的累计消费，传统做法是用自连接把“早于当前订单的记录”都连回来再 sum。窗口函数用 order by 在窗口内定义累计范围：
select
order_id,
user_id,
order_date,
amount,
sum(amount) over (
partition by user_id
order by order_date
rows between unbounded preceding and current row
) as running_total
from orders;
rows between unbounded preceding and current row 表示窗口从分区第一行到当前行，这就是累计语义。数据库在排序后只需维护一个滚动累加值，避免了自连接产生的笛卡尔式膨胀。
3. 同比计算替代多层嵌套
若要计算当前月销量相对上月的比例，子查询要按月份再嵌套一层。窗口函数可用 lag 取前一行：
select
user_id,
month,
sales,
lag(sales) over (
partition by user_id
order by month
) as prev_sales,
sales * 1.0 / nullif(
lag(sales) over (
partition by user_id
order by month
), 0
) as ratio
from monthly_sales;
lag 让当前行直接访问同分区上一行的值，无需任何子查询。nullif 用于防止除零，这种表达比嵌套子查询更贴近“取上月”的业务语言。
四、性能与索引的注意事项
窗口函数虽然减少了扫描次数，但 partition by 和 order by 的列仍建议有索引支持，否则数据库要在内存或磁盘做排序。以 PostgreSQL 为例，创建 (user_id, order_date) 的联合索引，可让 partition by user_id order by order_date 直接走索引有序扫描，避免额外排序阶段。
create index idx_orders_user_date on orders (user_id, order_date);
在 MySQL 8.0 之后窗口函数已原生支持，但排序内存受 sort_buffer_size 限制，数据量极大时需关注临时文件落盘。相比之下，子查询方案的问题不只是慢，更在于难以预测执行计划；窗口函数把计算模型标准化，优化器更容易选择顺序聚合。
另一个常见误区是认为窗口函数一定比子查询快。若子查询能被优化器重写为半连接且命中索引，差距可能缩小。但就代码可读性和迭代成本而言，窗口函数在大多数 OLAP 场景里都明显占优。
五、改写时的边界情况
不是所有子查询都能直接平移为窗口函数。例如返回多列且来自不同表的子查询，可能仍需 join；而窗口函数只能基于当前查询的输出行计算。此时可先用 with 子句把数据集准备好，再在上面开窗口。
with user_orders as ( select o.order_id, o.user_id, o.amount, u.city from orders o join users u on u.user_id = o.user_id ) select order_id, city, amount, sum(amount) over (partition by user_id) as user_total from user_orders;
这样逻辑分层清晰：with 负责取数，窗口负责分析。相比把 join 和聚合都揉进子查询，这种结构更容易让后来者看懂每一步在做什么，也方便单独测试中间结果。
总体来看，窗口函数替代子查询的关键，是把“对每一行都去查一次”的循环思维，转为“对数据集开一个滑动计算面”的集合思维。掌握 partition by 与 order by 的组合，就能覆盖绝大多数报表中的嵌套查询简化需求。