3,487
社区成员




select date_ref, curr_intrusion_score, count(*)
from (select utch.mcv_no,
utch.date_ref,
sum(oiw.intrusion_score) as curr_intrusion_score
from (select a.mcv_no, a.channel, a.target_method, date_ref
from campaign.spk2226_20150619 a,
(select date_ref
from (select rownum as row_num,
to_date('2015-05-01','yyyy-mm-dd') + rownum - 1 as date_ref
from dual
connect by rownum <= to_date('2015-05-30','yyyy-mm-dd')
- to_date('2015-05-01','yyyy-mm-dd') + 1
)
where mod(row_num, 5) = 0
) b
where a.contact_date >= b.date_ref - 60
and a.contact_date <= b.date_ref
) utch
inner join campaign.opt_intrusion_weight_p2 oiw
on utch.channel = oiw.channel
and utch.target_method = oiw.target_method
group by utch.mcv_no, utch.date_ref
)
group by date_ref, curr_intrusion_score
order by date_ref, curr_intrusion_score