chengben/Scripts/07.sql

--07、百元净资产网运成本
---北10
select a.smonth,a.sname,
--a.stype,
a.zichan,b.zhejiu,
a.zichan-b.zhejiu as jzc,c.cost_total,
c.cost_total/(a.zichan-b.zhejiu)*100 as jzc_rate
from 
(select '202203' as smonth,sname,stype,sum(column7+column8)/2 as zichan 
from sqmdb_cost.report80054 where period_type='M' and smonth between '202201' and '202203' group by sname,stype) a
left join
(select '202203' as smonth,sname,stype,sum(column16) as zhejiu 
from sqmdb_cost.report80053 where period_type='M' and smonth between '202201' and '202203' group by sname,stype) b
on a.smonth=b.smonth and a.sname=b.sname and a.stype=b.stype
left join
(select '202203' as smonth,sname,stype,sum(column1)/10000 as cost_total 
from sqmdb_cost.report50 where smonth between '202201' and '202203' group by sname,stype) c
on a.smonth=c.smonth and a.sname=c.sname and a.stype=c.stype
where a.sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')



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