造周数据存储过程

由于工作需要要求把天的数据转成周的，花了一点时间写了一个。记录如下：

create or replace procedure g_yearweek is
i number :=1;
CURSOR c_chn IS
select distinct(n_chn_id) from auditplatform.sum_mchn_mtgt_day_n_fina;

begin
 
for chn_rec in c_chn
  loop
  dbms_output.put_line(chn_rec.n_chn_id);
 for i in 158..321
  loop
      
  insert into sum_mchn_schn_yearweek (
n_week_id,n_chn_id,
n_pv,n_pv_user,n_sch)  
select i,chn_rec.n_chn_id,trunc(avg(d_n_pv)),trunc(avg(d_n_pv_user)),trunc(avg(d_n_sch))
  from auditplatform.sum_mchn_mtgt_day_n_fina t, auditplatform.dim_sys_yearweek w
 where to_date(vc_stat_date, 'yyyymmdd') <= w.dt_date_end
   and to_date(vc_stat_date, 'yyyymmdd') >= w.dt_date_beg
   and w.n_week_id = i and t.n_chn_id = chn_rec.n_chn_id;

end loop;

end loop;
commit;

end g_yearweek;

执行：

SQL> set serveroutput on;
SQL> exec g_yearweek;