oracle 统计/分析函数

最新推荐文章于 2024-08-21 21:54:48 发布
阿三哥哥
最新推荐文章于 2024-08-21 21:54:48 发布
阅读量649
点赞数
分类专栏： oracle 文章标签： analytic-function
本文链接：https://blog.youkuaiyun.com/yhx119/article/details/9357587
版权
oracle 专栏收录该内容
25 篇文章
订阅专栏
本文详细介绍了 Oracle SQL 中的多种分析函数，包括 ROLLUP 和 CUBE 的使用方法、Rank 和 Dense_Rank 的区别、First 和 Last 的应用场景、First_Value 和 Last_Value 的功能、Lag 和 Lead 的用法、Ratio_To_Report 的计算方式以及 Grouping_Sets 的优化技巧。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
--oracle 统计/分析函数
/*
Sql代码
<analytic-function>(<argument>,<argument>,...)    
over(    
<query-partition-clause>    
<order-by-clause>    
<windowing-clause>    
)   
说明： 
1.over是关键字，用于标识分析函数。 
2.<analytic-function>是指定的分析函数的名字。 
3.<argument>为参数，分析函数可以选取0-3个参数。 
4.分区子句<query-partition-clause>的格式为： partition by<value_exp>[,value_expr]... 
  关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。
  这里的"分区partition"和"组group" 都是同义词。 
5.排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
  order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last] 
  其中： 
  A.asc|desc:指定了排列顺序。 
  B.nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。 
6.窗口子句windowing-clause 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。
  在一组基于任意变化或固定的窗口中,可用该子句让分析函数计算出它的值。
  格式：
    {rows|range} 
    {between 
    {unbounded preceding|current row |<value_expr>{preceding|following} 
    }and 
    {unbounded preceding|current row |<value_expr>{preceding|following} 
    }|{unbounded preceding|current row |<value_expr>{preceding|following 
    }} 
    A.rows|range:此关键字定义了一个window。 
    B.between...and...:为窗品指一个起点和终点。 
    C.unbounded preceding：指明窗口是从分区(partition)的第一行开始。 
    D.current row:指明窗口是从当前行开始。 

    开窗函数： 
    开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
    over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
    over（partition by deptno）按照部门分区
    over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
    over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行，之后150行
    over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从第一行到最后一行
*/

/*
1.Oracle ROLLUP和CUBE 用法
    Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。
    如果是Group by  ROLLUP(A, B, C)的话，
    首先会对(A、B、C)进行GROUP BY，
    然后对(A、B)进行GROUP BY，
    然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。 
    
    如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，
    然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
    也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 
*/

Select nvl(to_char(deptno), 'all_dept'), Sum(sal)
  From emp
 Group By Rollup(deptno);

/*
10	      9341
20	      11023
30	      31110
all_dept	51474
*/

Select nvl(to_char(deptno), 'all_dept'),
       nvl(to_char(job), 'all_job'),
       Sum(sal)
  From emp
 Group By Rollup(deptno, job);

/*
10	      CLERK	      3341
10	      MANAGER	    2000
10	      PRESIDENT	  4000
10	      all_job	    9341
20	      CLERK	      6000
20	      ANALYST	    3123
20	      MANAGER	    1900
20	      all_job	    11023
30	      CLERK	      7000
30	      MANAGER	    1000
30	      SALESMAN	  23110
30	      all_job	    31110
all_dept	all_job	    51474
*/
 
Select decode(Grouping(deptno), 1, '_dept', to_char(deptno)) deptno,
       decode(Grouping(job), 1, '_job', to_char(job)) job,
       Sum(sal)
  From emp
 Group By cube(deptno, job)
 order by deptno,job;
 
/*
10	  CLERK	    3341
10	  MANAGER	  2000
10	  PRESIDENT	4000
10	  _job	    9341
20	  ANALYST	  3123
20	  CLERK	    6000
20	  MANAGER	  1900
20	  _job	    11023
30	  CLERK	    7000
30	  MANAGER	  1000
30	  SALESMAN	23110
30	  _job	    31110
_dept	ANALYST	  3123
_dept	CLERK	    16341
_dept	MANAGER	  4900
_dept	PRESIDENT	4000
_dept	SALESMAN	23110
_dept	_job	    51474
*/

/*
2.Rank的用法 
      功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。
      组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。
      每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。
      然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，
      DENSE_RANK则没有任何跳跃。 
      rank()是跳跃排序，比如有两个第二名时接下来就是第四名（同样是在各个分组内） 
      dense_rank()是连续排序，比如有两个第二名时仍然跟着第三名。 
*/
Select nvl(to_char(deptno), 'all_dept') deptno,
       Sum(sal) n_sal,
       rank() over(Order By Sum(sal) Desc) n_rank,
       dense_rank() over(Order By Sum(sal) Desc) n_dense_rank
  From emp
 Group By deptno;
/*
30	31110	1	1
20	11023	2	2
10	9341	3	3
*/

/*
3.First/Last的用法 
     First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），
     因此完整的语法需要在开始处加上一个集合函数以从中取出记录。 
     Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），
     因此完整的语法需要在开始处加上一个集合函数以从中取出记录。 
*/
Select ename,
       deptno,
       sal,
       Min(sal) keep(dense_rank First Order By deptno asc) over(Partition By deptno) "Worst",
       Max(sal) keep(dense_rank Last Order By deptno asc) over(Partition By deptno) "Best"
  From emp
 Order By deptno, sal;

