6.oracle开窗函数

===开窗函数===
1.1伪列定义
伪列：可以理解为一种伪装起来的，不易看到的字段，除非特意查询，否则不会显现
1.2 ROWNUM
    ROWNUM是对查询结果自动生成的一个从1开始的，连续的序号.他会随着查询步骤进而不断地变化，直到select子句查询结束;
    我们说rownum不支持>, >=, =, between and，只支持<, <=等
    虽说不支持，但并不会报错，只是返回的数据为空，这是因为根本不能满足这样的where条件。
    如where rownum>2：
    取回第1条数据的rownum为1，不满足，就舍弃这条记录。
    看下一条，然后取第2条数据的rownum还是为1，还是不满足，再舍弃。
    以此类推，最终舍弃了所有的数据，这就是所谓不支持的原因。;
SELECT EMP.*,ROWNUM FROM EMP;
SELECT EMP.*,ROWNUM FROM EMP where deptno=20;
SELECT EMP.*,ROWNUM FROM EMP order by SAL ASC; --SELECT执行优先级高于 order by 而 rownum跟随select变化
SELECT * FROM EMP WHERE  ROWNUM<4;
SELECT * FROM (select T.*,ROWNUM ROW_S FROM (SELECT * FROM EMP ORDER BY SAL DESC) T) WHERE ROW_S BETWEEN 2 AND 4;

SELECT * FROM EMP ORDER BY SAL;
SELECT T.*,ROWNUM RN FROM (SELECT * FROM EMP ORDER BY SAL) T;

SELECT *
  FROM (SELECT T.*, ROWNUM RN FROM (SELECT * FROM EMP ORDER BY SAL) T) TT
 WHERE RN BETWEEN 4 AND (SELECT COUNT(ENAME) FROM EMP) - 4;
 
  1.3ROWID  每一条数据存入数据库中，都会生成一条固定不变的，唯一的18位字符串。用以记录该数据在数据库中的物理位置
            在数据库直接查询数据rowid，始数据查询最快的，最准确的，但一般不用
  数据库查询数据方法：
  1.不直接使用rowid：
    1.1全盘扫描：按照条件，将所有文件从头到尾挨个判断，条件成立获取对应的rowid，最后按照rowid提取数据
    1.2索引扫描：按照条件，从索引目录中找到对应的rowid，最后按照rowid提取数据
  2.直接使用rowid：直接按照rowid提取数据;  
    以上三种数据依次提高效率;

SELECT EMP.*,ROWID FROM EMP;
SELECT EMP.*,ROWID FROM EMP WHERE ROWID='AAAR3sAAEAAAACXAAA';

2.1开窗函数的定义及语法 
   开窗函数又名分析函数，窗口函数，OLAP函数
   聚合函数：将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一个结果
   开窗函数：将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果
   OLAP:事务处理 -快    OLTP 数据分析-准确
  
   开窗函数的语法形式：
   分析函数名() over() --over是开窗函数的一个标志
   
   分析函数名：
   1.聚合函数：SUM AVG MAX MIN COUNT
   2.排序函数：ROW_NUMBER :连续且唯一  RANK:连续不唯一   DENSE_RANK ：不连续不唯一
   3.偏移类    LAG 向下  LEAD 向上    偏移量不允许写负数
       /*用法（以LAG为例）：LAG(COL_NAME,[OFFSET],[DEFVAL])OVER():向前偏移N行取数
      COL_NAME:要分析的字段
      OFFSET:偏移量 --默认偏移一行
      DEFVAL:默认返回值 --默认返回空null 返回的类型要与分析字段的类型一样*/
   分析子句：分组（PARTITION BY） 排序（order by）窗口（rows） --rows 和 order by联合使用
   分析子句不一定要写，也不一定全部都写，分析子句的使用按需

--创建测试表T_Person
CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT);
create table t_test (v_name varchar2(30),v_subject varchar2(30),v_score number);
--查看数据
select * from T_TEST;  ----v_name  v_subject  v_score
SELECT * FROM T_Person;  --FName   FCity   FAge    FSalary


SELECT T.*,sum(FSalary) over() sum_FSalary FROM T_PERSON T;
SELECT T.*,AVG(FSalary) over(partition by fcity) avg_FSalary FROM T_PERSON T; 
SELECT T.*,MAX(FSalary) over() max_FSalary FROM T_PERSON T;
SELECT T.*,MIN(FSalary) over() min_FSalary FROM T_PERSON T;
SELECT T.*,COUNT(DISTINCT FAGE) over() count_FSalary FROM T_PERSON T;
SELECT T.*,ROW_NUMBER() over(order by FSalary desc) row_FSalary FROM T_PERSON T; ---需要加order表达式1234567
SELECT T.*,dense_rank() over(order by FSalary desc) dense_FSalary FROM T_PERSON T; ---1223456
SELECT T.*,rank() over(order by FSalary desc) rank_FSalary FROM T_PERSON T; ---1224567
SELECT T.*,LAG(FSALARY,2) over(PARTITION BY FCITY order by FSalary desc) rank_FSalary FROM T_PERSON T;  --向下偏移
SELECT T.*,LEAD(FSALARY,2) over(PARTITION BY FCITY order by FSalary desc) rank_FSalary FROM T_PERSON T;  --向上偏移

ORDER BY子句：
/*开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。
使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。
ORDER BY子句的语法为：*/
ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
/*RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义*/
窗口条件
PRECEDING：之前的  
FOLLOWING：之后的
CURRENT：当前的
UNBOUNDED：不受限的
ROW： 行
ROWS BETWEEN N PRECEDING AND N FOLLOWING -- 前面N行---- 后面N行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 第一行 - 当前行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 当前行 - 最后一行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 第一行 - 最后一行
总结：
1 窗口子句不能单独出现，必须要有排序子句出现的情况下才能指定窗口范围
2 若出现排序子句（ORDER BY），同时未指定窗口范围，默认的窗口范围是第一行到当前行；
  若未出现排序子句，同时未指定窗口范围，默认的窗口范围是第一行到最后一行;

SELECT T.*,sum(FSalary) 
over(partition by fcity order by fsalary rows between 1 PRECEDING AND 1 FOLLOWING ) sum_FSalary 
FROM T_PERSON T;-- 前面N行---- 后面N行

SELECT T.*,sum(FSalary) 
over(partition by fcity order by fsalary rows between unbounded preceding and current row) sum_FSalary 
FROM T_PERSON T;-- 第一行 - 当前行

SELECT T.*,sum(FSalary) 
over(partition by fcity order by fsalary rows between  current row and unbounded following) sum_FSalary 
FROM T_PERSON T;-- 当前行 - 最后一行

SELECT T.*,sum(FSalary) 
over(partition by fcity order by fsalary rows between unbounded preceding and unbounded following) sum_FSalary 
FROM T_PERSON T;-- 第一行 - 最后一行