给定:
calendar_date Date_Key RowN
03-OCT-21 20211003 1
03-OCT-21 20211003 2
03-OCT-21 20211003 3
04-OCT-21 20211004 4
07-OCT-21 20211007 5
07-OCT-21 20211007 6
09-OCT-21 20211009 7
10-OCT-21 20211010 8
13-OCT-21 20211013 9
13-OCT-21 20211013 10
13-OCT-21 20211013 11
15-OCT-21 20211015 12
17-OCT-21 20211017 13
需要:从每个给定的当前日期滚动超过 7 个日历日期
这是我的代码,没有给我正确的结果
select z.calendar_date,z.date_key,ceil(z.rown/7) as Grp from
(select b.calendar_date,b.date_key ,rownum as RowN
from Samp.cal_date_dim a, Samp.cal_date_dim b
where b.date_key-a.date_key>0 and b.date_key-a.date_key<=6)z
代码选择说明:如果从 03 10 月开始,则从 10 月 3 日 - 10 月 9 日 -> 组 1 开始。如果从 04 10 月开始,则从 10 月 4 日 - 10 月 10 日 -> 组 2
。输出所需或期望的结果
calendar_date Grp
03-OCT-21 1
03-OCT-21 1
03-OCT-21 1
04-OCT-21 1
07-OCT-21 1
07-OCT-21 1
09-OCT-21 1
04-OCT-21 2
07-OCT-21 2
07-OCT-21 2
09-OCT-21 2
10-OCT-21 2
07-OCT-21 3
07-OCT-21 3
09-OCT-21 3
10-OCT-21 3
13-OCT-21 3
13-OCT-21 3
13-OCT-21 3
您似乎要求自我加入,以及dense_rank()
:
select dd.*, cd.seqnum as grp
from (select calendar_date, Date_Key,
dense_rank() over (order by calendar_date) as seqnum
from Samp.cal_date_dim
group by calendar_date, Date_Key
) cd join
Samp.cal_date_dim dd
on dd.calendar_date >= cd.calendar_date and
dd.calendar_date < cd.calendar_date + interval '7' day;