MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

这篇文章主要为大家介绍了MySQL实战,利用窗口函数SQL来分析班级学生的考试成绩及生活消费的示例过程,有需要的朋友可以借鉴参考下,希望能够有所帮助,祝大家多多进步

目录

  • 一、背景介绍
  • 二、建表语句和插入数据

    • 创建表格
    • 插入数据
  • 三、窗口函数分类介绍

  • 四、窗口函数应用

    • 1. 聚合函数 + over()搭配
    • 2. 排序函数 + over()搭配
    • 3. ntile()函数 + over()搭配
    • 4. 偏移函数 + over()搭配

一、背景介绍

今天,野鸡大学高(三)班的月考成绩出来了,这里先给大家公布一下各位同学的考试成绩。

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

接着,在给大家公布一下各位同学的生活消费情况。

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

下面我们利用上述考试成绩和生活消费记录,利用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()搭配;

具体每一类,有哪些函数呢?观察下面的思维导图。

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

对于over()里面,这里还有两个常用的关键字,必须要讲述。如下:

partition by + 字段:你可以想象成group by关键字,就是用于 “分组” 的关键字;

order by + 字段:这个更容易理解,就是用于 “排序” 的关键字;

四、窗口函数应用

上面给大家介绍了若干常用的 “窗口函数”,这里利用文首创建的数据,讲讲 “窗口函数” 的应用。

希望大家通过每个案例,来总结一下每个函数的含义,这里就不详细写了。

1. 聚合函数 + over()搭配

① 计算每位同学的得分与平均值的情况

select
sname
,subject
,score
,avg(score) over(partition by sname) as avg_score
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

② 计算每位同学1-3月消费情况和消费总额

select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname) as sum_cost
from
cost_fee

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

③ 计算每位同学1-3月消费情况和累计消费总额

select
sname
,buydate
,buycost
,sum(buycost) over(partition by sname order by buydate) as sum_cost
from
cost_fee

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

注意: 结合②③,大家可以发现partition by结合order by,与不结合order by,得到的完全是不同的结果。一个是分组求总和(不加order by);一个是分组求累计和(加order by)。

2. 排序函数 + over()搭配

① 计算每个科目的排名,相同的分数排名不同,顺序依次增加

select
sname
,subject
,score
,row_number() over(partition by subject order by score) rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

② 计算每个科目的排名,相同的分数排名相同,余下排名跳跃增加

select
sname
,subject
,score
,rank() over(partition by subject order by score) rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

③ 计算每个科目的排名,相同的分数排名相同,余下排名顺序增加

select
sname
,subject
,score
,dense_rank() over(partition by subject order by score) rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

3. ntile()函数 + over()搭配

ntile()函数有点乱入的感觉,你不知道给它分哪一类。该函数主要用 “数据切分”。如果说这个函数还有点用的话,就是他也可以对数据进行排序,类似于上面提到的row_number()函数。

① 对exam_score表,进行整张表切分

select
sname
,subject
,score
,ntile(4) over() rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

不信你下去试一下,ntile()里面不管写哪个数字,好像都可以。

② 对exam_score表,按照subject分组切分

select
sname
,subject
,score
,ntile(4) over(partition by subject) rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

即使是分组切分,你也会发现,这样毫无意义,因为score并没有排序。

③ 对exam_score表,对score排序后,按照subject分组切分(最有用)

select
sname
,subject
,score
,ntile(4) over(partition by subject order by score) rank1
from
exam_score

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

注意: 仔细观察这种用法,基本可以等效row_number()函数,效果是一样的。

4. 偏移函数 + over()搭配

① 展示各位同学的“上次购买时间”和“下次购买时间”

注:对于第一天,显示 “first buy”;对于最后一天,显示 “last buy”;

select
sname
,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 下次购买时间
from
cost_fee

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

② 截止到当前日期,每位同学的“首次购买时间”和“最后一次购买时间”

select
sname
,buydate
,first_value(buydate) over(partition by sname order by buydate) as 首次购买时间
,last_value(buydate) over(partition by sname order by buydate) as 最后一次购买时间
from
cost_fee

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

③ 展示每位同学的“首次购买时间”和“最后一次购买时间”

注意: 这里并没有说 “截止到当前日期”,请注意②③之间的区别呀。需求不同,结果就不同。

select
sname
,buydate
,first_value(buydate) over(partition by sname order by buydate) as 首次购买时间
,last_value(buydate) over(partition by sname ) as 最后一次购买时间
from
cost_fee

结果如下:

MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

以上就是MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费的详细内容,更多关于SQL窗口函数分析成绩及消费的资料请关注钦钦技术栈其它相关文章!

原文链接:https://huang-tong-xue.blog.csdn.net/article/details/115908762

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/4778/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 7月 23日 12:44:06
下一篇 2022年 7月 23日 12:44:18

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!