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

--省内口径（北十）
select 
m_cm.smonth as "date",
m_cm.prov_name as province_name,
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,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南'),'~') prov_name,
regexp_split_to_table(concat_ws('~',prov_1,prov_2,prov_3,prov_4,prov_5,prov_6,prov_7,prov_8,prov_9,prov_10),'~') prov_value
from sqmdb_cost.chengben_b10_month where smonth='202207') m_cm --本月
join 
(select smonth,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南'),'~') prov_name,
regexp_split_to_table(concat_ws('~',prov_1,prov_2,prov_3,prov_4,prov_5,prov_6,prov_7,prov_8,prov_9,prov_10),'~') prov_value
from sqmdb_cost.chengben_b10_month where smonth='202206') m_lm --上月
on m_cm.kpi_code=m_lm.kpi_code and m_cm.prov_name=m_lm.prov_name
join
(select smonth,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南'),'~') prov_name,
regexp_split_to_table(concat_ws('~',prov_1,prov_2,prov_3,prov_4,prov_5,prov_6,prov_7,prov_8,prov_9,prov_10),'~') prov_value
from sqmdb_cost.chengben_b10_accum where smonth='202207') y_cm --本年累计
on m_cm.kpi_code=y_cm.kpi_code and m_cm.prov_name=y_cm.prov_name
join
(select smonth,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南'),'~') prov_name,
regexp_split_to_table(concat_ws('~',prov_1,prov_2,prov_3,prov_4,prov_5,prov_6,prov_7,prov_8,prov_9,prov_10),'~') prov_value
from sqmdb_cost.chengben_b10_accum where smonth='202107') y_lm --上年同期
on m_cm.kpi_code=y_lm.kpi_code and m_cm.prov_name=y_lm.prov_name