/*
MILLER	 10	 111.00	  111	  4000
CLARK	   10	 2000.00	111	  4000
SMITH	   10	 3230.00	111	  4000
KING	   10	 4000.00	111	  4000
FORD	   20	 123.00	  123	  6000
JONES	   20	 1900.00	123	  6000
SCOTT	   20	 3000.00	123	  6000
ADAMS	   20	 6000.00	123	  6000
BLAKE	   30	 1000.00	1000	9000
ALLEN	   30	 1110.00	1000	9000
MARTIN	 30	 5000.00	1000	9000
JAMES	   30	 7000.00	1000	9000
TURNER	 30	 8000.00	1000	9000
WARD	   30	 9000.00	1000	9000
*/



/*
4.FIRST_VALUE/LAST_VALUE的用法 
     FIRST_VALUE、LAST_VALUE是两个分析函数。
     返回结果集中排在第一位和最后一位的值。语法是： 
     FIRST_VALUE (expr) OVER ( analytic_clause) 
*/
Select deptno,
       ename,
       sal,
       first_value(ename) over(Partition By deptno Order By sal Desc rows Between unbounded preceding And unbounded following) As rich_emp,
       last_value(ename) over(Partition By deptno Order By sal Desc rows Between unbounded preceding And unbounded following) As poor_emp
  From emp
 Order By deptno, sal;

/*
10	MILLER	111.00	 KING	  MILLER
10	CLARK	  2000.00	 KING	  MILLER
10	SMITH	  3230.00	 KING	  MILLER
10	KING	  4000.00	 KING	  MILLER
20	FORD	  123.00	 ADAMS	FORD
20	JONES	  1900.00	 ADAMS	FORD
20	SCOTT	  3000.00	 ADAMS	FORD
20	ADAMS	  6000.00	 ADAMS	FORD
30	BLAKE	  1000.00	 WARD	  BLAKE
30	ALLEN	  1110.00	 WARD	  BLAKE
30	MARTIN	5000.00	 WARD	  BLAKE
30	JAMES	  7000.00	 WARD	  BLAKE
30	TURNER	8000.00	 WARD	  BLAKE
30	WARD	  9000.00	 WARD	  BLAKE
*/

/*
5.Lag/Lead的用法 
     功能描述：可以访问结果集中的其它行而不用进行自连接。
     它允许去处理游标，就好像游标是一个数组一样。
     在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。
     Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD。 
*/
Select deptno,
       ename,
       sal,
       lag(sal) over(Partition By deptno Order By sal desc) As lag_sal,
       lead(sal) over(Partition By deptno Order By sal desc) As lead_sal
  From emp
 Order By deptno, sal desc;
/*
10	KING	  4000.00	 	     3230
10	SMITH	  3230.00	 4000	 2000
10	CLARK	  2000.00	 3230	 111
10	MILLER	111.00	 2000	 
20	ADAMS	  6000.00	 	     3000
20	SCOTT	  3000.00	 6000	 1900
20	JONES	  1900.00	 3000	 123
20	FORD	  123.00	 1900	
30	WARD	  9000.00	     	 8000
30	TURNER	8000.00	 9000	 7000
30	JAMES	  7000.00	 8000	 5000
30	MARTIN	5000.00	 7000	 1110
30	ALLEN	  1110.00	 5000	 1000
30	BLAKE	  1000.00	 1110	 
*/

/*
6.RATIO_TO_REPORT用法 
      功能描述：该函数计算expression/(sum(expression))的值，
      它给出相对于总数的百分比，即当前行对sum(expression)的贡献。 
      格式：RATIO_TO_REPORT (expr) OVER (query_partition_clause) */

Select deptno, ename, sal, ratio_to_report(sal) over(Partition By deptno)
  From emp
 Order By deptno, sal Desc;
/*
10	KING	4000.00	0.428219676694144
10	SMITH	3230.00	0.345787388930521
10	CLARK	2000.00	0.214109838347072
10	MILLER	111.00	0.0118830960282625
20	ADAMS	6000.00	0.544316429284224
20	SCOTT	3000.00	0.272158214642112
20	JONES	1900.00	0.172366869273338
20	FORD	123.00	0.0111584868003266
30	WARD	9000.00	0.289296046287367
30	TURNER	8000.00	0.257152041144327
30	JAMES	7000.00	0.225008036001286
30	MARTIN	5000.00	0.160720025715204
30	ALLEN	1110.00	0.0356798457087753
30	BLAKE	1000.00	0.0321440051430408
*/

/*
7.GROUPING SETS用法 
    从Oracle9i开始，使用GROUPING SETS操作符可以合并多个分组的结果。
    并且可以用GROUP BY GROUPING SETS来代替GROUP BY CUBE。
    你可以应用来指定你感兴趣的总数组合。
    因为它不必计算它不需要集合（也不会产生太多结果），所以对SQL引擎来说更为高效。 
*/
Select decode(Grouping(deptno), 1, '_dept', to_char(deptno)) deptno,
      decode(Grouping(job), 1, '_job', to_char(job)) job,
      Sum(sal)
 From emp
Group By Grouping Sets(deptno, job)
Order By deptno, job;

/*
10	  _job	    9341
20	  _job	    11023
30	  _job	    31110
_dept	ANALYST	  3123
_dept	CLERK	    16341
_dept	MANAGER	  4900
_dept	PRESIDENT	4000
_dept	SALESMAN	23110
*/