SQL_分析函数，分组排序

1 分析函数使用场景：
    1. 计算运行总量：逐一累加当前行与其之前行的每行记录数据。
    2. 查找当前行数据占总数据的百分比。
    3. 分区显示： 按照不同的部门或职位进行排列、统计。
    4. 计算流动数据行的平均值等


2 分析函数的基本语法
函数名称([参数,...])OVER(
    PARTITION BY 子句 字段,...
     [ORDER BY 子句 字段,...[ASC]DESC][NULLS FIRST|NULLS LAST]
     [WINDOWING 子句]);
     
函数名称:类似于统计函数(COUNT(),SUM()函数等)，或其他支持的函数。     
OVER 子句： 为分析函数指明一个查询结果集，此语句在 SELECT 子句中使用。
PARTITION BY 子句：将一个简单的结果集分为N组（或称为分区），而后按照不同的组对数据进行统计。
ORDER BY 子句：明确指明数据在每组内的排列顺序，分析函数的结果与排列顺序有关 。 （NULLS FIRST|NULLS LAST: 表示返回数据行中包含 NULL 值时出现在排序序列前还是排序尾。）
WINDOWING 子句（代名词）：给定在定义变化的固定的数据窗口方法，分析函数将此数据进行操作。

--在分析函数中，每一个分区（分组）称为一个窗口，每一个窗口都是当前行的计算范围。
--分析函数中三中子句的组合顺序：
  第 1 种组合： 函数名称([参数,...]) OVER(PARTITION BY 子句，ORDER BY 子句，WINDOWING 子句)。
  第 2 种组合： 函数名称([参数,...]) OVER(PARTITION BY 子句，ORDER BY 子句)。
  第 3 种组合： 函数名称([参数,...]) OVER(PARTITION BY 子句)。
  第 4 种组合： 函数名称([参数,...]) OVER(ORDER BY 子句，WINDOWING 子句)。
  第 5 种组合： 函数名称([参数,...]) OVER(ORDER BY 子句)。
  第 6 种组合： 函数名称([参数,...]) OVER()。

3 PARTITION BY 子句
    在分析函数中，最重要的概念就是数据进行分区，而后才可以利用分析函数针对每一分区的数据进行操作。如果在定义时没有使用分区，则表示全部的结果集作为一组。

--用例 ,本例中 SUM 是一个分析函数。
SELECT deptno,ename,sal,
       SUM(sal) OVER(PARTITION BY deptno) sum
FROM emp;

4 ORDER BY 子句
    ORDER BY 子句用于设置在每个分区内数据的排序结果，排序结果将直接影响分析函数的计算结果。

--用例 
SELECT deptno,ename,sal,hiredate
       RANK() OVER(PARTITION BY deptno ORDER BY sal,hiredate DESC) rk
FROM emp;


5 WINDOWING BY 子句
    分窗子句主要用于定义一个变化或固定的数据窗口方法，以及分析函数在操作的集合。有以下两种实现方法。
    1.实现一： 值域窗(RANGE WINDOW),逻辑偏移。当前分区中当前行的前N行到当前行的记录集。
    2.实现二： 行窗（ROWS WINDOW），物理偏移。以排序的结果顺序计算偏移当前行的起始记录集。
    
    如果要指定 RANGE 或 ROWS 的偏移量，可以采用如下几种排列：
    1. RANGE | ROWS 数字 PRECEDING。
    2. RANGE | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。 (默认方式)
    3. RANGE | ROWS BETWEEN CURRENT ROW  AND UNBOUNDED FOLLOWING 
    
    PRECEDING : 主要是设置一个偏移量，这个偏移量可以是用户设置的数字，或者是其他标记。
    BETWEEN ... AND : 设置一个偏移量的操作范围。
    UNBOUNDED PRECEDING: 不限制偏移量大小
    CURRENT ROW :表示当前行。
    FOLLOWING : 如果不写此语句，表示使用上 N行与当前行指定数据比较，如果编写此语句，表示当前行与下N行数据进行比较。

5.1 RANGE 子句
    RANGE子句设置的是一个查询范围的偏移量，排序列的数值必须大于等于(或小于等于)"当前行列数值 +/- 偏移量" 的所有行。


--设置 sal 上设置偏移量
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING AND 300 FOLLOWING) sum
FROM emp;

--设置偏移量为300，采用一下匹配的方式处理
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum
FROM emp;

--匹配当前行数据 ， CURRENT ROW 选项表示与当前行数据相同，所以只有相同的数据才会使用SUM()计算出总和。
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) sum
FROM emp;

--使用 UNBOUNDED 不设置边界。
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM emp;    

