您现在的位置是:群英 > 数据库 > 关系型数据库
Oracle的窗口函数是怎么理解,还有哪些分析函数
Admin发表于 2022-06-17 17:36:351946 次浏览
这篇文章分享给大家的内容是关于Oracle的窗口函数是怎么理解,还有哪些分析函数,本文介绍得很详细,内容很有参考价值,希望可以帮到有需要的小伙伴,接下来就让小编带领大家一起了解看看吧。

1、什么是窗口函数?

窗口函数也属于分析函数。Oracle从8.1.6开始提供窗口函数,窗口函数用于计算基于组的某种聚合值,
窗口函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。
与聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行

基本语法: ‹分析函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)。
语法中的‹分析函数›主要由序列函数(rank、dense_rank和row_number等组成)
与聚合函数(sum、avg、count、max和min等)作为窗口函数组成。

从窗口函数组成上看,它是group by 和 order by的功能组合,group by分组汇总后改变了表的行数,一行只有一个类别,而partiition by则不会减少原表中的行数。
恰如窗口函数的组成,它同时具有分组和排序的功能,且不减少原表的行数。
OVER 关键字表示把函数当成窗口函数而不是聚合函数。SQL 标准允许将所有聚合函数用做窗口函数,使用 OVER 关键字来区分这两种用法。

2、窗口函数——开窗

OVER 关键字后的括号中经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空,则窗口函数会对结果集中的所有行进行聚合运算。

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

为什么叫开窗呢?

因为在over()括号中的,partition() 函数可以将查询到的数据进行单独开一个窗口处理。譬如,查询每个班级的学生的排名情况,查询每个国家的历年人口等,诸如此类,都是在查询到的每一个班级、每一个国家中都开一个窗口,单独去执行命令。

rows和range分别表示选择后几行、选择数据范围
理解 rows between 含义,也叫做window子句:

preceding:往前following:往后current row:当前行unbounded:无边界,unbounded precending 表示从最前面的起点开始, unbounded following:表示到最后面的终点注:不加 partition by 的话则把整个数据集当作一个分区,不加 order by的话会对某些函数统计结果产生影响,如sum()

3、一些分析函数的使用方法

1.聚合函数

聚合函数 定义
sum() 求和
max() 求最大值
min() 求最小值
avg() 求平均值
count() 统计数

2.序列函数

序列函数 定义
row_number() 按照值排序时产生一个自增编号,值相等时不会重复,不会产生空位
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位

row_number()

select * ,row_number()over(oder by 成绩 desc) as 排名 from 班级表

查询结果:

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1    |
| 李四       | 女      | 3    | 100  | 2    |
| 张三       | 女      | 1    | 100  | 3    |
| 王五       | 女      | 2    | 99   | 4    |
| 赵四       | 男      | 2    | 90   | 5    |
| 孙六       | 男      | 2    | 90   | 6    |
| 喜羊羊     | 男      | 3    | 85   | 7    |
| 美羊羊     | 女      | 4    | 82   | 8    |
| 懒洋洋     | 女      | 1    | 80   | 9    |
| 慢羊羊     | 女      | 2    | 70   | 10   |
+------------+--------+------+------+------+

rank()

select * ,rank()over(oder by 成绩 desc) as 排名 from 班级表

查询结果:

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1    |
| 李四       | 女      | 3    | 100  | 1    |
| 张三       | 女      | 1    | 100  | 1    |
| 王五       | 女      | 2    | 99   | 4    |
| 赵四       | 男      | 2    | 90   | 5    |
| 孙六       | 男      | 2    | 90   | 5    |
| 喜羊羊     | 男      | 3    | 85   | 7    |
| 美羊羊     | 女      | 4    | 82   | 8    |
| 懒洋洋     | 女      | 1    | 80   | 9    |
| 慢羊羊     | 女      | 2    | 70   | 10   |
+------------+--------+------+------+------+

