29.Oracle深度学习笔记——分析函数
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 102450 2450
7839 KING 105000 7450
7934 MILLER 101300 8750
7876 ADAMS 201100 1100
7902 FORD 203000 4100
7566 JONES 202975 7075
7788 SCOTT 203000 10075
7369 SMITH 20 800 10875
7499 ALLEN 301600 1600
7698 BLAKE 302850 4450
7900 JAMES 30 950 5400
7654 MARTIN 301250 6650
7844 TURNER 301500 8150
7521 WARD 301250 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,<default>)访问之后的行,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)
原文链接：https://www.f2er.com/oracle/207439.html