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时候，取默认值，如不指定，则为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 ⼦句可以对结果集按照指定的排序规则进⾏排序，并且在⼀个
指定的范围内进⾏聚合运算。
orcl 中trunc函数的使用方法语法：ORDER BY字段名 RANGE|ROWS BETWEEN边界规则1 AND 边界规则2
PARTITION BY⼦句和ORDER BY 可以共同使⽤，从⽽可以实现更加复杂的功能
到此这篇关于Oracle数据库分析函数⽤法的⽂章就介绍到这了，更多相关Oracle函数内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论