📄 存储过程etl_bas_asset_call_x_new.prc
字号:
Tra_Loc_Mbl_Dur,
Tra_Loc_Mbl_Dstn,
Trd_Tol_Int_Cnt,
Trd_Tol_Int_Dur,
Trd_Tol_Int_Dstn,
Trd_Tol_Hmt_Cnt,
Trd_Tol_Hmt_Dur,
Trd_Tol_Hmt_Dstn,
Ip_Tol_Dms_Cnt,
Ip_Tol_Dms_Dur,
Ip_Tol_Dms_Dstn,
Ip_Tol_Int_Cnt,
Ip_Tol_Int_Dur,
Ip_Tol_Int_Dstn,
Ip_Tol_Hmt_Cnt,
Ip_Tol_Hmt_Dur,
Ip_Tol_Hmt_Dstn,
Tol_11808_Dms_Cnt,
Tol_11808_Dms_Dur,
Tol_11808_Dms_Dstn,
Tol_11808_Int_Cnt,
Tol_11808_Int_Dur,
Tol_11808_Int_Dstn,
Tol_11808_Hmt_Cnt,
Tol_11808_Hmt_Dur,
Tol_11808_Hmt_Dstn)
select var_pid_12,
Serv_Id, --用户唯一编号
Stats_Month, --月份
var_area_code,
sum(case
when substr(p2.node_id, 1, 4) between '0204' and '0205' then
Call_Cnt
end), --本地通话次数集中度
sum(case
when substr(p2.node_id, 1, 4) between '0204' and '0205' then
Call_Dur
end), --本地计费时长集中度
----
sum(case
when substr(p2.node_id, 1, 6) in
('020404', '020407', '020406') then
Call_Cnt
end), --区内发话次数
sum(case
when substr(p2.node_id, 1, 6) in
('020404', '020407', '020406') then
Call_Dur
end), --区内计费时长
count(distinct case
when substr(p2.node_id, 1, 6) in
('020404', '020407', '020406') then
Called_Nbr
end), --区内不同号码数
----
sum(case
when substr(p2.node_id, 1, 6) in ('020504', '020503') then
Call_Cnt
end), --区间发话次数
sum(case
when substr(p2.node_id, 1, 6) in ('020504', '020503') then
Call_Dur
end), --区间计费时长
count(distinct case
when substr(p2.node_id, 1, 6) in ('020504', '020503') then
Called_Nbr
end), --区间不同号码数
----
sum(case
when substr(p2.node_id, 1, 6) in ('020405') then
Call_Cnt
end), --市话拨打手机发话次数
sum(case
when substr(p2.node_id, 1, 6) in ('020405') then
Call_Dur
end), --市话拨打手机计费时长
count(distinct case
when substr(p2.node_id, 1, 6) in ('020405') then
Called_Nbr
end), --市话拨打手机不同号码数
----
sum(case
when substr(p2.node_id, 1, 6) in ('020502') then
Call_Cnt
end), --网话拨打手机发话次数
sum(case
when substr(p2.node_id, 1, 6) in ('020502') then
Call_Dur
end), --网话拨打手机计费时长
count(distinct case
when substr(p2.node_id, 1, 6) in ('020502') then
Called_Nbr
end), --网话拨打手机不同号码数
----
sum(case
when p2.item_id in ('220100108', '220100130') then
Call_Cnt
end), --电信(包括11808)IP接入次数
sum(case
when p2.item_id in ('220100108', '220100130') then
Call_Dur
end), --电信(包括11808)IP接入时长
----
sum(case
when substr(p2.node_id, 1, 6) in ('020402') then
Call_Cnt
end), --异商IP接入次数
sum(case
when substr(p2.node_id, 1, 6) in ('020402') then
Call_Dur
end), --异商IP接入时长
----
sum(case
when substr(p2.node_id, 1, 6) in ('020601') then
Call_Cnt
end), --电信拨号接入次数
sum(case
when substr(p2.node_id, 1, 6) in ('020601') then
Call_Dur
end), --电信拨号接入时长
----
--异商拨号暂无口径
null, --异商拨号接入次数
null, --异商拨号接入时长
----
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 4) between '0207' and '0209' and
p2.item_name not like '%优惠%' and
p2.item_id <> '220100108' and p2.item_id <> '220100130' then
Call_Cnt
end), --长途通话次数集中度
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 4) between '0207' and '0209' and
p2.item_name not like '%优惠%' and
p2.item_id <> '220100108' and p2.item_id <> '220100130' then
Call_Dur
end), --长途通话时长集中度
----
sum(case
when substr(p2.node_id, 1, 8) in ('02070101', '02070104') AND
p2.item_id <> '220100108' and p2.item_id <> '220100130' then
Call_Cnt
end), --传统国内长途发话次数
sum(case
when substr(p2.node_id, 1, 8) in ('02070101', '02070104') and
p2.item_id <> '220100108' and p2.item_id <> '220100130' then
Call_Dur
end), --传统国内长途计费时长
count(distinct case
when substr(p2.node_id, 1, 8) in ('02070101', '02070104') and
p2.item_id <> '220100108' and p2.item_id <> '220100130' then
Called_Nbr
end), --传统国内长途不同号码数
----
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 8) in ('02070102') and
p2.item_name not like '%优惠%' then
Call_Cnt
end), --异地行动长途发话次数
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 8) in ('02070102') and
p2.item_name not like '%优惠%' then
Call_Dur
end), --异地行动长途计费时长
count(distinct case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 8) in ('02070102') and
p2.item_name not like '%优惠%' then
Called_Nbr
end), --异地行动长途不同号码数
----
sum(case
when substr(p2.node_id, 1, 6) in
('020801', '020802', '020804') then
Call_Cnt
end), --传统国际长途发话次数
sum(case
when substr(p2.node_id, 1, 6) in
('020801', '020802', '020804') then
Call_Dur
end), --传统国际长途计费时长
count(distinct case
when substr(p2.node_id, 1, 6) in
('020801', '020802', '020804') then
Called_Nbr
end), --传统国际长途不同号码数
----
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 6) in
('020901', '020903', '020904') and
p2.item_name not like '%优惠%' then
Call_Cnt
end), --传统港澳台长途发话次数
sum(case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 6) in
('020901', '020903', '020904') and
p2.item_name not like '%优惠%' then
Call_Dur
end), --传统港澳台长途计费时长
count(distinct case
when p2.tree_type_id = 2123 and
substr(p2.node_id, 1, 6) in
('020901', '020903', '020904') and
p2.item_name not like '%优惠%' then
Called_Nbr
end), --传统港澳台长途不同号码数
----
sum(case
when p2.node_id like '02070201%' then
Call_Cnt
end), --IP国内长途发话次数
sum(case
when p2.node_id like '02070201%' then
Call_Dur
end), --IP国内长途计费时长
count(case
when p2.node_id like '02070201%' then
Called_Nbr
end), --IP国内长途不同号码数
----
sum(case
when p2.node_id like '020803%' and
p2.item_name not like '%11808%' then
Call_Cnt
end), --IP国际长途发话次数
sum(case
when p2.node_id like '020803%' and
p2.item_name not like '%11808%' then
Call_Dur
end), --IP国际长途计费时长
count(distinct case
when p2.node_id like '020803%' and
p2.item_name not like '%11808%' then
Called_Nbr
end), --IP国际长途不同号码数
----
sum(case
when p2.node_id like '020902%' and
p2.item_name not like '%11808%' then
Call_Cnt
end), --IP港澳台长途发话次数
sum(case
when p2.node_id like '020902%' and
p2.item_name not like '%11808%' then
Call_Dur
end), --IP港澳台长途计费时长
count(distinct case
when p2.node_id like '020902%' and
p2.item_name not like '%11808%' then
Called_Nbr
end), --IP港澳台长途不同号码数
----
sum(case
when p2.node_id like '02070202%' then
Call_Cnt
end), --11808国内长途发话次数
sum(case
when p2.node_id like '02070202%' then
Call_Dur
end), --11808国内长途计费时长
count(distinct case
when p2.node_id like '02070202%' then
serv_id
end), --11808国内长途不同号码数
----
sum(case
when p2.node_id like '020803%' and
p2.item_name like '%11808%' then
Call_Cnt
end), --11808国际长途发话次数
sum(case
when p2.node_id like '020803%' and
p2.item_name like '%11808%' then
Call_Dur
end), --11808国际长途计费时长
count(distinct case
when p2.node_id like '020803%' and
p2.item_name like '%11808%' then
Called_Nbr
end), --11808国际长途不同号码数
----
sum(case
when p2.node_id like '020902%' and
p2.item_name like '%11808%' then
Call_Cnt
end), --11808港澳台长途发话次数
sum(case
when p2.node_id like '020902%' and
p2.item_name like '%11808%' then
Call_Dur
end), --11808港澳台长途计费时长
count(distinct case
when p2.node_id like '020902%' and
p2.item_name like '%11808%' then
Called_Nbr
end) --11808港澳台长途不同号码数
from tmp_asset_call_bill_new p1,
zdyx_inf.inf_njf_sta_tree_struct_item_t p2
where p1.call_type = p2.item_id
and p1.area_code = var_area_code
and p2.tree_type_id = 2123
AND p2.item_name not like '%优惠%'
and p2.data_month = var_etl_month
group by Serv_Id, Stats_Month;
exception
when others then
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -