MySQL开窗、CASE WHEN以及CTE的使用和理解

一、开窗函数

什么时候适合用开窗函数:

1. 获取指定排名的数据；
2. 计算每一个占总体的占比；例：计算每个员工处理订单数量占所有金额的占比
3. 计算当前行的某个值和前面某行或后面某行的相同列的差值；例：计算网址每天的访问量和前一天访问量的差值

1、用法分类

1. rank、dense_rank、row_number、ntile等排序函数的使用
2. avg、sum、count等聚合函数的使用
3. 窗口函数在执行聚合运算时不会改变其他列的原始信息

2、over()的使用

• 方法名( ) over( ) 相当于使用窗口函数
• over( ) 中添加的数据可以为：分区(partition by)、分组(order by)、rows\range(规定执行范围)
• 使用over，且over( ) 不添加参数，则是对全部查询结果进行聚合运算，默认范围是整张表
• over( ) 在添加 order by 后，默认的开窗执行范围是从头到当前行
• over( ) 中如果添加 order by 后执行的顺序要在 where 中的 order by 之后
• over( ) 中如果添加 order by 后默认将查找的数据以 order by 分组

3、partition by 的使用

• 可以传入多个值，进行多级分区
• 执行完 select 查询之后，在结果集上进行分区
• 在查询语句中的执行顺序：FROM > WHERE > GROUP BY > HAVING
• 每个区之间是独立的，开窗函数对每个区的操作不影响其他区的数据

4、排序函数

• 排序函数有之前学过的 rank、dense_rank、row_number函数

• ntile(x) 函数：

  ​ 作用：将数据分组，并为每组添加一个相同的序号(1, 2, 3, …)

  ​ 语法：

  NTILE(4) over() 
  

  ​ 将查询到的数据分为X组，如上代码将数据平均分为4组，如果不能平均分则数据多的组会排在前面

5、windows frames 自定义窗口

• 使用 rows 和 range 来定义 windows frames

• rows 是根据 row_number(1,2,3,4,…) 来选取 windows frames 数据范围的

• range 是根据 rank(1,2,2,4,…) 来选取 windows frames 数据范围的

• 表示设置开窗函数的作用域(前面三行、从当前行到最后、…)

• 位置：在 over( ) 中的 order by 之后

  sum(num)