MySQL：窗口函数、索引、视图

1）窗口函数

# 1 语法
函数名([expr]) over(partition by <分组字段> order by <排序字段> rows between <数据范围/窗口>)
 
# 2 数据范围
rows between 2 preceding and current row  # 前2行到当前行
rows between unbounded preceding and current row   # 本行及之前所有行
rows between current row and unbounded following   # 本行及之后所有行

# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

常用窗口函数：聚合窗口函数、排序函数、偏移分析函数。

数据准备：

表结构如下：

 1.1 聚合窗口函数应用

sum()   count()  avg()  max()  min()

1.1.1 累计求和

 

 1.1.2 移动平均

 

 1.2 排序函数的应用

1.2.1 row_number()    rank()     dense_rank()

紧邻的括号不需添加任何字段。

row_number：一行一个序号，序号不重复。

rank：字段相同，序号相同，序号不连续。

dense_rank：字段相同，序号相同，序号连续。

 

 1.2.2 ntile(n)

ntile(n)：将数据按照顺序切分成n片，返回其所在切片值
ntile不支持rows between

1.3 偏移分析函数

lag(...)     lead(...)

# 语法
lag(exp_str,offset,defval) over(partion by ......order by ......)  # 向前
lead(exp_str,offset,defval) over(partion by ......order by ......) # 向后

exp_str：字段名称
offset：偏移量，默认值为1
defval：当前移（后移）没有数据时的默认值，若没有指定默认值，则返回NULL，

 

 2）索引

从应用层次划分：主键索引、唯一索引、普通索引、复合索引

索引的优缺点总结:

索引的优点：

    1. 大大的提高查询速度

    2. 可以显著的减少查询和排序的时间。

索引的缺点

    当对表中的数据进行增加，修改，删除的时候，索引要同时进行维护，数据量越大维护时间越长。

 2.1 主键索引

# 添加主键的时候会自动创建主键索引，主键索引是一种唯一性索引
# 1 创建表时直接创建主键索引，同添加主键方式
create table test_index(
         tid int primary key,
         ...
);

# 2 修改表结构，添加主键索引
alter table test_index add primary key(tid);

2.2 唯一索引

# 特点：索引列的所有值只能出现一次，必须唯一
# 目的：一般是为了避免数据重复
# 1 创建表时添加
CREATE TABLE 表名(
   列名 类型(长度),
   UNIQUE [索引名称] (列名)   # 添加唯一索引
);

# 2 使用create语句创建：在已有表上创建
create unique index 索引名 on 表名(列名);

# 3 修改表结构添加索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名);

2.3 普通索引

# 目的：加快数据的访问速度
# 应用场景：经常出现在查询条件（where column = ）或排序条件（order by column）中的数据列创建数据
# 1 使用create index 创建
create index 索引名 on 表名(列名[长度])

# 2 修改表结构添加索引
ALTER TABLE f ADD INDEX 索引名 (列名)

2.4 删除索引

# 删除test_index表中名为dname_indx的普通索引
alter table test_index drop index dname_index;

3）视图

3.1 视图

1、视图是一种虚拟表。

2、视图建立在已有表（基表）基础上。

3、可以理解为存储起来的select语句

4、视图向用户提供基表数据的另一种表现形式

3.2 作用

1、权限控制

2、简化复杂的多表查询

3.3 使用

# 语法：create view 视图名 as select 语句;

3.4 视图与表的区别

1、视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示。

2、通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果, 不允许更新）。

3、删除视图，表不受影响，而删除表，视图不再起作用。