本文正在参加「技术专题19期 漫谈数据库技术」活动
最近一直在更新mysql方面的博客，一部分原因是因为掘金的活动（嘿嘿嘿），但是还有一部分原因是在工作当中最近负责完成一部分数据可视化的功能，这个过程当中，awk日志分析，elk日志收集，基于DSL日志都有涉及到，当然也涉及到了存入mysql的数据的可视化操作，所以就顺势而为，整合了一下自己在mysql学到的遇到的东西，然后有小伙伴推荐mysql8.0的窗口函数，尝试后，和大家分享一下：
窗口函数定义
首先，在尝试任何一个东西的时候，都会自己问自己两个个问题:
1、他是干啥的？
2、没有他会这么样？
那么今天还是依赖这个来开始认识窗口函数：
首先说，窗口函数是MySQL 8.0新出的用来做数据分析的功能，可以将分析的结果列到每一列，在没有窗口函数之前，确实可以使用分组聚合来进行MySQL当中的数据统计，但是，有些麻烦的就是基于数据分析的化，很难进行全表数据的展示，窗口函数的出现极大的方便了这样的操作,举个例子：
获取50名学员每月的生活费，然后进行数据分析：
最简单的维度：
男生，女生生活费的平均值，这个采用分组很好实现。
select gender,avg(money) as avg_money from students group by gender;
但是，实际的数据分析过程当中，除了大维度的分析之外，还需要进行更细力度的分析，比如四分位数，众数，这些来查询数据的分布情况，或者是针对每个学员看一下他与平均值之间的差异，那么，这个时候在之前的sql层面实现就会比较繁琐，因为：
目前的数据格式：
| gender | avg_money |
|---|---|
| 男 | 500 |
| 女 | 525 |
希望的数据格式：
| id | student_name | gender | money | avg_money |
|---|---|---|---|---|
| 1 | 小芳 | 女 | 600 | 525 |
| 2 | 大宝 | 男 | 500 | 500 |
| 3 | 翠兰 | 男 | 550 | 500 |
| ...... | ...... | ...... | ...... | ...... |
显然下面的数据格式更加方便数据分析的具体操作，当然之前sql或者结合查询脚本也是可以实现的，但是会比较繁琐，而MySQL8.0推出的窗口函数正是为了解决这个问题的。
窗口函数定义
那么我们来看一下窗口函数的定义：
select student_name,gender,gender,money,avg(money) over(partition by gender) as avg_money from students;
窗口函数和分组函数是相似的，并且都可以使用sum/avg/count/max/min这些聚合函数，不同的是，对比分组函数，窗口函数可以展示所有列数据，并且把分组结果映射到每一类上：
| id | student_name | gender | money | avg_money |
|---|---|---|---|---|
| 1 | 小芳 | 女 | 600 | 525 |
| 2 | 大宝 | 男 | 500 | 500 |
| 3 | 翠兰 | 男 | 550 | 500 |
| ...... | ...... | ...... | ...... | ...... |
具体的语法构成：
avg(money) over(
partition by gender
) as avg_money
第一部分是要进行的运算，这里以 avg(money)为例，
第二部分是声明聚合的字段，over(partition by gender) 这里声明以性别来作为分组条件，但是注意整个语句后面没有使用group_by
括号当中，除了partition by可以声明分组字段之外，还可以有
（1）order by 基于每个分组的排序
（2）rows between 基于每个分组的数据范围，比如每个分组的第二到第十位
第三部分是起别名，这个大家应该是不陌生的，这里的是as avg_money
窗口函数
为了提供更好的数据分析效果，窗口函数除了常规的聚合函数之外，还提供了自己独有的一些数据分析函数，这里列出我觉的常用的一些：
| 函数 | 描述 |
|---|---|
| row_number | 单纯对行从上到下填充序号，对数据对比无操作变化。 |
| rank | 按照排序数据大小对比进行依次排序，相同大小顺序并列，排序会按照计数数位显示次序。 |
| dense_rank | 按照排序数据大小对比进行依次排序，相同大小顺序并列，排序名次依次顺延。 |
| first_value | 返回第一个值 |
| last_value | 返回第二个值 |
上面的部分说明有些晦涩，我们使用案例来展示一下，以学员年龄表位例子：
| id | 姓名(student_name) | 年龄(age) | 年级(grade) |
|---|---|---|---|
| 1 | 张三 | 18 | 1 |
| 2 | 李四 | 16 | 2 |
| 3 | 王五 | 17 | 1 |
| 7 | 赵六 | 18 | 2 |
| 8 | 侯七 | 17 | 3 |
| 9 | 冯八 | 16 | 3 |
| 13 | 钱九 | 16 | 3 |
| 14 | 孙十 | 17 | 1 |
| 15 | 李十一 | 15 | 2 |
| 16 | 周十二 | 17 | 1 |
| ..... | ..... | ..... | ..... |
row_number
select student_name,age,grade,row_number() over(partition by grade) as row_number from student;
结果如下：
| 姓名(student_name) | 年龄(age) | 年级(grade) | row_number |
|---|---|---|---|
| 张三 | 18 | 1 | 1 |
| 李四 | 16 | 2 | 2 |
| 王五 | 17 | 1 | 3 |
| 赵六 | 18 | 2 | 4 |
| 侯七 | 17 | 3 | 5 |
| 冯八 | 16 | 3 | 6 |
| 钱九 | 16 | 3 | 7 |
| 孙十 | 17 | 1 | 8 |
| 李十一 | 15 | 2 | 9 |
| 周十二 | 17 | 1 | 10 |
| ..... | ..... | ..... | ..... |
rank
select student_name,age,grade,rank() over(order by age) as rank_age from student;
| 姓名(student_name) | 年龄(age) | 年级(grade) | row_number |
|---|---|---|---|
| 李十一 | 15 | 2 | 1 |
| 李四 | 16 | 2 | 2 |
| 冯八 | 16 | 3 | 2 |
| 钱九 | 16 | 3 | 2 |
| 王五 | 17 | 1 | 5 |
| 侯七 | 17 | 3 | 5 |
| 孙十 | 17 | 1 | 5 |
| 周十二 | 17 | 1 | 5 |
| 张三 | 18 | 1 | 9 |
| 赵六 | 18 | 2 | 10 |
| ..... | ..... | ..... | ..... |
dense_rank
select student_name,age,grade,rank() over(order by age) as rank_age from student;
| 姓名(student_name) | 年龄(age) | 年级(grade) | row_number |
|---|---|---|---|
| 李十一 | 15 | 2 | 1 |
| 李四 | 16 | 2 | 2 |
| 冯八 | 16 | 3 | 2 |
| 钱九 | 16 | 3 | 2 |
| 王五 | 17 | 1 | 3 |
| 侯七 | 17 | 3 | 3 |
| 孙十 | 17 | 1 | 3 |
| 周十二 | 17 | 1 | 3 |
| 张三 | 18 | 1 | 4 |
| 赵六 | 18 | 2 | 4 |
| ..... | ..... | ..... | ..... |
first_value和last_value
这两个需要在partition by的基础上使用，这两个比较好理解，大家可以通过语句自己尝试，数据格式就不做展示了
select student_name,age,grade,first_value(age) over(partition by grade order by age) as first_age,last_value(age) over(partition by grade order by age) as last_age from student;
当然还有很多的窗口函数，这里就不一一举例了，大家可以到8.0官网去看看，这里就先聊这么多，最后还是请大家多多指点。嘿嘿嘿。
本文正在参加「技术专题19期 漫谈数据库技术」活动
