窗口函数简介

窗口函数允许你针对与当前行相关的一组行进行计算，而无需像 GROUP BY 那样将这些行合并为单一的输出结果。这意味着你在保留原始行级明细的同时，还能并列获得聚合、排名和对比结果。常见的应用场景包括：按区域对销量最高的产品进行排名、为财务报表计算累计汇总，以及计算随时间变化的移动平均值。其核心语法是将聚合函数与 OVER 子句结合使用，OVER 子句用于定义"窗口"内行集的划分方式。

基本语法与核心函数

窗口函数的调用方式与普通聚合函数类似，只是在末尾附加了 OVER 子句。在 OVER 内部，你可以定义 PARTITION BY（如何将行分组到窗口中）、ORDER BY（窗口内如何排序），以及可选的 frame 子句，例如 ROWS BETWEEN。最常用的窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM、AVG 和 COUNT —— 根据上下文不同，它们既可作为聚合函数使用，也可作为导航函数使用。

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER 在每个分区内分配一个唯一的顺序编号，当分区发生变化时从 1 重新开始。RANK 和 DENSE_RANK 行为类似，但对并列值会分配相同的编号，二者在处理并列值之后产生的跳号问题上有所不同。

排名与 Top-N 查询

排名是最常见的实际应用之一。假设你需要获取每个部门薪资最高的前 3 名员工。你可以计算一个排名列，然后对其进行过滤，可以使用子查询，也可以使用 CTE 以便代码更清晰。这种模式比相关子查询效率高得多，并且在应用代码中阅读起来非常自然。

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

注意 PARTITION BY department 会为每个部门重新开始排名，因此即使出现薪资并列的情况，每个分组也始终恰好会选出 3 名胜出者。

累计总计与移动窗口

累计总计和滚动平均值需要在 OVER 子句中定义窗口框架。`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` 可以计算从分区起始到当前行的累计求和。将窗口切换为固定范围（如 `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`）则可得到 7 行的移动平均值，这在时间序列分析中非常常见。

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

第一列单调递增，而移动平均值则能平滑掉日常波动，呈现出底层规律。窗口框架可以使用 `ROWS`（按物理行数）或 `RANGE`（按逻辑值），但在绝大多数报表需求中，`ROWS` 性能更优、行为也更可预测。

常见陷阱与性能优化技巧

一个常见错误是忽略窗口函数是在 `WHERE`、`HAVING` 和 `GROUP BY` 之后才执行的——因此无法在同一个查询中直接对窗口函数的结果进行过滤，除非借助 CTE 或子查询。另一个陷阱是把窗口函数与 `SELECT DISTINCT`混用，或以干扰规划器的方式对结果集进行聚合。性能方面，务必为 `PARTITION BY` 和 `ORDER BY` 中使用的列建立索引；在不需要基于值的窗口时，优先选择 `ROWS` 而非 `RANGE`。避免在 `WHERE` 子句中使用窗口函数，应先将查询包装为 CTE。

总结

在 PostgreSQL 中，只要需要在保留行级明细的同时进行分组计算，窗口函数就是最合适的工具。`OVER` 子句配合 `PARTITION BY` 和 `ORDER BY` 能让你完全掌控窗口的定义方式，而框架子句则进一步将这种控制力扩展到滚动计算。掌握排名、累计总计和移动平均值这三类用法，几乎可以覆盖后端开发者在实际分析查询中 80% 的场景。