Oracle数据库分析函数用法
- 格式：pdf
- 大小：110.66 KB
- 文档页数：3
Oracle数据库分析函数⽤法⽬录
1、什么是窗⼝函数？
2、窗⼝函数——开窗
3、⼀些分析函数的使⽤⽅法
4、OVER()参数——分组函数
5、OVER()参数——排序函数
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时候，取默认值，如不指定，则为NULLntile()⽤于将分组数据按照顺序切分成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数据库分析函数⽤法的⽂章就介绍到这了,更多相关Oracle函数内容请搜索以前的⽂章或继续浏览下⾯的
相关⽂章希望⼤家以后多多⽀持！
