chengben/Scripts/25.sql

--25、샘籃데籃頓契냥굶槻樓
---뒈懇
select t1.smonth,t1.sname,t1.cost_wuxian,
t1.cost_hexin,t1.cost_wuxian_hexin,t2.site_count,
t1.cost_wuxian_hexin/t2.site_count as site_cost
from 
(select smonth,sname,stype,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_wuxian,
(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18+column19+column20+column21+column22+column23+column24+column25+column26) as cost_wuxian_hexin
from sqmdb_cost.report50
where sname in('柯소鏤_붉굇','鉗�_붉굇','해뽈돎_붉굇','벳덱_붉굇','饉憩_붉굇','괏땍_붉굇','蠟소왯_붉굇','넓돠_붉굇','읜렌_붉굇','꾕鹵_붉굇','뷜彊_붉굇','衿갛_붉굇')
and smonth='202203') t1
left join
(select city_code,
case when city_code=1301 then '柯소鏤_붉굇'
when city_code=1302 then '鉗�_붉굇'
when city_code=1303 then '해뽈돎_붉굇'
when city_code=1304 then '벳덱_붉굇'
when city_code=1305 then '饉憩_붉굇'
when city_code=1306 then '괏땍_붉굇'
when city_code=1307 then '蠟소왯_붉굇'
when city_code=1308 then '넓돠_붉굇'
when city_code=1309 then '꾕鹵_붉굇'
when city_code=1310 then '읜렌_붉굇'
when city_code=1311 then '뷜彊_붉굇'
when city_code=1312 then '衿갛_붉굇'
else '' end as city_name,
count(*) as site_count
from (select distinct eqp_sid,city_code from sqmdb_cost.equip where profession ='盧땡轟窟') a
group by city_code) t2
on t1.sname=t2.city_name

---貢목
select t1.city_name,t1.area_name,t1.cost_wuxian,t1.cost_hexin,t1.cost_wuxian_hexin,t2.site_count,
t1.cost_wuxian_hexin/t2.site_count as site_cost
from
(select fz.smonth,dg.city_name,dg.area_name,fz.cost_wuxian,fz.cost_hexin,fz.cost_wuxian_hexin from 
(select smonth,period_type,segment1_name,city_code,city_name,grid_code,grid_name,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_wuxian,
(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18+column19+column20+column21+column22+column23+column24+column25+column26) as cost_wuxian_hexin
from sqmdb_cost.report49
--where sname in('붉굇伽굶꼬','柯소鏤_붉굇','鉗�_붉굇','해뽈돎_붉굇','벳덱_붉굇','饉憩_붉굇','괏땍_붉굇','蠟소왯_붉굇','넓돠_붉굇','읜렌_붉굇','꾕鹵_붉굇','뷜彊_붉굇','衿갛_붉굇')
where period_type='M' and grid_name!='북셕' and smonth='202203') fz
join sqmdb_cost.dict_grid dg
on fz.city_code=dg.grid_city_code::varchar and fz.grid_code=dg.grid_code::varchar) t1
join
(select dg.city_name,dg.area_name,sc.site_count from
(select city_code,area_code,count(*) as site_count from 
(select distinct eqp_sid,city_code,area_code from sqmdb_cost.equip where profession ='盧땡轟窟') et
group by city_code,area_code) sc
join sqmdb_cost.dict_grid dg
on sc.city_code=dg.city_code and sc.area_code=dg.area_code) t2
on t1.city_name=t2.city_name and t1.area_name=t2.area_name
order by t1.city_name,t1.area_name