5.2  ROWS 子句：
    ROWS子句最大的功能是设置一个当前行的起始物理偏移量。

--设置两行物理偏移    
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING ) sum
FROM emp;    


--设置查询行范围    
SELECT deptno,ename,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED  PRECEDING AND UNBOUNDED FOLLOWING) sum
FROM emp;    


6 分析函数范例
6.1 数据统计函数：
    SUM(DISTINCT|ALL 表达式) ：计算分区（分组）中的数据累加和。
    MIN(DISTINCT|ALL 表达式) : 查找分区（分组）中的最小值。
    MAX(DISTINCT|ALL 表达式) ：查找分区（分组）中的最小值。
    AVG(DISTINCT|ALL 表达式) ：计算分区（分组）中的数据平均值。
    COUNT(*|DISTINCT|ALL 表达式)：计算分区（分组）中的数据量。

--范例：
--范例一：
查询雇员编号是7639 的雇员姓名、职位、基本工资、部门编号、部门的人数、平均工资、最高工资、最低工资、总工资 。
SELECT * 
FROM (
   SELECT empno,ename,job,sal,deptno,
          COUNT(empno) OVER(PARTITION BY deptno)count,
          ROUND(AVG(sal) OVER(PARTITION BY deptno)) avg,
          SUM(sal) OVER(PARTITION BY deptno) sum,
          MAX(sal) OVER(PARTITION BY deptno) max,
          MIN(sal) OVER(PARTITION BY deptno) min
      FROM emp ) temp
WHERE temp.empno=7369;

--范例二：
查询每个雇员的编号、姓名、基本工资、所在部门的名称、部门位置，以及此部门的平均工资、最高工资和最低工资。（由于需要针对每一个部门找出整个部门的工资统计信息，所以可以利用分窗子句完成，而每个分窗子句的范围应该是所有数据，所以可以用“RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” 作为范围限定。）
SELECT e.empno,e.ename,e.sal,d.dname,d.loc,
   ROUND(AVG(sal) OVER(PARTITION BY e.deptno ORDER by sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) avg_salary,
   MAX(sal) OVER(PARTITION BY e.deptno ORDER by sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max_salary,
   MIN(sal) OVER(PARTITION BY e.deptno ORDER by sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min_salary
FROM emp e,dept d 
WHERE e.deptno=d.deptno;
   
6.2 等级函数：
    RANK()：根据 ORDER BY 子句的排序字段，从分区（分组）查询每一行数据，按照排序生成序号，会出现相同序号。
    DENSE_RANK()：根据 ORDER BY 子句的排序字段，从分区（分组）查询每一行数据，按照排序生成序号，不会出现相同序号。
    FIRST()：取出 DENSE_RANK 返回集合中第一行数据。
    LAST()：取出 DENSE_RANK 返回集合中最后一行数据。
    FIRST_VALUE(列)：返回分区（分组）中的第一个值。
    LAST_VALUE(列)：返回分区（分组）中的最后一个值。
    LAG(列名称[,行数字][,默认值])：访问分区（分组）中指定前N行的记录，如果没有则返回默认值。
    LEAD(列名称[,行数字][,默认值])：访问分区（分组）中指定后N行的记录，如果没有则返回默认值。
    ROW_NUMBER() :返回每组中的行号。

--范例一 记录标记函数：RANK() 和 DENSE_RANK()
SELECT deptno,ename,sal,
    RANK() OVER(PARTITION BY deptno ORDER BY sal) rank_result,
    DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) dense_rank_result
FROM emp;

--范例二 行标记函数： ROW_NUMBER()
SELECT deptno,ename,sal,
    ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) row_result_deptno,
    ROW_NUMBER() OVER(ORDER BY sal) row_result_all
FROM emp;

--范例三： 取出首行或尾行数据 DENSE_RANK
当用户使用了DENSE_RANK()函数进行数据排序后，可以利用FIRST取得返回结果集中的首行，或使用 LAST 取得返回结果集中的最后一行 。但必须使用 KEEP 语句完成。
KEEP 语句语法：
             分组函数()
             KEEP 
             (DENSE_RANK FIRST|LAST ORDER BY 表达式 [ASC|DESC][NULLS [FIRST|LAST]],...)
             [OVER() 分区查询]

查询每个部门的最高及最低工资：
SELECT deptno
    MAX(sal) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_salary,
    MIN(sal) KEEP(DENSE_RANK LAST ORDER BY sal DESC) min_salary
FROM emp
GROUP BY deptno;

--范例四 取出首行或尾行记录：FIRST_VALUE(列) 和 LAST_VALUE(列) 
SELECT deptno,ename,sal,
    FIRST_VALUE() OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) first_result,
    LAST_VALUE()  OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) last_result
