ITPub博客

首页 > 数据库 > Oracle > Oracle over函数学习

Oracle over函数学习

原创 Oracle 作者:静以致远√团团 时间:2015-07-17 17:10:30 0 删除 编辑
over是oracle的分析函数,不能单独使用,要与rank(),dense_rank(),row_number()等一起使用。

function本身 用于对窗口中的数据进行操作;
partitioning clause 用于将结果集分区
order by clause 用于对分区中的数据进行排序;
windowing clause 用于定义function在其上操作的行的集合,即function所影响的范围。

测试数据如下

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name)
  from over_test;
//以t_name分组,求出每个用户的个sal和

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(order by t_date)
  from over_test;
以t_date排序,求出没个时间点的累积和  如:id=10的sum(sal)=52+20+34+..+52+53=506  (相同时间点得到的结果是所有该时间点的值累加后的和)

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date)
  from over_test;
以t_name分组以t_date排序求每个用户每个时间点的sum(sal)

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date rows 1 preceding)
  from over_test;
以t_name分组,以t_date排序,求每个用户在当前时间与上1个时间点的sum(sal)
与rows结合时,preceding可以认为是偏移量或者幅度,即像前移动行数
如:id=18的用户,上一个时间点id=17,sum(sal)=89+35=124
    id=21的用户,上一个时间点id=18,sum(sal)=35+68=103

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date rows between 10 preceding and 1 preceding)
  from over_test;
 preceding还可以与between结合
以t_name分组,以t_date排序,求每个用户前10条到前1条记录之间的sum(sal)
如id=21的用户,sal为前10行至前1行sum(sal)=89+35=124

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date rows between 1 preceding and 2 following)
  from over_test;
following用来指定本列以后偏移的行数
以t_name分组,以t_date排序,求每个用户前1条到后2条记录之间的sum(sal),注意分组内有效
如id=19的用户的sum(sal)为前1条和后两条之间的和,sum(sal)=68+78+89  (注意53属于jarry分组)

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date rows between 1 following and 2 following)
  from over_test;
同preceding类似,以t_name分组,以t_date排序,求每个用户后1条到后2条记录之间的sum(sal)


select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date range between 1 following and 2 following)
  from over_test;
range不比rows,它是对指定的值进行运算后确定区间
以t_name分组,以t_date排序,求每个用户在[to_date+1,to_date+2]之间的sum(sal)
如id=4的用户 to_date+1=2015/7/8 13:09:56  to_date+2=2015/7/9 13:09:56
在2015/7/8 13:09:56-2015/7/9 13:09:56之间的有 13:09:56-2015/7/8 13:09:56 13:09:56-2015/7/9 13:09:56
所以sum(sal)=52+74=126

select id,
       t_name,
       t_date,
       t_sal,
       sum(t_sal) over(partition by t_name order by t_date range between 1 preceding and 2 following)
  from over_test;
同理,以t_name分组,以t_date排序,求每个用户在[to_date-1,to_date+2]之间的sum(sal)
如id=4的用户 to_date-1=2015/7/6 13:09:56  to_date+2=2015/7/9 13:09:56
在2015/7/6 13:09:56-2015/7/9 13:09:56之间的有13:09:56-2015/7/7 13:09:56 13:09:56-2015/7/8 13:09:56 13:09:56-2015/7/9 13:09:56
所以sum(sal)=55+52+74=181



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

请登录后发表评论 登录
全部评论
每个人都有梦想,去实现吧!

注册时间:2013-11-14

  • 博文量
    164
  • 访问量
    2103383