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

--省内口径（地市）
select 
m_cm.smonth as "date",
'河北' as province_name,
m_cm.city_name,
dc.city_code as city_code,
m_cm.kpi_name as index_name,
m_cm.kpi_code as index_id,
'万元' as index_unit,
m_cm.city_value as this_month,
m_lm.city_value as last_month,
(m_cm.city_value::numeric-m_lm.city_value::numeric) as month_on_month,
case when m_lm.city_value::numeric=0 then 0 else (m_cm.city_value::numeric-m_lm.city_value::numeric)/m_lm.city_value::numeric end as month_on_month_grow,
y_cm.city_value as this_year,
y_lm.city_value as last_year,
(y_cm.city_value::numeric-y_lm.city_value::numeric) as year_on_year,
case when y_lm.city_value::numeric=0 then 0 else (y_cm.city_value::numeric-y_lm.city_value::numeric)/y_lm.city_value::numeric end as year_on_year_grow
from
(select smonth,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','石家庄','唐山','秦皇岛','邯郸','邢台','保定','张家口','承德','廊坊','沧州','衡水','雄安','本部'),'~') city_name,
regexp_split_to_table(concat_ws('~',heb_city1,heb_city2,heb_city3,heb_city4,heb_city5,heb_city6,heb_city7,heb_city8,heb_city9,heb_city10,heb_city11,heb_city12,heb_city13),'~') city_value
from sqmdb_cost.chengben_heb_month where smonth='202207') m_cm --本月
join 
(select smonth,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','石家庄','唐山','秦皇岛','邯郸','邢台','保定','张家口','承德','廊坊','沧州','衡水','雄安','本部'),'~') city_name,
regexp_split_to_table(concat_ws('~',heb_city1,heb_city2,heb_city3,heb_city4,heb_city5,heb_city6,heb_city7,heb_city8,heb_city9,heb_city10,heb_city11,heb_city12,heb_city13),'~') city_value
from sqmdb_cost.chengben_heb_month where 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,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','石家庄','唐山','秦皇岛','邯郸','邢台','保定','张家口','承德','廊坊','沧州','衡水','雄安','本部'),'~') city_name,
regexp_split_to_table(concat_ws('~',heb_city1,heb_city2,heb_city3,heb_city4,heb_city5,heb_city6,heb_city7,heb_city8,heb_city9,heb_city10,heb_city11,heb_city12,heb_city13),'~') city_value
from sqmdb_cost.chengben_heb_accum where 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,kpi_code,kpi_name,
regexp_split_to_table(concat_ws('~','石家庄','唐山','秦皇岛','邯郸','邢台','保定','张家口','承德','廊坊','沧州','衡水','雄安','本部'),'~') city_name,
regexp_split_to_table(concat_ws('~',heb_city1,heb_city2,heb_city3,heb_city4,heb_city5,heb_city6,heb_city7,heb_city8,heb_city9,heb_city10,heb_city11,heb_city12,heb_city13),'~') city_value
from sqmdb_cost.chengben_heb_accum where 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