Oracle分析函数

分析函数

分析函数语法

语法如下：

FUNCTION_NAME(<argument>,<argument>...)OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)

参考例子：

sum(sal) over (partition by deptno order by ename) new_alias

• sum就是函数名。
• (sal)是分析函数的参数，每个函数有0~3个参数，参数可以是表达式，例如：sum(sal+comm)。
• over是一个关键字，用于标识分析函数，否则查询分析器不能区别sum()聚集函数和sum()分析函数。
• partitionby deptno 是可选的分区子句，如果不存在任何分区子句，则全部的结果集可看作一个单一的大区。
• orderby ename 是可选的order by子句，有些函数需要它，有些则不需要。依靠已排序数据的那些函数，如：用于访问结果集中前一行和后一行的LAG和LEAD，必须使用，其它函数，如AVG，则不需要。在使用了任何排序的开窗函数时，该子句是强制性的，它指定了在计算分析函数时一组内的数据是如何排序的。

FUNCTION子句

ORACLE提供了26个分析函数，按功能分5类

分析函数分类

1、等级(ranking)函数：用于寻找前N种查询

2、开窗(windowing)函数：用于计算不同的累计，如SUM，COUNT，AVG，MIN，MAX等，作用于数据的一个窗口上

      例子：

      sum(t.sal)over (order by t.deptno, t.ename) running_total

      sum(t.sal)over (partition by t.deptno order by t.ename) department_total

3、制表(reporting)函数：与开窗函数同名，作用于一个分区或一组上的所有列

      例子：

      sum(t.sal)over () running_total2，

      sum(t.sal)over (partition by t.deptno ) department_total2

      制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句！

4、LAG，LEAD函数：这类函数允许在结果集中向前或向后检索值，为了避免数据的自连接，它们是非常有用的。

      VAR_POP，VAR_SAMP，STDEV_POPE及线性的衰减函数：计算任何未排序分区的统计值

PARTITION子句

      按照表达式分区(就是分组)，如果省略了分区子句，则全部的结果集被看作是一个单一的组

ORDER BY子句

      分析函数中ORDER BY的存在将添加一个默认的开窗子句，这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行，没有ORDER BY时，默认的窗口是全部的分区在Order by 子句后可以添加nulls last，如：order by comm desc nulls last表示排序时忽略comm列为空的行。 

WINDOWING子句

      用于定义分析函数将在其上操作的行的集合

      Windowing子句给出了一个定义变化或固定的数据窗口的方法，分析函数将对这些数据进行操作。默认的窗口是一个固定的窗口，仅仅在一组的第一行开始，一直继续到当前行，要使用窗口，必须使用ORDER BY子句。根据2个标准可以建立窗口：数据值的范围(RANGES)或与当前行的行偏移量。

Range窗口

      Range5 preceding：将产生一个滑动窗口，他在组中拥有当前行以前5行的集合

      RANGE窗口仅对NUMBERS和DATES起作用，因为不可能从VARCHAR2中增加或减去N个单元。另外的限制是ORDER BY中只能有一列，因而范围实际上是一维的，不能在N维空间中。

例子：

      avg(t.sal)over(order by t.hiredate asc range 100 preceding)统计前100天平均工资

Row窗口

      利用ROW分区，就没有RANGE分区那样的限制了，数据可以是任何类型，且ORDER BY可以包括很多列

Specifying窗口

       UNBOUNDEDPRECEDING：这个窗口从当前分区的每一行开始，并结束于正在处理的当前行

       CURRENTROW：该窗口从当前行开始(并结束)

       NumericExpression PRECEDING：对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始，对RANGE来说，从行序值小于数字表达式的当前行的值开始。

       NumericExpression FOLLOWING：该窗口在当前行Numeric Expression行之后的行终止(或开始)，且从行序值大于当前行Numeric Expression行的范围开始(或终止)

       range between 100 preceding and100 following：当前行100前，当前行100后。

       注意：

       分析函数允许你对一个数据集进排序和筛选，这是SQL从来不能实现的。除了最后的Order by子句之外，分析函数是在查询中执行的最后的操作集，这样的话，就不能直接在谓词中使用分析函数，即不能在上面使用where或having子句！

Oracle分析函数实例

按区域查找上一年度订单总额占区域订单总额20%以上的客户

表结构：ORDERS_TMP

