# 窗口函数

URL: https://caijiao.org/postgresql/advanced/advanced-query/window
Source: docs/postgresql/advanced/advanced-query/window.md
Description: 介绍 PostgreSQL 中的窗口函数，帮助开发者在查询中进行基于窗口的计算，如排名、聚合、分区等操作，以提升查询的灵活性与复杂性。

窗口函数是 PostgreSQL 中的一个强大特性，允许开发者在查询结果集的“窗口”内进行操作。与聚合函数不同，窗口函数不对结果进行分组，而是在整个查询的结果集或特定分区内进行计算。常用于排名、累计和分析等任务。

## 窗口函数的基本语法

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

```sql
SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column)
FROM table_name;
````

* `window_function()` 是要应用的窗口函数。
* `PARTITION BY` 用于将数据分区，不同分区的数据会分别进行窗口函数计算。
* `ORDER BY` 用于定义窗口内的排序顺序。

## 常见的窗口函数

### 排名函数

排名函数用于在分区内为每一行分配一个排名。常见的排名函数包括：

* `ROW_NUMBER()`：为结果集中的每一行分配一个唯一的排名。
* `RANK()`：为结果集中的每一行分配一个排名，但若有重复的值，则排名会跳过。
* `DENSE_RANK()`：与 `RANK()` 类似，但排名不会跳过。

#### 示例：使用 `ROW_NUMBER()`

```sql
SELECT name, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
```

#### 示例：使用 `RANK()`

```sql
SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
```

#### 示例：使用 `DENSE_RANK()`

```sql
SELECT name, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
```

### 聚合函数

窗口聚合函数用于在窗口内进行聚合计算，不会将结果分组。常见的聚合函数包括：

* `SUM()`：计算窗口内的总和。
* `AVG()`：计算窗口内的平均值。
* `COUNT()`：计算窗口内的行数。
* `MIN()`、`MAX()`：计算窗口内的最小值和最大值。

#### 示例：使用 `SUM()`

```sql
SELECT name, salary, 
       SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM employees;
```

### 窗口函数的帧规范

窗口函数可以定义不同的帧规范来控制计算范围，主要的帧规范包括：

* `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`：从当前行的前面所有行到当前行。
* `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`：从当前行到所有后续行。
* `ROWS BETWEEN x PRECEDING AND x FOLLOWING`：指定前后多少行作为窗口。

#### 示例：使用 `SUM()` 和帧规范

```sql
SELECT name, salary, 
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;
```

### 窗口函数与排序

排序在窗口函数中非常重要，它决定了数据如何被窗口函数处理。通过 `ORDER BY` 子句来指定排序规则。

#### 示例：使用 `ORDER BY` 排序

```sql
SELECT name, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
```

## 复杂查询中的窗口函数应用

### 示例：计算部门内员工的平均工资

```sql
SELECT name, department_id, salary, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;
```

### 示例：计算每个员工的工资在其部门中的百分位

```sql
SELECT name, department_id, salary, 
       PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS salary_percentile
FROM employees;
```

## 窗口函数与分区

`PARTITION BY` 用于将数据划分为不同的“分区”，窗口函数将在每个分区内单独计算。分区可以基于一个或多个列来划分数据。

#### 示例：按部门分区，计算每个员工的工资排名

```sql
SELECT name, department_id, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS department_rank
FROM employees;
```

## 窗口函数与联接

窗口函数也可以与联接一起使用，可以在联接后的结果集中进行计算。

#### 示例：计算每个员工的薪资和部门内平均薪资差异

```sql
SELECT e.name, e.department_id, e.salary, 
       AVG(e.salary) OVER (PARTITION BY e.department_id) AS avg_salary_in_department,
       e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id) AS salary_difference
FROM employees e;
```

## 小结

窗口函数是 PostgreSQL 中强大而灵活的功能，能够帮助开发者在查询中进行更为复杂的计算和分析。通过使用窗口函数，可以轻松地实现排名、聚合、累计等操作，而无需改变数据的结构或进行多次查询。掌握窗口函数的使用，可以极大地提升数据处理的效率与灵活性。