ROWS Between

最新推荐文章于 2025-08-20 10:47:58 发布

原创最新推荐文章于 2025-08-20 10:47:58 发布 · 210 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

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