chengben/Scripts/工参治理测试.sql

--编号01（4G工参不准不准+经纬度偏离）
with a_2d as
   (select distinct ERROR_CLASS, a.city_name, a.ENBID, a.cell_id, CONSTRUCTION
      from rpt_0_4g_siteinfo_error a
     where a.is_alive = 1),

  b_err as
   (select distinct '基站经纬度偏离大于3000米' ERROR_CLASS,
                    a.city_name,
                    a.ENBID,
                    a.cell_id,
                    a.CONSTRUCTION
      from cfg_0_4g_siteinfo a, rpt_0_4g_mdt_deviate_db b
     where a.sdate = (select max(sdate) from cfg_0_4g_siteinfo)
       and a.is_alive = 1
       and a.city_name = b.city_name
       and a.enbid = b.enbid
     and b.DEVIATE_DISTANCE>3000)
  select city_name,
         ENBID,
         cell_id,
         string_agg(ERROR_CLASS, ',') as ERROR_CLASS,
         CONSTRUCTION
    from (select * from a_2d union all select * from b_err) a
--    where not exists
--              (SELECT distinct CITY_NAME, ENODEBID, PHYSTATION_ADDRESS
--                       FROM cfg_0_4g_siteinfo_white b
--                      where back_info like '%远距离覆盖需求%'
--                        and a.city_name = b.city_name
--                        and a.enbid = enodebid
--                        and a.cell_id::int = b.cell_id::int
--                        )
   group by city_name, ENBID, cell_id, CONSTRUCTION
   

--编号02（5G工参不全不准）
   SELECT distinct city_name, gnbid, CELL_ID, ERROR_CLASS, CONSTRUCTION
	  FROM rpt_0_5g_siteinfo_error
	 where sdate = (select max(sdate) from rpt_0_5g_siteinfo_error)
	   and is_alive = 1
	   
	
	   
select mdt.CITY_NAME      "城市名称",
           DISTRICT_NAME      "行政区名称",
           CITY_CODE          "城市编码",
           DISTRICT_CODE      "行政区编码",
           PHYSTATION_ADDRESS "物理站址",
           BBU_NAME           "BBU名称",
           ENBID              "基站enbid",
           STATION_TYPE       "基站类型",
           VENDER             "设备厂家",
           SCENE              "覆盖厂家",
           DEVIATE_DISTANCE   "基站偏离距离",
           cast(C1_ECI as varchar)    "小区1_eci",
           C1_CELL_NAME       "小区1_小区名称",
           C1_DISTANCE        "小区1_偏离距离",
           cast(C2_ECI as varchar)    "小区2_eci",
           C2_CELL_NAME       "小区2_小区名称",
           C2_DISTANCE        "小区2_偏离距离",
           cast(C3_ECI as varchar)    "小区3_eci",
           C3_CELL_NAME       "小区3_小区名称",
           C3_DISTANCE        "小区3_偏离距离",
           cast(C4_ECI as varchar)    "小区4_eci",
           C4_CELL_NAME       "小区4_小区名称",
           C4_DISTANCE        "小区4_偏离距离",
           cast(C5_ECI as varchar)    "小区5_eci",
           C5_CELL_NAME       "小区5_小区名称",
           C5_DISTANCE        "小区5_偏离距离",
           cast(C6_ECI as varchar)    "小区6_eci",
           C6_CELL_NAME       "小区6_小区名称",
           C6_DISTANCE        "小区6_偏离距离",
           cast(C7_ECI as varchar)    "小区7_eci",
           C7_CELL_NAME       "小区7_小区名称",
           C7_DISTANCE        "小区7_偏离距离",
           cast(C8_ECI as varchar)    "小区8_eci",
           C8_CELL_NAME       "小区8_小区名称",
           C8_DISTANCE        "小区8_偏离距离",
           cast(C9_ECI as varchar)    "小区9_eci",
           C9_CELL_NAME       "小区9_小区名称",
           C9_DISTANCE        "小区9_偏离距离",
	   wn.white_num "白名单小区数"
      from rpt_0_4g_mdt_deviate_db mdt
      left join
      (select city_name,enodebid,count(*) as white_num from 
      (select distinct city_name,enodebid,cell_id from 
      o2p.cfg_0_4g_siteinfo_white where back_info like '%远距离覆盖需求%') a
      group by city_name,enodebid) wn
      on mdt.city_name=wn.city_name 
      and mdt.enbid=wn.enodebid
      where mdt.DEVIATE_DISTANCE>3000	   
	   
	   

