chengben/Scripts/01.sql

--01、网运成本占收比（集团口径）
---北10
select 
smonth,
sname,
column6 as cost_with_rengong,
column10 as cost_no_rengong,
column14 as income,
column6/column14 rate_with_rengong,
column10/column14 rate_no_rengong
--column17 as 网运成本占收比（含人工）,
--column20 as 网运成本占收比（不含人工）
from sqmdb_cost.report50003 
where sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and smonth='202203'


---地市
select 
a.smonth,
a.sname,
a.cost_total,
b.income_year, --本年收入累计,
case when b.income_year=0 then null else a.cost_total/b.income_year end as cost_rate--网运成本占收比（集团口径）
--c.city_code
from
(select '202203' as smonth,sname,
sum(column1)/10000 as cost_total--成本合计（包含人工）
from sqmdb_cost.report50 where smonth between '202201' and '202203' group by sname) a 
left join
(select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b 
on a.smonth=b.smonth and a.sname=b.sname
left join
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) c 
on a.sname=c.city_pro
where a.sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and a.smonth='202203'



---网格（网格数据缺少分母）-目前只有分子
select smonth,segment1_name,city_name,grid_name,
column1 as cost_total
from sqmdb_cost.report49