| 12345678910111213141516171819202122232425262728 |
- --26、H端口实占率
- ---网格
- select
- '202206' as MONTH_ID,
- '网格' as ZB_TYPE,
- '河北' as PROV_NAME,
- dcg.city_code*100 as CITY_ID,
- dcg.city_name as CITY_NAME,
- dcg.grid_code as GRID_ID,
- a.area_name as GRID_NAME,
- '26' as ZB_CODE,
- 'H端口实占率' as ZB_NAME,
- '是' as IS_RATIO,
- a.ftth_port_sts_count as QZ_FZ,
- a.ftth_port_count as QZ_FM,
- case when a.ftth_port_count=0 then 0 else a.ftth_port_sts_count/a.ftth_port_count end as BY_QZ
- from
- (select p2.smonth,p2.city_name,p2.area_name,
- avg_num(p1.ftth_port_sts_count,p2.ftth_port_sts_count)/10000 as ftth_port_sts_count,
- avg_num(p1.ftth_port_count,p2.ftth_port_count)/10000 as ftth_port_count
- from
- (select * from sqmdb_cost.kuandai_ftth_month where smonth='202112')p1
- join
- (select * from sqmdb_cost.kuandai_ftth_month where smonth='202206')p2
- on p1.city_name=p2.city_name and p1.area_name=p2.area_name) a
- left join sqmdb_cost.dict_grid_code dgc on a.city_name=dgc.city_name and trim(a.area_name)=trim(dgc.grid_name)
- join sqmdb_cost.dict_code_grid dcg on dgc.grid_code=dcg.grid_code
- order by dcg.city_code
|