NameNull?TypeCUST_NBRNOTNULLNUMBER(5)REGION_IDNOTNULLNUMBER(5)SALESPERSON_IDNOTNULLNUMBER(5)YEARNOTNULLNUMBER(4)MONTHNOTNULLNUMBER(2)TOT_ORDERSNOTNULLNUMBER(7)TOT_SALESNOTNULLNUMBER(11,2)

测试数据：

CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES1171120017212204454200110237802767200123375010682001122169110672001234262415712200056241279200062506581522000324449415120009274864254200032350602542000446454251200010435580454200012239190

测试语句：

  select o.cust_nbr customer,         o.region_id region,         sum(o.tot_sales) cust_sales,         sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales    from orders_tmp o   where o.year = 2001   group by o.region_id, o.cust_nbr;

CUSTOMERREGIONCUST_SALESREGION_SALES4537802378027637506806510664315680651171220412204

解析：

      请注意上面的绿色高亮部分，group by的意图很明显：将数据按区域ID，客户进行分组，那么Over这一部分有什么用呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户)。

      这就是over函数的作用了！它的作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。

1、现在我们已经知道2001年度每个客户及其对应区域的订单总额，那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了

  select *    from (select o.cust_nbr customer,                 o.region_id region,                 sum(o.tot_sales) cust_sales,                 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales            from orders_tmp o           where o.year = 2001           group by o.region_id, o.cust_nbr) all_sales   where all_sales.cust_sales > all_sales.region_sales * 0.2;

CUSTOMERREGIONCUST_SALESREGION_SALES45378023780210664315680651171220412204

2、现在我们已经知道这些大客户是谁了！哦，不过这还不够，如果我们想要知道每个大客户所占的订单比例呢？看看下面的SQL语句，只需要一个简单的Round函数就搞定了。

  select all_sales.*,         100 * round(cust_sales / region_sales, 2) || '%' Percent    from (select o.cust_nbr customer,                 o.region_id region,                 sum(o.tot_sales) cust_sales,                 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales            from orders_tmp o           where o.year = 2001           group by o.region_id, o.cust_nbr) all_sales   where all_sales.cust_sales > all_sales.region_sales * 0.2;

CUSTOMERREGIONCUST_SALESREGION_SALESPERCENT453780237802100%106643156806594%1171220412204100%

总结：

1.    Over函数指明在那些字段上做分析，其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。

2.    Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。例如这里的SUM，还有诸如Rank，Dense_rank等。

使用分析函数来为记录排名

表结构：USER_ORDER

NameNull?TypeREGION_IDNOTNULLNUMBER(5)CUSTOMER_IDNOTNULLNUMBER(5)CUSTOMER_SALESNOTNULLNUMBER(11,2)

测试数据：

REGION_IDCUSTOMER_IDCUSTOMER_SALES51151162.001029903383.0067971585.001028986964.009211020541.009221036146.008161068467.00681141638.00531161286.00551169926.008191174421.007121182275.007111190421.006101196748.00691208959.0010301216858.00521224992.009241224992.009231224992.008181253840.00715125591.007131310434.0010271322747.008201413722.00661788836.0010261808949.00541878275.007141929774.008171944281.009252232703.00

注意：

      这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户，如果使用rownum会有什么样的后果呢？

测试语句：

  select rownum, t.*    from (select * from user_order order by customer_sales desc) t   where rownum <= 12   order by customer_sales desc;

ROWNUMREGION_IDCUSTOMER_IDCUSTOMER_SALES19252232703.0028171944281.0037141929774.004541878275.00510261808949.006661788836.0078201413722.00810271322747.0097131310434.00107151255591.00118181253840.0012521224992.00

很明显假如只是简单地按rownum进行排序的话，我们漏掉了另外两条记录(参考上面的结果)。

一、 针对上面的情况，Oracle从8i开始就提供了3个分析函数：rand，dense_rank，row_number来解决诸如此类的问题，下面我们来看看这3个分析函数的作用以及彼此之间的区别：

        rank，dense_rank，row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

1.    row_number

      row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

2.    dense_rank

      dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

3.    rank

      rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

      通过实例来说明，下面的例子演示了3个不同函数在遇到相同数据时不同排名策略：

