分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1.自动汇总函数rollup,cube,2.rank函数,rank,dense_rank,row_number3.lag,lead函数4.sum,avg,的移动增加,移动平均数5.ratio_to_report报表处理函数6.first,last取基数的分析函数基础数据Code:[Copytoclipboard]06:34:23SQL>select*fromt;BILL_MONTHAREA_CODENET_TYPELOCAL_FARE-------------------------------------------------2004055761G7393344.042004055761J5667089.852004055762G6315075.962004055762J6328716.152004055763G8861742.592004055763J7788036.322004055764G6028670.452004055764J6459121.492004055765G13156065.772004055765J11901671.702004065761G7614587.962004065761J5704343.052004065762G6556992.602004065762J6238068.052004065763G9130055.462004065763J7990460.252004065764G6387706.012004065764J6907481.662004065765G13562968.812004065765J12495492.502004075761G7987050.652004075761J5723215.282004075762G6833096.682004075762J6391201.442004075763G9410815.912004075763J8076677.412004075764G6456433.232004075764J6987660.532004075765G14000101.202004075765J12301780.202004085761G8085170.842004085761J6050611.372004085762G6854584.222004085762J6521884.502004085763G9468707.652004085763J8460049.432004085764G6587559.23BILL_MONTHAREA_CODENET_TYPELOCAL_FARE-------------------------------------------------2004085764J7342135.862004085765G14450586.632004085765J12680052.3840rowsselected.Elapsed:00:00:00.001.使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36SQL>setautoton06:43:36SQL>selectarea_code,sum(local_fare)local_fare06:43:502fromt06:43:513groupbyarea_code06:43:574unionall06:44:005select'合计'area_code,sum(local_fare)local_fare06:44:066fromt06:44:087/AREA_CODELOCAL_FARE------------------------576154225413.04576252039619.60576369186545.02576453156768.465765104548719.19合计333157065.316rowsselected.Elapsed:00:00:00.0