伪列与开窗(分析函数) oracle

一.什么是伪列，它有什么特点？

伪列，顾名思义，虚拟化的列，即表中的列

1.生命周期

伪列rownum的生命周期在查询中开始自动生成于from直到select完全形成此序号，用户一般无法更改，即伪列存在的前提是先有结果表.

值得注意的是，伪列的生成机制

当我们正常查询rownum时返回的是排好序的值，但是如果加上order by 将会打乱顺序

 如图所示，这是因为rownum先给定序号再排序，将序号确定后再根据deptno进行排序就会打乱rownum.

而我们先排序后再定rownum就不会乱序

查询员工信息表中的第二个员工信息。

select * from (select rownum rn,e.* from emp e) where rn =3;--必须用别名

值得一提的是：伪列由结果集的生成而生成，排序不会生成新的结果集

以下是ORACLE官方对rowid的解释：

Oracle 使用通用 rowid ( urowids ) 来存储索引组织表和外部表的地址。索引组织表具有逻辑 urowid，外部表具有外部 urowid。两种类型的 urowid 都存储在ROWID伪列中（堆组织表的物理 rowid 也是如此）。

Oracle 根据表的主键创建逻辑 rowid。只要主键不改变，逻辑 rowid 就不会改变。索引组织表的ROWID伪列的数据类型为UROWID. 您可以像访问ROWID堆组织表的伪列一样访问此伪列（使用SELECT...ROWID语句）。如果要to存储索引组织表的 rowid，则可以为表定义类型列UROWID并将ROWID伪列的值检索到该列中。

由此我们可知rowid的唯一标识是由primary决定，只要primary不发生变化，rowid就处于'锁定'状态

2.rownum的独立性

前面说过rownum的唯一标识性、锁定性

rownum还具有独立性：rownum不属于任何一个表，也不能被增删改

3.rownum的判断逻辑特殊性

当我们运行 

SELECT * FROM EMP WHERE ROWNUM>0;

SELECT * FROM EMP WHERE ROWNUM>1;

运行语句一时将会打印出所有结果，而运行语句二将没有结果.

rownum的逻辑判断：动态排序性

rownum永远从1开始排序

当设置条件  rownum>1 时，由于rownum的动态排序性，从第一条语句开始判断1>1?

判断失败从下一句语句开始排序，依然判断1>1?

直到全部排序失败.

想要合适的进行查询，要将结果集先生成，将rownum从动态转到静态，即可查询.

4.rownum是oracle数据库从数据文件或缓冲区中读取数据的顺序 类似于指针

5.rowid的唯一标识性可以实现去重

rowid的组成：

 数据对象编号：AAAR3S

文件编号：AAE

块编号：AAAACX

行编号：AAA

rowid作为数据库中数据的物理标识（唯一性），18位的rowid可以帮助用户使用oracle迅速找到该条数据，每一条数据在被储存到oracle的时候，数据库都会给予一条唯一的18位标识，用来记录该条记录在数据库中的物理地址.

6.rownum的范围查询及其分页作用

rownum可以在指定字段排序完成后筛选出指定范围的数据.

所谓分页即在已知每页放的数据的数量下，求指定页数的数据，通过rownum和别名就可以完成指定范围的数据.

二.开窗函数

1.概念

 顾名思义，开窗是开辟一扇新的路径更好的解决现有的问题

与聚合函数类似，开窗函数也是对数据集分组进行聚合计算，与聚合函数每组返回单一的值不同的是，开窗函数每组可以返回多个值.

作为21世纪的新函数，开窗在不同的tool中也有别名：分析函数

开窗函数相比于普通的聚合函数，异常强大，专门为一些特殊场景应运而生

普通分析函数的格式为
聚合函数()/关键字开窗函数() OVER(参数1，参数2，参数3)
其中三个参数在聚合函数时都可以省略
参数一：partition by...：和group by相同可以对数据进行分组，不同的是，partition by是重启一列进行分区，两个区由边界分隔，开窗函数在不同的区分别执行，在跨区时会初始化重新开始执行.
参数二：order by...：对指定的n个字段进行排序(n>=1)，按照排序的结果进行编号，若使用规定的row_number()、dense_rank()、rank()则会按照规定的规则对(重复)数据进行排序

row_number()：普通排序，不考虑重复与否，按照顺序排序(参数一二均不能省略)
dense_rank()：排序，若有重复则并列且排序连续，dense意为浓密的、稠密的，可以理解它规定的即使重复也排序连续(order by 不可省略)
rank()：排序，若有重复则并列但会跳过序号.(order by 不可省略)

参数三：开窗范围

当我们不设置任何参数时相当于执行普通的聚合函数

 当我们设置了相应的参数后

 当区内job相同时，则平均工资都相同，当job不同的时候，按照job排序，对于str的排序，计算机会比较job的首个字符，可以看到10号部门的第一个员工工资为10号部门的平均薪资，后面的平均薪资算法为(10组的平均工资加上他自己的工资除以2，往后类推)

