Oracle 分析函数Analytic Functions 说明.docx
- 文档编号:24886194
- 上传时间:2023-06-02
- 格式:DOCX
- 页数:34
- 大小:25.56KB
Oracle 分析函数Analytic Functions 说明.docx
《Oracle 分析函数Analytic Functions 说明.docx》由会员分享,可在线阅读,更多相关《Oracle 分析函数Analytic Functions 说明.docx(34页珍藏版)》请在冰豆网上搜索。
Oracle分析函数AnalyticFunctions说明
Oracle分析函数(AnalyticFunctions)说明
一.AnalyticFunctions说明
分析函数是oracle8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式.
官方对分析函数的说明如下:
Analyticfunctionscomputeanaggregatevaluebasedonagroupofrows.Theydifferfromaggregatefunctionsinthattheyreturnmultiplerowsforeachgroup.Thegroupofrowsiscalledawindowandisdefinedbytheanalytic_clause.Foreachrow,aslidingwindowofrowsisdefined.Thewindowdeterminestherangeofrowsusedtoperformthecalculationsforthecurrentrow.Windowsizescanbebasedoneitheraphysicalnumberofrowsoralogicalintervalsuchastime.
AnalyticfunctionsarethelastsetofoperationsperformedinaqueryexceptforthefinalORDERBYclause.AlljoinsandallWHERE,GROUPBY,andHAVINGclausesarecompletedbeforetheanalyticfunctionsareprocessed.Therefore,analyticfunctionscanappearonlyintheselectlistorORDERBYclause.
Analyticfunctionsarecommonlyusedtocomputecumulative,moving,centered,andreportingaggregates.
From:
AnalyticFunctions
分析函数是对一组查询结果进行运算,然后获得结果,从这个意义上,分析函数非常类似于聚合函数(AggregateFunction)。
区别是在调用分析函数时,后面加上了开窗子句over()。
聚合函数是对一个查询结果中的每个分组进行运算,并且对每个分组产生一个运算结果。
分析函数也是对一个查询结果中的每个分组进行运算,但每个分组对应的结果可以有多个。
产生这个不同的原因是分析函数中有一个窗口的概念,一个窗口对应于一个分组中的若干行,分析函数每次对一个窗口进行运算。
运算时窗口在查询结果或分组中从顶到底移动,对每一行数据生成一个窗口。
Oracle聚合函数(AggregateFunctions)说明
分析函数的over()部分的分析字句有3部分构成,分区语句,排序语句和窗口语句。
(1)分区语句(partitionby):
将查询结果分为不同的组,功能类似于groupby语句,是分析函数工作的基础。
默认是将所有结果作为一个分组。
(2)排序语句(orderby):
将每个分区进行排序。
(3)窗口语句:
定义当前窗口,具体说是对每一个分组,按照给定的排序规则排序后,从分组的顶部到底部依次迭代,每次针对当前的行可以定义一个包含若干行的窗口。
如果省略了窗口语句,默认使用从分组第一行到当前行的分组。
Analyticfunctionsarecommonlyusedindatawarehousingenvironments.Inthelistofanalyticfunctionsthatfollows,functionsfollowedbyanasterisk(*)allowthefullsyntax,includingthewindowing_clause.
--分析函数通常在数据仓库环境下使用,下表列出了所有的分析函数,其中加星号的支持全部语法,包括开窗选项。
AVG*
CORR*
COUNT*
COVAR_POP*
COVAR_SAMP*
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE*
LAG
LAST
LAST_VALUE*
LEAD
LISTAGG
MAX*
MIN*
NTH_VALUE*
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_(LinearRegression)Functions*
ROW_NUMBER
STDDEV*
STDDEV_POP*
STDDEV_SAMP*
SUM*
VAR_POP*
VAR_SAMP*
VARIANCE*
二.AnalyticFunctions使用示例
下面主要介绍一下以下几个函数的使用方法
1.Over()开窗函数
2.Nvl()函数
3.Rollup,Cube自动汇总函数
4.Rank,Dense_rank,Row_number函数
5.Lag,Lead函数
6.Sum,Avg,Count,Max函数
7.Ratio_to_report报表处理函数
8.First,Last,First_value,Last_value取基数的分析函数
9.Greatest,Least函数
10.Trunc,Round,Decode,Substr函数
2.1Over()开窗函数
Over()开窗函数是Oracle的分析函数,其语法如下:
函数名([参数])over([分区子句][排序子句[滑动窗口子句]])
分区子句类似于聚组函数所需要的groupby,排序子句可看成是SQL语句中的orderby,只不过在此语句中还可指定null值排前(nullsfirst)还是排后(nullslast)。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
over(orderbysalary)按照salary排序进行累计,orderby是个默认的开窗函数
over(partitionbydeptno)按照部门分区
over(orderbysalaryrangebetween50precedingand150following)
每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150
over(orderbysalaryrowsbetween50precedingand150following)
每行对应的数据窗口是之前50行,之后150行
over(orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing)
每行对应的数据窗口是从第一行到最后一行,等效:
over(orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing)
2.2Nvl()函数
NVL(EXP1,EXP2),函数返回exp1和exp2中第一个不为空的值。
如果exp1为空则返回exp2,否则返回exp1。
注意:
如果exp1不是字符串,那么返回的数据类型和exp1的数据类型相同,否则返回的数据类型为varchar2型。
SQL>selectnvl('Thisisnotnull',7)Frist,nvl(null,'MyOracle')Secondfromdual;
FRISTSECOND
-------------------------------
ThisisnotnullMyOracle
2.3自动汇总函数rollup,cube
Rollup:
表示的意思是:
除了分组的功能外,还进行累加的的,多了一个汇总。
如果是GROUPBYROLLUP(A,B,C)的话,GROUPBY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUPBY操作。
Cube提供了按照多字段汇总的功能。
如果是GROUPBYCUBE(A,B,C),GROUPBY顺序
(A、B、C)
(A、B)
(A、C)
(A),
(B、C)
(B)
(C),
最后对全表进行GROUPBY操作。
示例:
1.CREATETABLEstudentscore
2.(
3.student_namevarchar2(20),
4.subjectsvarchar2(20),
5.scorenumber
6.)
7.
8.
9.INSERTINTOstudentscoreVALUES('WBQ','ENGLISH',90);
10.INSERTINTOstudentscoreVALUES('WBQ','MATHS',95);
11.INSERTINTOstudentscoreVALUES('WBQ','CHINESE',88);
12.INSERTINTOstudentscoreVALUES('CZH','ENGLISH',80);
13.INSERTINTOstudentscoreVALUES('CZH','MATHS',90);
14.INSERTINTOstudentscoreVALUES('CZH','HISTORY',92);
15.INSERTINTOstudentscoreVALUES('CB','POLITICS',70);
16.INSERTINTOstudentscoreVALUES('CB','HISTORY',75);
17.INSERTINTOstudentscoreVALUES('LDH','POLITICS',80);
18.INSERTINTOstudentscoreVALUES('LDH','CHINESE',90);
19.INSERTINTOstudentscoreVALUES('LDH','HISTORY',95);
20.
21.select*fromstudentscore;
22.
23./*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
24.SELECTstudent_name,subjects,SUM(score)
25.FROMstudentscore
26.GROUPBYCUBE(student_name,subjects)
27.ORDERBY1;
28.
29. rgb(255,0,0);">等同于以下标准SQL 30. 31./*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ 32.SELECTNULL,subjects,SUM(score) 33.FROMstudentscore 34.GROUPBYsubjects 35.UNION 36.SELECTstudent_name,NULL,SUM(score) 37.FROMstudentscore 38.GROUPBYstudent_name 39.UNION 40.SELECTNULL,NULL,SUM(score) 41.FROMstudentscore 42.UNION 43.SELECTstudent_name,subjects,SUM(score) 44.FROMstudentscore 45.GROUPBYstudent_name,subjects 46. 47. 48./*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ 49.SELECTstudent_name,subjects,SUM(score) 50.FROMstudentscore 51.GROUPBYROLLUP(student_name,subjects); 52. 53. 54./*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ 55.SELECTstudent_name,NULL,SUM(score) 56.FROMstudentscore 57.GROUPBYstudent_name 58.UNION 59.SELECTNULL,NULL,SUM(score) 60.FROMstudentscore 61.UNION 62.SELECTstudent_name,subjects,SUM(score) 63.FROMstudentscore 64.GROUPBYstudent_name,subjects 65. 66. 67. 68. 69./*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ 70.SELECTGROUPING(student_name),GROUPING(subjects),student_name,subjects, 71.SUM(score) 72.FROMstudentscore 73.GROUPBYCUBE(student_name,subjects) 74.ORDERBY1,2; 75. 76. 77. 78./*Formattedon2009/11/0820: 36(FormatterPlusv4.8.8)*/ 79.SELECTGROUPING(student_name),GROUPING(subjects),student_name,subjects, 80.SUM(score) 81.FROMstudentscore 82.GROUPBYROLLUP(student_name,subjects) 83.ORDERBY1,2; 84. 85. 86. 87./*Formattedon2009/11/0820: 36(FormatterPlusv4.8.8)*/ 88.SELECTGROUPING_ID(student_name,subjects),student_name,subjects, 89.SUM(score) 90.FROMstudentscore 91.GROUPBYCUBE(student_name,subjects) 92.ORDERBY1; 93. 94. 95. 96./*Formattedon2009/11/0820: 36(FormatterPlusv4.8.8)*/ 97.SELECTGROUPING_ID(student_name,subjects),student_name,subjects, 98.SUM(score) 99.FROMstudentscore 100.GROUPBYROLLUP(student_name,subjects) 101.ORDERBY1; 102. 103. 104./*Formattedon2009/11/0820: 36(FormatterPlusv4.8.8)*/ 105.SELECTGROUPING(student_name),GROUPING(subjects), 106.CASE 107.WHENGROUPING(student_name)=0 108.ANDGROUPING(subjects)=1 109.THEN'学生成绩合计' 110.WHENGROUPING(student_name)=1 111.ANDGROUPING(subjects)=0 112.THEN'课目成绩合计' 113.WHENGROUPING(student_name)=1 114.ANDGROUPING(subjects)=1 115.THEN'总计' 116.ELSE'' 117.ENDsummary, 118.student_name,subjects,SUM(score) 119.FROMstudentscore 120.GROUPBYCUBE(student_name,subjects) 121.ORDERBY1,2; CREATETABLEstudentscore ( student_namevarchar2(20), subjectsvarchar2(20), scorenumber ) INSERTINTOstudentscoreVALUES('WBQ','ENGLISH',90); INSERTINTOstudentscoreVALUES('WBQ','MATHS',95); INSERTINTOstudentscoreVALUES('WBQ','CHINESE',88); INSERTINTOstudentscoreVALUES('CZH','ENGLISH',80); INSERTINTOstudentscoreVALUES('CZH','MATHS',90); INSERTINTOstudentscoreVALUES('CZH','HISTORY',92); INSERTINTOstudentscoreVALUES('CB','POLITICS',70); INSERTINTOstudentscoreVALUES('CB','HISTORY',75); INSERTINTOstudentscoreVALUES('LDH','POLITICS',80); INSERTINTOstudentscoreVALUES('LDH','CHINESE',90); INSERTINTOstudentscoreVALUES('LDH','HISTORY',95); select*fromstudentscore; /*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ SELECTstudent_name,subjects,SUM(score) FROMstudentscore GROUPBYCUBE(student_name,subjects) ORDERBY1; 等同于以下标准SQL /*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ SELECTNULL,subjects,SUM(score) FROMstudentscore GROUPBYsubjects UNION SELECTstudent_name,NULL,SUM(score) FROMstudentscore GROUPBYstudent_name UNION SELECTNULL,NULL,SUM(score) FROMstudentscore UNION SELECTstudent_name,subjects,SUM(score) FROMstudentscore GROUPBYstudent_name,subjects /*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ SELECTstudent_name,subjects,SUM(score) FROMstudentscore GROUPBYROLLUP(student_name,subjects); /*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ SELECTstudent_name,NULL,SUM(score) FROMstudentscore GROUPBYstudent_name UNION SELECTNULL,NULL,SUM(score) FROMstudentscore UNION SELECTstudent_name,subjects,SUM(score) FROMstudentscore GROUPBYstudent_name,subjects /*Formattedon2009/11/0820: 35(FormatterPlusv4.8.8)*/ SELECTGROUPING(student_name),GROUPING(subjects),student_name,subjects, SUM(score) FROMstudentscore GROUPBYCUBE(student_name,subjects) ORDERBY1,2; /*Formattedon2009/11/0820: 36(FormatterPlusv4.8.8)*/ SELECTGROUPING(student_name),GROUPI
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分析函数Analytic Functions 说明 分析 函数 Analytic