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

编辑: admin 分类: mysql 发布时间: 2022-02-23 来源:互联网
目录
  • 一、背景介绍
  • 二、建表语句和插入数据
    • 创建表格
    • 插入数据
  • 三、窗口函数分类介绍
    • 四、窗口函数应用
      • 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(
        sna【原URL http://www.yidunidc.com/sin.html 转载请说明出处】me 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()搭配

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

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

    结果如下:

    在这里插入图片描述

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

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

    结果如下:

    在这里插入图片描述

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

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

    结果如下:

    在这里插入图片描述

    注意: 结合②③,大家可以发现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
    

    结果如下:

    在这里插入图片描述

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

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

    结果如下:

    在这里插入图片描述

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

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

    结果如下:

    在这里插入图片描述

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

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

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

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

    结果如下:

    在这里插入图片描述

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

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

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

    结果如下:

    在这里插入图片描述

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

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

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

    结果如下:

    在这里插入图片描述

    注意: 仔细观察这种用法,基本可以等效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
    

    结果如下:

    在这里插入图片描述

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

    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
    

    结果如下:

    在这里插入图片描述

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

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

    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分析班级学生考试成绩及生活消费的详细内容,更多关于SQL窗口函数分析成绩及消费的资料请关注海外IDC网其它相关文章!

    【文章转自阿里云服务器代理商 http://www.558idc.com/aliyun.html 复制请保留原URL】