爱站程序员基地目录
- 一、背景介绍
- 二、建表语句和插入数据
- 创建表格
- 插入数据
- 1. 聚合函数 + over()搭配
- 2. 排序函数 + over()搭配
- 3. ntile()函数 + over()搭配
- 4. 偏移函数 + over()搭配
一、背景介绍
今天，野鸡大学高（三）班的月考成绩出来了，这里先给大家公布一下各位同学的考试成绩。
接着，在给大家公布一下各位同学的生活消费情况。
下面我们利用上述考试成绩和生活消费记录，利用mysql做一个简单的分析。
当然，从本文标题就可以看出来。本文就是要结合这份数据，为大家讲述SQL “窗口函数” 应该怎么用？
包括你以后学习hive或者oracle数据库，或者说数据分析面试，这都将是一个很重要的知识点。
二、建表语句和插入数据
创建表格
create table exam_score(sname varchar(20),age int,subject varchar(20),score varchar(20))charset=utf8;# ----------------------- #create table cost_fee(sname varchar(20),buydate varchar(20),buycost int)charset=utf8;
插入数据
insert into exam_score values(\'张三\' , 18, \'语文\' , 90),(\'张三\' , 18, \'数学\' , 80),(\'张三\' , 18, \'英语\' , 70),(\'李四\' , 21, \'语文\' , 88),(\'李四\' , 21, \'数学\' , 78),(\'李四\' , 21, \'英语\' , 71),(\'王五\' , 18, \'语文\' , 95),(\'王五\' , 18, \'数学\' , 83),(\'王五\' , 18, \'英语\' , 71),(\'赵六\' , 19, \'语文\' , 98),(\'赵六\' , 19, \'数学\' , 90),(\'赵六\' , 19, \'英语\' , 80);# ----------------------- #insert into cost_fee values(\'张三\',\'2019-01-01\',10),(\'张三\',\'2019-03-03\',23),(\'张三\',\'2019-02-05\',46),(\'李四\',\'2019-02-02\',15),(\'李四\',\'2019-01-07\',50),(\'李四\',\'2019-03-04\',29),(\'王五\',\'2019-03-08\',62),(\'王五\',\'2019-02-09\',68),(\'王五\',\'2019-01-11\',75),(\'赵六\',\'2019-02-08\',55),(\'赵六\',\'2019-03-10\',12),(\'赵六\',\'2019-01-12\',80);
三、窗口函数分类介绍
在正式讲述 “窗口函数” 应用之前，我这里先带着大家梳理一遍 “窗口函数” 的基础。我们可以将窗口函数分为如下几类：
聚合函数 + over()搭配；
排序函数 + over()搭配；
ntile()函数 + over()搭配；
偏移函数 + over()搭配；
具体每一类，有哪些函数呢？观察下面的思维导图。
对于over()里面，这里还有两个常用的关键字，必须要讲述。如下：
partition by + 字段：你可以想象成group by关键字，就是用于 “分组” 的关键字；
order by + 字段：这个更容易理解，就是用于 “排序” 的关键字；
四、窗口函数应用
上面给大家介绍了若干常用的 “窗口函数”，这里利用文首创建的数据，讲讲 “窗口函数” 的应用。
希望大家通过每个案例，来总结一下每个函数的含义，这里就不详细写了。
1. 聚合函数 + over()搭配
① 计算每位同学的得分与平均值的情况
selectsname,subject,score,avg(score) over(partition by sname) as avg_scorefromexam_score
结果如下：
② 计算每位同学1-3月消费情况和消费总额
selectsname,buydate,buycost,sum(buycost) over(partition by sname) as sum_costfromcost_fee
结果如下：
③ 计算每位同学1-3月消费情况和累计消费总额
selectsname,buydate,buycost,sum(buycost) over(partition by sname order by buydate) as sum_costfromcost_fee
结果如下：
注意： 结合②③，大家可以发现partition by结合order by，与不结合order by，得到的完全是不同的结果。一个是分组求总和（不加order by）；一个是分组求累计和（加order by）。
2. 排序函数 + over()搭配
① 计算每个科目的排名，相同的分数排名不同，顺序依次增加
selectsname,subject,score,row_number() over(partition by subject order by score) rank1fromexam_score
结果如下：
② 计算每个科目的排名，相同的分数排名相同，余下排名跳跃增加
selectsname,subject,score,rank() over(partition by subject order by score) rank1fromexam_score
结果如下：
③ 计算每个科目的排名，相同的分数排名相同，余下排名顺序增加
selectsname,subject,score,dense_rank() over(partition by subject order by score) rank1fromexam_score
结果如下：
3. ntile()函数 + over()搭配
ntile()函数有点乱入的感觉，你不知道给它分哪一类。该函数主要用 “数据切分”。如果说这个函数还有点用的话，就是他也可以对数据进行排序，类似于上面提到的row_number()函数。
① 对exam_score表，进行整张表切分
selectsname,subject,score,ntile(4) over() rank1fromexam_score
结果如下：
不信你下去试一下，ntile()里面不管写哪个数字，好像都可以。
② 对exam_score表，按照subject分组切分
selectsname,subject,score,ntile(4) over(partition by subject) rank1fromexam_score
结果如下：
即使是分组切分，你也会发现，这样毫无意义，因为score并没有排序。
③ 对exam_score表，对score排序后，按照subject分组切分（最有用）
selectsname,subject,score,ntile(4) over(partition by subject order by score) rank1fromexam_score
结果如下：
注意： 仔细观察这种用法，基本可以等效row_number()函数，效果是一样的。
4. 偏移函数 + over()搭配
① 展示各位同学的“上次购买时间”和“下次购买时间”
注：对于第一天，显示 “first buy”；对于最后一天，显示 “last buy”；
selectsname,buydate,lag(buydate,1,\'first day\') over(partition by sname order by buydate) as 上次购买时间,lead(buydate,1,\'last day\') over(partition by sname order by buydate) as 下次购买时间fromcost_fee
结果如下：
② 截止到当前日期，每位同学的“首次购买时间”和“最后一次购买时间”
selectsname,buydate,first_value(buydate) over(partition by sname order by buydate) as 首次购买时间,last_value(buydate) over(partition by sname order by buydate) as 最后一次购买时间fromcost_fee
结果如下：
③ 展示每位同学的“首次购买时间”和“最后一次购买时间”
注意： 这里并没有说 “截止到当前日期”，请注意②③之间的区别呀。需求不同，结果就不同。
selectsname,buydate,first_value(buydate) over(partition by sname order by buydate) as 首次购买时间,last_value(buydate) over(partition by sname ) as 最后一次购买时间fromcost_fee
结果如下：
以上就是MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费的详细内容，更多关于SQL窗口函数分析成绩及消费的资料请关注脚本之家其它相关文章！
您可能感兴趣的文章:
- 非常实用的MySQL函数全面总结详解示例分析教程
- MySQL数据库基础篇SQL窗口函数示例解析教程
- mysql 计算函数详情
- MySQL实例精讲单行函数以及字符数学日期流程控制
- MySQL必备基础之分组函数 聚合函数 分组查询详解
- 浅谈MySQL函数
- MySQL空间数据存储及函数
- mysql函数全面总结
