CREATE OR REPLACE PROCEDURE "PROC_STAT_MONTHUSERCOUNT"
as
begin
delete from tbl_stat_month_usercount where stuc_datatime=trunc(sysdate-1,'MM');
delete from tbl_stat_daily_usercount where dauc_datatime=trunc(sysdate-1);
--各业务用户
[@more@]insert all
into tbl_stat_month_usercount values (trunc(sysdate-1,'MM'),usse_busiid_fk,总用户,当月新增用户数,付费总用户,当月新增付费用户,卸载总用户,当月新增卸载用户,总沉默用户,0,Vip总用户,当月新增Vip用户,0,0,0,0,0,0,0,0,0,0)
into tbl_stat_daily_usercount values (trunc(sysdate-1),usse_busiid_fk,总用户,前一天新增用户数,付费总用户,0,前一天新增付费用户,卸载总用户,前一天新增卸载用户,总沉默用户,0,Vip总用户,前一天新增Vip用户,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
select usse_busiid_fk,count(*) 总用户,count( case when us.usse_activatedate>=trunc(sysdate-1,'MM') then 1 end ) 当月新增用户数,count( case when us.usse_activatedate>=trunc(sysdate-1) then 1 end ) 前一天新增用户数, count(fee.smre_uid_fk) 付费总用户,count( case when fee.smre_date>=trunc(sysdate-1,'MM') then fee.smre_uid_fk end ) 当月新增付费用户,count( case when fee.smre_date>=trunc(sysdate-1) then fee.smre_uid_fk end ) 前一天新增付费用户, count(dect.deac_uid_fk) 卸载总用户,count( case when dect.deac_date>=trunc(sysdate-1,'MM') then dect.deac_uid_fk end ) 当月新增卸载用户,count( case when dect.deac_date>=trunc(sysdate-1) then dect.deac_uid_fk end ) 前一天新增卸载用户, 0 总沉默用户,0 当月新增沉默用户,0 前一天新增沉默用户, count(vip.vip_uid) Vip总用户,count( case when us.createdate>=trunc(sysdate-1,'MM') then vip.vip_uid end ) 当月新增Vip用户,count( case when us.usse_activatedate>=trunc(sysdate-1) then vip.vip_uid_fk end ) 前一天新增Vip用户 ,0,0,0,0,0,0
from
...
commit;
execute immediate 'truncate table tbl_temp_dailyusercount';
insert into tbl_temp_dailyusercount
select trunc(sysdate-1) stuc_datatime, bi.busi_id stuc_bussiid_fk ,q.totalcarduser,q.newcarduser,q.newdailycarduser, --c.totalsilenceuser,c.newsilenceuser,newdailysilenceuser, wapm.totalwappaymonthuser,wapm.newwappaymonthuser,wapm.newdailywappaymonthuser, b.totalpaymonthuser,b.newpaymonthuser,b.newdailypaymonthuser, a.totalsmsrechargeuser,a.newsmsrechargeuser,a.newdailysmsrechargeuser, wa.totalwaprechargeuser,wa.newwaprechargeuser,wa.newdailywaprechargeuser from
...
commit;
merge into tbl_stat_daily_usercount dauc
using (select trunc(sysdate-1) datatime,us.usse_busiid_fk,count( fee.smre_uid_fk ) 新增且付费,
count(q.qcpm_his_uid_fk) 新增且付费点卡,count(sm.smre_uid_fk) 新增且付费充值,count(smch.smpc_uid_fk) 新增且付费包月 ,count(wapmh.wamo_his_uid_fk) 新增且Wap包月,count(wa.ware_uid_fk) 新增且Wap充值
...
commit;
update tbl_stat_daily_usercount dauc set (dauc.dauc_feedailynewusercount,dauc.dauc_carddailynewusercount,
dauc.dauc_smsrechdailynewusercount,dauc.dauc_smspmdailynewusercount,dauc_waprechdailynewusercount,dauc_wappmdailynewusercount) =
(select count( fee.smre_uid_fk ) 新增且付费, count(q.qcpm_his_uid_fk) 新增且付费点卡,count(sm.smre_uid_fk) 新增且付费充值,count(smch.smpc_uid_fk) 新增且付费包月 ,count(wapmh.wamo_his_uid_fk) 新增且Wap包月,count(wa.ware_uid_fk) 新增且Wap充值