SQL开窗函数
文章目录
- 开窗函数
- 简介
- 为什么使用开窗函数
- 使用开窗函数的经典场景
- 场景1：排名
- 场景2：累计计算
- 场景3：移动平均/移动总计
- 场景4：比较相邻行
- 场景5：计算占比
- 总结
开窗函数
简介
SQL的开窗函数是MYSQL在8版本后提供用来简化SQL语句的编写的一个方法
标志词是over()
它与我们的聚合函数类似
但是又不一致
聚合函数是在group by会分成几组，会压缩我们原本的数据信息
而我们的聚合函数可以直接在我们原本的数据行上添加一列我们想知道的信息
select*,avg(sal)over(partitionbyjob)fromemployee;avg(sal) over(partition by job) 前一部分是运算的开窗函数，后一部分是进行数据处理的分区
这个语句是可以使用我们的 聚合函数+子查询进行复现的
SELECTe.*,e1.avg_salAS"avg(sal) over(partition by job)"FROMemployee eJOIN(SELECTjob,AVG(sal)ASavg_salFROMemployeeGROUPBYjob)e1ONe.job=e1.job;那下面这条语句就很难简单的去实现了
累加值就是这个日期前的累加在一起
像这种以明细的方式进行累加是我们开窗函数的典型应用场景
！！！
sum(sal) over (partition by dname order by hiredate desc)
解释一下：就是按照dname进行分区嘛，然后分区里面根据hiredate 倒序排序
重要的是sum(sal)只会计算你当前和前面的累加数据，相当于是一个过程
而不是直接将所有工资一sum()就赋值给每个数据了
为什么使用开窗函数
使用开窗函数的经典场景
场景1：排名
需求：对员工按工资进行排名，展示不同排名函数的区别。
假设员工表employees只有四个字段：id（员工ID）、name（姓名）、salary（工资）、job(职业)。数据如下：
| id | name | salary | job |
|---|---|---|---|
| 1 | Alice | 8000 | doctor |
| 2 | Bob | 7000 | doctor |
| 3 | Carol | 9000 | doctor |
| 4 | David | 7000 | joker |
| 5 | Eve | 8500 | joker |
SELECTid,name,salary,job ROW_NUMBER()OVER(partitionbyjobORDERBYsalaryDESC)ASrow_number_rank,RANK()OVER(partitionbyjobORDERBYsalaryDESC)ASrank,DENSE_RANK()OVER(partitionbyjobORDERBYsalaryDESC)ASdense_rankFROMemployees;结果：
| id | name | salary | job | row_number_rank | rank | dense_rank |
|---|---|---|---|---|---|---|
| 3 | Carol | 9000 | doctor | 1 | 1 | 1 |
| 1 | Alice | 8000 | doctor | 2 | 2 | 2 |
| 2 | Bob | 7000 | doctor | 3 | 3 | 3 |
| 5 | Eve | 8500 | joker | 1 | 1 | 1 |
| 4 | David | 7000 | joker | 2 | 2 | 2 |
- 注意：分组生效：可以看到 Doctor 和 Joker 是分开排名的，各自的最高工资都是第 1 名。
ROW_NUMBER ()：即使 Bob 和 David 工资相同（7000），它也会强制给出一个序号（3 和 4），就像排队一样，一人一个号，不并列。
排名函数区别：
ROW_NUMBER()：无论值是否相同，都按顺序分配唯一的连续整数（1,2,3,…）。RANK()：相同值的行排名相同，但会跳过并列占用的名次。例如：1,2,2,4（两个并列第二后，下一个是第四名）。DENSE_RANK()：相同值的行排名相同，且排名数字连续。例如：1,2,2,3（两个并列第二后，下一个是第三名）。
场景2：累计计算
需求：计算每个月的销售额累计值。
假设销售表sales只有两个字段：month（月份，格式为’YYYY-MM’）、amount（销售额）。数据如下：
| month | amount |
|---|---|
| 2023-01 | 1000 |
| 2023-02 | 1500 |
| 2023-03 | 1200 |
SELECTmonth,amount,SUM(amount)OVER(ORDERBYmonth)AScumulative_amountFROMsales;结果：
| month | amount | cumulative_amount | 说明 |
|---|---|---|---|
| 2023-01 | 1000 | 1000 | |
| 2023-02 | 1500 | 2500 | (1000+1500) |
| 2023-03 | 1200 | 3700 | (1000+1500+1200) |
说明：默认的窗口范围是从分区第一行到当前行（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），所以是累计计算。
场景3：移动平均/移动总计
需求：计算3个月的移动平均销售额和移动总计。
使用与场景2相同的销售表。
SELECTmonth,amount,AVG(amount)OVER(ORDERBYmonthROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_avg,SUM(amount)OVER(ORDERBYmonthROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_sumFROMsales;结果：
| month | amount | moving_avg | moving_sum | 说明 |
|---|---|---|---|---|
| 2023-01 | 1000 | 1000.0000 | 1000 | – 只有当前行 |
| 2023-02 | 1500 | 1250.0000 | 2500 | – 前1行+当前行（1000+1500） |
| 2023-03 | 1200 | 1233.3333 | 3700 | – 前2行+当前行（1000+1500+1200） |
| 2023-04 | 1800 | 1500.0000 | 4500 | – (1500+1200+1800) 注意：这里假设有4月数据 |
说明：通过ROWS BETWEEN 2 PRECEDING AND CURRENT ROW定义了窗口为当前行及其前两行（共3行）。移动平均和移动总计都是基于这个窗口计算。
场景4：比较相邻行
需求：计算本月销售额相比上月的增长额和增长率。
使用与场景2相同的销售表。
SELECTmonth,amount,LAG(amount)OVER(ORDERBYmonth)ASprev_amount,amount-LAG(amount)OVER(ORDERBYmonth)ASgrowth_amount,ROUND((amount-LAG(amount)OVER(ORDERBYmonth))/LAG(amount)OVER(ORDERBYmonth)*100,2)ASgrowth_rate_percentFROMsales;结果：
| month | amount | prev_amount | growth_amount | growth_rate_percent | 说明 |
|---|---|---|---|---|---|
| 2023-01 | 1000 | NULL | NULL | NULL | |
| 2023-02 | 1500 | 1000 | 500 | 50.00 | – (1500-1000)/1000*100 = 50% |
| 2023-03 | 1200 | 1500 | -300 | -20.00 | – (1200-1500)/1500*100 = -20% |
说明：
LAG(column, n)：获取当前行向前数第n行的值（默认为1，即上一行）。- 第一行没有上一行，所以返回NULL。
- 通过当前值减去上一行的值，可以得到增长额；再除以上一行的值，可以得到增长率。
场景5：计算占比
需求：计算每个员工工资占部门总工资的比例。
假设员工表employees有字段：id（员工ID）、name（姓名）、dept（部门）、salary（工资）。数据如下：
| id | name | dept | salary |
|---|---|---|---|
| 1 | Alice | IT | 8000 |
| 2 | Bob | IT | 7000 |
| 3 | Carol | HR | 9000 |
SELECTid,name,dept,salary,salary/SUM(salary)OVER(PARTITIONBYdept)*100ASdept_salary_percentFROMemployees;结果：
| id | name | dept | salary | dept_salary_percent | 说明 |
|---|---|---|---|---|---|
| 1 | Alice | IT | 8000 | 53.33 | – 8000/(8000+7000)=53.33% |
| 2 | Bob | IT | 7000 | 46.67 | – 7000/(8000+7000)=46.67% |
| 3 | Carol | HR | 9000 | 100.00 | – 9000/9000=100% |
说明：在窗口函数中，SUM(salary) OVER (PARTITION BY dept)计算每个部门的总工资。然后每个员工的工资除以部门总工资，得到该员工工资在部门中的占比。
总结
以上5个场景覆盖了开窗函数最常用的应用，每个例子都尽量精简字段，并详细解释了关键细节。开窗函数的核心在于通过OVER子句定义窗口（分区和排序），结合不同的窗口函数（如ROW_NUMBER,RANK,DENSE_RANK,SUM,AVG,LAG等）实现复杂的分析计算。