sql常见的聚合函数有哪些(SQL中的开窗函数详解可代替聚合函数使用)
sql常见的聚合函数有哪些
SQL中的开窗函数详解可代替聚合函数使用在没学习开窗函数之前,我们都知道,用了分组之后,查询字段就只能是分组字段和聚合的字段,这带来了极大的不方便,有时我们查询时需要分组,又需要查询不分组的字段,每次都要又到子查询,这样显得sql语句复杂难懂,给维护代码的人带来很大的痛苦,然而开窗函数出现了,曙光也来临了。如果要想更具体了解开窗函数,请看书《程序员的SQL金典》,开窗函数在mysql不能使用。
开窗函数与聚合函数一样,都是对行的集合组进行聚合计算。它用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用group by语句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。定义看不懂不要紧,会用就行。
举个简单例子 查询每个工资小于5000的员工信息(姓名,城市 年龄 薪水),并且显示小于5000的员工个数,尝试使用下面语句:
SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) FROM T_Person WHERE FSALARY<5000
消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'T_Person.FName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
可以使用子查询实现,语句:
SELECT FName, FCITY, FAGE, FSalary, ( SELECT COUNT(FName) FROM T_Person WHERE FSALARY<5000 ) PersonNum FROM T_Person WHERE FSALARY<5000
结果:
使用开窗函数实现,查询结果一模一样,就不粘贴了:
SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER() as PersonNum FROM T_Person WHERE FSALARY<5000
1.开窗函数格式:函数名(列) OVER(选项)
2.聚合开窗函数格式:聚合函数(列) OVER(PARTITION BY 字段)
over关键字把聚合函数当成聚合开窗函数而不是聚合函数,SQL标准允许将所有的聚合函数用做聚合开窗函数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号为空,则开窗函数会对结果集合的所有行进行聚合运算。
PARTITION BY来定义行的分区来进行聚合运算,与group by 不同,partition by 字句创建的分区是独立于结果集的,创建的分区只是用于进行聚合运算,而且不同的开窗函数所创建的分区不互相影响,例如:查询所有人员的信息,并查询所属城市的人员数以及同年龄的人员数:
SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY) CityNum, COUNT(FName) OVER(PARTITION BY FAGE) AgeNum FROM T_Person ORDER by FCITY
查询所有人员的信息,并查询所属城市的人员数,每个城市的人按照年龄排序语句:
SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY ORDER BY FAGE) CityNum FROM T_Person
3.排序开窗函数格式:排序函数() OVER(ORDER BY 字段)
(1)主要函数有ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()
ROW_NUMBER() 加行号,一般可以用于分页查询(现在被offset fetch取代 ),对于没有主键列的表加行号作用很明显,删除重复数据等。
按照薪水高低给所有人员排序,同样薪水的排名不一样,可以用row_number(),
with a as ( SELECT FName, FSalary, FCity, FAge, ROW_NUMBER() over(ORDER BY FSalary) as RowNum FROM T_Person ) SELECT * FROM a
使用rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名
SELECT FName, FSalary, FCity, FAge, RANK() over(PARTITION BY FCITY ORDER BY FSalary) as RankNum FROM T_Person
使用dense_rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名
ntile(数字) over(order by 字段):数字表示一组多少个数,并根据数量得出分组的数量
SELECT *,NTILE(5) OVER(ORDER BY FSalary) AS NileNum FROM T_Person
总结
到此这篇关于SQL中的开窗函数详解可代替聚合函数使用的文章就介绍到这了,更多相关SQL 开窗函数内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
- mysql快速备份数据(如何使用Maxwell实时同步mysql数据)
- mysql url连接参数配置(浅谈Mysql连接数据库时host和user的匹配规则)
- sqlserver去除空格函数(sqlServer实现去除字符串空格)
- thinkphp伪静态实例(thinkPHP+mysql+ajax实现的仿百度一下即时搜索效果详解)
- mysql update 多个字段(mysql update case 更新字段值不固定的操作)
- mysql锁技术(MySQL 加锁控制并发的方法)
- 创建数据库入门教程mysql(MySQL数据库安装教程一学就会)
- mysql清空数据库所有表格(MySQL用truncate命令快速清空一个数据库中的所有表)
- SQL语句的几个好习惯
- SQL Server中GROUPING SETS
- mysql删除数据库的命令(MySQL 线上数据库清理数据的方法)
- mysql数据库迁移命令(MySQL数据迁移相关总结)
- mysql xml转换json(Mysql将查询结果集转换为JSON数据的实例代码)
- sqlserver数据库文件包括(详解SQL Server数据库状态和文件状态)
- sqlserver2016的安装(Sql Server2016 正式版安装程序图解教程)
- MySql存储过程
- 王铲铲的致富之路无限金币卡法攻略教学(王铲铲的致富之路无限金币卡法攻略教学)
- 文明6金币太少怎么办 文明6无限刷钱教程(文明6金币太少怎么办)
- 开国中将,王牌军63军首任政委,两个连襟一个上将一个少将传为佳话(王牌军63军首任政委)
- 臭名昭著的731部队最高负责人 石井四郎(臭名昭著的731部队最高负责人)
- 王牌部队,你看的剧情我看的时尚(你看的剧情我看的时尚)
- 被鉴定的古董价值300万 当心,你可能遇到诈骗了(被鉴定的古董价值300万)
热门推荐
- dedecms怎么上线(dedecms关闭后台/会员/留言/板验证码的方法)
- mysql事务未提交会锁表吗(Mysql事务中Update是否会锁表?)
- css经典技巧(CSS性能优化提高css性能的方法)
- display:inline、block、inline-block 的区别
- Visual Studio代码缩略图
- html5复选框属性教程(HTML5单选框、复选框、下拉菜单、文本域的实现代码)
- dedecms有哪些功能(织梦DedeCMS v5.7全文检索使用说明sphinx)
- canvas时钟代码(canvas实现烟花的示例代码)
- linux怎么设置php代码(linux Cron定时执行带参数的PHP代码)
- docker进入mysql查看路径(Docker 环境运行 Mysql 和开启 Binlog 配置主从同步的设置方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9