oracle聚合函数及其扩展使用

《oracle聚合函数及其扩展使用》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、数据源FrameworkManagerReportStudio未指定，或none不支持无average(avg)平均值平均值count计数计数countdistinct计数差异计数差异maximum(max)最大值最大值minimum(min)最小值最小值median中间值中间值standarddeviation(stddev)标准偏差标准偏差sum求和合计variance(var)差量差量countnonzero计数非零自动averagenonzero未知自动external未知自动any未知自动c

2、alculated未知自动unknown未知自动first_period未知自动last_period未知自动以下汇总函数仅存在于FrameworkManager或ReportStudio中。在数据源中没有对应的汇总函数。FrameworkManagerReportStudio自动自动已计算已计算无对应的汇总函数汇总无对应的汇总函数不适用聚合函数的扩展使用1.ROLLUP子句ROLLUP是GROUPBY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明：EG：select

3、*fromemployees2;EMPLOYEE_IDDIVISIJOB_IDFIRST_NAMELAST_NAMESALARY-------------------------------------------------------------------------1BUSPREJamesSmith8000002SALMGRRonJohnson3500003SALWORFredHobbs1400004SUPMGRSusanJones2000005SALWORRobGreen3500006SUP

4、WORJaneBrown2000007SUPMGRJohnGrey2650008SUPWORJeanBlue1100009SUPWORHenryHeyson12500010OPEMGRKevinBlack22500011OPEMGRKeithLong16500012OPEWORFrankHoward12500013OPEWORDoreenPenn14500014BUSMGRMarkSmith15500015BUSMGRJillJones17500016OPEENGMeganCraig24500017S

5、UPTECMatthewBrant11500018OPEMGRTonyClerke20000019BUSMGRTanyaConway20000020OPEMGRTerryCliff21500021SALMGRSteveGreen27500022SALMGRRoyRed37500023SALMGRSandraSmith33500024SALMGRGailSilver22500025SALMGRGeraldGold24500026SALMGREileenLane23500027SALMGRDoreenUp

6、ton23500028SALMGRJackEwing23500029SALMGRPaulOwens24500030SALMGRMelanieYork25500031SALMGRTracyYellow22500032SALMGRSarahWhite23500033SALMGRTerryIron22500034SALMGRChristineBrown24700035SALMGRJohnBrown24900036SALMGRKelvinTrenton25500037BUSWORDamonJones28000

7、0已选择37行。首先我们来看单独的GROUPBY语句：selectdivision_id,SUM(salary)fromemployees2groupbydivision_id;DIVISISUM(SALARY)-----------------BUS1610000OPE1320000SAL4936000SUP1015000接下来是扩展了的ROLLUP的使用：selectdivision_id,SUM(salary)fromemployees2groupbyROLLUP(division_id);DI

8、VISISUM(SALARY)-----------------BUS1610000OPE1320000SAL4936000SUP10150008881000再来看个例子：selectdivision_id,job_id,SUM(salary)fromemployees2groupbyROLLUP(division_id,job_id);DIVISIJOB_IDSUM(SALARY)-----------------------BUSMGR530000B