dense_rank()

select * ,row_number()over(oder by 成绩 desc) as 排名 from 班级表

查询结果:

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1   |
| 李四       | 女      | 3    | 100  | 1   |
| 张三       | 女      | 1    | 100  | 1   |
| 王五       | 女      | 2    | 99   | 2   |
| 赵四       | 男      | 2    | 90   | 3   |
| 孙六       | 男      | 2    | 90   | 3   |
| 喜羊羊     | 男      | 3    | 85   | 4   |
| 美羊羊     | 女      | 4    | 82   | 5   |
| 懒洋洋     | 女      | 1    | 80   | 6   |
| 慢羊羊     | 女      | 2    | 70   | 7   |
+------------+--------+------+------+------+

3.其他类

其他类 定义
percent_rank() 分组内当前行的rank值-1/分组内总行数-1
lag() 用于统计窗口内往上第n行值 第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL
lead() 用于统计窗口内往下第n行值 第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL
ntile() 用于将分组数据按照顺序切分成n片,返回当前切片值
first_value() 取分组内排序后,截止到当前行,第一个值
last_value() 取分组内排序后,截止到当前行,最后一个值
cume_dist() 返回小于等于当前值的行数/分组内总行数

4、OVER()参数——分组函数

partition by 子句:

窗口函数的 over 关键字后括号中的可以使用 partition by 子句来定义行的分区来供进行聚合计算。
与 group by 子句不同,partition by 子句创建的分区是独立于结果集的,创建的分区只是供进行
聚合计算的,而且不同的窗口函数所创建的分区也不互相影响。

5、OVER()参数——排序函数

order by 子句:

窗口函数中可以在over关键字后的选项中使用order by 子句来指定排序规则,而且有的窗口函数还
要求必须指定排序规则。使用order by 子句可以对结果集按照指定的排序规则进行排序,并且在一个
指定的范围内进行聚合运算。
语法:ORDER BY字段名  RANGE|ROWS  BETWEEN边界规则1  AND  边界规则2

PARTITION BY子句和ORDER BY 可以共同使用,从而可以实现更加复杂的功能



通过以上内容的阐述,相信大家对“Oracle的窗口函数是怎么理解,还有哪些分析函数”已经有了进一步的了解,更多相关的问题,欢迎关注群英网络或到群英官网咨询客服。

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。

标签: oracle窗口函数
相关信息推荐
2022-08-11 17:37:20 
摘要:在oracle中,like语句常与where语句配合使用,用于在一个字符型字段中检索包含的对应子串;该语句主要是针对字符型字段,语法为“select * from 表名 where 字段名 like 对应值(子串)”。
2022-06-13 17:16:54 
摘要:在SQL中可以通过DELETE方法删除某一行的数据,使用语法如“delete from ms_cf01 where brxm='张三' and id='7598';”,其中“ms_cf01”表示所要删除那条数据所属的表。
2022-05-12 16:35:47 
摘要:在mysql中,可以利用SELECT语句配合SUM()函数和“GROUP BY”来分组求和,语法为“SELECT 查询字段 SUM(求和字段) AS 字段 FROM 表名 WHERE 条件 GROUP BY 分组字段一,分组字段二;”。
群英网络助力开启安全的云计算之旅
立即注册,领取新人大礼包
  • 联系我们
  • 24小时售后:4006784567
  • 24小时TEL :0668-2555666
  • 售前咨询TEL:400-678-4567

  • 官方微信

    官方微信
Copyright  ©  QY  Network  Company  Ltd. All  Rights  Reserved. 2003-2019  群英网络  版权所有   茂名市群英网络有限公司
增值电信经营许可证 : B1.B2-20140078   粤ICP备09006778号
免费拨打  400-678-4567
免费拨打  400-678-4567 免费拨打 400-678-4567 或 0668-2555555
微信公众号
返回顶部
返回顶部 返回顶部