MySQL的窗口函数是一种在查询结果集的特定“窗口”上执行计算的高级功能，能够在不合并行的前提下实现复杂分析。以下是详细介绍：
1. 基本概念
- 窗口函数：对一组相关行（窗口）进行计算，并为每行返回结果，不减少原行数。
- 与聚合函数的区别：普通聚合函数（如
SUM、AVG）合并多行为单行，而窗口函数保留所有行，附加计算结果。
2. 核心语法
函数名([参数]) OVER (
[PARTITION BY 分组字段]
[ORDER BY 排序字段]
[ROWS|RANGE 窗口框架]
)
- PARTITION BY：定义窗口的分组（类似
GROUP BY，但不合并行）。 - ORDER BY：指定窗口内排序方式。
- ROWS|RANGE：定义计算范围（如
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW）。
3. 常用窗口函数
1). 排名函数
- ROW_NUMBER()：连续唯一排名（无重复）。
- RANK()：允许并列，后续名次跳过（如1,1,3）。
- DENSE_RANK()：允许并列，后续名次连续（如1,1,2）。
示例：按部门工资排名
SELECT
name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
2). 聚合函数
- SUM()、AVG()、MAX()、MIN() 等可结合窗口使用。
示例：计算部门累计工资
SELECT
name, hire_date, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_salary
FROM employees;
3). 分布函数
- NTILE(n)：将数据分为
n个桶，分配桶编号。
示例：将工资分为4档
SELECT
name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
4). 前后行函数
- LAG(字段, n)：取当前行前第
n行的值。 - LEAD(字段, n)：取当前行后第
n行的值。
示例：比较当前与前一行的工资
SELECT
name, salary,
LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
salary - LAG(salary) OVER (ORDER BY hire_date) AS diff
FROM employees;
4. 窗口框架
定义计算范围，常用选项：
- ROWS BETWEEN ... AND ...
UNBOUNDED PRECEDING：窗口开始。CURRENT ROW：当前行。n PRECEDING/n FOLLOWING：前/后n行。
示例：计算3行移动平均
SELECT
date, revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;
5. 使用场景
- 排名/分组排名：如部门内工资排名。
- 累计计算：如每月累计销售额。
- 趋势分析：如移动平均、环比/同比计算。
- 数据分桶：如按分数划分等级。
6. 注意事项
- 执行顺序：窗口函数在
WHERE、GROUP BY之后执行，不能在WHERE中直接使用。 - 性能：大数据量时需注意效率，合理使用索引和分区。
- 默认框架：若指定
ORDER BY，默认范围为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
7.示例
185. 部门工资前三高的所有员工
表: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentId 是 Department 表中 ID 的外键（reference 列）。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门ID和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
select
t.Department
,t.Employee
,t.Salary
from (
select
em.name as Employee
,em.Salary
,de.name as Department
,DENSE_RANK() over(
partition by de.id
order by salary desc
) as rn
from Employee em
left join Department de on em.departmentId = de.id
) t
where t.rn<=3
子查询中DENSE_RANK是允许并列，后续名次连续（如1,1,2）。 子查询的输出结果为
| Employee | Salary | Department | rn |
|---|---|---|---|
| Max | 90000 | IT | 1 |
| Joe | 85000 | IT | 2 |
| Randy | 85000 | IT | 2 |
| Will | 70000 | IT | 3 |
| Janet | 69000 | IT | 4 |
| Henry | 80000 | Sales | 1 |
| Sam | 60000 | Sales | 2 |
外层查询再指定rn即可。
585. 2016年的投资
Insurance 表：
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| pid | int |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
+-------------+-------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
- 他在 2015 年的投保额 (
tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。 - 他所在的城市必须与其他投保人都不同（也就是说 (
lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。
思路：
使用窗口函数 COUNT(*) OVER (PARTITION BY tiv_2015) 统计每个投保人在2015年的投保金额出现的次数。如果次数大于等于2，说明至少有一个其他投保人具有相同的2015年投保金额。
使用窗口函数 COUNT(*) OVER (PARTITION BY lat, lon) 统计每个经纬度组合出现的次数。如果次数等于1，说明该经纬度组合是唯一的。
select
round(sum(tiv_2016),2) as tiv_2016
from
(select
*
,count(*) over(partition by tiv_2015) as cnt_tiv_2015
,count(*) over(partition by lat,lon) as cnt_pos
from Insurance
) t
where t.cnt_tiv_2015>=2
and t.cnt_pos =1
