数据分析实训 阶段二 模块二 mysql高级应用 by拉钩教育

一、窗口函数

1、窗口函数的定义

窗口函数，又称为分析函数，用于处理相对复杂的报表统计分析场景。mysql从8.0之后开始支持窗口函数。

• 窗口
  窗口，又可以理解为记录的集合。
  窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
• 窗口的分类
  静态窗口：对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；
  滑动窗口：不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
• 窗口函数基本用法

函数名（[expr]） over子句
函数() over()

over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句。
如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：

函数名（[expr]） over(partition by <要分列的组> order by <要排序的列> rows
between <数据范围>)

2、窗口函数的分类

3、窗口函数和聚合函数的区别

窗口函数（专有窗口函数+聚合类窗口函数）和普通场景下的聚合函数也很容易混淆，二者区别
如下：
普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执
行，有几条记录执行完还是几条（多到多）。
分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行。

排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可
以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整
个表的数据行。
窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来
作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单
的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

3、窗口函数的应用

a. 累计计算函数

b.排序函数

c.偏移分析函数

rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded
preceding and current row.
# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding
and unbounded following

二、索引

1、索引的定义

• 索引可以提升查询速度，会影响where条件查询（where xxx=’’），order by（order by yyy）排序
• 索引是针对字段的，需要添加到字段上
• 索引在大量数据场景下效果明显

2、索引分类

• 从索引的存储结构划分：
  B Tree索引、Hash索引、fulltext全文索引、R Tree索引（了解）
• 从应用层次划分：
  主键索引、唯一索引、普通索引、复合索引
• 从索引的键值(字段) 类型划分：
  主键索引、辅助索引（二级索引）
• 从索引数据和内容数据逻辑关系划分：
  聚集索引（聚簇索引）、非聚集索引（非聚簇索
  引）

3、常见索引

• 主键索引 (PRIMARY KEY)
  • 一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。
  • 添加主键的时候会自动创建主键索引，主键索引是一种唯一性索引
  • 根据主键进行where条件查询，效率高

# 创建表的时候直接添加主键索引 (就是添加主键的方式，最常用)
CREATE TABLE 表名(
did INT primary key,
字段1 字段类型(长度),
字段2 字段类型(长度)
);
# 修改表结构 添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

• 唯一索引(UNIQUE)
  • 唯一索引可以保证数据记录的唯一性。
  • 特点: 索引列的所有值都只能出现一次, 必须唯一.
  • 在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

# 创建表的时候直接添加唯一索引
CREATE TABLE 表名(
列名 类型(长度),
-- 添加唯一索引
UNIQUE [索引名称] (列名)
);
# 使用create语句创建: 在已有的表上创建索引
create unique index 索引名 on 表名(列名(长度));
# 修改表结构添加索引
ALTER TABLE 表名 ADD UNIQUE 索引名( 列名 )

• 普通索引 (NORMAL INDEX)
  • 普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDER BY column）中的数据列创建索引。
  • 普通索引对数据没有唯一、非空的强制要求

# 使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名[长度])
# 修改表结构添加索引
ALTER TABLE f ADD INDEX 索引名 (列名)

4、删除索引

由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索引。

ALTER TABLE 表名 DROP INDEX 索引名;

5、索引的优缺点

• 索引的优点
  a. 大大的提高查询速度
  b. 可以显著的减少查询和排序的时间。
• 索引的缺点
  当对表中的数据进行增删改操作时，索引要同时进行维护，数据量越大维护时间越长。

三、视图

1、视图定义

1. 视图是一种虚拟表。
2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4. 视图向用户提供基表数据的另一种表现形式

2、视图的作用

• 权限控制时可以使用
  • 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列,起到权限控制的作用
• 简化复杂的多表查询
• 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查
  询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
• 视图主要就是为了简化多表的查询

3、视图的使用

• 创建视图

create view 视图名 [column_list] as select语句;
#其中 view: 表示视图；column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询的属性相同；as : 表示视图要执行的操作；select语句: 向视图提供数据内容

• 通过视图进行查询
  略

4、视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
• 通过视图不能改变表中数据，一般情况下视图不允许更新
• 删除视图，表不受影响，而删除表，视图不再起作用