ROWS Between

ROWS Between

表结构：

spu_id pt click_pv

商品ID 日期 点击量

1.对应5天后的click_pv 比如1号要6号的

select t.*,
       nvl(lead(click_pv, 4) over(partition by spu_id order by pt), 0) as lead5_pv
  from click t

2.后5天的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id order by pt rows between current row and 4 following) as sum5_pv
  from click t

 

3.求每个spu_id的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id) as sum_pv
  from click t

 

 

4.求每个spu_id，当前日期和之后所有日期的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id order by pt desc) as sum_pv1,
       sum(click_pv) over(partition by spu_id order by pt rows between current row and unbounded following) as sum_pv2
  from click t

 5.求每个spu_id，当前日期和之前所有日期的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id order by pt) as sum_pv1,
       sum(click_pv) over(partition by spu_id order by pt rows between unbounded preceding and current row) as sum_pv2
  from click t

 

6.求每个spu_id，当前日期和之前2天日期的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id order by pt rows between 2 preceding and current row) as sum_pv2
  from click t

7.求每个spu_id，当前日期和之后2天日期的click_pv之和 

select t.*,
       sum(click_pv) over(partition by spu_id order by pt rows between current row and 2 following) as sum_pv2
  from click t

8.求每个spu_id，之前2天和之后2天日期的click_pv之和

select t.*,
       sum(click_pv) over(partition by spu_id order by pt rows between 2 preceding and 2 following) as sum_pv2
  from click t

 

SUM() OVER()

中如果省略rows between，默认就是unbounded preceding and unbounded following