PostgreSQL 窗口函数这东西用得好是真上瘾。我最初接触它是因为一个看着特别简单的业务需求给每个部门的员工按工资排个名取前三名。用 GROUP BY 做吧只能查出每个部门的最高工资拿不到完整的人用子查询做吧SQL 写得又臭又长。后来在 PostgreSQL 里试了试ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC)一条 SQL 搞定从那一刻起我就知道这玩意儿的威力远不止做一个排名这么简单。这篇博文我就把几年用下来积累的窗口函数高级玩法、踩过的坑、优化的细节一次性讲透。特别是排名和聚合分析这个组合拳学完你会发现很多复杂的取数逻辑原来可以写得这么清爽。这篇内容适合谁看只要你在跟数据打交道不管是后端开发写业务 SQL、数据分析师跑报表、还是 DBA 做性能优化窗口函数都是绕不开的硬技能。它最核心的价值总结起来就一句话在不改变行数粒度的情况下完成对一组行的计算。这句话是理解一切窗口函数用法的钥匙建议你先把这句话刻在脑子里。1. 为什么需要窗口函数GROUP BY 做不到的事很多人刚接触窗口函数时最大的困惑是我明明用 GROUP BY 加各种聚合函数也能算出来不少东西为什么要学一个新概念我用一个实际的例子来把这个痛点摊开讲。假设有一张销售表sales记录每个销售员每天的业绩表结构大致如下CREATE TABLE sales ( id BIGSERIAL PRIMARY KEY, salesperson TEXT NOT NULL, sale_date DATE NOT NULL, amount NUMERIC(10,2) NOT NULL );业务方现在提了一个需求查询每个销售员每一天的业绩并且带上该销售员截止到今天的累计业绩。如果用 GROUP BY我们必须按salesperson做分组但一旦 GROUP BY 了分组内的行就会被压缩成一行你没法同时查出每天一行的明细又带上累计值这个聚合结果——除非你用相关子查询去逐行关联但那个性能和维护性都相当糟糕SQL 会写成这样SELECT s.salesperson, s.sale_date, s.amount, (SELECT SUM(t.amount) FROM sales t WHERE t.salesperson s.salesperson AND t.sale_date s.sale_date) AS cumulative_amount FROM sales s ORDER BY s.salesperson, s.sale_date;这段 SQL 不是不能用数据量小的时候跑着没问题。但一旦数据量到了百万级这个相关子查询就会变成性能噩梦——每一行都要去扫描一次子查询。而窗口函数解决这个问题只需要加一列SELECT salesperson, sale_date, amount, SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS cumulative_amount FROM sales ORDER BY salesperson, sale_date;两种写法效果一样但后者对优化器友好得多SQL 也短得多。这就是窗口函数的第一大价值在保留明细行粒度的同时把聚合计算作为一种列加到每一行上。为了彻底讲明白窗口函数我从原理到实战一步步深入拆解。1.1 窗口函数的核心概念和计算流程PostgreSQL 的窗口函数语法结构是这样的函数名(参数) OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS|RANGE BETWEEN 窗口帧起点 AND 窗口帧终点] )这看起来有点唬人但拆开看就三条PARTITION BY决定把数据按什么维度切成一个个小组。不写的话默认把整个结果集当成一个组。这个和 GROUP BY 的语义有点像但结果的行数不会因此减少。ORDER BY决定在每个组内部按什么顺序排列。排序的意义在于很多窗口函数比如排名、累计、移动平均的结果和顺序强相关不写排序很多函数就没法用。ROWS/RANGE BETWEEN决定当前行对应的计算范围边界。它扩展了窗口的视野默认情况在下面单独说。窗口函数在实际执行时的计算流程我建议按照这个顺序去理解数据库先执行 FROM、WHERE、GROUP BY、HAVING 这些常规查询步骤生成一个中间结果集。然后窗口函数在这个中间结果集上执行。也就是说WHERE 过滤完之后才轮到窗口函数计算窗口函数的结果不能用于 WHERE 子句。系统按 PARTITION BY 把中间结果集切成多个分区。在每个分区内部按 ORDER BY 排序。对每一行计算它对应的窗口帧前面语法里的ROWS|RANGE BETWEEN...范围内的数据把函数应用到该窗口帧内的所有行上。全部行计算完成后如果有 ORDER BY查询级别的最后再对整个结果集排序输出。这里有一个非常关键的点也是新手最容易踩的第一个坑窗口函数是在 WHERE 之后执行的。这意味着什么举个例子你想找出每个部门工资涨幅超过 20% 的员工并给他按部门内工资排名。你会条件反射地写WHERE growth_rate 0.2但这个growth_rate如果是窗口函数算出来的直接在 WHERE 里引用会直接报错。原因很简单WHERE 执行时窗口函数的结果根本还没算出来。正确做法是把窗口函数放在子查询或者 CTE 里先生成结果再到外层查询去过滤。1.2 窗口函数与普通聚合函数的本质区别这个区别值得单独拿一节来说因为很多写了好几年 SQL 的人依然会在这一点上犯迷糊。我先说结论普通聚合函数如SUM、COUNT、AVG把多行压缩成一行行的粒度被改变。窗口函数如SUM() OVER(...)不改变行数每一行仍然保留自己的所有字段额外增加一个计算出来的列。再用一个类比辅助理解GROUP BY 像榨汁机把一堆水果多行榨成一瓶果汁一行水果的原始形态没了窗口函数像在玩多米诺骨牌每一张牌依旧是一张牌但每张牌上面都多写了一个数字——这个数字是它自己和前面若干张牌叠加计算的结果。还有一个常见的认知误区窗口函数不只是ROW_NUMBER()、RANK()这些排名专用的函数。任何聚合函数只要加上 OVER 子句都能变身成窗口函数。当你看到SUM(...) OVER (...)这种写法时不要把它当成什么特别的东西它就是把普通聚合函数移到窗口上下文中去用。掌握了这一点你会发现自己能做的事情瞬间多了一倍不仅仅是 AVG、SUM像COUNT、MIN、MAX、EXPLORE如果用的 PG 版本支持这些聚合函数全部都可以在窗口模式下工作。2. 窗口函数三大排名金刚ROW_NUMBER、RANK、DENSE_RANK排名这是很多人第一次接触窗口函数的场景。 PostgreSQL 里有三个最基础、也最容易混淆的排名函数。我把它们放在一起讲因为它们的语法长得差不多结果却差很多。SELECT employee_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank FROM employees ORDER BY dept_id, salary DESC;我直接给一个运行结果示例大家把涨薪逻辑演算一遍employee_iddept_idsalaryrow_numrankdense_rank1A100001112A90002223A90003224A80004435B95001116B8000222刚开始接触的时候很多人觉得这三个函数差不多等到真正取数时才发现差别很大。2.1 三个排名函数的结果差异对比ROW_NUMBER()纯粹地按行编号不管排序值是否相同每行都会得到一个独一无二的序号从 1 开始连续递增。比如上面例子中两个 9000 的员工一个排第 2一个排第 3谁也不让谁。如果 PARTITION BY 中分组列和 ORDER BY 排序列的联合值有重复ROW_NUMBER 会任意编号不确定谁拿 2 谁拿 3——这在实际应用中要特别注意如果你不想看到随机结果需要在 ORDER BY 后面补一个唯一性字段比如 employee_id兜底让排名结果稳定可预测。RANK()当排序值有重复时重复值共享同一个名次下一名直接跳过对应数字。比如两个 9000 的员工都排第 2 名下一个 8000 的员工就只有第 4 名第 3 名被空出来了。DENSE_RANK()翻译过来就叫稠密排名。重复值共享名次的做法和 RANK 一样但不跳动数字。两个并列第 2 名之后下一位还是第 3 名中间不会有空缺。为了直观记忆我通常用比赛来类比ROW_NUMBER 是每个运动员单独编号挤在一起也是不同编号RANK 是如果出现并列后面的名次自动跳号DENSE_RANK 是不管几个人并列名次永远连续不跳。2.2 业务场景中该怎么选经典案例解析有了这三个函数我们面临的就是用哪个的问题。我总结了自己的经验读者可以直接套用到日常业务里拿第几名这种需求用 ROW_NUMBER。最常见的场景是排行榜比如找每个班级分数最高的学生、每个分组里最新的那条订单。这种需求因为第 1 名只有一个人所以即使用 RANK 或者 DENSE_RANK 也不会出太大问题但 ROW_NUMBER 最稳因为不管排序值是否重复它总能保证每行序号唯一。一般配合子查询或 CTE 使用在外层再套一层WHERE row_num 1就能精准拿到每个分组的第一名。并列显示并跳号用 RANK。比较典型的是比赛成绩表、运动会排名这种业务。比如好几个运动员成绩一样快那必须都算金牌银牌自动空掉。这个时候 RANK 才符合业务语义。排行榜要连续名次用 DENSE_RANK。比较常见的是积分排行、游戏段位这种东西。用户积一样并列升一个段位但后面的人名次不需要被跳过。比如两个人同积分排第 1 名下一个应该还是第 2 名而不是第 3 名这样用户看着不会觉得名次怎么空一格我连第 2 名都抢不到了吗。2.3 排名函数在去重与最新数据提取中的应用排名函数不只是用来排名次它还有一个隐藏的高级用法分组去重。这儿有一个很经典的面试题有一张订单表orders同一个用户可能有多个订单现在要取出每个用户的最近一笔订单。用 GROUP BY 取不出完整记录只能取出用户号和 max(order_time)用相关子查询性能又差这时候用 ROW_NUMBER秒解WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY order_time DESC ) AS rn FROM orders ) SELECT * FROM ranked_orders WHERE rn 1;这里面有一个细节很多人没注意到如果用户在同一秒下了两笔单ORDER BY order_time DESC无法保证哪一笔排在前面。生产环境里 order_time 如果精度不够比如只有秒这个查询的结果就可能不稳定。我有一年就因为这个问题给用户算最近一次充值金额时同一秒两笔充值结果随机取了其中一笔后来排查了半天才发现是时间精度的问题。我的建议是去重/取最新场景下ORDER BY里一定带上主键或者唯一键做兜底写成ORDER BY order_time DESC, id DESC确保每一行都有明确的排序依据结果100%稳定可复现。另外不要试图在窗口函数里做条件过滤。有段时间我在公司代码评审里经常看到有人写ROW_NUMBER() OVER (PARTITION BY user_id -- 这里不能写 WHERE 或者 AND status paid )OVER 子句里不能加 WHERE这是语法层面就限制死的。如果取最新一笔已支付订单正确做法是先把status paid过滤掉再在过滤后的结果集上做窗口计算WITH paid_orders AS ( SELECT * FROM orders WHERE status paid ), ranked_orders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC, id DESC) AS rn FROM paid_orders ) SELECT * FROM ranked_orders WHERE rn 1;3. 聚合窗口函数进阶累计、移动平均与同环比计算排名函数只是窗口函数的入门菜真正让它发挥巨大价值的是聚合函数在窗口模式下的各种变体。尤其是当你和窗口帧这个概念结合时能做的事会远超你的想象。这一节我尽量把聚合窗口讲透。3.1 窗口帧的本质如何精确控制计算范围我先把这个概念讲透因为它极其重要。PARTITION BY划定了在哪一组里计算ORDER BY划定了组内怎么排序但是对于每一行到底要对哪些行进行计算这个问题需要一个更精确的定义这就是窗口帧Window Frame要做的事。PostgreSQL 支持两种帧模式ROWS模式以行为单位直接指定相对当前行的偏移量。比如SUM(amount) OVER ( PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )这表示对当前行往上数2行一直到当前行这个范围内的 amount 求和。如果今天是 5 号那算的就是 3 号、4 号、5 号三天的销售额相当于一个宽度为 3 天的滑动窗口。RANGE模式以排序值为单位给出行需要落入的值区间。比如SUM(amount) OVER ( PARTITION BY salesperson ORDER BY sale_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW )RANGE 模式下PostgreSQL 会找到当前sale_date往前推7天这个时间点把从该点到当前日期之间所有行都纳入计算。注意RANGE 模式要求 ORDER BY 的字段是唯一的或者至少是可确定比较的否则 PostgreSQL 会报错。比如RANGE BETWEEN 1 PRECEDING AND CURRENT ROW在ORDER BY sale_date上如果 sale_date 出现重复值会报Frame starting offset must not be null这类错误。两个模式的选择原则我很简单日期 精确到天、且数据量不大用 RANGE 更直观需要精确控制行数比如固定取最近的 30 条记录用 ROWS 更稳妥。还有一个特别容易出错的地方当你写了 ORDER BY 但没显式声明帧时PostgreSQL 默认使用的帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——从分区起点到当前行。这个默认值解释了为什么带 ORDER BY 的 SUM会自动变成累计求和它天然地把当前行之前的所有行算进去了。但如果你的 ORDER BY 排序值有重复RANGE 模式会把所有和当前行排序值相同的行一并包含进来这个行为有时会让人措手不及。所以我建议在写累计、移动平均这类查询时显式写明帧边界不要依赖默认值用明确的窗口定义来控制行为。3.2 实战用聚合窗口函数做移动平均分析移动平均在金融数据分析里用得非常频繁。我说一个自己做过的实际案例有一张股票日线行情表stock_daily字段是trade_date, close_price要给每行算出 5 日均价MA5。用窗口函数就是一行的事SELECT trade_date, close_price, AVG(close_price) OVER ( ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) AS ma5 FROM stock_daily WHERE stock_code 000001 ORDER BY trade_date;注意一个有意思的细节第 1 天到第 4 天的 MA5 值是怎么算的 因为ROWS BETWEEN 4 PRECEDING AND CURRENT ROW会向上去找 4 行但前面不够 4 行所以统计时实际只有 1 到 4 行参与平均。也就是说最开始几天的 MA5 其实是一个缩水版的平均值并非完整的 5 日均线。很多做数据分析的同行第一次看到前几天的数据和后面走势对不上就是这个原因。如果业务上要求前 4 天直接显示 NULL比如有些交易软件就是前 4 天没有均线你需要额外加条件判断SELECT trade_date, close_price, CASE WHEN ROW_NUMBER() OVER (ORDER BY trade_date) 5 THEN AVG(close_price) OVER (ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) ELSE NULL END AS ma5 FROM stock_daily ORDER BY trade_date;3.3 动态分组聚合和 GROUP BY 搭配使用窗口函数不排斥普通聚合很多时候你需要把两者结合起来用。比如先按月份 GROUP BY 算出月度总销售额再对连续 3 个月度值做移动平均。这种情况下GROUP BY 先跑窗口函数后跑两者各司其职非常和谐WITH monthly AS ( SELECT TO_CHAR(sale_date, YYYY-MM) AS month, SUM(amount) AS monthly_amount FROM sales GROUP BY 1 ) SELECT month, monthly_amount, AVG(monthly_amount) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3m FROM monthly ORDER BY month;看到没有GROUP BY 负责把明细汇总成月粒度窗口函数负责在月粒度上继续做滑动计算。这是窗口函数和普通聚合的最佳协作模式掌握了你会发现报表 SQL 的层次感一下就出来了。3.4 常见需求计算分组占比与累计占比再来说占比分析。每个部门有多少人每个人占部门总人数的比例是多少这个需求用普通聚合很难写用窗口函数就非常自然SELECT dept_id, employee_id, salary, ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id), 2) AS pct_of_dept FROM employees ORDER BY dept_id, pct_of_dept DESC;这里的SUM(salary) OVER (PARTITION BY dept_id)是一个聚合窗口它计算每个部门的总工资但因为带了 OVER每一行都能拿到这个部门总值。 有了这个总值占比就顺手算出来了。注意我们在除法里用了100.0而不是100这是为了触发浮点数除法避免整数相除直接变成 0 的经典错误。累计占比也就是帕累托分析常用的累计百分比稍微复杂一点需要用到双窗口嵌套WITH dept_salaries AS ( SELECT dept_id, employee_id, salary, SUM(salary) OVER (PARTITION BY dept_id) AS dept_total, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT dept_id, employee_id, salary, dept_total, SUM(salary) OVER ( PARTITION BY dept_id ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) * 100.0 / dept_total AS cumulative_pct FROM dept_salaries ORDER BY dept_id, rn;SUM(salary) OVER (PARTITION BY dept_id ORDER BY rn)计算的是按排名从高到低累计的工资之和除以部门总工资就得到了累计占比。这类分析在做TOP 20% 客户贡献了多少收入时特别好用。4. 高级实战技巧跨行访问与结果复用排名和聚合是窗口函数的两大核心用法但窗口函数的边界远不止于此。如果配合LAG、LEAD、FIRST_VALUE、LAST_VALUE这几个函数你还能做出很多跨行访问的效果——这在处理时间序列、对比分析时是真正的神器。4.1 LAG 和 LEAD访问上/下一行的数据LAG(column, offset, default)用于拿到当前行往前数 offset 行的某个字段值LEAD(column, offset, default)用于拿到当前行往后数 offset 行的某个字段值。默认情况下 offset 是 1default 是 NULL。用它来做环比增长再合适不过SELECT sale_date, amount, LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day_amount, ROUND( (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) * 100.0 / NULLIF(LAG(amount, 1) OVER (ORDER BY sale_date), 0), 2 ) AS daily_growth_pct FROM daily_sales ORDER BY sale_date;这里我用了两个细节都是实际战斗中踩坑总结出来的NULLIF(prev_amount, 0)如果上一天金额是 0直接相除会报division by zero错误。用 NULLIF 把 0 转成 NULL这样结果就是 NULL 而不是报错。LAG 函数写了三遍。如果觉得太啰嗦可以用 CTE 先算出来再引用会清爽很多。LEAD的经典用法是算相邻两行的差值比如计算某个传感器每两次读数之间的变化量或者用于错位比较如果当前行和下一行的类型从 A 变成了 B表示一个事件发生了切换。4.2 FIRST_VALUE 与 LAST_VALUE组内端点的妙用这两个函数用来取分组排序后的第一个值和最后一个值。但注意LAST_VALUE的行为有一个非常经典的陷阱它默认的窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是说最后一个值并不是全组的最后一行而是当前行之前的最后一行——除非你显式声明帧范围。SELECT dept_id, employee_id, salary, FIRST_VALUE(employee_id) OVER w AS top_salary_emp, LAST_VALUE(employee_id) OVER w AS bottom_salary_emp FROM employees WINDOW w AS ( PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING );把 LAST_VALUE 的帧扩展成UNBOUNDED FOLLOWING它才能真正取到整个分区的最后一行。另外这里我还用到了一个比较冷门的 PostgreSQL 语法WINDOW子句。当多个窗口函数复用同一个窗口定义时用WINDOW w AS (...)可以避免重复写一大段 OVER 子句这个细节很多人不知道但能让 SQL 干净很多。在业务中FIRST_VALUE 常用于取每个品类最新入库的价格先按时间排序再取第一个LAST_VALUE 常用于取历史表单中当前有效配置项等。因为有了稳定的帧控制思路会清晰许多。4.3 窗口函数的结果如何复用CTE 与子查询的艺术窗口函数计算出来的结果没法直接在 WHERE 或者同层次的 GROUP BY 里再次引用。 这是一个硬限制也是写窗口函数时最常遇到的报错来源window functions are not allowed in WHERE。所以如果你想取每个分组里排名第一的记录就必须先把窗口计算结果放在子查询或 CTE 里外层再过滤。PostgreSQL 的 WITH 子句天然适合干这件事代码可读性极高WITH ranked AS ( SELECT employee_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT dept_id, employee_id, salary FROM ranked WHERE rn 1;反复嵌套窗口函数也是一样比如我要先算每个用户的月累计消费再算所有用户月累计消费的中位数就得先在 CTE 中算出前者再在下一次窗口计算里套用后者。这种情况一律建议多写几层 CTE而不是一次性写一个一万行的巨型 SQL——那不是技术能力的问题是维护性的灾难。4.4 窗口函数处理时间序列的高级示例同比、环比、累计时间序列分析是个大话题这里我给一个组合示例把前面讲到的多个窗口函数揉在一起比较有代表性。 业务背景计算每个销售员每天的销售额、环比增长、以及累计销售额。WITH daily_data AS ( SELECT salesperson, sale_date, SUM(amount) AS daily_amount FROM sales GROUP BY salesperson, sale_date ), combined AS ( SELECT salesperson, sale_date, daily_amount, LAG(daily_amount) OVER ( PARTITION BY salesperson ORDER BY sale_date ) AS prev_day_amount, SUM(daily_amount) OVER ( PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM daily_data ) SELECT salesperson, sale_date, daily_amount, cumulative_amount, CASE WHEN prev_day_amount IS NULL THEN NULL ELSE ROUND( (daily_amount - prev_day_amount) * 100.0 / NULLIF(prev_day_amount, 0), 2 ) END AS daily_growth_pct FROM combined ORDER BY salesperson, sale_date;这里一开始的 GROUP BY 先把同一天同一个人的多笔订单汇总成一条日粒度记录然后 LAG 计算前一天的值SUM 计算累加值最后在外层算增长率。 整个过程逻辑分层清楚每个窗口函数负责自己的职责互不干扰很能代表实际工作中窗口函数组合拳的写法。5. 性能优化与常见问题排查实录窗口函数好用但它不是没有代价。如果不懂得优化一个看似简单的窗口查询跑在千万级表上可能直接把数据库拖垮。我的经验是窗口函数的性能问题90% 出在排序上。5.1 窗口函数的执行瓶颈PARTITION BY 与 ORDER BY 的排序代价当你写ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC)时PostgreSQL 内部要做两件事先按 dept_id 分区再在每个分区内按 salary 排序。这个分组内排序是非常消耗 CPU 和内存的操作数据规模一大就显形。要优化核心思路是制造有序的数据流。PostgreSQL 的窗口函数执行计划里如果输入的数据已经按照PARTITION BYORDER BY的字段顺序排列好优化器就有可能直接利用这个顺序省掉一次显式排序。具体来说如果分区键和排序列能匹配上索引的最左前缀PostgreSQL 可以直接用索引顺序扫描来避免大量排序。比如CREATE INDEX idx_sales_person_date ON sales(salesperson, sale_date)当窗口查询用PARTITION BY salesperson ORDER BY sale_date时这个索引就派上了大用场。在多个窗口函数共用同一个排序定义时尽量用WINDOW子句把它们合并到同一个排序操作中避免对同一个数据集反复排序。我在实际项目中见过一个典型的慢查询一条 SQL 里面同时用了ROW_NUMBER(),SUM() OVER (...),LAG() OVER (...)分别是三种不同的PARTITION BY/ORDER BY组合结果执行计划里出现了三次独立的排序操作。后来把三个窗口函数合并成同一个窗口定义之后查询时间直接缩短了一半以上。5.2 内存配置work_mem 对窗口函数的影响窗口函数的排序是在内存中进行的如果数据量很大超过work_mem的上限PostgreSQL 会把排序结果溢写到磁盘的临时文件里。一旦触发临时文件查询性能会断崖式下降能从毫秒级退化到秒级甚至分钟级。可以通过查看执行计划来发现问题Sort Method: external merge Disk: 32000kB只要看到external merge或者Disk:字样的 Sort Method就说明排序已经落到磁盘了。这时候有两个方向一是调大work_mem让排序尽量在内存完成二是优化查询本身减少参与排序的数据量比如先过滤掉不必要的大字段只取最后需要的列。调大work_mem也有讲究。它不是全局调一次就万事大吉。如果并发执行多个窗口查询每个查询都可能消耗work_mem大小的内存全局调太大会导致数据库内存耗尽。比较稳妥的做法是只在有压力的大查询里用SET LOCAL work_mem 256MB临时调大跑完自动恢复。5.3 常见报错速查表与解决思路我把多年收集的窗口函数相关报错整理成一张速查表你在写 SQL 时遇到同样的问题可以先对照排查报错信息原因解决办法window functions are not allowed in WHERE在 WHERE 里直接用了窗口函数的结果把窗口函数放到子查询/CTE外层用 WHERE 过滤column xxx must appear in the GROUP BY clause or be used in an aggregate function多次出现 GROUP BY 和窗口函数混用时非聚合字段没在 GROUP BY 中严格区分先 GROUP BY 的结果集与窗口函数作用的列frame starting offset must not be null使用 RANGE 模式时ORDER BY 字段出现重复值改用 ROWS 模式或者保证 ORDER BY 字段唯一division by zero除数是 0常见于算增长率和占比用NULLIF(denominator, 0)保护function row_number() over (order by ...) is not supported部分 PG 版本/工具兼容性问题比如某些 BI 工具生成的 SQL 有怪异语法检查客户端驱动版本或重写为标准窗口语法查询速度非常慢执行计划里面出现Sort Method: external merge Disk排序内存不足调大 work_mem 或优化排序字段5.4 窗口函数与 DISTINCT、GROUP BY 的执行顺序问题最后再讲一个容易踩的坑窗口函数、DISTINCT 和 GROUP BY 的执行顺序。我把 PostgreSQL 的完整执行顺序列出来FROM / JOINWHEREGROUP BYHAVING窗口函数DISTINCTORDER BYLIMIT / OFFSET这个顺序意味着一件事如果你写了SELECT DISTINCT ... ROW_NUMBER() OVER (...)DISTINCT 居然是在窗口函数之后去重的。换句话说DISTINCT 会去重掉窗口函数计算的结果——如果两行数据除了窗口计算结果之外完全相同窗口函数算出来的不同值也可能导致它们被识别为不同的行。举个具体例子SELECT DISTINCT dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY employee_id) AS rn FROM employees;这是一段看起来想去重部门的 SQL但结果不但有每个部门而且每个部门出现了 1 个员工就有几行编号。想去重应该用SELECT DISTINCT dept_id直接去掉窗口函数或者用 GROUP BY dept_id。窗口函数加 DISTINCT两个高级特性叠加不等于更高级的逻辑反而很容易得到意想不到的结果。同样的前面说过窗口函数在 WHERE 之后执行如果你写了WHERE ROW_NUMBER() OVER (...) 1一定会报错。窗口函数的计算结果无法直接参与 WHERE 过滤必须包一层。这个规则不用死记记住窗口函数是最后阶段的计算器就够了。6. 综合案例一张报表里的窗口函数全应用为了把前面的知识点串起来我完整做一个实战项目构建一个月度销售分析报表要求包含每名销售员每月的总销售额、销售排名、环比增长率、年度累计、以及该员工销售额占全部门总销售额的比例。我构造一个假业务销售员属于不同部门每月有多笔订单。第一步先做月粒度汇总CREATE VIEW monthly_sales AS SELECT dept_id, salesperson, TO_CHAR(sale_date, YYYY-MM) AS month, SUM(amount) AS monthly_amount FROM sales GROUP BY dept_id, salesperson, TO_CHAR(sale_date, YYYY-MM);第二步在月粒度汇总基础上完成多个窗口分析WITH base AS ( SELECT dept_id, salesperson, month, monthly_amount, SUM(monthly_amount) OVER ( PARTITION BY dept_id, month ) AS dept_month_total, RANK() OVER ( PARTITION BY dept_id, month ORDER BY monthly_amount DESC ) AS rank_in_dept, LAG(monthly_amount) OVER ( PARTITION BY salesperson ORDER BY month ) AS prev_month_amount, SUM(monthly_amount) OVER ( PARTITION BY salesperson ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM monthly_sales ) SELECT dept_id, salesperson, month, monthly_amount, cumulative_amount, rank_in_dept, ROUND(monthly_amount * 100.0 / dept_month_total, 2) AS pct_of_dept, CASE WHEN prev_month_amount IS NULL THEN NULL ELSE ROUND( (monthly_amount - prev_month_amount) * 100.0 / NULLIF(prev_month_amount, 0), 2 ) END AS mom_growth_pct FROM base ORDER BY month, dept_id, rank_in_dept;一次查询里我们用到了SUM() OVER (PARTITION BY dept_id, month)算部门月度总盘子为占比做分母。RANK() OVER (PARTITION BY dept_id, month ORDER BY monthly_amount DESC)部门内部排名且允许并列。LAG() OVER (PARTITION BY salesperson ORDER BY month)算上个月业绩做环比。SUM() OVER (PARTITION BY salesperson ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)做年度累计。生成的报表一下子就有了四个维度的分析能力看排名看累计看占比看环比。这就是窗口函数组合拳的威力平时写四条 SQL 才能算完的指标现在一条查询全搞定而且结果是在同一份数据快照上计算出来的绝对不会有各个指标数据源不一致的对账问题。7. 写在最后的几点实战心得从入门到熟练我摸索窗口函数最痛苦的一段时间是搞不清它底层怎么跑的。后来我给自己总结了一套理解方法在这里分享给大家写窗口函数之前先在脑子里过一遍“先分组再排序再开窗最后聚合”这四步。还有一个习惯值得养成写完窗口 SQL 后执行EXPLAIN ANALYZE强制自己看一遍执行计划。窗口函数性能出问题执行计划里一眼就能看到低效的排序操作。别等生产环境卡死了才去排查。最后再多说一句关于学习和踩坑的建议别只练排名这一个功能。窗口函数最有含金量的用法集中在聚合分析——移动平均、累计求和、占比、同环比这些。你只要把SUM() OVER()、LAG()、ROWS BETWEEN这三个组合搞熟练日常 80% 的分析需求都能信手拈来。真正把窗口函数玩熟的人写出来的 SQL 通常短小精悍又逻辑清晰一眼就能看出设计意图而不是堆一坨让人头皮发麻的子查询。希望这篇文章能帮你把这条路走得比我当初更顺一些。