17,082
社区成员
发帖
与我相关
我的任务
分享
select case when length(a)=8 then a||'+'||to_char(to_date(a,'yyyymmdd')+1,'yyyymmdd')
else replace(a,',','+') end a,b from(
select wm_concat(a)a,sum(b)b from(
select a,b,
row_number()over(partition by to_date(a,'YYYYMMDD')-rownum order by a)rn,
dense_rank()over(order by to_date(a,'YYYYMMDD')-rownum)dk
from (
select * from test_p order by a) )
group by dk,trunc((rn-1)/2))
select * from test_p
A B
20090901 10
20090902 20
20090903 7
20090904 5
20090906 5
20090908 5
A B
20090901+20090902 30
20090903+20090904 12
20090906+20090907 5
20090908+20090909 5
select case when length(a)=8 then a||'+'||to_char(to_date(a,'yyyymmdd')+1,'yyyymmdd')
else replace(a,',','+') end a,b from(
select wm_concat(a)a,sum(b)b from(
select a,b,
row_number()over(partition by to_date(a,'YYYYMMDD')-rownum order by a)rn,
dense_rank()over(order by to_date(a,'YYYYMMDD')-rownum)dk
from (
select * from test_p order by a) )
group by dk,trunc((rn-1)/2))
A B
20090901+20090902 30
20090903+20090904 12
20090906+20090907 5
20090908+20090909 5