测试语句：

  select region_id,         customer_id,         customer_sales,         rank() over(order by customer_sales desc) rank,         dense_rank() over(order by customer_sales desc) dense_rank,         row_number() over(order by customer_sales desc) row_number    from user_order;

REGION_IDCUSTOMER_IDCUSTOMER_SALESRANKDENSE_RANKROW_NUMBER9252232703.001118171944281.002227141929774.00333541878275.0044410261808949.00555661788836.006668201413722.0077710271322747.008887131310434.009997151255591.001010108181253840.001111119231224992.001212129241224992.00121213521224992.0012121410301216858.00151315691208959.001614166101196748.001715177111190421.001816187121182275.001917198191174421.00201820551169926.00211921531161286.00222022681141638.002321238161068467.002422249221036146.002523259211020541.002624261028986964.0027252767971585.002826281029903383.0029272951151162.00302830

请注意上面的红字部分，演示了3种不同的排名策略：

• 对于第一条相同的记录，3种函数的排名都是一样的。
• 当出现第二条相同的记录时，rank和dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录。
• 当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13，14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增。

一、 假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持，我们所要做的仅仅是在over函数中order by的前面增加一个分组子句：partition by region_id。

测试语句：

select region_id,       customer_id,       sum(customer_sales) total,       rank() over(partition by region_id order by sum(customer_sales) desc) rank,       dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,       row_number() over(partition by region_id order by sum(customer_sales) desc) row_number  from user_order group by region_id, customer_id;

REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER5418782751115212249922225511699263335311612864445115116255566178883611169120895922261011967483336811416384446797158555571419297741117131310434222715125559133371111904214447121182275555817194428111182014137222228181253840333819117442144481610684675559252232703111924122499222292312249922239221036146434921102054154510261808949111102713227472221030121685833310289869644441029903383555

      现在我们看到的排名将是基于各个地区的，而非所有区域的了！Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。

带空值的排列

在前面我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值？

测试语句：

select region_id,       customer_id,       sum(customer_sales) cust_total,       sum(sum(customer_sales)) over(partition by region_id) region_total,       rank() over(partition by region_id order by sum(customer_sales) desc) rank  from user_order group by region_id, customer_id;

REGION_IDCUSTOMER_IDCUST_TOTALREGION_TOTALRANK54 55856411521224992558564125511699265585641353116128655856414511511625585641566178883663077661……………      我们看到这里有一条记录的CUST_TOTAL字段值为NULL，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：

测试语句：

select region_id,       customer_id,       sum(customer_sales) cust_total,       sum(sum(customer_sales)) over(partition by region_id) reg_total,       rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank  from user_order group by region_id, customer_id;

REGION_IDCUSTOMER_IDCUST_TOTALREGION_TOTALRANK521224992370736615511699263707366253116128637073663511511623707366454 3707366566178883663077661……………

黄色高亮处，NULLS LAST/FIRST告诉Oracle让空值排名最后。

Top/Bottom N查询

      在日常的工作生产中，我们经常碰到这样的查询：找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示：

一、找出所有订单总额排名前3的大客户：

测试语句：

select *  from (select t.region_id,               t.customer_id,               sum(t.customer_sales) cust_total,               rank() over(order by sum(t.customer_sales) desc nulls last) rank          from user_order t         group by t.region_id, t.customer_id) tmp where tmp.rank <= 3;

REGION_IDCUSTOMER_IDCUST_TOTALRANK925223270318171944281271419297743

二、找出每个区域订单总额排名前3的大客户：

测试语句：

select *  from (select t.region_id,               t.customer_id,               sum(t.customer_sales) cust_total,               rank() over(order by sum(t.customer_sales) desc nulls last) rank          from user_order t         group by t.region_id, t.customer_id) tmp where tmp.rank <= 3;

REGION_IDCUSTOMER_IDCUST_TOTALREGION_TOTALRANK5212249923707366155116992637073662531161286370736636617888366307766169120895963077662610119674863077663714192977468684951713131043468684952715125559168684953817194428168547311820141372268547312818125384068547313925223270367393741924122499267393742923122499267393742102618089496238901110271322747623890121030121685862389013

First/Last排名查询

      想象一下下面的情形：找出订单总额最多、最少的客户。按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名，第二个查询按照订单总额升序排列以期拿到第一名。是不是很烦？因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。

      幸好Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题。还是用实例说话：

