29.Oracle深度学习笔记——分析函数

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50830886

1.  分析函数

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

普通的聚合函数用groupby分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partitionby), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

2.  测试一

使用SCOTT用户进行：

sys@TOADDB> alter user scott account unlock;

User altered.

sys@TOADDB> conn scott/huawei

Connected.

显示各部门员工的工资，并附带显示该部分的最高工资。

scott@TOADDB>

SELECT E.DEPTNO,

       E.EMPNO,

       E.ENAME,

       E.SAL,

      LAST_VALUE(E.SAL)

      OVER(PARTITION BY E.DEPTNO

           ORDER BY E.SAL ROWS

           BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL

      FROM EMPE;

 

   DEPTNO   EMPNO ENAME                 SAL    MAX_SAL

---------- ---------- ---------- --------------------

         10     7934 MILLER        1300    5000

         10     7782 CLARK          2450    5000

         10     7839 KING             5000    5000

         20     7369 SMITH            800    3000

         20     7876 ADAMS        1100    3000

         20     7566 JONES          2975    3000

         20     7788 SCOTT          3000    3000

         20     7902 FORD            3000    3000

         30     7900 JAMES            950    2850

         30     7654 MARTIN                1250    2850

         30     7521 WARD          1250    2850

         30     7844 TURNER                1500    2850

         30     7499 ALLEN          1600    2850

         30     7698 BLAKE          2850    2850

14 rows selected.

按照deptno分组，然后计算每组值的总和

SELECT EMPNO,

       ENAME,

       DEPTNO,

       SAL,

       SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal

      FROMSCOTT.EMP;

 

    EMPNO ENAME       DEPTNO       SAL    MAX_SAL

---------- ---------- ---------- --------------------

     7782 CLARK       10      2450    2450

     7839 KING       10      5000    7450

     7934 MILLER              10      1300    8750

     7876 ADAMS              20      1100    1100

     7902 FORD                20      3000    4100

     7566 JONES       20      2975    7075

     7788 SCOTT       20      3000  10075

     7369 SMITH                20         800  10875

     7499 ALLEN       30      1600    1600

     7698 BLAKE       30      2850    4450

     7900 JAMES                30         950    5400

     7654 MARTIN            30      1250    6650

     7844 TURNER            30      1500    8150

     7521 WARD               30      1250    9400

14 rows selected.

其中：

           --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后

3.  常见分析函数

OVER()函数

RANK()

ROW_NUMBER()

lag()

lead()

4.  其他

值域窗(RANGE WINDOW)

RANGE N PRECEDING 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 –/+ N)的所有行,因此与ORDER BY子句有关系。

行窗(ROW WINDOW)

ROWS N PRECEDING 选定窗为当前行及之前N行。

还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING

函数 AVG(<distinct all> eXPr)

一组或选定窗中表达式的平均值 CORR(expr, expr) 即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关) ~ 1(正相关),0表示不相关

COUNT(<distinct> <*><expr>) 计数

COVAR_POP(expr, expr) 总体协方差

COVAR_SAMP(expr, expr) 样本协方差

CUME_DIST 累积分布,即行在组中的相对位置,返回0 ~ 1

DENSE_RANK 行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数

FIRST_VALUE 一个组的第一个值

LAG(expr, <offset>, <default>) 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行)

LAST_VALUE 一个组的最后一个值

LEAD(expr, <offset>, <default>)访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行)

MAX(expr) 最大值

MIN(expr) 最小值

NTILE(expr) 按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 ~ 4的值,而不能等分则多出的部分在值最小的那组

PERCENT_RANK 类似CUME_DIST,1/(行的序数 - 1)

RANK 相对序数,答应并列,并空出随后序号

RATIO_TO_REPORT(expr) 表达式值 / SUM(表达式值)

ROW_NUMBER 排序的组中行的偏移

STDDEV(expr) 标准差

STDDEV_POP(expr) 总体标准差

STDDEV_SAMP(expr) 样本标准差

SUM(expr) 合计

VAR_POP(expr) 总体方差

VAR_SAMP(expr) 样本方差

VARIANCE(expr) 方差

REGR_ xxxx(expr, expr) 线性回归函数

REGR_SLOPE：返回斜率，等于COVAR_POP(expr1,expr2) / VAR_POP(expr2)

REGR_INTERCEPT：返回回归线的y截距，等于

AVG(expr1) - REGR_SLOPE(expr1, expr2) *AVG(expr2)

REGR_COUNT：返回用于填充回归线的非空数字对的数目

REGR_R2：返回回归线的决定系数，计算式为：

If VAR_POP(expr2) = 0 then return NULL

If VAR_POP(expr1) = 0 and VAR_POP(expr2) !=0 then return 1

If VAR_POP(expr1) > 0 and VAR_POP(expr2!= 0 then

return POWER(CORR(expr1,expr),2)

REGR_AVGX：计算回归线的自变量(expr2)的平均值，去掉了空对(expr1,expr2)后，等于AVG(expr2)

REGR_AVGY：计算回归线的应变量(expr1)的平均值，去掉了空对(expr1,expr2)后，等于AVG(expr1)

REGR_SXX： 返回值等于REGR_COUNT(expr1,expr2) * VAR_POP(expr2)

REGR_SYY： 返回值等于REGR_COUNT(expr1,expr2) * VAR_POP(expr1)

REGR_SXY: 返回值等于REGR_COUNT(expr1,expr2) * COVAR_POP(expr1, expr2)