首页 > 数据库 >mysql基础的sql语法

mysql基础的sql语法

时间:2023-06-21 15:12:55浏览次数:54  
标签:窗口 函数 sql rank 语法 mysql 成绩 SELECT 1.1

1. mysql基础语法

目录

1.1. MYSQL的窗口函数

1.1.1. 什么是窗口函数

含义:窗口函数也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据进行实时分析处理。

作用:
解决排名问题, e.g.每个班级按成绩排名
解决TOPN问题, e.g.每个班级前两名的学生

语法:
select 窗口函数 over (partition by 用于分组的列名, order by 用于排序的列名

分类:
专用窗口函数:rank(),dense_rank(),row_number()
汇总函数:max(),min(),count(),sum(),avg()

注意:窗口函数是对where后者group by子句处理后的结果进行操作,因此按照SQL语句的运行顺序,窗口函数一般放在select子句中。

1.1.2. 窗口函数的用法

1.1.2.1. 专用窗口函数

rank()函数

按班级分类,将成绩降序排序

SELECT*,
rank() over (PARTITION BY 班级 ORDER BY 成绩 DESC)  AS ranking
FROM class;

说明

rank()是排序函数,括号中不需要有参数;
通过partition by将班级分类,相当于之前用过的group by子句功能,但是group by子句分类汇总会改变原数据的行数,而用窗口函数自救保持原行数;
通过order by将成绩降序排列,与之前学的order by子句用法一样,后边可以升序asc或者降序desc;

总结:

窗口函数这里的“窗口”表示范围,可以理解为将原数据划分范围,即分组,然后用函数实现某些目的
窗口函数有分组和排序的功能
不减少原表的行数

1.1.2.2. 其他专用窗口函数:dense_rank/row_number

用法与rank()函数相同

SELECT*,
dense_rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC)  AS ranking
FROM class;
SELECT*,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC)  AS ranking
FROM class;

区别
当成绩相同时,会存在并列的情况,主要区别是三个函数如何处理并列情况:

  • 在rank()函数,如果有并列情况,会占用下一个名次的位置,比如,成绩为100的学生有三个并列第一,那么99分的学生是第二名,通过rank()函数,名次是:1,1,1,4;
  • 在dense()函数中,如果有并列的情况,不会占用下一个名词,同用上个例子,名次是:1,1,1,2;
  • 在row_number()函数中,会忽略并列的情况,同用上述例子,名次是:1,2,3,4;

案例

#要求按成绩排名,如果出现并列,需要出现类似1,1,1,2的形式
SELECT *,
dense_rank() over (PARTITION BY 班级 ORDER BY 成绩) AS 排名
FROM class;

#在test1表中按成绩排名,如果有并列情况,则两个分数的排名相同,也就是平分后的下一个名词应该是下一个连续的整数值,名词之间不应该有间隔
dense_rank () over (ORDER BY score DESC) AS ranking
FROM test1;

1.1.3. 经典面试问题-topN问题

1.1.3.1. 相关业务问题:

每个类别下用户最喜欢的产品是哪个?
每个类别下用户点击最多的5个商品是什么?
这类问题就需要分组取最大值,最小值,每组最大的n条记录

1.1.3.2. 解决方法

分组取最大值(用关联子查询)

#查询每个学号成绩是最大的所有信息
SELECT* FROM score AS a
WHERE 成绩=( SELECT MAX(成绩)
FROM score AS b
WHERE a.`学号`=b.`学号`);

分组取最小值

#查询每个学号成绩是最大的所有信息
SELECT* 
FROM score AS a 
WHERE 成绩=( SELECT MIN(成绩) 
FROM score AS b 
WHERE a.`学号`=b.`学号`); 

查询每个学生成绩最高的两个科目

SELECT *
FROM (SELECT*,row_number() over (PARTITION BY 姓名 ORDER BY 成绩 DESC) AS ranking 
FROM test1) AS newtest
WHERE ranking<=2;