FROM emp
WHERE deptno=10 ;    


--范例五 比较相邻记录：
SELECT deptno,ename,sal,
    LAG(sal,2,0) OVER(PARTITION BY deptno ORDER BY sal) lag_result,
    LEAD(sal,2,0) OVER(PARTITION BY deptno ORDER BY sal) lead_result
FROM emp
WHERE deptno=20 ;


6.3 报表函数：
    CUME_DIST() : 计算一行在分区(分组)中的相对位置
    NTILE(数字)：将一个分许(分组)分为 '表达式' 的散列表示。
    RATIO_TO_REPORT(表达式)： 该函数结算 expression/(sum(expression)) 的值，它给出相对于总数的百分比。
    
    
--范例一 ： CUME_DIST()
SELECT deptno,ename,sal,
    CUME_DIST OVER(PARTITION BY deptno ORDER BY sal) cume
FROM emp
WHERE deptno IN (10,20) ;

--范例二 ：NTILE(数字)
SELECT deptno,sal,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) sum_result,
    NTILE(3) OVER(PARTITION BY deptno ORDER BY sal) ntile_result_a,
    NTILE(6) OVER(PARTITION BY deptno ORDER BY sal) ntile_result_b
FROM emp
;


--范例三 ：
SELECT deptno,SUM(sal),
    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) sum_result,
    ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5) rate,
    ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5)*100||'%' precent
FROM emp
ORDER BY deptno
;




7 行列转换
用户在制作数据报表时，经常会使用到数据的行列转换操作。


