ITPub博客

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

oracle分析函数

原创 Linux操作系统 作者:木呼 时间:2011-02-28 17:37:12 0 删除 编辑
  语法:
  function_name(arg1,arg2,...)
  over
  ((partition-clause) (order-by-clause) (windowing clause))--中括号显示不出来,圆括号凑合吧。
  
  1.function_name
  Oracle提供了26个分析函数,按功能可分为5类
  一、等级函数,如row_number函数。用与排序,寻找前N个数据等。
  二、开窗函数,用与数据的一个窗口上。开窗函数与制表函数非常相似,事实上名字也一样,如:SUM,MIN,MAX等。关键的不同在于ORDER BY子句。有ORDER BY子句时,函数作用与窗口。请注意,要使用窗口,必须使用ORDER BY子句。
  三、制表函数,用与一个分区上的所有列。
  四、向前或向后检索值的函数,如LAG,LEAD。
  五、统计函数以及线性的衰减函数等。
  
  2.Over
  Over是一个关键字,用来区分普通的聚集函数和分析函数。
  例如:SUM(),SUM()OVER
  
  3.Partition by
  Partition是可选的分区子句。如果没有Partition子句,则全部结果集可看作是一个单一的大分区。
  
  4.order-by
  order by也是可选的子句,有些函数需要它,有些不需要。依靠已排序数据的那些函数则必须使用它,例如LAG,LEAD。在使用开窗函数时,该子句是强制的。
  例:
  select * from test;
  DATE_ID ID SUM(VALUE)
  ----------- ---------- ----------
  2008-3-1 111 3853832.24
  2008-3-1 115 965.13
  2008-3-1 116 2273.69
  2008-4-1 111 3485652
  2008-4-1 115 979.37
  2008-4-1 116 2247.35
  2008-5-1 111 6089902.32
  2008-5-1 115 1000.1
  2008-5-1 116 2238.06
  
  SQL> select date_id,
   2 id,
   3 value,
   4 sum(value) over() p_o,
   5 sum(value) over(partition by date_id) p_date,
   6 sum(value) over(partition by date_id order by id) p_id,
   7 sum(value) over(order by date_id) o_id
  8 from test
   9 /
  DATE_ID ID VALUE P_O P_DATE P_ID O_ID
  ----------- ---------- ---------- ---------- ---------- ---------- ----------
  2008-3-1 111 3853832.24 13439090.2 3857071.06 3853832.24 3857071.06
  2008-3-1 115 965.13 13439090.2 3857071.06 3854797.37 3857071.06
  2008-3-1 116 2273.69 13439090.2 3857071.06 3857071.06 3857071.06
  2008-4-1 111 3485652 13439090.2 3488878.72 3485652 7345949.78
  2008-4-1 115 979.37 13439090.2 3488878.72 3486631.37 7345949.78
  2008-4-1 116 2247.35 13439090.2 3488878.72 3488878.72 7345949.78
  2008-5-1 111 6089902.32 13439090.2 6093140.48 6089902.32 13439090.2
  2008-5-1 115 1000.1 13439090.2 6093140.48 6090902.42 13439090.2
  2008-5-1 116 2238.06 13439090.2 6093140.48 6093140.48 13439090.2
  
  在这里可以看到P_0是每一行数据的和,而P_DATE是按日期分区,求每个分区内每行数据之和。
  Order_by子句看起来有些奇怪。使用了partition的oreder by 同样按分区计算,计算每个分区内当前行与前面所有行数据之和。没有使用partition的order by 也是同样,它将当前日期下的数据总和加上了前面日期的总和(所谓‘前一行(个)’,‘后一行(个)’是根据order by 子句排序结果来的。)。
  
  Order by 语法:
  Order by expression (ASC|DESC) (NULLS FIRST|NULLS LAST)
  
  再看一个例子:
  SQL> select date_id,
   2 id,
   3 value,
   4 sum(value) over(order by date_id desc) o_id,
   5 sum(value) over(order by date_id) o_id
   6 from test
   7 /
  
  DATE_ID ID VALUE O_ID O_ID
  ----------- ---------- ---------- ---------- ----------
  2008-3-1 111 3853832.24 13439090.2 3857071.06
  2008-3-1 115 965.13 13439090.2 3857071.06
  2008-3-1 116 2273.69 13439090.2 3857071.06
  2008-4-1 111 3485652 9582019.2 7345949.78
  2008-4-1 115 979.37 9582019.2 7345949.78
  2008-4-1 116 2247.35 9582019.2 7345949.78
  2008-5-1 111 6089902.32 6093140.48 13439090.2
  2008-5-1 115 1000.1 6093140.48 13439090.2
  2008-5-1 116 2238.06 6093140.48 13439090.2
  
  不加DESC:当前行数据为当前日期下的VALUE和加上当前日期之前的所有日期下的VALUE。
  加DESC:当前行数据为当前日期下的VALUE和加上当前日期之后的所有日期下的VALUE。
  
  和普通的order by 一样,ASC是默认的。要以用来指定NULL值是出现在第一行还是最后一行。默认时NULL比任何其它值都大。
  
  使用ORDER BY为何会出现以上结果?这与windowing子句有关。分析函数中的order by会默认一个开窗子句,窗口是当前行和前面所有行(每个分区内)。没有order by 时默认的窗口为整个分区。
  
  5.windowing
  windowing 子句给出了一个定义变化或固定数据窗口方法,分析函数将对这些数据进行操作。其实就是定义了分析函数将对分区内的哪些数据行进行操作。
  
  Row窗口和Range窗口:
  SQL> select date_id,
   2 id,
   3 value,
   4 sum(value) over(order by date_id range 31 preceding) a,
   5 sum(value) over(order by date_id range 30 preceding) b,
   6 sum(value) over(order by date_id rows 1 preceding) c
   7 from test
   8 /
  
  DATE_ID ID VALUE A B C
  ----------- ---------- ---------- ---------- ---------- ----------
  2008-3-1 111 3853832.24 3857071.06 3857071.06 3853832.24
  2008-3-1 115 965.13 3857071.06 3857071.06 3854797.37
  2008-3-1 116 2273.69 3857071.06 3857071.06 3238.82
  ――――――――――――――――――――――――――――――――――
  |2008-4-1 111 3485652 7345949.78 3488878.72 3487925.69 |
  |2008-4-1 115 979.37 7345949.78 3488878.72 3486631.37 |
  |2008-4-1 116 2247.35 7345949.78 3488878.72 3226.72 |
  ――――――――――――――――――――――――――――――――――
  2008-5-1 111 6089902.32 9582019.2 9582019.2 6092149.67 
  2008-5-1 115 1000.1 9582019.2 9582019.2 6090902.42 
  2008-5-1 116 2238.06 9582019.2 9582019.2 3238.16 
  
  Row窗口比较好理解,列C的取值方法就是当前行的VALUE值加上前一行的VALUE。
  rows 1 preceding定义的窗口为当前行与前一行,共两行数据。分析函数SUM作用于当前行和前一行的VALUE。
  Range窗口:如果说Row窗口是根据行位置来做偏移,那么Range窗口则是根据行数据值来做偏移。range 31 preceding定义的窗口为DATA_ID<=DATA_ID-31的所有行。既当前日期向前偏移31天。
  看看range 31 preceding和range 30 preceding的不同:3月份有31天,4月份有30天。当DATA_ID=‘2008-4-1’
  range 31 preceding定义的窗口为:DATA_ID<=‘2008-3-1’,正好包括了前6行。
  range 30 preceding定义的窗口为:DATA_ID<=‘2008-3-2’,正好丢掉了3月1号的数据。
  因为Range要根据数据进行偏移,Range窗口仅对NUMBER和DATE起作用,且order by 中只能有一列。
  
  specifying 窗口
  我们再来看preceding。Row和Range定义了以何种方式偏移,下面介绍如何定义范围,既窗口的大小。
  unbounded preceding 从当前分区第一行开始,结束于当前行
  current row 从当前行开始 ( 并结束 )
  numberic expression preceding 从当前行的数字表达式之前的行开始 (注意Row和Range)
  numberic expression following 从当前行的数字表达式之后的行结束
  上面的表达式也可以用BETWEEN子句连接起来
  
  SQL> select date_id,
   2 id,
   3 value,
   4 sum(value) over(order by date_id rows between 1 preceding and 1 following) d
   5 from test
  
  DATE_ID ID VALUE d
  ----------- ---------- ---------- ----------
  2008-3-1 111 3853832.24 3854797.37
  2008-3-1 115 965.13 3857071.06
  2008-3-1 116 2273.69 3488890.82
  2008-4-1 111 3485652 3488905.06
  2008-4-1 115 979.37 3488878.72
  2008-4-1 116 2247.35 6093129.04
  2008-5-1 111 6089902.32 6093149.77
  2008-5-1 115 1000.1 6093140.48
  2008-5-1 116 2238.06 3238.16
  
  between 1 preceding and 1 following定义的窗口为当前行和当前行前一行,和当前行后一行,共3行数据,然后对这三行数据求和。
  
  注意:
  除了ORDER BY 子句外,分析函数是在查询中执行的最后操作集。因此分析函数不能直接出现在谓词中。分析函数只能出现在查询的选择列表或ORER BY 子句中。

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

上一篇: Oracle AQ使用
请登录后发表评论 登录
全部评论

注册时间:2010-04-19

  • 博文量
    93
  • 访问量
    152559