测试语句：

select min(t.customer_id) keep(dense_rank first order by sum(t.customer_sales) desc) first,       min(t.customer_id) keep(dense_rank last order by sum(t.customer_sales) desc) last  from user_order t where t.customer_sales is not null group by customer_id;

FIRSTLAST251

解析：

1.    为什么这里要用min函数

      min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。

2.    Keep这个东西是干什么的

      从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。

3.    fist/last是干什么的

      dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。

4.    dense_rank和dense_rank()有什么不同，能换成rank吗

      不能换成rank，报错：ORA-02000: missing DENSE_RANK

按层次查询

      有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。

测试语句：

 

select region_id,       customer_id,       sum(customer_sales) cust_total,       ntile(5) over(order by sum(customer_sales) desc) til  from user_order  where customer_sales is not null group by region_id, customer_id;

REGION_IDCUSTOMER_IDCUST_TOTALTILE54 1925223270318171944281171419297741102618089491661788836182014137222102713227472713131043427151255591281812538402923122499229241224992352122499231030121685836912089593610119674837111190421371211822754819117442145511699264531161286468114163848161068467492210361465921102054151028986964567971585510299033835511511625

      Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。

窗口函数

一、窗口函数简介

      到目前为止，我们所学习的分析函数在计算/统计一段时间内的数据时特别有用，但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢？举些例子来说：

• 列出每月的订单总额以及全年的订单总额
• 列出每月的订单总额以及截至到当前月的订单总额
• 列出上个月、当月、下一月的订单总额以及全年的订单总额
• 列出每天的营业额及一周来的总营业额
• 列出每天的营业额及一周来每天的平均营业额

      仔细回顾一下前面我们介绍到的分析函数，我们会发现这些需求和前面有一些不同：前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集，而这里有部分需求例如2，需要随着遍历记录集的每一条记录的同时进行统计。

      这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合：

• 通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
• 通过指定一个时间间隔：例如在交易日之前的前30天
• 通过指定一个范围值：例如所有占到当前交易量总额5%的记录

二、窗口函数示例－全统计

下面我们以需求：列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用。

表结构：ORDERS

NameNull?TypeMONTHNOTNULLNUMBER(2)TOT_SALESNOTNULLNUMBER(11,2)

测试数据：

MONTHTOT_SALES161069724286763637031454114655929356501485760691484605209392898105101171153288912492458

测试语句：

Oracle为这种情况提供了一个子句：rows between ... preceding and... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录：

select month,       sum(tot_sales) month_sales,       sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) total_sales  from orders group by month;

MONTHMONTH_SALESTOTAL_SALES161069763077662428676630776636370316307766454114663077665592935630776665014856307766760691463077668460520630776693928986307766105101176307766115328896307766124924586307766

      这个统计在记录集形成的过程中执行了12次，这是相当费时的！

unboundedpreceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：

测试语句：

select month,       sum(tot_sales) month_sales,       sum(sum(tot_sales)) over(order by month rows between 1 preceding and unbounded following) total_sales  from orders group by month;

MONTHMONTH_SALESTOTAL_SALES161069763077662428676630776636370315697069454114652683935592935463136265014854090216760691434972818460520299579693928982388882105101171928362115328891535464124924581025347

      很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。

 

一、窗口函数进阶－滚动统计(累积/均值)

      考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。

      很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: curreent row就可以了。

测试语句：

select month,       sum(tot_sales) month_sales,       sum(sum(tot_sales)) over(order by month rows between unbounded preceding and current row) current_total_sales  from orders group by month;

MONTHMONTH_SALESCURRENT_TOTAL_SALES16106976106972428676103937336370311676404454114622175505592935281048565014853311970760691439188848460520437940493928984772302105101175282419115328895815308124924586307766

      在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：sum(sum(tot_sales))换成avg(sum(tot_sales))即可。

一、窗口函数进阶－根据时间范围统计

      前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：

表结构：CUST_ORDER

NameNull?TypeORDER_DTNOTNULLDATESALE_PRICENOTNULLNUMBER(11,2)

测试数据：

MONTHTOT_SALES2012-10-125.002012-10-356.002012-10-445.002012-10-558.002012-10-695.002012-10-732.002012-10-814.002012-10-988.002012-10-1066.00

测试语句：

