「MySQL」（三）高级应用

本文主要内容

1. 窗口函数的概念、语法及应用

2. 索引的概念、常见的索引分类以及索引的优缺点

3. 视图的概念、作用、应用以及它与基表的区别

目录

本文主要内容

第1节 窗口函数

1.1 什么是窗口函数

1.2 窗口函数应用

1.2.1 累计计算函数

1.2.2 排序函数

1.2.3 偏移分析函数

第2节 MySQL索引

2.1 什么是索引

2.2 常见索引分类

2.2.1 主键索引 (PRIMARY KEY)

2.2.2 唯一索引 (UNIQUE)（了解）

2.2.3 普通索引 (NORMAL INDEX)

2.2.4 复合索引

2.2.5 删除索引

2.3 索引的优缺点总结

第3节 MySQL 视图

3.1 什么是视图

3.2 视图的作用

3.3 视图的使用

3.3.1 创建视图

3.3.2 通过视图进行查询

3.3.3 判断是否存在视图

3.4 视图与表的区别

---

第1节 窗口函数

1.1 什么是窗口函数

    MySQL从8.0开始支持窗口函数，有的也叫分析函数（处理相对复杂的报表统计分析场景），这个功能在大多商业数据库和部分开源数据库中早已支持。

窗口函数：窗口、函数（应用在窗口内的函数）-----窗口类似于窗户，限定一个空间范围

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数的基本用法：

函数名() over 子句

其中，over是关键字，用来指定函数执行的窗口范围。

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

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

• 知识点总结

  sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)

A: 需要被加工的字段名称          B: 分组的字段名称

C: 排序的字段名称                     D: 计算的行数范围

1. 分组(partition by)：按照字段值进行分组，窗口函数在不同的分组上分别执行。

2. 排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。

3. 窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

• ！！当使用聚合函数累计时,注意尽量不要出现有C无D的情况！！

  • 因为累计结果会把order by的字段值相同的行一起计算。

# 1.D子句规则
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 1 following # 从前面三行和下面一行，总共五行

# 2.当order by后面缺少窗口从句条件（有C无D），窗口规范默认规则：(如果C字段值相同，则不是)
rows between unbounded preceding and current row.

# 3.当order by和窗口从句都缺失,(无CD) 窗口规范规则：
rows between unbounded preceding and unbounded following.

1.2 窗口函数应用

一般把窗口函数分为两种：

1. 专有窗口函数： ①rank()；② dense_rank()；③ row_number()；

2. 聚合类窗口函数： 普通场景下的聚合函数往往和group by一起使用，但是窗口环境下，聚合函数可以不联group by而直接应用进来。例：sum();count();avg();max();min()。

• 普通聚合函数和窗口聚合函数的区别：
  • 普通聚合函数是将多条记录聚合为一条（多到一）；
  • 窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。

1.2.1 累计计算函数

        例：sum();count();avg();max();min()；...。

• 注意：

①使用聚合窗口函数时，一般是需要重启select语句（把之前的select语句当作子查询表）；

②over语句中一般都要用排序，即使他本来顺序就没错，除非BCD都不需要的；

③近三个月指的是前2月+本月。

例1：查询出2018-2019年每月的支付总额和当年累积支付总额

SELECT a.year,
    a.month,
    a.pay_amount,
    sum(a.pay_amount) over(partition by a.year order by a.month)
FROM
(SEL