动态merge into

 

CREATE OR REPLACE PROCEDURE PROC_USSDUSER
AS
  

    query_str      varchar2(4000);

   stime           VARCHAR2(20);
   yestime           VARCHAR2(20);
   rq             VARCHAR2(20);
   area           VARCHAR2(4);
   area1           VARCHAR2(4);
   monthday          INTEGER;

BEGIN

     stime:=trim(to_char(add_months(sysdate,-1),'yyyy-mm'));
     yestime:=trim(to_char(add_months(sysdate,-2),'yyyy-mm'));
     rq:=stime||'-01';
     area:='m'||to_number(to_char(add_months(sysdate,-1),'mm'));
     area1:='m'||to_number(to_char(add_months(sysdate,-2),'mm'));
     monthday := trunc(sysdate,'mm')-add_months(trunc(sysdate,'mm'),-1);

             --会话次数
              query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,sum(SessionCount) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.SessionCount=(tmpdata.counter)
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,0,0,decode(tmpdata.counter,null,0,tmpdata.counter),0,0,0)';
              EXECUTE IMMEDIATE query_str ;
           ---交互次数
                query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT, sum(INTERACTCOUNT) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.INTERCOUNT=(tmpdata.counter)
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,0,0,0,decode(tmpdata.counter,null,0,tmpdata.counter),0,0)';
              EXECUTE IMMEDIATE query_str ;
           ---时长
               query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,sum(TimeSpan) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.TimeSpan=(tmpdata.counter)
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,0,decode(tmpdata.counter,null,0,tmpdata.counter),0,0,0,0)';
           EXECUTE IMMEDIATE query_str ;
          --每天使用用户数
                   query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,count(msisdn) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.dayuser=trunc(tmpdata.counter/'||monthday||')
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,0,0,0,0,trunc(tmpdata.counter/'||monthday||'),0)';
             EXECUTE IMMEDIATE query_str ;
           --新用户数
                query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,count(msisdn) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          and NewOrOld=1
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.newuser=tmpdata.counter
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,tmpdata.counter,0,0,0,0,0,0,0)';
           EXECUTE IMMEDIATE query_str ;
             --月不重复用户数
                  query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,count(distinct msisdn) counter
                          FROM    UssdDayPhoneTable_Partition partition ('||area||') T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''')
                    when matched then update set d.distinctuser=tmpdata.counter
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,tmpdata.counter,0,0,0,0,0,0)';
          EXECUTE IMMEDIATE query_str ;
            --上月留存用户
               query_str:= 'merge into ussd_month_user d
                    using (SELECT connectchannel conn,DISTRICT,count(distinct msisdn) counter FROM (select connectchannel,DISTRICT,msisdn from UssdDayPhoneTable_Partition partition ('||area||')
                          WHERE TO_CHAR(STAMP,''YYYY-MM'')='''||stime||''' intersect select connectchannel,DISTRICT,msisdn from UssdDayPhoneTable_Partition partition ('||area1||')
                          where to_char(stamp,''yyyy-mm'')='''||yestime||''') group by connectchannel,DISTRICT) tmpdata on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''' )
                    when matched then update set d.LastMonthUser=tmpdata.counter when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,tmpdata.counter,0,0,0,0,0)';
           
             EXECUTE IMMEDIATE query_str ;
            ----总用户数ALLUSER
             query_str:= 'merge into ussd_month_user d
                    using (
                         SELECT t.connectchannel conn,t.DISTRICT,COUNT(distinct msisdn) counter
                          FROM    UssdDayPhoneTable_Partition T
                          WHERE TO_CHAR(T.STAMP,''YYYY-MM'')<='''||stime||'''
                          group by t.connectchannel,t.DISTRICT
                    ) tmpdata
                     on (d.DISTRICT=tmpdata.DISTRICT and d.connectchannel=tmpdata.conn and to_char(d.stamp,''yyyy-mm-dd'')='''||rq||''' )
                    when matched then update set d.ALLUSER=(tmpdata.counter)
                    when not matched then insert values(tmpdata.DISTRICT,to_date('''||rq||''',''yyyy-mm-dd''),tmpdata.conn,0,0,0,0,0,0,0,tmpdata.Counter)';
             EXECUTE IMMEDIATE query_str ;
       
          

END;