select trunc(order_dt) day,       sum(sale_price) daily_sales,       avg(sum(sale_price)) over(order by trunc(order_dt) range between interval '2' day preceding and interval '2' day following) five_day_avg  from cust_order  group by order_dt

MONTHTOT_SALESFIVE_DAY_AVG2012-10-12540.52012-10-356462012-10-44563.52012-10-55857.22012-10-69548.82012-10-73257.42012-10-814592012-10-988502012-10-106656

为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。

 

一、窗口函数进阶－first_value/last_value

Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。

测试语句：

select month,       sum(tot_sales) monthly_sales,       first_value(sum(tot_sales)) over(order by month rows between 1 preceding and 1 following) prev_month,       last_value(sum(tot_sales)) over(order by month rows between 1 preceding and 1 following) next_month,       avg(sum(tot_sales)) over(order by month rows between 1 preceding and 1 following) rolling_avg  from orders group by month

MONTHTOT_SALESPREV_MONTHNEXT_MONTHROLLING_AVG1610697610697428676519686.52428676610697637031558801.3333333333637031428676541146535617.6666666674541146637031592935590370.6666666675592935541146501485545188.6666666676501485592935606914567111.33333333376069145014854605205229738460520606914392898486777.3333333339392898460520510117454511.66666666710510117392898532889478634.66666666711532889510117492458511821.33333333312492458532889492458512673.5

首先我们来看:rows between 1 preceding and 1following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！

 

一、窗口函数进阶－比较相邻记录

通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？