select mdt.CITY_NAME      "城市名称",
           DISTRICT_NAME      "行政区名称",
           CITY_CODE          "城市编码",
           DISTRICT_CODE      "行政区编码",
           PHYSTATION_ADDRESS "物理站址",
           BBU_NAME           "BBU名称",
           ENBID              "基站enbid",
           STATION_TYPE       "基站类型",
           VENDER             "设备厂家",
           SCENE              "覆盖厂家",
           DEVIATE_DISTANCE   "基站偏离距离",
           cast(C1_ECI as varchar)    "小区1_eci",
           C1_CELL_NAME       "小区1_小区名称",
           C1_DISTANCE        "小区1_偏离距离",
           cast(C2_ECI as varchar)    "小区2_eci",
           C2_CELL_NAME       "小区2_小区名称",
           C2_DISTANCE        "小区2_偏离距离",
           cast(C3_ECI as varchar)    "小区3_eci",
           C3_CELL_NAME       "小区3_小区名称",
           C3_DISTANCE        "小区3_偏离距离",
           cast(C4_ECI as varchar)    "小区4_eci",
           C4_CELL_NAME       "小区4_小区名称",
           C4_DISTANCE        "小区4_偏离距离",
           cast(C5_ECI as varchar)    "小区5_eci",
           C5_CELL_NAME       "小区5_小区名称",
           C5_DISTANCE        "小区5_偏离距离",
           cast(C6_ECI as varchar)    "小区6_eci",
           C6_CELL_NAME       "小区6_小区名称",
           C6_DISTANCE        "小区6_偏离距离",
           cast(C7_ECI as varchar)    "小区7_eci",
           C7_CELL_NAME       "小区7_小区名称",
           C7_DISTANCE        "小区7_偏离距离",
           cast(C8_ECI as varchar)    "小区8_eci",
           C8_CELL_NAME       "小区8_小区名称",
           C8_DISTANCE        "小区8_偏离距离",
           cast(C9_ECI as varchar)    "小区9_eci",
           C9_CELL_NAME       "小区9_小区名称",
           C9_DISTANCE        "小区9_偏离距离",
	   wn.white_num "白名单小区数"
      from rpt_0_4g_mdt_deviate_db mdt
      left join
      (select city_name,enodebid,count(*) as white_num from 
      (select distinct city_name,enodebid,cell_id from 
      o2p.cfg_0_4g_siteinfo_white where back_info like '%远距离覆盖需求%') a
      group by city_name,enodebid) wn
      on mdt.city_name=wn.city_name 
      and mdt.enbid=wn.enodebid
      where mdt.DEVIATE_DISTANCE>1000 and mdt.DEVIATE_DISTANCE<=2000
	   
	   
	   
select mdt.CITY_NAME      "城市名称",
           DISTRICT_NAME      "行政区名称",
           CITY_CODE          "城市编码",
           DISTRICT_CODE      "行政区编码",
           PHYSTATION_ADDRESS "物理站址",
           BBU_NAME           "BBU名称",
           ENBID              "基站enbid",
           STATION_TYPE       "基站类型",
           VENDER             "设备厂家",
           SCENE              "覆盖厂家",
           DEVIATE_DISTANCE   "基站偏离距离",
           cast(C1_ECI as varchar)    "小区1_eci",
           C1_CELL_NAME       "小区1_小区名称",
           C1_DISTANCE        "小区1_偏离距离",
           cast(C2_ECI as varchar)    "小区2_eci",
           C2_CELL_NAME       "小区2_小区名称",
           C2_DISTANCE        "小区2_偏离距离",
           cast(C3_ECI as varchar)    "小区3_eci",
           C3_CELL_NAME       "小区3_小区名称",
           C3_DISTANCE        "小区3_偏离距离",
           cast(C4_ECI as varchar)    "小区4_eci",
           C4_CELL_NAME       "小区4_小区名称",
           C4_DISTANCE        "小区4_偏离距离",
           cast(C5_ECI as varchar)    "小区5_eci",
           C5_CELL_NAME       "小区5_小区名称",
           C5_DISTANCE        "小区5_偏离距离",
           cast(C6_ECI as varchar)    "小区6_eci",
           C6_CELL_NAME       "小区6_小区名称",
           C6_DISTANCE        "小区6_偏离距离",
           cast(C7_ECI as varchar)    "小区7_eci",
           C7_CELL_NAME       "小区7_小区名称",
           C7_DISTANCE        "小区7_偏离距离",
           cast(C8_ECI as varchar)    "小区8_eci",
           C8_CELL_NAME       "小区8_小区名称",
           C8_DISTANCE        "小区8_偏离距离",
           cast(C9_ECI as varchar)    "小区9_eci",
           C9_CELL_NAME       "小区9_小区名称",
           C9_DISTANCE        "小区9_偏离距离",
	   wn.white_num "白名单小区数"
      from rpt_0_4g_mdt_deviate_db mdt
      left join
      (select city_name,enodebid,count(*) as white_num from 
      (select distinct city_name,enodebid,cell_id from 
      o2p.cfg_0_4g_siteinfo_white where back_info like '%远距离覆盖需求%') a
      group by city_name,enodebid) wn
      on mdt.city_name=wn.city_name 
      and mdt.enbid=wn.enodebid
      where mdt.DEVIATE_DISTANCE>2000 and mdt.DEVIATE_DISTANCE<=3000