MySQL窗口函数详解与应用

### 1. 应用场景

窗口函数适用于多种复杂的查询需求，例如：
- 如何计算患者上次就诊距今的时间？
- 如何计算环比增长率？
- 如何获取每个部门薪资排名前N的员工列表？
- 如何计算每个部门每人的薪资占部门总薪资的百分比？

对于这些需求，使用传统的SQL语句实现起来较为复杂，通常需要编写冗长的自连接SQL，不仅效率低下，而且难以维护。使用窗口函数可以显著简化这些查询。

### 2. 窗口函数简介

MySQL从8.0版本开始支持窗口函数，这一功能在大多数商业数据库和部分开源数据库中早已存在，有时也称为分析函数。

#### 什么是窗口？

窗口可以理解为一组记录的集合，窗口函数是在满足特定条件的记录集合上执行的特殊函数。对于每条记录，窗口函数都会在这个窗口内执行，有的函数窗口大小固定（静态窗口），有的函数窗口大小随记录变化（滑动窗口）。

#### 窗口函数与普通聚合函数的区别

- **聚合函数**：将多条记录聚合为一条记录。
- **窗口函数**：每条记录都会执行，最终结果仍然是多条记录。
- **聚合函数可以用于窗口函数**：例如，可以在窗口内计算动态的聚合值。

### 3. 窗口函数的基本用法

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

```sql
函数名([expr]) OVER (窗口子句)
```

其中，`OVER` 是关键字，用于指定函数执行的窗口范围。窗口子句可以包含以下部分：

- **窗口名称**：给窗口指定一个别名，使SQL更清晰易读。
- **PARTITION BY 子句**：按指定字段分组，窗口函数在不同的分组上分别执行。
- **ORDER BY 子句**：按指定字段排序，窗口函数按排序后的记录顺序执行。
- **FRAME 子句**：定义窗口的子集，通常用于滑动窗口。

### 4. 常见窗口函数应用

#### 4.1 序号函数

- **ROW_NUMBER()**：生成唯一序号。
- **RANK()**：生成带有并列序号的排名。
- **DENSE_RANK()**：生成不带空缺序号的排名。

##### 示例：查询每个用户订单金额最高的前三个订单

```sql
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY user_no ORDER BY amount DESC) AS row_num,
order_id, user_no, amount, create_date
FROM order_tb
) AS t
WHERE t.row_num <= 3;
```

#### 4.2 分布函数

- **PERCENT_RANK()**：计算当前行的百分位数。
- **CUME_DIST()**：计算当前行的累积分布。

##### 示例：计算订单金额的百分位数

```sql
SELECT t.* FROM (
SELECT RANK() OVER w AS Rank_num,
PERCENT_RANK() OVER w AS percent,
order_id, user_no, amount
FROM order_tb
WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC)
) AS t;
```

#### 4.3 前后函数

- **LAG(字段名, n)**：获取当前行前n行的值。
- **LEAD(字段名, n)**：获取当前行后n行的值。

##### 示例：查询上一个订单距离当前订单的时间间隔

```sql
SELECT order_id, user_no, amount, create_date, last_date, DATEDIFF(create_date, last_date) AS diff
FROM (
SELECT order_id, user_no, amount, create_date,
LAG(create_date, 1) OVER w AS last_date
FROM order_tb
WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
) AS t;
```

#### 4.4 头尾函数

- **FIRST_VALUE(expr)**：获取分区中的第一个值。
- **LAST_VALUE(expr)**：获取分区中的最后一个值。

##### 示例：查询截止到当前订单，按照日期排序的第一个订单和最后一个订单的订单金额

```sql
SELECT * FROM (
SELECT order_id, user_no, amount, create_date,
FIRST_VALUE(amount) OVER w AS first_amount,
LAST_VALUE(amount) OVER w AS last_amount
FROM order_tb
WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
) AS t;
```

#### 4.5 其他函数

- **NTH_VALUE(expr, n)**：获取窗口中第n个值。
- **NTILE(n)**：将分区中的有序数据分为n个桶。

##### 示例：将每个用户的订单按照订单金额分成3组

```sql
SELECT * FROM (
SELECT NTILE(3) OVER w AS nf,
order_id, user_no, amount, create_date
FROM order_tb
WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC)
) AS t;
```

#### 4.6 聚合函数作为窗口函数

聚合函数（如SUM、AVG、MAX、MIN、COUNT）可以在窗口中每条记录上动态应用，计算指定窗口内的聚合值。

##### 示例：计算每个用户截止到当前订单的累计订单金额、平均订单金额、最大订单金额、最小订单金额和订单数

```sql
SELECT * FROM (
SELECT order_id, user_no, amount, create_date,
SUM(amount) OVER w AS sum1,
AVG(amount) OVER w AS avg1,
MAX(amount) OVER w AS max1,
MIN(amount) OVER w AS min1,
COUNT(amount) OVER w AS count1
FROM order_tb
WINDOW w AS (PARTITION BY user_no ORDER BY order_id)
) AS t;
```

### 5. 总结

窗口函数在处理复杂查询时非常强大，可以显著简化SQL语句，提高查询效率。通过本文的介绍，希望读者能够更好地理解和应用窗口函数，提升SQL查询能力。