所以这句语句的意义是：查询各部门员工按照岗位排序的平均工资.

2.重复排序： 
row_number()：普通排序，不考虑重复与否，按照顺序排序
dense_rank()：排序，若有重复则并列且排序连续，dense意为浓密的、稠密的，可以理解它规定的即使重复也排序连续
rank()：排序，若有重复则并列但会跳过序号.

3.偏移排序：

LAG(EXP_STR,OFFSET,DEFVAL)

LEAD(EXP_STR,OFFSET,DEFVAL)

其中第一个参数是指选择的列，第二个参数是对应方向的偏移量，第三个参数是当超过范围时的默认值）

默认偏移值是1，当指定的偏移值没有对应的字段，就会返回null

SELECT O.*,LEAD(AMOUNT)OVER(PARTITION BY USER_NO ORDER BY AMOUNT) 偏移后 FROM ORDER_INFO O;

4.指定数值

FIRST_VALUE()

SELECT O.*,FIRST_VALUE(AMOUNT)OVER(PARTITION BY USER_NO ORDER BY AMOUNT) 该区间首个数据 FROM ORDER_INFO O;

 

FIRST_VALUE很好理解，按照数据库一条一条读取的方式，总会先拿到第一个数据

LAST_VALUE()

同样的 数据，last_value的值不再按照最后一个计算，因为数据库是一条一条读入，所以按照动态读取数据，id为1、user_no为001的行只有一条数据，所以返回该数据

即：unbounded preceding and current row

想要显示最后一个对order by加上参数 unbounded preceding and unbounded following

 

 

5.开窗范围：

PRECEDING:之前的的

FOLLOWING之后的

CURRENT:当前的

UNBOUNDED: 不受限制的

开窗范围作为一种更确切确定排序范围的手段，紧跟在order by后面，甚至有些实例要求有一定的范围

语法：order by 字段名 ranges/rows between 边界规则1 and 边界规则2

1）从不受限制的之前到现在的行：从第一行到当前行

SELECT E.*,
       AVG(SAL) OVER(PARTITION BY DEPTNO ORDER BY JOB ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 排序
  FROM EMP E
  

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是窗口范围最常用的一种定位框架. 是按照行数定位的

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是按照范围进行定位的
具体区别如图所示：

SELECT O.*,
       SUM(AMOUNT) OVER(PARTITION BY USER_NO ORDER BY AMOUNT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) row排序
  FROM ORDER_INFO O;
  SELECT O.*,
       SUM(AMOUNT) OVER(PARTITION BY USER_NO ORDER BY AMOUNT RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) range排序
  FROM ORDER_INFO O;

 

从图中可以看出，row以行作为求值范围，逐行求和，range以数值范围作为范围，当user_no为002时amount重复，导致排序的结果是所有重复的值都是一样的求和值

这决定了他们的应用场景不同

此开窗范围还可以用于排序、求最大值最小值等

SELECT O.*,
       MAX(AMOUNT) OVER(PARTITION BY USER_NO ORDER BY CREATE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 排序
  FROM ORDER_INFO O;
  SELECT O.*,
       MAX(AMOUNT) OVER(PARTITION BY USER_NO ORDER BY AMOUNT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 排序
  FROM ORDER_INFO O;

当使用max()时，求的是到当前行的最大值，注意不要让让max的参数和order by 的参数一致，否则看不出结果

 这个开窗范围也可以省略，本身一个个求值就相当于是放置到目前为止的最大值.

2）当前行+之前N+之后N

SELECT SUM(AMOUNT)OVER(PARTITION BY USER_NO ORDER BY AMOUNT ROWS BETWEEN N PRECEDING AND N FOLLOWING) 前N和后N FROM ORDER_INFO

实例如图所示：

当该行处于第一行或者最后一行，只计算另一端.

使用range返回的都是当前行的amount

3）当前行后N行到后M行

SELECT O.*,SUM(AMOUNT)OVER(PARTITION BY USER_NO ORDER BY AMOUNT ROWS BETWEEN N FOLLOWING AND M FOLLOWING ) 后N到后M FROM ORDER_INFO O ;

注意 处于边界的行没有后续值 开窗计算后的值是null而不是0

使用range返回的都是null

开窗范围还有很多

基本注意事项就是关于空值、省略、使用RANGE/ROW 区别的问题

前N到后N ：N PRECEDING AND N FOLLOWING

前N到当前: N PRECEDING AND CURRENT ROW

前N到最后:N PRECEDING AND UNBOUNDED FOLLOWING

当前到最后:CURRENT ROW AND UNBOUNDED FOLLOWING

当前到后N:CURRENT ROW AND N FOLLOWING

第一到当前:UNBOUNDED PRECEDING AND CURRENT ROW

第一到最后:UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

第一到后N:UNBOUNDED PRECEDING AND N FOLLOWING