窗口函数特性

窗口函数（Window Functions）

窗口函数是 PostgreSQL 的一大亮点，它允许你在查询结果中对一组行进行计算，而不需要将结果分组（不像 GROUP BY 那样会减少结果集的行数）。MySQL 也支持窗口函数，但 PostgreSQL 的实现更强大，功能更丰富。

---

窗口函数的核心功能

窗口函数的典型用途包括：

1. 排名计算：如 RANK()、DENSE_RANK()、ROW_NUMBER()。
2. 运行总计：如 SUM()、AVG() 等聚合函数的窗口版本。
3. 移动平均：如计算滚动窗口的平均值。
4. 分区内操作：如每个分组内的最大值、最小值等。

---

PostgreSQL 窗口函数的语法

窗口函数的基本语法如下：

SELECT
    column1,
    column2,
    window_function() OVER (
        PARTITION BY columnX
        ORDER BY columnY
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS alias_name
FROM table_name;

• PARTITION BY：将数据按某列分组，但不会减少行数。
• ORDER BY：定义窗口内的排序规则。
• ROWS BETWEEN：定义窗口的范围。

---

PostgreSQL 窗口函数的优势

1. 灵活的窗口范围定义：
   PostgreSQL 支持复杂的窗口范围定义，比如：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从窗口开始到当前行）。
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING（当前行的前后各一行）。
  MySQL 的窗口范围定义相对简单，灵活性不如 PostgreSQL。

1. 支持更多函数：
   PostgreSQL 提供了丰富的窗口函数，比如：

• NTILE(n)：将结果分成 n 个桶。
• LEAD() 和 LAG()：访问当前行前后任意偏移的行。
• FIRST_VALUE() 和 LAST_VALUE()：获取窗口内的第一行或最后一行的值。

1. 性能优化：
   PostgreSQL 对窗口函数的执行有更好的优化，尤其是在处理大数据集时，性能表现更优。

---

示例：排名与累计总和

假设我们有一个销售表 sales，包含以下数据：

| salesperson | region | sales |
|-------------|--------|-------|
| Alice | East | 500 |
| Bob | East | 300 |
| Charlie | West | 700 |
| David | West | 400 |

我们希望：

1. 按区域（region）对销售额（sales）进行排名。
2. 计算每个区域的累计销售额。

在 PostgreSQL 中可以这样写：

SELECT
    salesperson,
    region,
    sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank,
    SUM(sales) OVER (PARTITION BY region ORDER BY sales DESC) AS cumulative_sales
FROM sales;

结果：

| salesperson | region | sales | rank | cumulative_sales |
|-------------|--------|-------|------|------------------|
| Alice | East | 500 | 1 | 500 |
| Bob | East | 300 | 2 | 800 |
| Charlie | West | 700 | 1 | 700 |
| David | West | 400 | 2 | 1100 |

---

MySQL 的局限性

虽然 MySQL 也支持窗口函数，但在以下方面不如 PostgreSQL：

1. 窗口范围的灵活性：MySQL 不支持复杂的窗口范围定义，比如 ROWS BETWEEN 的高级用法。
2. 函数支持的丰富性：MySQL 的窗口函数种类较少，比如 NTILE() 等函数在 MySQL 中不支持。
3. 性能优化：在大数据集上，PostgreSQL 的窗口函数性能通常优于 MySQL。