Oracle中的常见的分析函数

基本语法

function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

function_name()：函数名称

argument：参数

over( )：开窗函数

partition_Clause：分区子句，数据记录集分组，partition by column1,column2....

order by_Clause：排序子句，数据记录集排序，order by condition1,condition2....

windowing_Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying
 

--显示各部门员工的工资，并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

1、count() over()  ：统计分区中各组的行数，partition by 可选，order by 可选

select emp.*,count(*) over() from emp; --总计数
select emp.*,count(*) over(order by sal) from emp; --递加计数
select emp.*,count(*) over(partition by deptno) from emp; --分组计数
select emp.*,count(*) over(partition by deptno order by sal) from emp;--分组递加计数

2、sum() over()  ：统计分区中记录的总和，partition by 可选，order by 可选

 

select emp.*,sum(sal) over() from emp; --总计数
select emp.*,sum(sal) over(order by sal) from emp; --递加计数
select emp.*,sum(sal) over(partition by deptno) from emp; --分组计数
select emp.*,sum(sal) over(partition by deptno order by sal) from emp;--分组递加计数

3、avg() over()  ：统计分区中记录的平均值，partition by 可选，order by 可选:以下重复的功能不在写，就写最后一个分组递加的

select emp.*,avg(sal) over(partition by deptno order by sal) from emp;--分组递加平均数

4、min() over() ：统计分区中记录的最小值，partition by 可选，order by 可选      max() over() ：统计分区中记录的最大值，partition by 可选，order by 可选

select emp.*,min(sal) over(partition by deptno order by sal desc) from emp;
select emp.*,max(sal) over(partition by deptno order by sal) from emp;

5、rank() over()  ：跳跃排序，partition by 可选，order by 必选

--跳跃排序，相同的记同一个值，下一个记顺序值。比如并列第一，两个都是第一，第二个分数就是第三名
select emp.*,rank() over (partition by deptno order by sal) from emp;

6、dense_rank() ：连续排序，partition by 可选，order by 必选

--连续排序，相同的记同一个值，下一个记顺序值。比如并列第一，两个都是第一，第二个分数就是第二名
select emp.*,dense_rank() over (partition by deptno order by sal) from emp;

7、row_number() over() ：排序，无重复值，partition by 可选，order by 必选

--无重复值，相同值依然排序，并列第一也是按顺序第一，第二
select emp.*,row_number() over (partition by deptno order by sal) from emp;

8、ntile(n) over() ：partition by 可选，order by 必选 n表示将分区内记录平均分成n份，多出的按照顺序依次分给前面的组

--这里14条记录，分成三组，多出来了从前到后一个组依次加1，直到加完为止
select emp.*,ntile(3) over(order by sal desc) from emp;
select emp.*,ntile(3) over(partition by deptno order by sal desc) from emp;

9、first_value() over() ：取出分区中第一条记录的字段值，partition by 可选，order by 可选；

last_value() over() ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选

select emp.*,first_value(sal) over() from emp;
select emp.*,first_value(sal) over(partition by deptno) from emp;
select emp.*,first_value(sal) over(order by sal) from emp;
select emp.*,first_value(sal) over(partition by deptno order by sal) from emp;

10、first ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

 last ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

--配合over开窗函数使用，over子句中只能由partition by语句，可省略，但不能有order by语句
--如下功能描述，查询福利最大的工资最高的员工的工资，分组查询各个组福利最多，工资最高的员工
--求各个组中，福利最好，工资最高的员工的工资总和，keep函数可返回多行，如果值相同
select emp.*,max(sal) keep(dense_rank last order by comm desc nulls last) over() maxsalcomm,
max(sal) keep(dense_rank last order by comm desc nulls last) over(partition by deptno)  deptmaxsal,
sum(sal) keep(dense_rank first order by comm desc nulls first) over(partition by deptno)  deptcommsum from emp

结果：

11、lag() over() ：取出前n行数据，partition by 可选，order by 必选

      lead() over() ：取出后n行数据，partition by 可选，order by 必选

--lead() over() ：取出后n行数据，partition by 可选，order by 必选
--lag,lead中第一个参数是取几位，后面的参数是用来替换取出数据的数据
select emp.*,lag(ename,1,'hahaha') over(partition by deptno order by sal) from emp;

结果：

12、ratio_to_report(a) over(partition by b) ：求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段 partition by 可选，order by 不可选

这里的report的入参必须是number类型

select emp.*,ratio_to_report(sal) over(partition by deptno) from emp;

 

求平均数AVG ：用于计算一个组和数据窗口内表达式的平均值。有两种情况：

第一种，不加order by语句，只根据某一个字段分组计算平均数如下所示

/**
avg（column）函数，求平均值：统计emp各个组的平均工资
*/
select e.*,avg(e.sal) over(partition by e.deptno) avgSal from emp e;

--上面的语句等同于下面的语句结果
select e.*, e2.avgsal
  from emp e,
       (select avg(t.sal) avgsal, t.deptno from emp t group by t.deptno) e2
 where e.deptno = e2.deptno;

第二种，加了order by语句，这时，对应的某一行记录的avgsal并不再是这一分组的总的平均值了，而是同一分组到这个值的累积平均数：

select e.*,avg(e.sal) over(partition by e.deptno order by e.sal) avgSal from emp e;

如下是copy别人写的文章，其中有些不常用，就没研究了。

----------------开窗函数

RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置

DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置

ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号

FIRST ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

LAST ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

FIRST_VALUE ：返回组中数据窗口的第一个值

LAST_VALUE ：返回组中数据窗口的最后一个值。

LAG ：可以访问结果集中的其它行而不用进行自连接

LEAD ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行

----------------数据分析函数

STDDEV ：计算当前行关于组的标准偏离

STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根

STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根

VAR_POP ：该函数返回非空集合的总体变量（忽略null）

VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）

VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP

COVAR_POP ：返回一对表达式的总体协方差

COVAR_SAMP：返回一对表达式的样本协方差

CORR ：返回一对表达式的相关系数

---

CUME_DIST ：计算一行在组中的相对位置

NTILE ：将一个组分为"表达式"的散列表示

PERCENT_RANK：和CUME_DIST（累积分配）函数类似

PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值

PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值

RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比

REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

---------------统计求和函数

CUBE :按照OLAP的CUBE方式进行数据统计,即各个维度均需统计

ROLLUP :