说明
为了不受并列的影响,该题用row_number()
注意在子查询后边加别名
易错的写法:select*,row_number() over(partition by 姓名 order by 成绩 desc) as ranking from test where ranking<=2;按照sql运行顺序,where后边不能加别名,因为select子句在where子句之后运行
涉及到既要分组又要排序的情况,要想到用窗口函数

1.1.3.3. TOPN问题模板

SELECT *
FROM (SELECT*,row_number() over (PARTITION BY 姓名 ORDER BY 成绩 DESC) AS ranking
FROM test1) AS newtest
WHERE ranking<=N;

1.1.4. 聚合函数作为窗口函数

作用:聚合函数作为窗口函数,是起到"累加/累计"的效果,比如,就是截止到本行,最大值?最小值是多少
与专用窗口函数的区别:括号中需要有指定列,不能为空

用法:与专用窗口函数相同

#查询成绩的累加 
SELECT*, SUM(成绩) over (ORDER BY 成绩 DESC) AS '求和' 
FROM class; 

案例

查找单科成绩高于该科目平均成绩的学生名单

法一解题思路:
单科成绩,表示需要按科目进行分组,分组有两种:group by子句和窗口函数的partition by;
要求平均值,需要通过avg()实现,并且找到大于平均值的学生,那么不能减少行数,则用partition by;

步骤:
聚合函数作为窗口函数

与平均值比较
注意这里不能用where 成绩>分组平均值,因为where子句在select子句之前执行
正确语句是套用子查询:

select *
from (select *,
avg(成绩) over(parition by 科目) as 分组平均值)
from test) as a
where 成绩>分组平均值;

法二关联子查询
思路
单科成绩:需要对每门科目进行分组
平均成绩:avg()求每组的平均值
学生名单:输出信息中需要有学生姓名

步骤1:求分组平均值

SELECT AVG(成绩) AS 平均值,科目 
FROM test3  
GROUP BY 科目; 

步骤2:比较

SELECT* 
FROM test3 AS a 
WHERE 成绩>(SELECT AVG(成绩) AS 平均值 
FROM test3 AS b 
WHERE a.`科目`=b.`科目`); 

因为是按照科目分组,所以应该将科目进行关联

1.1.5. 窗口函数的移动平均(以平均值为例)

作用:通过preceding,following,current row等调整作用范围,基本语法为
ROWS BETWEEN 一个时间点 AND 一个时间点

时间点可以表示为:

n PRECEDING : 前n行
n FOLLOWING:后n行
CURRENT ROW : 当前行
UNBOUNDED PRECEDING:窗口第一行
UNBOUNDED FOLLOWING:窗口的最后一行

用法:

查询前两行到当前行的平均成绩

SELECT *,
AVG(成绩) over (ORDER BY 姓名 ROWS 2 preceding) AS result 
FROM test3;

解释:
rows N proceding---N表示在当前行的前N行,比如,N=2,当前行在第4行,那么该平均值是第2行,第3行,第4行,这三行数据的平均值

注意事项
窗口函数中的如果省略partition by,则结果不进行分组,则以整个表为范围,也就是窗口为整个表;
如果省略order by 则不进行排序;

1.1.6. 总结

1.窗口函数的语法:
窗口函数 over (partition by 用于分列的列名 order by 用于排序的列名);
2.功能:既能分组又可以排序,且不改变行数
3.分类:

  • 专用窗口函数---rank(),dense_rank(),row_number()(括号中没有参数,注意区分三者区别)
  • 聚合函数---max(),min(),sum(),avg(),count() (括号中有参数)
    4.注意事项
    原则上一般写在select子句中
    5.应用场景
    经典TOPN问题:找出每个部门工资排名前N的员工

1.1.6.1. 模板

select*
from(select*,row_number() over (partition by 部门 order by salary desc) as ranking
from 表名) as a
where ranking<=N;

注意:不要忘记起别名,子查询的别名以及排序结果的别名
经典排名问题
业务需求“在每个组内排名”,比如,每个部门按业绩来排名
在每个组内比较问题
可以用关联子查询,也可以用窗口函数实现

标签:窗口,函数,sql,rank,语法,mysql,成绩,SELECT,1.1
From: https://www.cnblogs.com/xulinforDB/p/17496257.html

