[大数据]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) over(
  	order by date
  	rows/range ......
  ) as `sum`
  

• range 后跟的范围只有在使用 order by 后才会生效(不使用的话默认是整张表)

• rows 后跟的范围可以在不使用 order by 时生效

• rows 和 range 后的取值情况：

  一般写法(rows 和 range 均可用)：

  • UNBOUNDED PRECEDING：对上限无限制
  • PRECEDING： 当前行之前的 n 行 （ n 表示具体数字如：5 PRECEDING ）
  • CURRENT ROW：仅当前行
  • FOLLOWING：当前行之后的 n 行 （ n 表示具体数字如：5 FOLLOWING ）
  • UNBOUNDED FOLLOWING：对下限无限制
  • 需要注意的是：upper_bound 需要在 lower_bound 之前，比如：ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING 是错误的写法

  rows 简略写法：(使用current row作为上边界或下边界时可使用)

  • ROWS UNBOUNDED PRECEDING 等价于 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • ROWS n PRECEDING 等价于 BETWEEN n PRECEDING AND CURRENT ROW
  • ROWS CURRENT ROW 等价于 BETWEEN CURRENT ROW AND CURRENT ROW
  • 注意，这种简略的写法不适合 FOLLOWING的情况

  range 写法：

  • RANGE UNBOUNDED PRECEDING
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • RANGE CURRENT ROW

6、默认的 window frames

? 如果开窗函数的 over( ) 中指定了 " order by * " 子句，则会默认添加一个window frames ，把每个相同的 * 分为一组；默认为：range unbounded preceding

7、CTE表达式

? 使用：在单个语句的执行范围内创建一个或多个临时结果集，实现代码的重复使用

? 语法：

with 临时表名1 as (
	select ... from ...
),
	临时表名2 as (
		select ... from ...
	)
select ... from 临时表名

? 解释：使用 with 把一段查询语句的结果作为一个临时表，可以在后面直接使用，可以同时创建多个临时表

8、分析函数

• 共学5个分析函数： LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTH_VALUE
• LEAD 、LAG 、NTILE以及排序函数等返回的数据只受 order by 排序的影响
• FIRST_VALUE、LAST_VALUE、NTH_VALUE 以及 SUM、AVG、MAX、MIN、COUNT 等函数返回的数据受order by 的影响以及 window frame的影响(使用时如果添加了order by 则需要使用 rows\range 来自定义window frame的范围)
  • 对于返回数据受 order by 影响的函数，如果 over( ) 中进行了 partition by 分区操作，则其window frame 更改为每个分区

1. lead( x [, y] [, z] ) over( )

   含义：新增一列，数据为列名为 x 对应的下 y 行数据(默认为1)，如果不存在则显示为 z (默认为null)；数据的顺序可以在 over( ) 中添加 order by 来自定义。

2. lag(x [, y] [, z] ) over( )

   含义：新增一列，数据为列名为 x 对应的上 y 行数据(默认为1)，如果不存在则显示为 z (默认为null)；数据的顺序可以在 over( ) 中添加 order by 来自定义。

3. first_value( x ) over( )

   含义：新增一列，返回指定列 x 的第一个值。

4. last_value( x ) over( )

   含义：新增一列，返回指定列 x 的最后一个值。

5. nth_value( x, n ) over( )

   含义：新增一列，返回列名为 x 的第 n 行的值。

9、使用开窗函数的位置

不能使用的地方

1. 不能在 WHERE 子句中使用开窗函数
2. 不能在 HAVING 子句中使用开窗函数
3. 不能在 GROUP BY 子句中使用开窗函数

可以使用的地方

1. 在 ORDER BY 中使用开窗函数
2. 和 GROUP BY 一起使用：和 GROUP BY 一起使用时，开窗函数处理的数据是聚合后的数据

二、补充语法

1、CASE WHEN 语法

case 
	when 条件 then 结果
	when 条件 then 结果
	...
	else 结果
end

1. CASE WHEN 可以在 GROUP BY 中使用

2. CASE WHEN 可以在聚合函数中使用，自定义函数需要选择的数据

3. 单个CASE WHEN筛选了一列中不同值的数据，最后的结果中会有多行数据

4. 多个CASE WHEN筛选不同数据，最后的结果会有多列数据

2、CTE公用表表达式

? 语法：

WITH new_name1 AS(
	CTE表达式
)， new_name2 AS(
	CTE表达式
)
select 
	...
from new_name1/new_name2

注意点：名字需要自己定义；后面的SELECT语句必须有；每个CTE之间使用 ’ , ’ 分隔；CTE表达式称为内部查询，外面的SELECT语句称为外部查询

1. 使用时先把问题的解决过程变成步骤，将完成各个步骤的SELECT语句分别作为CTE，最后进行拼接。

2. 如果一些结果需要用到多层聚合时，可以先创建子查询，然后转换成CTE。

三、事务和索引

1、事务

? 四大特征：原子性，一致性，隔离性，持久性

? 原子性：

? 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

? 一致性：

? 数据库总是从一个一致性的状态转换到另一个一致性的状态。

? 隔离性：

? 通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。

? 持久性：

? 一旦事务提交，则其所做的修改会永久保存到数据库。

? 事务开启步骤：

? 1）开启事务：begin 或 start transaction

• 开启事务后执行修改命令，变更数据会保存到 MySQL 服务端的缓存文件中，而不维护到物理表中

• MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务，那么每条sql语句都会被当作一个事务执行提交的操作

  2）事务中的 SQL 操作

  3）结束事务：提交事务(commit)或回滚事务(rollback)

• 提交事务：将本地缓存文件中的数据提交到物理表中，完成数据的更新

• 回滚事务：放弃本地缓存文件中的缓存数据，表示回到开始事务前的状态

2、索引

? 作用：查询已经添加索引的列的数据时，速度大大提高

? 使用联合索引要遵循最左原则（在创建联合索引时最左侧的字段在使用索引查询时只有带上该字段索引才会有效）