用model写的一个统计

with t as(SELECT to_date('20160101','yyyy-mm-dd') d_day,1id_no,3trans_time FROM dual UNION ALL
SELECT to_date('20160102','yyyy-mm-dd'),1,2 FROM dual UNION ALL
SELECT to_date('20160102','yyyy-mm-dd'),1,3 FROM dual UNION ALL
SELECT to_date('20160103','yyyy-mm-dd'),1,1 FROM dual UNION ALL
SELECT to_date('20160105','yyyy-mm-dd'),1,4 FROM dual UNION ALL
SELECT to_date('20160106','yyyy-mm-dd'),1,7 FROM dual UNION ALL
SELECT to_date('20160109','yyyy-mm-dd'),1,2 FROM dual UNION ALL
SELECT to_date('20160115','yyyy-mm-dd'),1,3 FROM dual
)

select id_no,ss,min(d_day)||'-'||max(d_day) dday,sum(trans_time) stt from
(select * from(select d_day,id_no,trans_time,row_number()over(partition by id_no order by d_day) n from t)
model
partition by (id_no)
dimension by(n)
measures(d_day,trans_time,to_date('11110101','yyyymmdd') ss)
rules
(ss[1]=d_day[1],
ss[n>1]=case when d_day[cv()]-ss[cv()-1]<4 then ss[cv()-1] else d_day[cv()] end))
group by id_no,ss
order by id_no,ss

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30363279/viewspace-2128115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30363279/viewspace-2128115/