chengben/Scripts/05.sql

--05、百元资产网运成本（集团口径）表九
---北10
select 
'202203' as smonth,
a.sname,
--a.stype,
a.cost_total,b.zichan,
a.cost_total/b.zichan*100 as cost_bai
from
(select sname,sum(column1)/10000 as cost_total from sqmdb_cost.report50 
where smonth between '202201' and '202203' and stype='分省展现' group by sname) a
left join
(select sname,(column7+column8)/2 as zichan from sqmdb_cost.report80054 where smonth='202203') b
on a.sname=b.sname
where a.sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')


---地市
select 
'202203' as smonth,
a.sname,
--a.stype,
a.cost_total,b.zichan,
a.cost_total/b.zichan*100 as cost_bai
from
(select sname,sum(column1)/10000 as cost_total from sqmdb_cost.report50 
where smonth between '202201' and '202203' and stype='北方省汇总展现' group by sname) a
left join
(select sname,(column7+column8)/2 as zichan from sqmdb_cost.report80054 where smonth='202203') b
on a.sname=b.sname
where a.sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')