Oracle over函数学习

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