oracle存储过程实例
–拿到表名去ALL_SOURCE查看在哪个PKD包里面
select from sjjs_dq.r_nbst_gift_mstc2;
SELECT
FROM ALL_SOURCE t
WHERE 1=1–T.OWNER = ‘all objects’
— AND t.TYPE = ‘PACKAGE bodies’
AND t.TEXT LIKE ‘%sjjs_dq.r_nbst_gift_mstc2%’;
–以下是存储实例
create or replace package body sjjs_dq.pkd_202206_proc as
/
create by mzy
date : 20220606
desc : 202206月份数据集市日报开发
/
–202206月份数据集市日报开发
procedure p_202206_proc(v_date in varchar2) as
/
create by mzy
date : 20220606
desc : 202206月份数据集市日报开发
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
begin
dbms_output.put_line(v_date);
–看视频领红包报表
p_video_gift_dstc(v_date);
–烟感卫士数据报表
p_smoke_guard_dlist(v_date);
–手厅首页2楼运营位报表
p_stpg_operate_rpt(v_date);
–套餐升级享话费活动报表
–p_pkgup_scharge_rpt(v_date);
–电子渠道宽带新装连带看家下单统计报表
p_kdxz_kjxd_dstc(v_date);
–宁波手厅福利活动-
p_nbst_gift_mstc(v_date);
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_202206_proc’,err_code,err_msge,err_line);
end p_202206_proc;
–看视频领红包报表
procedure p_video_gift_dstc(v_date in varchar2) as
/
create by mzy
date : 20220606
desc : 看视频领红包报表
need by 周雨虹
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
begin
dbms_output.put_line(v_date);
–del
delete from sjjs_dq.rp1_video_gift_dstc where cal_date = v_date;
commit;
–insert -参与活动次数 参与活动人数 参与观看次数 参与观看人数
— 参与点赞次数 参与点赞人数 成功邀请次数 成功邀请人数
insert into sjjs_dq.rp1_video_gift_dstc value(
select /+parallel(4)/ v_date as cal_date,
count(1) as wt_pv1,
count(distinct t.mobile) as wt_uv1,
count(case when t.businessname = ‘观看视频’ then 1 else null end) as wt_pv2,
count(distinct case when t.businessname = ‘观看视频’ then t.mobile else null end) as wt_uv2,
count(case when t.businessname = ‘点赞视频’ then 1 else null end) as wt_pv3,
count(distinct case when t.businessname = ‘点赞视频’ then t.mobile else null end) as wt_uv3,
count(case when t.businessname = ‘分享视频’ then 1 else null end) as wt_pv4,
count(distinct case when t.businessname = ‘分享视频’ then t.mobile else null end) as wt_uv4
from sjjs_dq.w_business_record_task_ac5442 t
where replace(substr(t.createtime,1,10),’-‘,”) = v_date
);
commit;
–del
delete from sjjs_dq.rp2_video_gift_dstc where cal_date = v_date;
commit;
–insert –参与分享次数 参与分享人数
execute immediate ‘insert into sjjs_dq.rp2_video_gift_dstc value(
select /+parallel(4)/ ‘||v_date||’ as cal_date,
t1.wt_event,t1.wt_mobile,t1.wt_date
from sjjs_dq.dzqd_usertrace_m’||substr(v_date,1,6)||’ t1
where t1.wt_date >= to_date(‘||v_date||’,”yyyymmdd”)
and t1.wt_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
and t1.wt_event = ”202239_ZJXCXKSPLHB_SY_QFX”
)’;
commit;
–del
delete from sjjs_dq.rp3_video_gift_dstc where cal_date = v_date;
commit;
–insert 月累计，总累计
insert into sjjs_dq.rp3_video_gift_dstc value(
select /+parallel(4)/ v_date as cal_date,
count(distinct case when ta.create_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
then ta.mobile else null end) as wt_uv1,
count(distinct case when ta.create_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and ta.businessname = ‘观看视频’
then ta.mobile else null end) as wt_uv2,
count(distinct case when ta.create_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and ta.businessname = ‘点赞视频’
then ta.mobile else null end) as wt_uv3,
count(distinct case when ta.create_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and ta.businessname = ‘分享视频’
then ta.mobile else null end) as wt_uv4,
count(distinct ta.mobile) as wt_uv5,
count(distinct case when ta.businessname = ‘观看视频’ then ta.mobile else null end) as wt_uv6,
count(distinct case when ta.businessname = ‘点赞视频’ then ta.mobile else null end) as wt_uv7,
count(distinct case when ta.businessname = ‘分享视频’ then ta.mobile else null end) as wt_uv8
from (
select to_date(t.createtime,’yyyy-mm-dd hh24:mi:ss’) as create_time,
t.businessname,t.mobile
from sjjs_dq.w_business_record_task_ac5442 t
)ta
where ta.create_time < to_date(v_date,’yyyymmdd’) + 1
);
commit;
–del
delete from sjjs_dq.rp4_video_gift_dstc where cal_date = v_date;
commit;
–insert -参与分享人数 日统计，月累计，总累计
insert into sjjs_dq.rp4_video_gift_dstc value(
select v_date as cal_date,
count(case when t.wt_date >= to_date(v_date,’yyyymmdd’) then 1 else null end) as wt_pv1,
count(distinct case when t.wt_date >= to_date(v_date,’yyyymmdd’)
then t.wt_mobile else null end) as wt_uv1,
count(distinct case when t.wt_date >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
then t.wt_mobile else null end) as wt_uv2,
count(distinct t.wt_mobile) as wt_uv3
from sjjs_dq.rp2_video_gift_dstc t
where t.wt_date < to_date(v_date,’yyyymmdd’) + 1
);
commit;
–del
delete from sjjs_dq.rp5_video_gift_dstc where cal_date = v_date;
commit;
–nsert
insert into sjjs_dq.rp5_video_gift_dstc value(
select v_date as cal_date,
count(case when t.businessname = ‘1元消费抵扣券’ then 1 else null end) as wt_num1,
count(case when t.businessname = ‘5元消费抵扣券’ then 1 else null end) as wt_num2,
count(case when t.businessname = ’10元消费抵扣券’ then 1 else null end) as wt_num3,
count(case when t.businessname = ‘500MB流量券’ then 1 else null end) as wt_num4,
count(case when t.businessname = ‘1GB流量券’ then 1 else null end) as wt_num5,
count(case when t.businessname = ‘3GB流量券’ then 1 else null end) as wt_num6
from sjjs_dq.w_business_record_ac5442 t
where replace(substr(createtime,1,10),’-‘,”) = v_date
);
commit;
–del
delete from sjjs_dq.r_video_gift_dstc where cal_date = v_date;
commit;
–insert
insert into sjjs_dq.r_video_gift_dstc value(
select t1.cal_date,t1.wt_pv1,t1.wt_uv1,
t1.wt_pv2,t1.wt_uv2,
t1.wt_pv3,t1.wt_uv3,
nvl(t3.wt_pv1,0) as wt_pv4,
nvl(t3.wt_uv1,0) as wt_uv4,
t1.wt_pv4 as wt_pv5,
t1.wt_uv4 as wt_uv5,
nvl(t2.wt_uv1,0) as wt_muv1,
nvl(t2.wt_uv2,0) as wt_muv2,
nvl(t2.wt_uv3,0) as wt_muv3,
nvl(t3.wt_uv2,0) as wt_muv4,
nvl(t2.wt_uv4,0) as wt_muv5,
nvl(t2.wt_uv5,0) as wt_suv1,
nvl(t2.wt_uv6,0) as wt_suv2,
nvl(t2.wt_uv7,0) as wt_suv3,
nvl(t3.wt_uv3,0) as wt_suv4,
nvl(t2.wt_uv8,0) as wt_suv5,
nvl(t4.wt_num1,0) as wt_num1,
nvl(t4.wt_num2,0) as wt_num2,
nvl(t4.wt_num3,0) as wt_num3,
nvl(t4.wt_num4,0) as wt_num4,
nvl(t4.wt_num5,0) as wt_num5,
nvl(t4.wt_num6,0) as wt_num6
from sjjs_dq.rp1_video_gift_dstc t1
left join sjjs_dq.rp3_video_gift_dstc t2 on t1.cal_date = t2.cal_date
left join sjjs_dq.rp4_video_gift_dstc t3 on t1.cal_date = t3.cal_date
left join sjjs_dq.rp5_video_gift_dstc t4 on t1.cal_date = t4.cal_date
where t1.cal_date = v_date
);
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_video_gift_dstc’,err_code,err_msge,err_line);
end p_video_gift_dstc;
–烟感卫士数据报表
procedure p_smoke_guard_dlist(v_date in varchar2) as
/
create by mzy
date : 20220614
desc : 烟感卫士数据报表
need by 倪侃
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
begin
–dbms
dbms_output.put_line(v_date);
–del
execute immediate ‘truncate table sjjs_dq.rp1_smoke_guard_dlist’;
commit;
–insert
insert into sjjs_dq.rp1_smoke_guard_dlist value(
select t.uuid,t.acti_conf_name, t.player,f.page_name, u.value,u.play_date,t.id,f.acti_conf_id,f.show_index
from sjjs_dq.t_acti_handle_user t,
sjjs_dq.T_ACTI_USER_FIELD u,
sjjs_dq.T_ACTI_CONF_INTERACT_FIELD f
where 1=1
–and t.play_date >= to_date(‘20220101’, ‘yyyyMMdd’)–改时间
and t.play_date <= to_date(v_date, ‘yyyyMMdd’)–改时间
and t.acti_conf_id = ‘327161e4f1384b3b9ebe7c1b319a9347’–改活动id
and t.type = ‘1’—1办理
and t.status = ‘0’
and t.id =u.handle_id
and f.status=’1′
and u.interact_field_id=f.page_code
and f.acti_conf_id=’327161e4f1384b3b9ebe7c1b319a9347′
);
commit;
–del
execute immediate ‘truncate table sjjs_dq.r_smoke_guard_dlist’;
commit;
–inset
insert into sjjs_dq.r_smoke_guard_dlist value(
select tba.uuid,tba.id,tba.acti_conf_name,
tba.player,tba.play_date,
tba.value as tb_value1, –第1个登记项亲友号码,
tb2.value as tb_value2, –第2个登记项区县,
tb3.value as tb_value3, –第3个登记项联系姓名,
tb4.value as tb_value4, –第4个登记项具体地址,
tb5.value as tb_value5, –第5个登记项地市,
tb6.value as tb_value6, –第6个登记项区县,
tb7.value as tb_value7 –第7个登记项详细地址
from sjjs_dq.rp1_smoke_guard_dlist tba,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘2’) tb2,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘3’) tb3,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘4’) tb4,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘5’) tb5,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘6’) tb6,
(select from sjjs_dq.rp1_smoke_guard_dlist where show_index = ‘7’) tb7
where tba.show_index = ‘1’
and tba.id = tb2.id(+)
and tba.id = tb3.id(+)
and tba.id = tb4.id(+)
and tba.id = tb5.id(+)
and tba.id = tb6.id(+)
and tba.id = tb7.id(+)
);
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_smoke_guard_dlist’,err_code,err_msge,err_line);
end p_smoke_guard_dlist;
–手厅首页2楼运营位报表
procedure p_stpg_operate_rpt(v_date in varchar2) as
/
create by mzy
date : 20220620
desc : 手厅首页2楼运营位报表
need by 胡谷君
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
begin
–dbms
dbms_output.put_line(v_date);
–del
delete from sjjs_dq.r_stpg_operate_drpt where cal_date = v_date;
commit;
–iinsert
execute immediate ‘insert into sjjs_dq.r_stpg_operate_drpt value(
select /+parallel(4)/ ‘||v_date||’ as cal_date,t1.page_name,
t1.locate_name,count(1) as wt_pv,count(distinct t.wt_mobile) as wt_uv
from sjjs_dq.dzqd_usertrace_m’||substr(v_date,1,6)||’ t
inner join sjjs_dq.wb1_stpg_operate_rpt t1 on t.wt_pn = t1.wt_pn and t.wt_en = t1.wt_en
where t.wt_date >= to_date(‘||v_date||’,”yyyymmdd”)
and t.wt_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
–and wt_pn = ”20220525_SYEL”
group by t1.page_name,t1.locate_name
)’;
commit;
–del
delete from sjjs_dq.r_stpg_operate_mrpt where cal_date = substr(v_date,1,6);
commit;
–iinsert
execute immediate ‘insert into sjjs_dq.r_stpg_operate_mrpt value(
select /+parallel(4)/ ‘||substr(v_date,1,6)||’ as cal_date,t1.page_name,
t1.locate_name,count(1) as wt_pv,count(distinct t.wt_mobile) as wt_uv
from sjjs_dq.dzqd_usertrace_m’||substr(v_date,1,6)||’ t
inner join sjjs_dq.wb1_stpg_operate_rpt t1 on t.wt_pn = t1.wt_pn and t.wt_en = t1.wt_en
where t.wt_date >= trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”)
and t.wt_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
–and wt_pn = ”20220525_SYEL”
group by t1.page_name,t1.locate_name
)’;
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_stpg_operate_rpt’,err_code,err_msge,err_line);
end p_stpg_operate_rpt;
–套餐升级享话费活动报表
procedure p_pkgup_scharge_rpt(v_date in varchar2) as
/
create by mzy
date : 20220621
desc : 套餐升级享话费活动报表
need by 刘建华
/
err_code int;
err_line varchar2(2000);
err_msge varchar2(2000);
begin
–dbms
dbms_output.put_line(v_date);
–del
delete from sjjs_dq.rp1_pkgup_scharge_rpt where cal_date = v_date;
commit;
–insert
execute immediate ‘insert into sjjs_dq.rp1_pkgup_scharge_rpt value(
select /+parallel(4)/ ‘||v_date||’ as cal_date,
nvl(t2.city_name,”未知”) as city_name,
count(1) as wt_pv,count(distinct t.wt_mobile) as wt_uv,
”首页访问” as wt_type
from sjjs_dq.dzqd_usertrace_m’||substr(v_date,1,6)||’ t
left join sjjs_dq.t_phone_region_water t1 on substr(t.wt_mobile,1,7) = t1.hlr_code
left join sjjs_dq.t_city_name t2 on t1.region_code = t2.gegion_code
where t.wt_date >= to_date(‘||v_date||’,”yyyymmdd”)
and t.wt_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
and instr(t.wt_es,”wap.zj.10086.cn/ai/busih5/index.html”) > 0
and wt_pn = ”ACC202206140001”
group by t2.city_name
)’;
commit;
–del
delete from sjjs_dq.rp2_pkgup_scharge_rpt where cal_date = v_date;
commit;
–insert
execute immediate ‘insert into sjjs_dq.rp2_pkgup_scharge_rpt value(
select /+parallel(4)/ ‘||v_date||’ as cal_date,
nvl(t2.city_name,”未知”) as city_name,
count(case when t.wt_en = ”EVC202206140001” then 1 else null end) as wt_pv1,
count(distinct case when t.wt_en = ”EVC202206140001” then t.wt_mobile else null end) as wt_uv1,
count(case when t.wt_en = ”EVC202206140007” then 1 else null end) as wt_pv2,
count(distinct case when t.wt_en = ”EVC202206140007” then t.wt_mobile else null end) as wt_uv2,
count(case when t.wt_en = ”EVC202206140008” then 1 else null end) as wt_pv3,
count(distinct case when t.wt_en = ”EVC202206140008” then t.wt_mobile else null end) as wt_uv3,
count(case when t.wt_en = ”EVC202206140003” then 1 else null end) as wt_pv4,
count(distinct case when t.wt_en = ”EVC202206140003” then t.wt_mobile else null end) as wt_uv4,
count(case when t.wt_en = ”EVC202206140004” then 1 else null end) as wt_pv5,
count(distinct case when t.wt_en = ”EVC202206140004” then t.wt_mobile else null end) as wt_uv5,
count(case when t.wt_en = ”EVC202206140005” then 1 else null end) as wt_pv6,
count(distinct case when t.wt_en = ”EVC202206140005” then t.wt_mobile else null end) as wt_uv6,
count(case when t.wt_en = ”EVC202206140002” then 1 else null end) as wt_pv7,
count(distinct case when t.wt_en = ”EVC202206140002” then t.wt_mobile else null end) as wt_uv7,
count(case when t.wt_en = ”EVC202206140006” then 1 else null end) as wt_pv8,
count(distinct case when t.wt_en = ”EVC202206140006” then t.wt_mobile else null end) as wt_uv8,
count(case when t.wt_en = ”EVC202206140009” then 1 else null end) as wt_pv9,
count(distinct case when t.wt_en = ”EVC202206140009” then t.wt_mobile else null end) as wt_uv9
from sjjs_dq.dzqd_usertrace_m’||substr(v_date,1,6)||’ t
left join sjjs_dq.t_phone_region_water t1 on substr(t.wt_mobile,1,7) = t1.hlr_code
left join sjjs_dq.t_city_name t2 on t1.region_code = t2.gegion_code
where t.wt_date >= to_date(‘||v_date||’,”yyyymmdd”)
and t.wt_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
and wt_pn = ”ACC202206140001”
group by t2.city_name
)’;
commit;
–del
delete from sjjs_dq.r_pkgup_scharge_rpt1 where cal_date = v_date;
commit;
–insert
insert into sjjs_dq.r_pkgup_scharge_rpt1 value(
select v_date as cal_date,
t1.city_name,
nvl(t2.wt_uv,0) as wt_uv1,nvl(t2.wt_pv,0) as wt_pv1,
nvl(t3.wt_uv1,0) as wt_uv2,nvl(t3.wt_pv1,0) as wt_pv2,
nvl(t3.wt_uv2,0) as wt_uv3,nvl(t3.wt_pv2,0) as wt_pv3,
nvl(t3.wt_uv3,0) as wt_uv4,nvl(t3.wt_pv3,0) as wt_pv4,
nvl(t3.wt_uv4,0) as wt_uv5,nvl(t3.wt_pv4,0) as wt_pv5,
nvl(t3.wt_uv5,0) as wt_uv6,nvl(t3.wt_pv5,0) as wt_pv6,
nvl(t3.wt_uv6,0) as wt_uv7,nvl(t3.wt_pv6,0) as wt_pv7,
nvl(t3.wt_uv7,0) as wt_uv8,nvl(t3.wt_pv7,0) as wt_pv8,
nvl(t3.wt_uv8,0) as wt_uv9,nvl(t3.wt_pv8,0) as wt_pv9,
nvl(t3.wt_uv9,0) as wt_uv10,nvl(t3.wt_pv9,0) as wt_pv10
from sjjs_dq.t_city_name t1
left join sjjs_dq.rp1_pkgup_scharge_rpt t2 on t1.city_name = t2.city_name and t2.cal_date = v_date
left join sjjs_dq.rp2_pkgup_scharge_rpt t3 on t1.city_name = t3.city_name and t3.cal_date = v_date
);
commit;
–insert -全省
insert into sjjs_dq.r_pkgup_scharge_rpt1 value(
select v_date as cal_date,’全省’ as city_name,
sum(wt_uv1) as wt_uv1,sum(wt_pv1) as wt_pv1,
sum(wt_uv2) as wt_uv2,sum(wt_pv2) as wt_pv2,
sum(wt_uv3) as wt_uv3,sum(wt_pv3) as wt_pv3,
sum(wt_uv4) as wt_uv4,sum(wt_pv4) as wt_pv4,
sum(wt_uv5) as wt_uv5,sum(wt_pv5) as wt_pv5,
sum(wt_uv6) as wt_uv6,sum(wt_pv6) as wt_pv6,
sum(wt_uv7) as wt_uv7,sum(wt_pv7) as wt_pv7,
sum(wt_uv8) as wt_uv8,sum(wt_pv8) as wt_pv8,
sum(wt_uv9) as wt_uv9,sum(wt_pv9) as wt_pv9,
sum(wt_uv10) as wt_uv10,sum(wt_pv10) as wt_pv10
from sjjs_dq.r_pkgup_scharge_rpt1
where cal_date = v_date
);
commit;
–del
delete from sjjs_dq.r_pkgup_scharge_rpt2
where create_time >= to_date(v_date,’yyyymmdd’)
and create_time < to_date(v_date,’yyyymmdd’) + 1;
commit;
–insert
execute immediate ‘
insert into sjjs_dq.r_pkgup_scharge_rpt2 value(
select /+parallel(4)/ distinct c.crm_complet_date,
nvl(t2.city_name,”未知”) as city_name,
decode(p.is_paperless,”1”,”模组套餐”,
”2”,”老旧套餐”,
”3”,”花卡套餐”,
”4”,”新浙江人套餐”) as is_paperless,
c.transact_billid,
p.req_info4,
p.req_info3,
(select offer_name
from sjjs_dq.td_offerinfo‘||substr(v_date,1,6)||’ o1
where main_offer = ”true”
and o.cust_order_id = o1.cust_order_id) as pkg_name,
(select offer_name
from sjjs_dq.td_offerinfo‘||substr(v_date,1,6)||’ o2
where (offer_name like ”%流量%” or offer_name like ”%上网%”)
and o.cust_order_id = o2.cust_order_id) as flow_name,
(select offer_name
from sjjs_dq.td_offerinfo‘||substr(v_date,1,6)||’ o3
where offer_name like ”%基本模组%”
and o.cust_order_id = o3.cust_order_id) as voice_name,
c.create_time,
(case c.order_status
when ”5” then ”成功”
when ”6” then ”失败”
else null end) as busi_status,
b.handle_info
from sjjs_dq.td_offerinfo‘||substr(v_date,1,6)||’ o
right join sjjs_dq.td_customerorder‘||substr(v_date,1,6)||’ c on c.id = o.cust_order_id
and c.busi_type = ”19003001”
right join sjjs_dq.td_packageinfo‘||substr(v_date,1,6)||’ p on o.cust_order_id = p.cust_order_id
right join sjjs_dq.tdbusiness‘||substr(v_date,1,4)||’ b on o.cust_order_id = b.cust_order_id
left join sjjs_dq.t_city_name t2 on c.city = t2.gegion_code
where o.offer_name is not null
and b.create_time >= to_date(‘||v_date||’,”yyyymmdd”)
and b.create_time < to_date(‘||v_date||’,”yyyymmdd”) + 1
)’;
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_pkgup_scharge_rpt’,err_code,err_msge,err_line);
end p_pkgup_scharge_rpt;
–电子渠道宽带新装连带看家下单统计报表
procedure p_kdxz_kjxd_dstc(v_date in varchar2) as
/
create by mzy
desc : 电子渠道宽带新装连带看家下单统计报表
date : 20220706
need by 王腾飞
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
begin
–dbms
dbms_output.put_line(v_date);
–del
delete from sjjs_dq.r_kdxz_kjxd_dstc
where create_time >= trunc(add_months(to_date(v_date,’yyyymmdd’),-1),’mm’)
and create_time < to_date(v_date,’yyyymmdd’) + 1;
commit;
–insert
insert into sjjs_dq.r_kdxz_kjxd_dstc value(
select c.id,
c.order_billid,
c.transact_billid,
d.name as touch_name,
c.city,
c.market_name,
decode(c.deal_type, ‘1’, ‘直接受理’, ‘2’, ‘预受理’, ‘3’, ‘直接受理失败转预受理’, ‘4’, ‘超时转预受理’, ‘other’) DEAL_TYPE_NAME, decode(c.order_status, ‘1’, ‘下单成功’, ‘2’, ‘下单失败’, ‘3’, ‘已取消’, ‘4’, ‘订单受理中’, ‘5’, ‘订单受理成功’, ‘6’, ‘订单受理失败’, ‘other’) ORDER_STATUS,
c.market_id,
c.grade_id,
c.grade_name,
c.create_time,
z.zhijia_market_name,
z.zhijia_market_code
from sjjs_dq.td_customer_order c
left join sjjs_dq.tf_sys_dict d on c.touch_channel_code = d.code and d.parent_code = ‘TouchChannel’,
sjjs_dq.tf_market_zhijia z
where c.grade_id=z.parent_grade_code
and z.type=’看家’
and c.create_time >= trunc(add_months(to_date(v_date,’yyyymmdd’),-1),’mm’)
and c.create_time < to_date(v_date,’yyyymmdd’) + 1
);
commit;
–del
delete from sjjs_dq.r_kdxz_zwxd_dstc
where create_time >= trunc(add_months(to_date(v_date,’yyyymmdd’),-1),’mm’)
and create_time < to_date(v_date,’yyyymmdd’) + 1;
commit;
–insert
insert into sjjs_dq.r_kdxz_zwxd_dstc value(
select c.id,
c.order_billid,
c.transact_billid,
–c.touch_channel_code,
t.name as channel_name,
d.city_name,
c.market_name,
decode(c.deal_type, ‘1’, ‘直接受理’, ‘2’, ‘预受理’, ‘3’, ‘直接受理失败转预受理’, ‘4’, ‘超时转预受理’, ‘other’) DEAL_TYPE_NAME, decode(c.order_status, ‘1’, ‘下单成功’, ‘2’, ‘下单失败’, ‘3’, ‘已取消’, ‘4’, ‘订单受理中’, ‘5’, ‘订单受理成功’, ‘6’, ‘订单受理失败’, ‘other’) ORDER_STATUS,
c.market_id,
c.grade_id,
c.grade_name,
c.create_time,
z.zhijia_market_name,
z.zhijia_market_code
from sjjs_dq.td_customer_order c
inner join sjjs_dq.tf_market_zhijia z on c.grade_id=z.parent_grade_code
left join sjjs_dq.tf_sys_dict t on c.TOUCH_CHANNEL_CODE = t.code and t.parent_code = ‘TouchChannel’
left join sjjs_dq.t_city_name d on c.CITY = d.gegion_code
where 1=1
and z.type=’组网’
and c.create_time >= trunc(add_months(to_date(v_date,’yyyymmdd’),-1),’mm’)
and c.create_time < to_date(v_date,’yyyymmdd’) + 1
);
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_kdxz_kjxd_dstc’,err_code,err_msge,err_line);
end p_kdxz_kjxd_dstc;
–宁波手厅福利活动-
procedure p_nbst_gift_mstc(v_date in varchar2) as
/
create by mzy
date : 20220706
desc : 宁波手厅福利活动
need by 梁洁
/
err_code int;
err_msge varchar2(2000);
err_line varchar2(2000);
tb_num1 int;
tb_num2 int;
begin
–dbms
dbms_output.put_line(v_date);
execute immediate ‘select count(1)
from sjjs_dq.r_nbst_gift_mstc1 partition for (trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”))’ into tb_num1;
–del
if (tb_num1 > 0) then
execute immediate ‘alter table sjjs_dq.r_nbst_gift_mstc1
truncate partition for (trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”))’;
commit;
end if;
–insert
insert into sjjs_dq.r_nbst_gift_mstc1 value(
select player as phone_no,’掌厅’ as play_channel,play_date
from sjjs_dq.t_acti_handle_user t
where t.acti_conf_id = ’54a13b3cd318417497d5b14ad3c1d289′
and t.play_date >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.play_date < to_date(v_date,’yyyymmdd’) + 1
and status = ‘0’
and type = ‘1’
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc2 where p_month = substr(v_date,1,6);
commit;
–insert
execute immediate ‘insert into sjjs_dq.r_nbst_gift_mstc2 value(
select substr(‘||v_date||’,1,6) as p_month,
ta.busi_num,tb.excg_num
from (
select count(1) as busi_num
from sjjs_dq.t_acti_handle_user t
where t.acti_conf_id = ”54a13b3cd318417497d5b14ad3c1d289”
and t.play_date >= trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”)
and t.play_date < to_date(‘||v_date||’,”yyyymmdd”) + 1
and status = ”0”
and type = ”1”
)ta
left join (
select count(1) as excg_num
from sjjs_dq.FLUX_BOOK_INOUTD‘||v_date||’
where p_day = ‘||v_date||’
and RES_TYPE = ”5”–流量券
AND BUSI_id = ”710000000003”
AND ACCT_ID <> 711100044125
AND STATE = ”U”
and INOUT_TYPE = ”O”
)tb on 1=1
)’;
commit;
execute immediate ‘select count(1)
from sjjs_dq.r_nbst_gift_mstc3 partition for (trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”))’ into tb_num2;
–del
if (tb_num2 > 0) then
execute immediate ‘alter table sjjs_dq.r_nbst_gift_mstc3
truncate partition for (trunc(to_date(‘||v_date||’,”yyyymmdd”),”mm”))’;
commit;
end if;
–insert
insert into sjjs_dq.r_nbst_gift_mstc3 value(
select player as phone_no,’掌厅’ as play_channel,play_date
from sjjs_dq.t_acti_handle_user t
where t.acti_conf_id = ‘b7ebac261ab6438aafaa1d55583e54fd’
and t.play_date >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.play_date < to_date(v_date,’yyyymmdd’) + 1
and status = ‘0’
and type = ‘1’
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc4 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc4 value(
select /+parallel(4)/ substr(v_date,1,6) as p_month,count(1) as suc_num
from sjjs_dq.t_acti_handle_user t
where t.acti_conf_id = ‘b7ebac261ab6438aafaa1d55583e54fd’
and t.status = ‘0’
and t.type = ‘1’
and t.play_date >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.play_date < to_date(v_date,’yyyymmdd’) + 1
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc5 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc5 value(
select /+parallel(4)/ ta.*,nvl(tb.prize_type,’0′) as prize_type
from (
select month as p_month,user_id,’掌厅’ as channel_name,
num,t.recharge_time,t.money/100 as recharge_money,
decode(t.prize_type,1,’5元消费抵扣权益’,
2,’10元消费抵扣权益’,
3,’5GB流量券’) as prize_name
from sjjs_tae.ACT_NINGB_RECHARGE_CHANCE_INFO t
where 1=1
and t.recharge_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.recharge_time < to_date(v_date,’yyyymmdd’) + 1
)ta
left join (
select userid as user_id,prizename as prize_name,
‘1’ as prize_type
from sjjs_dq.t_yx_lotterydetail_acdt t
where t.acid = ‘AC20220217160745′
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) < to_date(v_date,’yyyymmdd’) + 1
–and userid = ‘15067432935’
)tb on ta.user_id = tb.user_id and ta.prize_name = tb.prize_name
);
commit;
–del
delete from sjjs_dq.rp1_nbst_gift_mstc6 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.rp1_nbst_gift_mstc6 value(
select /+parallel(4)/ ta.p_month,ta.prize_name,count(1) as prize_num
from (
select month as p_month,user_id,
decode(t.prize_type,1,’5元消费抵扣权益’,
2,’10元消费抵扣权益’,
3,’5GB流量券’) as prize_name
from sjjs_tae.ACT_NINGB_RECHARGE_CHANCE_INFO t
where 1=1
and t.recharge_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.recharge_time < to_date(v_date,’yyyymmdd’) + 1
)ta
inner join (
select userid as user_id,prizename as prize_name
from sjjs_dq.t_yx_lotterydetail_acdt t
where t.acid = ‘AC20220217160745′
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) < to_date(v_date,’yyyymmdd’) + 1
–and userid = ‘15067432935’
)tb on ta.user_id = tb.user_id and ta.prize_name = tb.prize_name
group by ta.p_month,ta.prize_name
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc6 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc6 value(
select substr(v_date,1,6) as p_month,
prize_name,1200000 as total_ynum,
sum(prize_num) as prize_tnum1,
100000 as total_mnum,
sum(case when p_month < substr(v_date,1,6) then ta.prize_rem else 0 end) as prize_remt,
sum(case when p_month < substr(v_date,1,6) then ta.prize_rem else 0 end) + 100000 as prize_remta,
sum(case when p_month = substr(v_date,1,6) then ta.prize_num else 0 end) as prize_tnum2,
sum(case when p_month = substr(v_date,1,6) then ta.prize_rem else 0 end) as prize_remtb,
1200000 – sum(prize_num) as prize_tnum3
from (
select p_month,prize_name,prize_num,(100000-prize_num) as prize_rem
from sjjs_dq.rp1_nbst_gift_mstc6
where p_month <= substr(v_date,1,6)
)ta
group by prize_name
);
commit;
–del
delete from sjjs_dq.rp1_nbst_gift_mstc7 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.rp1_nbst_gift_mstc7 value(
select month as p_month,user_id,max(num) as prize_num,
sum(MONEY)/100 as charge_money
from sjjs_tae.ACT_NINGB_RECHARGE_CHANCE_INFO t
where 1=1
and t.recharge_time >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and t.recharge_time < to_date(v_date,’yyyymmdd’) + 1
group by month,user_id
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc7 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc7 value(
select ta.p_month,
decode(ta.prize_num,1,’5元消费抵扣权益’,
2,’10元消费抵扣权益’,
3,’5GB流量券’) as prize_name,
ta.prize_uv as prize_uv1,
ta.charge_money,
tb.prize_uv as prize_uv2,
tc.prize_uv as prize_uv3
from (
select p_month,prize_num,
count(1) as prize_uv,
sum(charge_money) as charge_money
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month = substr(v_date,1,6)
group by p_month,prize_num
)ta
left join (
select prize_num,count(1) as prize_uv
from (
select prize_num,user_id,count(1) as tnum
from (
select prize_num,user_id
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month >= to_char(add_months(to_date(v_date,’yyyymmdd’),-1),’yyyymm’)
and p_month <= substr(v_date,1,6)
)ua
group by prize_num,user_id
having count(1) = 2
)ub
group by prize_num
)tb on ta.prize_num = tb.prize_num
left join(
select prize_num,count(1) as prize_uv
from (
select prize_num,user_id,count(1) as tnum
from (
select prize_num,user_id
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month >= to_char(add_months(to_date(v_date,’yyyymmdd’),-2),’yyyymm’)
and p_month <= substr(v_date,1,6)
)ua
group by prize_num,user_id
having count(1) = 3
)ub
group by prize_num
)tc on ta.prize_num = tc.prize_num
);
commit;
–del
delete from sjjs_dq.rp1_nbst_gift_mstc8 where p_month = substr(v_date,1,6);
commit;
–iinsert -获取每月连续充值的用户
insert into sjjs_dq.rp1_nbst_gift_mstc8 value(
select substr(v_date,1,6) as p_month,user_id,prize_num,count(1) as tnum
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month >= to_char(add_months(to_date(v_date,’yyyymmdd’),-1),’yyyymm’)
and p_month <= substr(v_date,1,6)
group by user_id,prize_num
having count(1) = 2
union all
select substr(v_date,1,6) as p_month,user_id,prize_num,count(1) as tnum
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month >= to_char(add_months(to_date(v_date,’yyyymmdd’),-2),’yyyymm’)
and p_month <= substr(v_date,1,6)
group by user_id,prize_num
having count(1) = 3
);
–delete
delete from sjjs_dq.r_nbst_gift_mstc8 where p_month = substr(v_date,1,6);
commit;
–iinsert
insert into sjjs_dq.r_nbst_gift_mstc8 value(
select ta.p_month,
decode(ta.prize_num,1,’5元消费抵扣权益’,
2,’10元消费抵扣权益’,
3,’5GB流量券’) as prize_name,
ta.prize_uv as prize_uv1,
ta.charge_money,
nvl(tb.prize_uv,0) as prize_uv2,
nvl(tc.prize_uv,0) as prize_uv3
from (
select p_month,prize_num,
count(distinct USER_ID) as prize_uv,
sum(charge_money) as charge_money
from sjjs_dq.rp1_nbst_gift_mstc7
where p_month <= substr(v_date,1,6)
group by p_month,prize_num
)ta
left join (
select prize_num,count(1) as prize_uv
from (
select user_id,prize_num,max(tnum) as tnum
from sjjs_dq.rp1_nbst_gift_mstc8 t
where p_month <= substr(v_date,1,6)
group by user_id,prize_num
)ua
where tnum = 2
group by prize_num
)tb on ta.prize_num = tb.prize_num
left join(
select prize_num,count(1) as prize_uv
from (
select user_id,prize_num,max(tnum) as tnum
from sjjs_dq.rp1_nbst_gift_mstc8 t
where p_month <= substr(v_date,1,6)
group by user_id,prize_num
)ua
where tnum = 3
group by prize_num
)tc on ta.prize_num = tc.prize_num
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc9 where p_month = substr(v_date,1,6);
commit;
–inisert
insert into sjjs_dq.r_nbst_gift_mstc9 value(
select /+parallel(4)/ substr(v_date,1,6) as p_month,
userid,’掌厅’ as channel_name,
to_date(t.chancecreatetime,’yyyy-mm-dd hh24:mi:ss’) as create_time,
t.prizename
from sjjs_dq.t_yx_lotterydetail_acdt t
where t.acid = ‘AC20220314144508′
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) < to_date(v_date,’yyyymmdd’) + 1
);
commit;
–del
delete from sjjs_dq.rp1_nbst_gift_mstc10 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.rp1_nbst_gift_mstc10 value(
select /+parallel(4)/ substr(v_date,1,10) as p_month,
ta.prizename,ta.prize_mnum,ta.prize_no,
(tb.prize_mnum – ta.prize_mnum) as prize_rnum
from (
select t.prizename,count(1) as prize_mnum,
decode(prizename,’1GB流量券’,1,
‘2GB流量券+2元消费抵扣’,2,
‘3GB流量券+3元消费抵扣’,3,
‘5GB流量券+5元消费抵扣’,4,
’10GB流量券’,5) as prize_no
from sjjs_dq.t_yx_lotterydetail_acdt t
where t.acid = ‘AC20220314144508′
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) < to_date(v_date,’yyyymmdd’) + 1
group by prizename
)ta
left join sjjs_dq.wb1_nbst_gift_mstc10 tb on ta.prize_no = tb.prize_no
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc10 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc10 value(
select substr(v_date,1,6) as p_month,t1.prize_name,
t1.prize_tnum,t2.prize_mnum as prize_mno1,t1.prize_mnum as prize_mno2,
t2.prize_rnum as prize_rnum1,
(t1.prize_mnum + t2.prize_rnum) as prize_rnum2,
t2.prize_pnum,
(t1.prize_mnum – t2.prize_pnum) as prize_rnum3,
(t1.prize_tnum – t2.prize_mnum) as prize_rnum4
from sjjs_dq.wb1_nbst_gift_mstc10 t1
left join (
select prizename,sum(prize_mnum) as prize_mnum,prize_no,
sum(case when p_month < substr(v_date,1,6) then prize_rnum else 0 end) as prize_rnum,
sum(case when p_month = substr(v_date,1,6) then prize_mnum else 0 end) as prize_pnum
from sjjs_dq.rp1_nbst_gift_mstc10
where p_month <= substr(v_date,1,6)
group by prizename,prize_no
)t2 on t1.PRIZE_NO = t2.PRIZE_NO
);
commit;
–del
delete from sjjs_dq.r_nbst_gift_mstc11 where p_month = substr(v_date,1,6);
commit;
–insert
insert into sjjs_dq.r_nbst_gift_mstc11 value(
select /+parallel(4)/ substr(v_date,1,6) as p_month,count(distinct t.userid) as p_num
from sjjs_dq.t_yx_lotterydetail_acdt t
where t.acid = ‘AC20220314144508′
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) >= trunc(to_date(v_date,’yyyymmdd’),’mm’)
and to_date(substr(t.chancecreatetime,1,10),’yyyy-mm-dd’) < to_date(v_date,’yyyymmdd’) + 1
);
commit;
exception
when others then
err_code := sqlcode;
err_msge := sqlerrm;
err_line := dbms_utility.format_error_backtrace;
sjjs_dq.p_insert_model_log(‘pkd_202206_proc’,’p_nbst_gift_mstc’,err_code,err_msge,err_line);
end p_nbst_gift_mstc;
end pkd_202206_proc;