PostgreSQL窗口函数在报表统计中的高效用法

报表统计中最让人头疼的场景，不是单表求和，也不是简单的分组计数，而是那些需要同时看到明细和汇总、需要排名、需要同比环比、需要累计值的混合需求。很多人第一反应是写多层子查询，或者把数据拉到应用层用代码循环处理。这两种方案在数据量小的时候能跑，一旦数据量上到百万级，执行时间从秒变成分钟，内存占用飙升，整个报表接口直接超时。

PostgreSQL的窗口函数就是专门解决这类问题的。它不是简单的语法糖，而是一套基于数据集分区和排序的计算框架，能让你在一条SQL里完成原本需要多次扫描表的复杂统计。窗口函数的核心思路是：在不改变结果集行数的情况下，为每一行计算一个基于窗口范围的聚合值或排名值。

窗口函数的语法看起来比普通聚合函数多了一个OVER子句，这个OVER就是定义窗口的地方。最简形式是这样：

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

这条语句会返回每个员工的薪资，同时在每一行上附带该员工所在部门的平均薪资。结果集的行数等于employees表的行数，没有发生任何聚合压缩。PARTITION BY把数据按部门切分成独立的计算区域，每个区域内的AVG互不干扰。

如果把PARTITION BY去掉，窗口就会覆盖整个结果集：

SELECT 
    employee_name,
    salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

每一行都会显示全公司的薪资总和。这个看似简单的操作，如果用子查询实现，需要额外扫描一次表，而窗口函数可以在一次扫描中完成。

窗口函数里的ORDER BY和查询最外层的ORDER BY是完全独立的两套逻辑。窗口内的ORDER BY定义的是数据在窗口中的处理顺序，它直接决定了累计类函数的行为。看这个例子：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day
FROM daily_sales;

第一个累计值running_total是按日期顺序的累计销售额，每一行显示从最早日期到当前日期的总和。第二个rolling_7day是滚动7天窗口，只包含当前行和之前6行。这里ROWS BETWEEN定义了窗口的物理范围，用行数来界定。如果没有这个子句，默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是从分区开始到当前行的所有行。

RANGE和ROWS的区别很微妙但影响巨大。ROWS按物理行数计算，RANGE按值的范围计算。如果sale_date有重复日期，RANGE会把同一天的所有行视为一个逻辑单位，而ROWS严格按行号处理。报表统计中处理时间序列数据时，这个差异经常导致数据对不上，排查起来很痛苦。

PostgreSQL提供了ROW_NUMBER、RANK、DENSE_RANK和NTILE四个排名函数，每个的适用场景完全不同。ROW_NUMBER给每一行分配唯一序号，即使排序字段值相同也不会并列；RANK在值相同时给出相同排名，但会跳过后续序号；DENSE_RANK同样给相同值相同排名，但不跳过序号。

SELECT 
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num,
    RANK() OVER (ORDER BY sales_amount DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_num
FROM product_sales;

假设前三名销售额分别是100万、100万、90万。ROW_NUMBER会给1、2、3；RANK会给1、1、3；DENSE_RANK会给1、1、2。报表里如果要显示"第几名"，用DENSE_RANK最符合直觉；如果要取"前N条记录"，用ROW_NUMBER加WHERE条件最稳妥，不会因为并列导致数量超出预期。

NTILE则用来把数据均匀分成指定数量的桶，在做分层分析时特别有用。比如把客户按消费金额分成高、中、低三档：

SELECT 
    customer_id,
    total_spent,
    NTILE(3) OVER (ORDER BY total_spent DESC) AS customer_tier
FROM customer_summary;

LAG和LEAD是报表统计中实现环比计算的最直接工具。LAG取当前行之前第N行的值，LEAD取之后第N行的值。月度销售额环比增长率可以这样算：

SELECT 
    month,
    monthly_sales,
    LAG(monthly_sales, 1) OVER (ORDER BY month) AS prev_month_sales,
    ROUND((monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY month)) * 100.0 / 
          LAG(monthly_sales, 1) OVER (ORDER BY month), 2) AS mom_growth_pct
FROM monthly_sales_summary;

同比计算同理，把偏移量从1改成12即可。需要注意的是，LAG和LEAD在没有对应行时会返回NULL，这个特性可以用来判断数据连续性。如果某个月份数据缺失，prev_month_sales为NULL，环比增长率自然也是NULL，不会产生错误计算。

FIRST_VALUE、LAST_VALUE和NTH_VALUE这三个函数则用来获取窗口内特定位置的值。LAST_VALUE有个容易踩的坑：默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，所以LAST_VALUE取到的永远是当前行的值。要真正取到分区内最后一行，必须显式指定窗口范围：

SELECT 
    department,
    employee_name,
    hire_date,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM employees;