从第五部分的介绍我们可以知道，利用first_value(sum(tot_sales) over(order by month rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。

lag函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个的记录集内两条相邻记录的时候特别有用。

测试语句：

select month,       sum(tot_sales) tot_sales,       lag(sum(tot_sales), 1) over(order by month) prev_month_sales  from orders group by month;

MONTHTOT_SALESPREV_MONTH_SALES1610697 24286766106973637031428676454114663703155929355411466501485592935760691450148584605206069149392898460520105101173928981153288951011712492458532889

lag(sum(tot_sales),1)中的1表示以1月为基准。

报表函数

一、报表函数简介

回顾一下前面《窗口函数》中关于全统计一节，我们使用了Oracle提供的：

sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：

测试语句：

select month,       sum(tot_sales) tot_sales,       sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) win_sales,       sum(sum(tot_sales)) over() rpt_sales  from orders group by month;

MONTHTOT_SALESWIN_SALESRPT_SALES161069763077666307766242867663077666307766363703163077666307766454114663077666307766559293563077666307766650148563077666307766760691463077666307766846052063077666307766939289863077666307766105101176307766630776611532889630776663077661249245863077666307766

over函数的空括号表示该记录集的所有记录都应该被列入统计的范围，如果使用了partition by则先分区，再依次统计各个分区。

二、RATIO_TO_REPORT函数

报表函数特(窗口函数)特别适合于报表中需要同时显示详细数据和统计数据的情况。例如在销售报告中经常会出现这样的需求：列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例：

方法一：

测试语句：

select all_sales.*,       100 * round(cust_sales / region_sales, 2) || '%' Percent  from (select o.cust_nbr customer,               o.region_id region,               sum(o.tot_sales) cust_sales,               sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales          from orders_tmp o         where o.year = 2001         group by o.region_id, o.cust_nbr) all_sales

CUSTOMERREGIONCUST_SALESREGION_SALESPERCENT453780237802100%763750680656%106643156806594%1171220412204100%

方法二：

测试语句：

select o.cust_nbr customer,       o.region_id region,       sum(o.tot_sales) cust_sales,       sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales,       100 * round(sum(tot_sales) / sum(sum(tot_sales))                   over(partition by region_id),                   2) || '%' percent_of_region  from orders_tmp o where o.year = 2001 group by o.region_id, o.cust_nbr

方法三：

测试语句：

select o.cust_nbr customer,        o.region_id region,        sum(o.tot_sales) cust_sales,        100 * round(ratio_to_report(sum(o.tot_sales))                    over(partition by region_id),                    2) || '%' region_sales   from orders_tmp o  where o.year = 2001  group by o.region_id, o.cust_nbr

Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。

分析函数总结

这一篇是对前面所有关于分析函数的文章的总结：

一、统计方面

  Sum( ) Over ([Partition by  ] [Order by  ])  Sum( ) Over ([Partition by  ] [Order by  ]          Rows Between   Preceding And   Following)          Sum( ) Over ([Partition by  ] [Order by  ]        Rows Between   Preceding And Current Row)  Sum( ) Over ([Partition by  ] [Order by  ]        Range Between Interval ' ' 'Day' Preceding                     And Interval ' ' 'Day' Following )

二、排列方面

  Rank() Over ([Partition by  ] [Order by  ] [Nulls First/Last])     Dense_rank() Over ([Patition by  ] [Order by  ] [Nulls First/Last])        Row_number() Over ([Partitionby  ] [Order by  ] [Nulls First/Last])        Ntile( ) Over ([Partition by  ] [Order by  ])

三、最大值/最小值查找方面

  Min( )/Max( ) Keep (Dense_rank First/Last [Partition by  ] [Order by  ])

四、首记录/末记录查找方面

  First_value / Last_value(Sum( ) Over ([Patition by  ] [Order by  ]        Rows Between   Preceding And   Following  ))

五、相邻记录之间比较方面

  Lag(Sum( ), 1) Over([Patition by  ] [Order by  ])

常用分析函数

AVG

语法如下：

AVG([DISTINCT | ALL ] expr)[ OVER(analytic_clause) ]

功能描述：

用于计算一个组和数据窗口内表达式的平均值。

例子：

计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来

SELECT manager_id,       last_name,       hire_date,       salary,       AVG(salary) OVER(PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg  FROM employees;

MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG100Kochhar21-SEP-891700017000100De Haan13-JAN-931700015000100Raphaely07-DEC-941100011966.6667100Kaufling01-MAY-95790010633.3333100Hartstein17-FEB-96130009633.33333100Weiss18-JUL-96800011666.6667100Russell01-OCT-961400011833.3333

CORR

语法如下：

CORR(expr,expr)

功能描述：

功能描述：返回一对表达式的相关系数，它是如下的缩写：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数,相关系数给出了关联的强度，0表示不相关。

例子：

返回1998年月销售收入和月单位销售的关系的累积系数。

SELECT t.calendar_month_number,       CORR(SUM(s.amount_sold), SUM(s.quantity_sold)) OVER(ORDER BY t.calendar_month_number) as CUM_CORR  FROM sales s, times t WHERE s.time_id = t.time_id   AND calendar_year = 1998 GROUP BY t.calendar_month_number ORDER BY t.calendar_month_number;

COUNT

语法如下：

      COUNT([distinct][*][expr])

功能描述：

对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。

例子：

下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水。

SELECT last_name,       salary,       COUNT(*) OVER() AS cnt1,       COUNT(*) OVER(ORDER BY salary) AS cnt2,       COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3  FROM employees;

COVAR_POP

语法如下：

       COVAR_POP(expr,expr)

功能描述：

返回一对表达式的总体协方差。

例子：

下例CUM_COVP返回定价和最小产品价格的累积总体协方差。

SELECT last_name,       salary,       COUNT(*) OVER() AS cnt1,       COUNT(*) OVER(ORDER BY salary) AS cnt2,       COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3  FROM employees;

COVAR_SAMP

语法如下：

      COVAR_SAMP(expr,expr)

功能描述：

返回一对表达式的样本协方差

CUME_DIST

功能描述：

计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3

DENSE_RANK

功能描述：

根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。

FIRST

功能描述：

从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录

例子：

下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值。

SELECT last_name,       department_id,       salary,       MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY commission_pct) OVER(PARTITION BY department_id) "Worst",       MAX(salary) KEEP(DENSE_RANK LAST ORDER BY commission_pct) OVER(PARTITION BY department_id) "Best"  FROM employees ORDER BY department_id, salary;

FIRST_VALUE

功能描述：

         返回组中数据窗口的第一个值。

例子：

下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字。

SELECT department_id,       last_name,       salary,       FIRST_VALUE(last_name) OVER(PARTITION BY department_id ORDER BY salary ASC) AS lowest_sal  FROM employees

LAG

语法如下：

      LAG(expr,[offset],[default])

参数分析：

• expr: 是要做对比的字段。
• offset：偏移量，默认值为1。
• 当没有上一个值时用default值代替。

功能描述：

         可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD。

测试数据：

TCH_IDCOL_NAME_CNCOL_VALUE1aaa102bbb203ccc301ddd401eee50测试语句：

select t.tch_id,       t.col_name_cn,       t.col_value,       lag(t.col_value, 1, null) over(partition by t.tch_id order by t.col_value) sal  from tache_columns t;

TCH_IDCOL_NAME_CNCOL_VALUESAL1aaa10 1ddd40101eee50402bbb20 3ccc30 

LAST

功能描述：

从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

例子：

     参考FIRST的例子

LAST_VALUE

功能描述：

返回组中数据窗口的最后一个值。例子：

SELECT department_id,       last_name,       salary,       LAST_VALUE(last_name) OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal  FROM employees

LEAD

语法如下：

      LEAD(expr,[offset],[default])

参数分析：

• expr: 是要做对比的字段。
• offset：偏移量，默认值为1。
• 当没有上一个值时用default值代替。

功能描述：

LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中最后一行）

测试数据：

TCH_IDCOL_NAME_CNCOL_VALUE1aaa102bbb203ccc301ddd401eee50测试语句：

select t.tch_id,       t.col_name_cn,       t.col_value,       lead(t.col_value, 1, null) over(partition by t.tch_id order by t.col_value) sal  from tache_columns t;

TCH_IDCOL_NAME_CNCOL_VALUESAL1aaa10401ddd40501eee50 2bbb20 3ccc30 

MAX

语法如下：

         MAX(expr)

功能描述：

         在一个组中的数据窗口中查找表达式的最大值。

例子：

      下面例子中dept_max返回当前行所在部门的最大薪水值。

SELECT department_id,       last_name,       salary,       MAX(salary) OVER(PARTITION BY department_id) AS dept_max  FROM employees WHERE department_id in (10, 20, 30);

MIN

语法如下：

      MAX(expr)

功能描述：

      在一个组中的数据窗口中查找表达式的最小值。

例子：

      下面例子中dept_min返回当前行所在部门的最小薪水值。

SELECT department_id,       last_name,       salary,       MIN(salary) OVER(PARTITION BY department_id) AS dept_max  FROM employees WHERE department_id in (10, 20, 30);

NTILE

语法如下：

NTILE (expr)

功能描述：

将一个组分为"表达式"的散列表示，例如，如果表达式=4，则给组中的每一行分配一个数（从1到4），如果组中有20行，则给前5行分配1，给下5行分配2等等。如果组的基数不能由表达式值平均分开，则对这些行进行分配时，组中就没有任何percentile的行数比其它percentile的行数超过一行，最低的percentile是那些拥有额外行的percentile。例如，若表达式=4，行数=21，则percentile=1的有5行，percentile=2的有5行等等。

例子：

下例中把6行数据分为4份。

SELECT last_name, salary, NTILE(4) OVER(ORDER BY salary DESC) AS quartile  FROM employees

PERCENT_RANK

功能描述：

和CUME_DIST（累积分配）函数类似，对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组中所有的行数）。该函数总是返回0～1（包括1）之间的数。

例子：

下例中如果Khoo的salary为2900，则pr值为0.6，因为RANK函数对于等值的返回序列值是一样的。

SELECT department_id,       last_name,       salary,       PERCENT_RANK() OVER(PARTITION BY department_id ORDER BY salary) AS pr  FROM employees ORDER BY department_id, salary;

PERCENTILE_CONT

功能描述：

返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数PERCENT_RANK，如果没有正好对应的数据值，就通过下面算法来得到值：

RN = 1+ (P*(N-1)) 其中P是输入的分布百分比值，N是组内的行数

CRN = CEIL(RN) FRN =FLOOR(RN)

if (CRN = FRN = RN)

then  (value of expression from row at RN)

else

(CRN - RN) * (value ofexpression for row at FRN) +

(RN - FRN) * (value ofexpression for row at CRN)

注意：本函数与PERCENTILE_DISC的区别在找不到对应的分布值时返回的替代值的计算方法不同。

PERCENTILE_DISC

功能描述：

返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数CUME_DIST，如果没有正好对应的数据值，就取大于该分布值的下一个值。

注意：本函数与PERCENTILE_CONT的区别在找不到对应的分布值时返回的替代值的计算方法不同。

 

RANK

功能描述：

根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。

例子：

下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK、ROW_NUMBER函数的区别）

SELECT d.department_id,       e.last_name,       e.salary,       RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary) as drank  FROM employees e, departments d WHERE e.department_id = d.department_id   AND d.department_id IN ('60', '90');

