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

目录
  • 一、背景介绍
  • 二、建表语句和插入数据
    • 创建表格
    • 插入数据
  • 三、窗口函数分类介绍
  • 四、窗口函数应用
    • 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()搭配

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

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窗口函数分析成绩及消费的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL数据库基础篇SQL窗口函数示例解析教程

    目录 本文简介 正文介绍 聚合函数 + over() 排序函数 + over() ntile()函数 + over() 偏移函数 + over() 本文简介 前段时间,黄同学写了一篇<MySQL窗口实战>文章(文章如下),但是里面大多数是以实战练习为主,没有做详细的解释. 传送门:MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费 于是,私信了月牙美女,看看她能否写一篇<窗口函数基础篇>,正好和之前那篇文章配套.这不,很快她就写好了,今天就给大家做一个分享,旨在和大家交流学

  • mysql函数全面总结

    目录 1.MySQL中常用字符串函数 2.数值函数 3.日期和时间函数 4.流程函数 5.其他常用函数 1.MySQL中常用字符串函数 函数 功能 CANCAT(S1,S2,-Sn)  连接 S1,S2,-Sn 为一个字符串 INSERT(str,x,y,instr)  将字符串 str 从第 x 位置开始,y 个字符长的子串替换为字符串 instr LOWER(str)  将字符串 str 中所有字符变为小写 UPPER(str)  将字符串 str 中所有字符变为大写  LEFT(str ,

  • 非常实用的MySQL函数全面总结详解示例分析教程

    目录 1.MySQL中关于函数的说明 2.单行函数分类 3.字符函数 4.数学函数 5.日期时间函数 6.其它常用系统函数 7.流程控制函数 8.聚合函数 1)聚合函数的功能和分类: 2)聚合函数的简单使用 3)五个聚合函数中传入的参数,所支持的数据类型有哪些? 4)聚合函数和group by的使用"最重要": 1.MySQL中关于函数的说明 "概念":类似java.python中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名: "好处":

  • MySQL空间数据存储及函数

    目录 一.数据类型 1.什么是MySQL空间数据 2.什么是geojson 3.格式化空间数据类型(geometry相互转换geojson) 二.空间分析 1.根据点位及半径,生成缓冲区 三.判断点位所在城市 四.常用的空间函数 前言: 不久前开发了一个地图相关的后端项目,需要提供一些点线面相关的存储.查询.分析相关的操作,于是对MySQL空间函数进行充分调研并应用在项目中;MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构),还有有细分类型Point, Line

  • MySQL实例精讲单行函数以及字符数学日期流程控制

    目录 一.字符函数 1.大小写控制函数 2.字符控制函数 二.数学函数 三.日期函数 四.其他函数 五.流程控制函数 一.字符函数 1.大小写控制函数 ①UPPER():转换成大写 SELECT UPPER('Hello'); ②LOWER():转换成小写 SELECT LOWER('Hello'); 2.字符控制函数 ①LENGTH():获取参数值的字节个数 SELECT LENGTH('叶绿体不忘呼吸aaaa'); ②CONCAT():拼接字符串 SELECT CONCAT('Hello',

  • 浅谈MySQL函数

    目录 1.数学函数 2.字符串函数 3.日期函数 4.加密函数  主要MySQL函数介绍又以下: 数学函数 字符串函数 时间函数 加密函数 1.数学函数 注意: 每个函数前面都需要加 : SELECT . 数学函数 ABS() 返回绝对值 如: (-100) 值 : 100 PI() 返回Π的圆规率 如 (不用写) 值 : 3.1415926 CEIL() 向上取整数 如:(3.14) 值 :4 ( 注意第三个是i ) FLOOR() 向下取整数 如: (3.14) 值 :3 POW(x,y)

  • mysql 计算函数详情

    目录 2.字段拼接 2.举一些mysql 函数例子 2.1 符处理 2.2 期处理 2.3 值处理 函数没有SQL的可移植性强 能运行在多个系统上的代码称为可移植的(portable).相对来说,多数SQL语句是可移植的,在SQL实现之间有差异时,这些差异通常不那么难处理. 而函数的可移植性却不强.几乎每种主要的DBMS的实现都支持其他实现不支持的函数,而且有时差异还很大. 为了代码的可移植,许多SQL程序员不赞成使用特殊实现的功能.虽然这样做很有好处, 但不总是利于应用程序的性能.如果不使用这

  • MySQL必备基础之分组函数 聚合函数 分组查询详解

    目录 一.简单使用 二.搭配DISTINCT去重 三.COUNT()详细介绍 四.分组查询 一.简单使用 SUM:求和(一般用于处理数值型) AVG:平均(一般用于处理数值型) MAX:最大(也可以用于处理字符串和日期) MIN:最小(也可以用于处理字符串和日期) COUNT:数量(统计非空值的数据个数) 以上分组函数都忽略空NULL值的数据 SELECT SUM(salary) AS 和,AVG(salary) AS 平均,MAX(salary) AS 最大,MIN(salary) AS 最小

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

    目录 一.背景介绍 二.建表语句和插入数据 创建表格 插入数据 三.窗口函数分类介绍 四.窗口函数应用 1. 聚合函数 + over()搭配 2. 排序函数 + over()搭配 3. ntile()函数 + over()搭配 4. 偏移函数 + over()搭配 一.背景介绍 今天,野鸡大学高(三)班的月考成绩出来了,这里先给大家公布一下各位同学的考试成绩. 接着,在给大家公布一下各位同学的生活消费情况. 下面我们利用上述考试成绩和生活消费记录,利用mysql做一个简单的分析. 当然,从本文标

  • MySQL线上死锁分析实战

    前言 MySQL 的锁机制相信大家在学习 MySQL 的时候都有简单的了解过,那既然有锁就必定绕不开死锁这个问题.其实 MySQL 在大部分场景下是不会存在死锁问题的(比如并发量不高,SQL 写得不至于太拉胯的情况),但是在高并发的业务场景下,一不注意就会产生死锁,而这个死锁分析起来也比较麻烦. 前段时间在公司实习的时候就遇到了一个比较奇怪的死锁,之前一直没来得及好好整理,最近有空复现了一下,算是积累一点经验. 业务场景 简单说一下业务背景,公司做的是电商直播,我负责的是主播端相关的业务.而这个

  • MySQL实战记录之如何快速定位慢SQL

    目录 开启慢查询日志 系统变量 修改配置文件 设置全局变量 分析慢查询日志 mysqldumpslow pt-query-digest 用法实战 总结 开启慢查询日志 在项目中我们会经常遇到慢查询,当我们遇到慢查询的时候一般都要开启慢查询日志,并且分析慢查询日志,找到慢sql,然后用explain来分析 系统变量 MySQL和慢查询相关的系统变量如下 参数 含义 slow_query_log 是否启用慢查询日志, ON为启用,OFF为没有启用,默认为OFF log_output 日志输出位置,默

  • SQL Server携程核心系统无感迁移到MySQL实战

    目录 前言 一.架构综述 二.应用场景 1.新单秒级各端同步 2.自动发单与工作台 3.查询与数据分析 三.系统升级实践 1.分布式订单缓存 2.无损迁移工艺 3.数仓衔接 四.核心问题精编 1.SQLServer & MySQL 流量迁移如何细粒度监控 2.如何解决双写期间DB一致性问题 3.引入订单缓存后导致的数据不同步问题处理 4.存量订单数据如何一次性校准 5.一些数据库特性差异问题 五.预警实践 1.百亿级数据差异校验预警 2.ALL Shard 实时订单总量监控 3.“流浪地球”实操

  • Python实战之实现简易的学生选课系统

    一.实验目的 实现学生选课系统 二.实验环境 Python3.6 pymysql(Python连接MySQL) xlrd(操作Excel) 三.程序结构 1.首先运行First_run.py: 功能:创建数据库.表等信息 2.运行seconnd_run.py: 功能: 实现学生选课 3.账号密码.xlsx: 存放学生信息(可以存班级花名册) 如: `` 四.数据库结构 表之间的联系 五.各表功能 student_login:存放学生账号信息(直接导入班级花名册,具体看代码) 字段: s_no:学

  • MySQL 存储过程的优缺点分析

    MySQL 5.0 版本开始支持存储过程.存储过程(Stored Procedure)是数据库中存储的复杂程序,以便外部应用调用的一种数据库对象.存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(可选)来调用执行. 存储过程可以有效提高 SQL 语句的复用率,并且可以将相关的一组 SQL 放入到存储过程中,从而避免了应用程序的多次查询带来的与 MySQL 服务器的连接延迟和占用的网络资源.下面是一个存储过程的示例,用于传入一个 id 来

  • MySQL实战教程之Join语句执行流程

    目录 Join语句执行流程 一.Index Nested-Loop Join 二.Simple Nested-Loop Join 三.Block Nested-Loop Join 四.总结 Join语句执行流程 Hi,我是阿昌,今天学习记录的是关于Join语句执行流程的内容. 在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类: 不让使用 join,使用 join 有什么问题呢? 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢? 创建两个表 t1 和 t2 来说明.

  • mysql全文搜索 sql命令的写法

    mysql全文搜索,sql的写法: MATCH (col1,col2,-) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION]) 比如: SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); MATCH()函数对于一个字符串执行资料库内的自然语言搜索.一个资料库就是1套1个或2个包含在FULLTEXT内的列.搜索字符串作为对 AGAINST()的参数而被

  • MySQL实战之Insert语句的使用心得

    一.Insert的几种语法 1-1.普通插入语句 INSERT INTO table (`a`, `b`, `c`, --) VALUES ('a', 'b', 'c', --); 这里不再赘述,注意顺序即可,不建议小伙伴们去掉前面括号的内容,别问为什么,容易被同事骂. 1-2.插入或更新 如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用"INSERT INTO - ON DUPLICATE KEY UPDATE -"语句: 情景示例:这张表

  • MySQL 权限控制细节分析

    今天周天,早上懒了一会儿,起的有点儿晚,中午没事儿干,重新看了看MySQL里面的权限控制模块,再次回头看,还是有很多收获的细节,这里记录一下,方便自己后续查看.     关于权限部分的内容,之前3月11号的文章中有写过一些,今天的内容,我们使用一个一个的细节知识点来撰写(本文中所使用的MySQL版本是5.7.16),在写这些知识点之前,我们首先介绍一下MySQL的权限控制粒度.然后了解一下MySQL中客户端发起请求的时候,服务端所做的核实工作,先来看权限控制粒度: 1.全局层级 全局权限使用于给

随机推荐