mysql开窗函数

### MySQL 开窗函数使用教程与示例 开窗函数是MySQL 8.0版本引入的重要特性之一，允许在查询中执行复杂的分析操作[^1]。以下是关于开窗函数的详细说明和具体示例。 #### 1. 基本语法 开窗函数的基本语法结构如下： ```sql <开窗函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序列清单>) ``` 其中： - `<开窗函数>` 是具体的函数名称。 - `PARTITION BY` 定义了分组逻辑，类似于 `GROUP BY`，但不会减少行数。 - `ORDER BY` 定义了窗口内数据的排序方式。 #### 2. 序号函数 序号函数用于生成基于排序规则的行号或排名。 - **row_number()**：为每一行生成唯一的行号。 ```sql SELECT *, row_number() OVER (ORDER BY price DESC) AS row_num FROM product; ``` 此查询将根据 `price` 列降序排列，并为每行分配一个唯一的行号[^2]。 - **rank()**：为每一行生成排名，相同值会共享相同的排名，且跳过后续的排名。 ```sql SELECT *, rank() OVER (ORDER BY price DESC) AS ranking FROM product; ``` - **dense_rank()**：与 `rank()` 类似，但不会跳过排名。 ```sql SELECT *, dense_rank() OVER (ORDER BY price DESC) AS dense_ranking FROM product; ``` #### 3. 聚合函数 聚合函数可以结合开窗函数使用，从而在不减少行数的情况下进行分组计算。 - **sum()**：计算累积和。 ```sql SELECT name, dt, cnt, sum(cnt) OVER (PARTITION BY name ORDER BY dt) AS cnt_all FROM linux; ``` 上述查询以 `name` 分组，并按日期 `dt` 排序，计算每个用户每天启动次数的累积总和[^3]。 - **avg()**：计算累积平均值。 ```sql SELECT name, dt, cnt, avg(cnt) OVER (PARTITION BY name ORDER BY dt) AS avg_cnt FROM linux; ``` #### 4. 分布函数 分布函数用于计算相对位置或分布比例。 - **percent_rank()**：计算当前行在窗口中的百分比排名。 ```sql SELECT *, percent_rank() OVER (ORDER BY price DESC) AS percent_ranking FROM product; ``` - **cume_dist()**：计算当前行及之前所有行占窗口的比例。 ```sql SELECT *, cume_dist() OVER (ORDER BY price DESC) AS cume_distribution FROM product; ``` #### 5. 前后函数 前后函数用于访问当前行前后的值。 - **lag()**：获取上一行的值。 ```sql SELECT name, dt, cnt, lag(cnt) OVER (PARTITION BY name ORDER BY dt) AS prev_cnt FROM linux; ``` - **lead()**：获取下一行的值。 ```sql SELECT name, dt, cnt, lead(cnt) OVER (PARTITION BY name ORDER BY dt) AS next_cnt FROM linux; ``` #### 6. 头尾函数 头尾函数用于获取窗口内的首尾值。 - **first_value()**：获取窗口中的第一个值。 ```sql SELECT *, first_value(price) OVER (ORDER BY price DESC) AS first_price FROM product; ``` - **last_value()**：获取窗口中的最后一个值（需指定范围）。 ```sql SELECT *, last_value(price) OVER (ORDER BY price DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price FROM product; ``` ### 注意事项 - 确保 MySQL 版本为 8.0 或更高版本。 - 开窗函数的性能可能受数据量影响，建议合理使用索引优化查询。