Oracle数据库分析函数用法

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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数据库分析函数用法的文章就介绍到这了,更多相关Oracle函数内容请搜索极客世界以前的文章或继续浏览下面的相关文章希望大家以后多多支持极客世界！