7.1 DECODE
--范例一： 使用decode()
查询每个部门中各个职位的总工资。
SELECT deptno,
    SUM(DECODE(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
    SUM(DECODE(job,'MANAGER',sal,0)) MANAGER_JOB,
    SUM(DECODE(job,'ANALYST',sal,0)) ANALYST_JOB,
    SUM(DECODE(job,'CLERK',sal,0)) CLERK_JOB,
    SUM(DECODE(job,'SALESMAN',sal,0)) SALESMAN_JOB
FROM emp
GROUP BY deptno;

--范例二： 不使用decode()，使用子查询。
查询每个部门中各个职位的总工资。
SELECT temp.dno,SUM(PRESIDENT_JOB),SUM(MANAGER_JOB),SUM(ANALYST_JOB),SUM(CLERK_JOB),SUM(SALESMAN_JOB)
FROM (
    SELECT deptno dno,
        (SELECT SUM(sal) FROM emp WHERE job='PRESIDENT' AND empno=e.empno ) PRESIDENT_JOB,
        (SELECT SUM(sal) FROM emp WHERE job='MANAGER' AND empno=e.empno ) MANAGER_JOB,
        (SELECT SUM(sal) FROM emp WHERE job='ANALYST' AND empno=e.empno ) ANALYST_JOB,
        (SELECT SUM(sal) FROM emp WHERE job='CLERK' AND empno=e.empno ) CLERK_JOB,
        (SELECT SUM(sal) FROM emp WHERE job='SALESMAN' AND empno=e.empno ) SALESMAN_JOB
    FROM emp e
) temp
GROUP BY temp.dno
ORDER BY temp.dno DESC;

7.2  11G以后 ，PIVOT() 和 UNPIVOT()

7.2.1 PIVOT() ：将行转换为列。
语法：
    SELECT *|列 [别名] ...
    FROM 子查询
    PIVOT(
        统计函数(列)s FOR 转换列名称 IN (
            内容1 [[AS] 别名],
            内容2 [[AS] 别名],
              ...
            内容n [[AS] 别名],
        
        )
    )
    [WHERE 条件(s)]     
    [GROUP BY 分组字段1,分组字段2,... ]     
    [HAVING 过滤条件(s) ]     
    [ORDER BY 排序字段 ASC|DESC ];

子查询:    此处规定了在PIVOT()函数操作过程中，所需要使用到的数据（设置子查询确定行和列）。
统计函数(列)：在转换过程中，设置要进行统计的数据列及统计函数，可以设置多个统计函数。
FOR 转换列名称： 将子查询中返回的指定数据变为显示的列。

--范例一： 使用PIVOT()
SELECT * FROM (SELECT deptno,job,sal FROM emp)
PIVOT(
    SUM(sal)
    FOR job IN (
    'PRESIDENT' AS PRESIDENT_JOB,
    'MANAGER' AS MANAGER_JOB,
    'ANALYST' AS ANALYST_JOB,
    'CLERK' AS CLERK_JOB,
    'SALESMAN' AS SALESMAN_JOB
    )
) ORDER BY deptno;


--范例二： 使用PIVOT() 查询更多信息
SELECT * FROM (
    SELECT job,deptno,sal,
        SUM(sal) OVER(PARTITION BY deptno) sum_sal,
        MAX(sal) OVER(PARTITION BY deptno) max_sal,
        MIN(sal) OVER(PARTITION BY deptno) min_sal
    FROM emp)
PIVOT(
    SUM(sal)
    FOR job IN(
          'PRESIDENT' AS PRESIDENT_JOB,
        'MANAGER' AS MANAGER_JOB,
        'ANALYST' AS ANALYST_JOB,
        'CLERK' AS CLERK_JOB,
        'SALESMAN' AS SALESMAN_JOB  
    )
) ORDER by deptno;
    



--范例三：设置多个统计函数，查询每个部门不同职位的总工资，以及每个部门不同职位的最高工资。
SELECT * FROM (SELECT deptno,job,sal FROM emp)
PIVOT(
    SUM(sal) AS sum_sal,MAX(sal) as sum_max
    FOR job IN(
          'PRESIDENT' AS PRESIDENT_JOB,
        'MANAGER' AS MANAGER_JOB,
        'ANALYST' AS ANALYST_JOB,
        'CLERK' AS CLERK_JOB,
        'SALESMAN' AS SALESMAN_JOB  
    )
) ORDER by deptno;

--范例四 ：输出为XML：
SELECT * FROM (SELECT deptno,job,sal FROM emp)
PIVOT XML(
        SUM(sal)
        FOR job IN (ANY)
)ORDER BY deptno;

--范例五 ：设置多个统计列，如果emp表中增加了一个性别sex，同时要针对不同职位的不同性别进行总工资的统计，可以在FOR 语句中设置多个列。
SELECT * FROM (SELECT deptno,job,sal,sex FROM emp)
PIVOT(
    SUM(sal) AS sum_sal,MAX(sal) as sum_max
    FOR(job,sex) IN(
        ('MANAGER','男') AS manager_male_job,
        ('MANAGER','女') AS manager_female_job,
        ('CLERK','男') AS CLERK_male_job,
        ('CLERK','男') AS CLERK_female_job
    )
) ORDER BY deptno;

7.2.2 UNPIVOT() 函数 ： 将列重新转换为行。
语法：
    SELECT *|列 [别名] ...
    FROM 子查询
    UNPIVOT [INCLUDE NULLS|EXCLUDE NULLS](
        统计函数(列)s FOR 转换列名称 IN (
            内容1 [[AS] 别名],
            内容2 [[AS] 别名],
              ...
            内容n [[AS] 别名],
        
        )
    )
    [WHERE 条件(s)]     
    [GROUP BY 分组字段1,分组字段2,... ]     
    [HAVING 过滤条件(s) ]     
    [ORDER BY 排序字段 ASC|DESC ];

子查询:    此处规定了在PIVOT()函数操作过程中，所需要使用到的数据（设置子查询确定行和列）。
统计函数(列)：在转换过程中，设置要进行统计的数据列及统计函数，可以设置多个统计函数。
FOR 转换列名称： 将子查询中返回的指定数据变为显示的列。
INCLUDE NULLS: 列变为行转换之后保留所有的 null 数据。
EXCLUDE NULLS（默认）: 列变为行转换之后不保留所有的 null 数据。

--范例一 ：
WITH temp AS(
    SELECT  * FROM  (SELECT deptno,job,sal FROM emp)
    PIVOT(
        SUM(sal)
        FOR job IN (
             'PRESIDENT' AS PRESIDENT_JOB,
              'MANAGER' AS MANAGER_JOB,
              'ANALYST' AS ANALYST_JOB,
              'CLERK' AS CLERK_JOB,
              'SALESMAN' AS SALESMAN_JOB 
              )
        ) ORDER BY deptno)
SELECT * FROM temp
UNPIVOT (
    sal_sum FOR job IN (
       PRESIDENT_JOB as 'PRESIDENT', 
       MANAGER_JOB as 'MANAGER', 
       ANALYST_JOB as 'ANALYST', 
       CLERK_JOB as 'CLERK', 
       SALESMAN_JOB as 'SALESMAN' 
    )
)    ORDER BY deptno;    
        

--范例二 ： 显示所有数据 ，
WITH temp AS(
    SELECT  * FROM  (SELECT deptno,job,sal FROM emp)
    PIVOT(
        SUM(sal)
        FOR job IN (
             'PRESIDENT' AS PRESIDENT_JOB,
              'MANAGER' AS MANAGER_JOB,
              'ANALYST' AS ANALYST_JOB,
              'CLERK' AS CLERK_JOB,
              'SALESMAN' AS SALESMAN_JOB 
              )
        ) ORDER BY deptno)
SELECT * FROM temp
UNPIVOT  INCLUDE NULLS(
    sal_sum FOR job IN (
       PRESIDENT_JOB as 'PRESIDENT', 
       MANAGER_JOB as 'MANAGER', 
       ANALYST_JOB as 'ANALYST', 
       CLERK_JOB as 'CLERK', 
       SALESMAN_JOB as 'SALESMAN' 
    )
)    ORDER BY deptno;




递归查询（层次查询）
    层次查询是一种确定数据行之间关系结构的一种操作，例如，在现实社会的工作中一定会存在“管理层”、“职员层”这样的基本分层关系，在学校也会分为“教学管理层”、“教师层”、“学生层”这样的三层结构。
    Oracle 中也可以利用其自身所带的工具实现这样的层次结构，实现的操作语法如下：
1 设置层次函数
    LEVEL ...
    CONNECT BY [NOCYCLE] PRIOR 连接条件
    [START WITH 开始条件]3
LEVEL：可以根据数据所处的层次结构实现自动的层次编号，例如，1/2/3。
CONNECT BY ： 指的是数据之间的连接，例如雇员数据依靠 mgr 找到领导，就是一个连接条件，其中 NOCYCLE 需要结合 CONNECT_BY_ISCYCLE 伪列确定出父子节点循环关系。
START WITH : 根节点数据的开始条件。

2 主要掌握的知识
    CONNECT_BY_ISLEAF 伪列:在一个树状结构中，节点会分为两种，即根节点、叶子节点，用户可以利用 CONNECT_BY_ISLEAF 伪列判断某一个节点是根节点还是叶子节点，如果此列返回的是数字0，则表示根节点；如果返回1 ，则表示叶子节点。
    
    CONNECT_BY_ROOT 列语句：主要作用是取得某一字段在本次分层中的根节点数据名称，如果按照领导层次划分，则所有数据的根节点都应该是 KING。
    
    SYS_CONNECT_BY_PATH(列,char)函数：按照给出的节点关系，自动地将当前根节点中的所有路径进行显示。
    
    ORDER SIBLINGS BY 字段语句：因为 ORDER BY 会破坏层次结构，所以使用 ORDER SIBLINGS BY 防止破坏层次结构。
    
    CONNECT_BY_ISCYCLE ：在进行数据层次设计的过程中，最重要的是根据指定的数据列确定数据间的层次关系，但是有时也可能出现死循环。例如KING的领导是BLAKE，而BLAKE的领导是KING就表示一个循环关系。为了判断循环关系的出现，在Oracle中提供了一个 CONNECT_BY_ISCYCLE 伪列，判断是否出现循环，如果出现循环，则显示1；如果没有出现循环，则显示0 。同时，如果要判断是否为循环节点，还需要 NOCYCLE 支持。

--用例1 ： 观察分层的基本关系：
SELECT empno,LPAD('|-',LEVEL*2,'')||ename empname,mgr ,LEVEL
FROM emp
CONNECT BY PRIOR empno=mgr
START WITH mgr IS NULL;
    
--用例2 ： 综合运用
SELECT ename,LEAP('|-',LEVEL*2,'')||ename empname ,LEVEL ,
       DECODE(CONNECT_BY_ISLEAF,0,'根节点',1,'叶子节点') isleaf,
       DECODE(CONNECT_BY_ISCYCLE,0,'【√】没有循环',1,'[X]存在循环') iscycle
FROM emp
CONNECT BY NOCYCLE PRIOR empno=mgr
START WITH empno=7839
ORDER SIBLINGS BY ename;

--用例3 ： CONNECT_BY_ROOT
SELECT empno,LPAD('|-',LEVEL*2,'')||ename empname,mgr ,LEVEL
    CONNECT_BY_ROOT ename
FROM emp
CONNECT BY PRIOR empno=mgr
START WITH empno=7566;
       
--用例4 ： SYS_CONNECT_BY_PATH (常用)
    SELECT empno,LPAD('|- ',LEVEL*2,' ')||SYS_CONNECT_BY_PATH(ename,' => ')  empname,mgr ,LEVEL,
        DECODE(CONNECT_BY_ISLEAF,0,'根节点',1,'叶子节点') isleaf
    FROM emp
    CONNECT BY PRIOR empno=mgr
    START WITH mgr IS NULL;