RATIO_TO_REPORT

语法如下：

       RATIO_TO_REPORT(expr)

功能描述：

该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献

例子：

下例计算每个员工的工资占该类员工总工资的百分比。

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER() AS rr  FROM employees

REGR_ (Linear Regression) Functions

功能描述：

这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用。

• REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)
• REGR_INTERCEPT：返回回归线的y截距，等于 AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
• REGR_COUNT：返回用于填充回归线的非空数字对的数目
• REGR_R2：返回回归线的决定系数，计算式为：

If VAR_POP(expr2) = 0then return NULL

If VAR_POP(expr1) = 0 andVAR_POP(expr2) != 0 then return 1

If VAR_POP(expr1) > 0and VAR_POP(expr2 != 0 then 

returnPOWER(CORR(expr1,expr),2)

• REGR_AVGX：计算回归线的自变量(expr2)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr2)
• REGR_AVGY：计算回归线的应变量(expr1)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr1)
• REGR_SXX：返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
• REGR_SYY：返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
• REGR_SXY: 返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

ROW_NUMBER

功能描述：

返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。

例子：

下例返回每个员工再在每个部门中按员工号排序后的顺序号。

SELECT department_id,       last_name,       employee_id,       ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY employee_id) AS emp_id  FROM employees

STDDEV

功能描述：

计算当前行关于组的标准偏离。

STDDEV_POP

功能描述：

该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。

STDDEV_SAMP

功能描述：

该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。

SUM

功能描述：

该函数计算组中表达式的累积和。

例子：

下例计算同一经理下员工的薪水累积值。

SELECT manager_id,       last_name,       salary,       SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum  FROM employees WHERE manager_id in (101, 108);

MANAGER_IDLAST_NAMESALARYC_MAVG101Whalen44004400101Mavris650010900101Baer1000020900101GreenBerg1200032900101Higgins1200044900…………

VAR_POP

功能描述：

（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：

(SUM(expr* expr) -SUM(expr)* SUM(expr)/ COUNT(expr)) / COUNT(expr)

VAR_SAMP

功能描述：

（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

VARIANCE

功能描述：

该函数返回表达式的变量，Oracle计算该变量如下：

如果表达式中行数为1，则返回0；如果表达式中行数大于1，则返回VAR_SAMP。

SYS_CONNECT_BY_PATH 

语法如下：

SYS_CONNECT_BY_PATH ( column , char )

参数分析：

• column：列名。
• char：2个字段之间的连接符号，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')。

例子：

表结构：USER_ORDER

NameNull?TypeDEPTNONOTNULLNUMBER(2)DNAMENULLVARCHAR2(14)LOCNULLVARCHAR2(13)

测试数据：

DEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON

根据部门编号排序，连接部门名称

测试语句1：

select t.deptno, t.dname, row_number() over(order by t.deptno) rnFirst  from dept t

测试语句2：

select tmp1.deptno,       tmp1.dname,       tmp1.rnFirst,       lead(rnFirst) over(order by tmp1.deptno desc) rnNext  from (select t.deptno,               t.dname,               row_number() over(order by t.deptno) rnFirst          from dept t) tmp1

测试语句3：

select sys_connect_by_path(tmp2.dname, '、')  from (select tmp1.deptno,               tmp1.dname,               tmp1.rnFirst,               lead(rnFirst) over(order by tmp1.deptno desc) rnNext          from (select t.deptno,                       t.dname,                       row_number() over(order by t.deptno) rnFirst                  from dept t) tmp1) tmp2 start with tmp2.rnNext is nullconnect by tmp2.rnNext = prior rnFirst

测试语句4：

select ltrim(max(sys_connect_by_path(tmp2.dname, '、')),'、')  from (select tmp1.deptno,               tmp1.dname,               tmp1.rnFirst,               lead(rnFirst) over(order by tmp1.deptno desc) rnNext          from (select t.deptno,                       t.dname,                       row_number() over(order by t.deptno) rnFirst                  from dept t) tmp1) tmp2 start with tmp2.rnNext is nullconnect by tmp2.rnNext = prior rnFirst