ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析函数 over

分析函数 over

原创 Linux操作系统 作者:tthero00boo 时间:2013-09-26 17:24:03 0 删除 编辑
 
聚合函数:
count,min,max,sum,avg

特征:

     除了count(*),grouping,和grouping_id外,所有的聚合函数都会忽略null.当我们遇到Null 时,可以在聚合函数中使用NVL 函数来处理null
     Count 和Regr_count 聚合函数不会返回null,而是返回0或者某个数字。 其他的聚合函数当没有rows 则返回null

分析函数 over关键字

分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行
如一个函数sum, 什么都不带就是sum()聚集函数, sum()over(...)这样就是sum()分析函数

FUNCTION_NAME(,...)
OVER
()

例如: sum(sal) over (partition by deptno order by ename) new_alias


sum就是函数名
(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)
over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数

partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区

order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.

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

select a.*,sum(dataid)over( order by dataid range between 3 preceding and 1 following ) from datadic a;
select a.*,sum(dataid)over( order by dataid rows between 3 preceding and 1 following ) from datadic a;

range窗口的限制是:只能用于number或date类型的数据中,因为只有number和date可以进行加减操作,而不能从VARCHAR2中增加或减去N个单元.
另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中

ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列
    
range是数值上进行操作,rows是行的偏移量,可以用特定的子句进行指定特定的窗口,
n preceding :如果是range,表示比当前行dataid值小n的行开始。如果是rows,表示从当前行之前的n行开始。
n following :如果是range,表示比当前行dataid值大n的行结束,如果是rows,表示从当前行开始,当前行之后的n行结束。
between unbounded preceding and current rows :从窗口的第一行到当前行。

over后的写法:
over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
over(partition by deptno)按照部门分区

over(partition by deptno order by salary)     

与over函数结合的几个函数介绍
 
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).

rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).

dense_rank()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 

row_number()  rank()  dense_rank()
        1        1            1 
        2        2            2   
        3        2            2   
        4        4            3   


lag(arg1,arg2,arg3):
arg1是返回的列或表达式
arg2是希望检索的当前行分区的偏移量,是表示取前第几行的。
arg3是在arg2表示的数目超出了分组的范围时返回的默认值.

lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后;
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)
select a.dataid,
       lag(dataid, 1 , 9999) over(order by a.dataid) lag,
       lead(dataid, 1 , 8888) over(order by a.dataid) lead
  from datadic a;

    DATAID        LAG       LEAD
---------- ---------- ----------
         1       9999          2
         2          1          3
         3          2          4
         4          3          5
         5          4        140
       140          5        141
       141        140       8888
lag(expression<,offset><,default>)函数可以访问组内当前行之前的行,
而lead(expression<,offset><,default>)函数则正相反,组内当前行之后的行.

其中,offset是正整数,默认为1.因组内第一个条记录没有之前的行,最后一行没有之后的行,default就是用于处理这样的信息,默认为空.

注意:这2个函数必须指定 order By 字句.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28859270/viewspace-773429/,如需转载,请注明出处,否则将追究法律责任。

上一篇: linux kill & jobs
请登录后发表评论 登录
全部评论

注册时间:2013-06-30

  • 博文量
    31
  • 访问量
    142519