澳门皇冠金沙网站▎在线官网
做最好的网站

Oracle分析函数用法详解

2020-04-29 作者:网络服务   |   浏览(149)

有时候不用的指标的绝对值不能比,但是转转为百分比的形式就容易看出波动了,是数据分析的好用的一个分析函数

OVER(PARTITION BY)开窗函数用法

开窗函数,Oracle从8.1.6开始提供开窗函数,开窗函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:
对于每个组返回多行,而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。

20:00:24 SYS@orcl conn scott/tiger;Connected.20:00:30 SCOTT@orcl create table test20:01:22 2 (20:01:22 3 name varchar(20),20:01:22 4 kemu varchar(20),20:01:22 5 score number20:01:22 6 );Table created.Elapsed: 00:00:00.0420:01:23 SCOTT@orcl insert into test values('testa','yuwen',10);1 row created.Elapsed: 00:00:00.0220:01:35 SCOTT@orcl insert into test values('testa','英语',100);1 row created.Elapsed: 00:00:00.0020:01:35 SCOTT@orcl insert into test values('testb','yuwen',60);1 row created.Elapsed: 00:00:00.0120:01:36 SCOTT@orcl insert into test values('testb','yuwen',120);1 row created.Elapsed: 00:00:00.0020:01:36 SCOTT@orcl insert into test values('testc','yuwen',40);1 row created.Elapsed: 00:00:00.0020:01:37 SCOTT@orcl commit;Commit complete.Elapsed: 00:00:00.0020:01:42 SCOTT@orcl select name,20:03:32 2 score,20:03:32 3 ratio_to_report(score) over() as ratio1,20:03:32 4 ratio_to_report(score) over(partition by kemu) as ratio220:03:32 5 from test ;NAME SCORE RATIO1 RATIO2-------------------- ---------- ---------- ----------testa 10 .03030303 .043478261testb 60 .181818182 .260869565testc 40 .121212121 .173913043testb 120 .363636364 .52173913testa 100 .303030303 1Elapsed: 00:00:00.0020:03:33 SCOTT@orcl

开窗的窗口范围

-- 窗口范围为当前行数据幅度减3加3后的范围内的.
/*SELECT 
   T1.STUDENT_NAME
   ,T1.CLASS_NAME
   ,T1.COURSE_SCORE
   ,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) SUM_SCORE
  FROM V_RPT_COURSE_STUDENT T1
WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'
AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'
AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'*/

/*1 高蓉蓉 1班  76  155   76,79
  2 雷蕾   1班  79  237   76,79,82
  3 杨正坤 1班  82  331   79,82,85,85
  4 李永承 1班  85  252   82,85,85
  5 敬金凤 1班  85  252   82,85,85  */

澳门皇冠金沙网站,在线官网,总结

窗口范围为当前行前后各移动1,0行

/*SELECT 
   T1.STUDENT_NAME
   ,T1.CLASS_NAME
   ,T1.COURSE_SCORE
   ,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) SUM_SCORE
  FROM V_RPT_COURSE_STUDENT T1
WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'
AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'
AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'
*/

/*1 高蓉蓉 1班  76  76   76
  2 雷蕾  1班  79  155   76,79
  3 杨正坤 1班  82  161  79,82
  4 李永承 1班  85  167  82,85
  5 敬金凤 1班  85  170  85,85 */

以上所述是小编给大家介绍的Oracle百分比分析函数RATIO_TO_REPORT() OVER(),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

窗口不做限制

/*SELECT 
   T1.STUDENT_NAME
   ,T1.CLASS_NAME
   ,T1.COURSE_SCORE
   ,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM_SCORE1
   ,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM_SCORE2
   ,SUM(T1.COURSE_SCORE) OVER() SUM_SCORE3
  FROM V_RPT_COURSE_STUDENT T1
WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'
AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'
AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'*/


/*1 高蓉蓉 1班  76  407 407 407
  2 雷蕾  1班   79  407    407 407
  3 杨正坤 1班  82  407 407 407
  4 李永承 1班  85  407 407 407
  5 敬金凤 1班  85  407 407 407*/

与OVER函数结合的几个函数介绍

/*与OVER函数结合的几个函数介绍
 ROW_NUMBER() OVER()、RANK() OVER()和DENSE_RANK() OVER()函数的使用

 RANK        跳跃排序
 DENSE_RANK  连续排序

 */

/*SELECT T.*
     ,RANK() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_RANK
     ,ROW_NUMBER() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_ROW_NUMBER
     ,DENSE_RANK() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_DENSE_RANK
     ,SUM(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_SUM
 FROM ( 
 SELECT 
   T1.STUDENT_NAME
   ,T1.CLASS_NAME
   ,T1.COURSE_SCORE
  FROM V_RPT_COURSE_STUDENT T1
WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'
AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'
AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88' ) T
*/

/*1 敬金凤 1班  85  1   1   1   170
 2  李永承 1班  85  1   2   1   170
 3  杨正坤 1班  82  3   3   2   252
 4  雷蕾   1班  79  4  4   3   331
 5  高蓉蓉 1班  76  5   5   4   407*/

 -- 加上IGNORE NULLS后,如果第一条是判断的那个字段是空的,则默认取下一条
 -- FIRST_VALUE和LAST_VALUE常用在计算排过序的结果集中的最大值和最小值。

 SELECT T.*
     ,FIRST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) LOW_SCORE1
     ,FIRST_VALUE(T.COURSE_SCORE IGNORE NULLS) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) LOW_SCORE2
     ,LAST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE 
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HIGH_SCORE1
     ,LAST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) HIGH_SCORE2


    ,MIN(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) MIN_SCORE1                                 
    ,MAX(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) MAX_SCORE1

    ,MIN(T.COURSE_SCORE) OVER() MIN_SCORE2                                 
    ,MAX(T.COURSE_SCORE) OVER() MAX_SCORE2
 FROM ( 
 SELECT 
   T1.STUDENT_NAME
   ,T1.CLASS_NAME
   ,T1.COURSE_SCORE
  FROM V_RPT_COURSE_STUDENT T1
WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'
AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'
AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88' ) T


/*1 高蓉蓉 1班  76  76  76  85  76  76  76  76  85
 2  雷蕾  1班  79  76  76  85  79  76  79  76  85
 3  杨正坤 1班  82  76  76  85  82  76  82  76  85
 4  李永承 1班  85  76  76  85  85  76  85  76  85
 5  敬金凤 1班  85  76  76  85  85  76  85  76  85*/

LAG() OVER()函数用法(取出前N行数据)

WITH T AS 
(SELECT 1 ID ,'A' NAME FROM DUAL
 UNION
 SELECT 2 ID ,'B' NAME FROM DUAL
 UNION
 SELECT 3 ID ,'C' NAME FROM DUAL
 UNION
 SELECT 4 ID ,'D' NAME FROM DUAL
 UNION
 SELECT 5 ID ,'E' NAME FROM DUAL
) 
SELECT ID,NAME,LAG(ID,1,'') OVER(ORDER BY NAME) AS LAG_ID FROM T;

/*1 A   
  2 B   1
  3 C   2
  4 D   3
  5 E   4
*/

本文由澳门皇冠金沙网站发布于网络服务,转载请注明出处:Oracle分析函数用法详解

关键词: