chengben/Scripts/网运成本报表/zwjs_cb_jt_city.sql

--集团（地市）
select 
m_cm.smonth as "date",
m_cm.prov_name as province_name,
city_name_short(m_cm.city_name) as city_name,
dc.city_code,
m_cm.kpi_name as index_name,
m_cm.kpi_code as index_id,
'万元' as index_unit,
m_cm.prov_value as this_month,
m_lm.prov_value as last_month,
(m_cm.prov_value::numeric-m_lm.prov_value::numeric) as month_on_month,
case when m_lm.prov_value::numeric=0 then 0 else (m_cm.prov_value::numeric-m_lm.prov_value::numeric)/m_lm.prov_value::numeric end as month_on_month_grow,
y_cm.prov_value as this_year,
y_lm.prov_value as last_year,
(y_cm.prov_value::numeric-y_lm.prov_value::numeric) as year_on_year,
case when y_lm.prov_value::numeric=0 then 0 else (y_cm.prov_value::numeric-y_lm.prov_value::numeric)/y_lm.prov_value::numeric end as year_on_year_grow
from
(select smonth,prov_name,city_name,
regexp_split_to_table(concat_ws('~','网运成本合计 ','1.修理费 ','（1）维保 ','（2）代维 ','（3）网优 ','（4）耗材 ','（5）修理 ','2.能耗成本 ','3.铁塔电费 ','4.铁塔租费 ','5.租赁费 ','6.客户接入工料 ','7.客户端 ','8.频率占用费 ','9.紧密型外包成本','10.其他 '),'~') kpi_name,
regexp_split_to_table(concat_ws('~','JT_CB_001','JT_CB_002','JT_CB_003','JT_CB_004','JT_CB_005','JT_CB_006','JT_CB_007','JT_CB_008','JT_CB_009','JT_CB_010','JT_CB_011','JT_CB_012','JT_CB_013','JT_CB_014','JT_CB_015','JT_CB_016'),'~') kpi_code,
regexp_split_to_table(concat_ws('~',cost_total,cost_fix_total,cost_fix_1_wb,cost_fix_2_dw,cost_fix_3_wy,cost_fix_4_hc,cost_fix_5_xl,cost_nenghao,cost_tower_elec,cost_tower_zu,cost_zulin,cost_liao,cost_client,cost_freq,cost_waibao,cost_other),'~')::numeric prov_value
from sqmdb_cost.chengben_jt_month where stype='地市' and smonth='202207') m_cm --本月
join 
(select smonth,prov_name,city_name,
regexp_split_to_table(concat_ws('~','网运成本合计 ','1.修理费 ','（1）维保 ','（2）代维 ','（3）网优 ','（4）耗材 ','（5）修理 ','2.能耗成本 ','3.铁塔电费 ','4.铁塔租费 ','5.租赁费 ','6.客户接入工料 ','7.客户端 ','8.频率占用费 ','9.紧密型外包成本','10.其他 '),'~') kpi_name,
regexp_split_to_table(concat_ws('~','JT_CB_001','JT_CB_002','JT_CB_003','JT_CB_004','JT_CB_005','JT_CB_006','JT_CB_007','JT_CB_008','JT_CB_009','JT_CB_010','JT_CB_011','JT_CB_012','JT_CB_013','JT_CB_014','JT_CB_015','JT_CB_016'),'~') kpi_code,
regexp_split_to_table(concat_ws('~',cost_total,cost_fix_total,cost_fix_1_wb,cost_fix_2_dw,cost_fix_3_wy,cost_fix_4_hc,cost_fix_5_xl,cost_nenghao,cost_tower_elec,cost_tower_zu,cost_zulin,cost_liao,cost_client,cost_freq,cost_waibao,cost_other),'~')::numeric prov_value
from sqmdb_cost.chengben_jt_month where stype='地市' and smonth='202206') m_lm --上月
on m_cm.kpi_code=m_lm.kpi_code and m_cm.city_name=m_lm.city_name
join 
(select smonth,prov_name,city_name,
regexp_split_to_table(concat_ws('~','网运成本合计 ','1.修理费 ','（1）维保 ','（2）代维 ','（3）网优 ','（4）耗材 ','（5）修理 ','2.能耗成本 ','3.铁塔电费 ','4.铁塔租费 ','5.租赁费 ','6.客户接入工料 ','7.客户端 ','8.频率占用费 ','9.紧密型外包成本','10.其他 '),'~') kpi_name,
regexp_split_to_table(concat_ws('~','JT_CB_001','JT_CB_002','JT_CB_003','JT_CB_004','JT_CB_005','JT_CB_006','JT_CB_007','JT_CB_008','JT_CB_009','JT_CB_010','JT_CB_011','JT_CB_012','JT_CB_013','JT_CB_014','JT_CB_015','JT_CB_016'),'~') kpi_code,
regexp_split_to_table(concat_ws('~',cost_total,cost_fix_total,cost_fix_1_wb,cost_fix_2_dw,cost_fix_3_wy,cost_fix_4_hc,cost_fix_5_xl,cost_nenghao,cost_tower_elec,cost_tower_zu,cost_zulin,cost_liao,cost_client,cost_freq,cost_waibao,cost_other),'~')::numeric prov_value
from sqmdb_cost.chengben_jt_accum where stype='地市' and smonth='202207') y_cm --本年累计
on m_cm.kpi_code=y_cm.kpi_code and m_cm.city_name=y_cm.city_name
join 
(select smonth,prov_name,city_name,
regexp_split_to_table(concat_ws('~','网运成本合计 ','1.修理费 ','（1）维保 ','（2）代维 ','（3）网优 ','（4）耗材 ','（5）修理 ','2.能耗成本 ','3.铁塔电费 ','4.铁塔租费 ','5.租赁费 ','6.客户接入工料 ','7.客户端 ','8.频率占用费 ','9.紧密型外包成本','10.其他 '),'~') kpi_name,
regexp_split_to_table(concat_ws('~','JT_CB_001','JT_CB_002','JT_CB_003','JT_CB_004','JT_CB_005','JT_CB_006','JT_CB_007','JT_CB_008','JT_CB_009','JT_CB_010','JT_CB_011','JT_CB_012','JT_CB_013','JT_CB_014','JT_CB_015','JT_CB_016'),'~') kpi_code,
regexp_split_to_table(concat_ws('~',cost_total,cost_fix_total,cost_fix_1_wb,cost_fix_2_dw,cost_fix_3_wy,cost_fix_4_hc,cost_fix_5_xl,cost_nenghao,cost_tower_elec,cost_tower_zu,cost_zulin,cost_liao,cost_client,cost_freq,cost_waibao,cost_other),'~')::numeric prov_value
from sqmdb_cost.chengben_jt_accum where stype='地市' and smonth='202107') y_lm --上年同期
on m_cm.kpi_code=y_lm.kpi_code and m_cm.city_name=y_lm.city_name
left join sqmdb_cost.dict_city dc on m_cm.city_name=dc.city_name