相关文章

  • mysql超时参数详解
    1.mysql超时参数详解目录1.mysql超时参数详解1.1.分类1.1.1.连接、网络类超时1.1.2.connect_timeout:默认为10S1.1.3.wait_timeout:默认是8小时,即28800秒1.1.4.interactive_timeout:默认是8小时,即28800秒1.2.net_read_timeout:默认是30S1.2.1.net_write_timeout:默认是60S1.2......
  • mysql索引及索引创建原则
    1.mysql索引及索引创建原则目录1.mysql索引及索引创建原则1.1.使用场景1.1.1.什么时候用索引1.1.2.索引的弱点1.1.3.MySQL会使用到索引的场景如下:1.2.查看表上的索引1.3.索引类型1.3.1.组合索引1.3.1.1.组合索引生效规则1.3.2.前缀索引1.3.3.函数索引1.3.4.唯一索......
  • mysql索引和基本概念
    1.mysql索引和基本概念目录1.mysql索引和基本概念1.1.声明1.2.什么是索引1.3.二分查找法(BinarySearch)1.4.二叉查找树(BST)1.4.1.二叉树的特点1.4.2.二叉树存在的问题1.5.平衡二叉树(AVLTree)1.5.1.平衡二叉树的特点1.6.索引需要存储什么1.7.B树的特点1.7.1.B树是......
  • mysql 密码插件 validate_password
    MySQL密码增强插件2016-07-0110:02pursuer.chen阅读(668)评论(0)编辑[收藏](javascript:void(0))介绍以前没有太注意MySQL密码安全策略的配置方法,只是人为了将密码设为复杂密码,但是没有找到配置的方法,今天姜承尧的微信公众号正好发布了一篇关于这个的文章,所以在这里也顺......
  • MySQL笔记整理
    SELECT0+'123.00';SELECT0+'123.0qwe';SELECT0+'qwe1';SELECT0+null;SELECT'123.00'/4;SELECT'123.0qwe'/4;SELECT'qwe1'/4;SELECT'1qwe'/4;SELECTnull/4;SELECTconvert(......
  • 离线安装binlog2sql恢复误删除数据
    下载离线包先在一台能上网的机器下载好离线包gitclonehttps://github.com/danfengcao/binlog2sql.gitpip3download-rrequirements.txt./将依赖包下载到./目录因为是mysql8.0还需要安装PyMySQL和mysql-replicationpip3downloadPyMySQL==0.9.3./pip3downloadmysq......
  • MySQL自带的性能压力测试工具mysqlslap
    1.MySQL自带的性能压力测试工具mysqlslap目录1.MySQL自带的性能压力测试工具mysqlslap1.1.概述1.2.常用参数[options]详解1.3.测试范例:1.3.1.实例11.3.2.实例21.3.3.实例3(自定义sql语句)1.3.4.实例4(指定sql脚本)1.3.5.实际测试中的复杂情况。(指定表字段)1.4.测试结......
  • mysql proxy实现读写分离
    Mysql-proxy实现读写分离目录Mysql-proxy实现读写分离环境说明Mysql-proxy简介部署mysql-proxy服务读写分离测试总结环境说明Mysql-proxy简介mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡,读写分离,failover等MySQLProxy就是这么一个中间层代理,简单的说,MySQLPro......
  • MySQL性能压测工具SysBench详解(非常详细)
    MySQL性能压测工具SysBench详解(非常详细)概述掌握数据库的性能情况是非常必要的。本文介绍了MySQL基准测试的基本概念,以及使用sysbench对MySQL进行基准测试的详细方法;基准测试与压力测试简介1、什么是基准测试数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可......
  • mysql的用户和权限管理
    1.mysql用户和权限管理目录1.mysql用户和权限管理1.1.常用授权语句1.1.1.5.7以及以前的版本1.1.1.1.存储过程权限管理1.1.2.8.0的版本:1.1.3.删除用户及权限1.1.4.修改1.1.4.1.修改密码1.1.4.2.修改用户账号名称1.1.5.回收权限1.1.6.grant授权和直接操作权限表的区别......