123456789101112131415161718192021222324252627282930313233343536373839 |
- --08、网络能耗占收比
- ---北10
- select
- smonth as 月份,
- sname as 省份,
- column1 as 能耗成本,
- column5 as 本年收入累计,
- column8 as 百元收入网络能耗,
- column1/column5 as 网络能耗占收比
- from sqmdb_cost.report50010
- where sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
- and smonth='202203'
- ---地市
- select
- a.smonth as 月份,
- a.sname as 公司,
- --a.stype as 数据类型,
- a.cost_nenghao as 网络能耗,
- b.income_year as 本年收入累计,
- case when b.income_year=0 then null else a.cost_nenghao/b.income_year*100 end as 百元收入网络能耗,
- case when b.income_year=0 then null else a.cost_nenghao/b.income_year end as 网络能耗占收比
- from
- (select '202203' as smonth,sname,stype,
- sum(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)/10000 as cost_nenghao
- from sqmdb_cost.report50 where stype ='北方省汇总展现' and smonth between '202201' and '202203' group by sname,stype) 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
- where
- a.sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
- ---网格(网格数据缺少分母)-目前只有分子
- select smonth,segment1_name,city_name,grid_name,
- (column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)/10000 as cost_nenghao
- from sqmdb_cost.report49
|