窗口函数和GROUP BY可以共存于同一条查询中，执行顺序是先GROUP BY聚合，然后窗口函数在聚合结果上计算。这个特性让很多复杂报表变得异常简洁。比如要同时显示每个部门的总薪资、部门内员工薪资排名、以及该员工薪资占部门总薪资的比例：

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 2) AS salary_pct
FROM employees
ORDER BY department, salary DESC;

一条SQL完成了明细展示、汇总计算、排名分析和占比计算，执行计划只扫描一次employees表。如果用传统子查询方式，至少要扫描三次。

PostgreSQL支持在窗口函数的聚合中使用FILTER子句，这让条件性聚合在窗口层面也能实现。比如同时统计每个部门的薪资总额和仅限高级别员工的薪资总额：

SELECT 
    department,
    employee_name,
    salary,
    level,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    SUM(salary) FILTER (WHERE level = 'Senior') OVER (PARTITION BY department) AS senior_total
FROM employees;

这个功能在制作需要分类汇总的报表时极其高效，避免了CASE WHEN嵌套或者多次JOIN。

当多个窗口函数使用相同的分区和排序规则时，可以用WINDOW子句给窗口命名，避免重复书写：

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER dept_window AS dept_avg,
    MIN(salary) OVER dept_window AS dept_min,
    MAX(salary) OVER dept_window AS dept_max,
    COUNT(*) OVER dept_window AS dept_count
FROM employees
WINDOW dept_window AS (PARTITION BY department ORDER BY salary DESC);

窗口定义还可以基于已有窗口进一步限定，形成窗口链：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER monthly_window AS month_total,
    SUM(amount) OVER (monthly_window ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_rolling
FROM daily_sales
WINDOW monthly_window AS (PARTITION BY date_trunc('month', sale_date) ORDER BY sale_date);

窗口函数的性能核心在于排序操作。PARTITION BY和ORDER BY的组合决定了PostgreSQL需要如何组织数据。如果查询中多个窗口函数使用相同的PARTITION BY和ORDER BY，数据库可以复用同一次排序结果，执行效率显著提升。这就是为什么推荐使用WINDOW子句统一声明窗口定义。

索引对窗口函数的影响和普通查询不同。如果WHERE条件过滤掉了大部分数据，在过滤列上建索引依然有效。但窗口函数内部的ORDER BY通常需要额外排序，除非数据恰好按那个顺序物理存储。对于大数据量的窗口计算，work_mem参数的设置很关键，增大work_mem可以让排序操作在内存中完成，避免磁盘溢出。

另一个容易忽视的优化点是窗口范围的定义。ROWS BETWEEN比RANGE BETWEEN的计算开销小很多，因为ROWS只需要计数，RANGE需要比较值。如果业务逻辑允许用ROWS替代RANGE，优先选择ROWS。

假设需要生成一份销售综合报表，包含以下指标：每个产品的销售额、销售额排名、销售额占全品类比例、月度累计销售额、环比增长率、以及滚动3个月平均销售额。用窗口函数一条SQL搞定：

WITH monthly_product_sales AS (
    SELECT 
        date_trunc('month', sale_date) AS sale_month,
        product_id,
        SUM(amount) AS monthly_amount
    FROM sales
    GROUP BY date_trunc('month', sale_date), product_id
)
SELECT 
    sale_month,
    product_id,
    monthly_amount,
    RANK() OVER (PARTITION BY sale_month ORDER BY monthly_amount DESC) AS monthly_rank,
    ROUND(monthly_amount * 100.0 / SUM(monthly_amount) OVER (PARTITION BY sale_month), 2) AS pct_of_month,
    SUM(monthly_amount) OVER (PARTITION BY product_id ORDER BY sale_month) AS ytd_cumulative,
    ROUND((monthly_amount - LAG(monthly_amount, 1) OVER (PARTITION BY product_id ORDER BY sale_month)) * 100.0 / 
          LAG(monthly_amount, 1) OVER (PARTITION BY product_id ORDER BY sale_month), 2) AS mom_growth,
    AVG(monthly_amount) OVER (PARTITION BY product_id ORDER BY sale_month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3month_avg
FROM monthly_product_sales
ORDER BY sale_month, monthly_rank;

这个查询先用CTE做月度聚合，然后在一个SELECT中完成排名、占比、累计、环比、滚动平均五项计算。执行计划中，排序操作只发生一次，所有窗口函数复用同一排序结果。数据量在千万级别时，响应时间依然可以控制在秒级。

窗口函数真正强大的地方在于它改变了我们思考SQL的方式。以前遇到复杂统计需求，第一反应是拆成多步查询或者写存储过程。现在可以直接用声明式的方式描述计算逻辑，数据库优化器会自动选择最高效的执行路径。掌握窗口函数不是可选项，而是PostgreSQL高效开发的必备技能。