chengben/Scripts/工参治理.sql

--call rpt_0_4g_siteinfo_backup()
--call rpt_0_5g_siteinfo_backup()

--call o2p.perform_4g_sdrflow(null);--数据日期
--call o2p.perform_5g_sdrflow(null);--数据日期，注释掉2个存储过程，需要手动执行导出后导入到对应的表里
--call o2p.rpt_0_4g_siteinfo_error(null);--工参日期
--call o2p.rpt_0_5g_siteinfo_error(null);--工参日期
--call o2p.rpt_0_4g_mdt_deviate_db(null);--MDT日期
--call o2p.rpt_0_4g_flow_no_siteinfo(null);--数据日期
--call o2p.rpt_1_5g_flow_no_siteinfo(null);--数据日期

--truncate table o2p.cfg_0_4g_siteinfo 

select '4G_cfg' as tb,count(*) from cfg_0_4g_siteinfo
union all
select '5G_cfg' as tb,count(*) from cfg_0_5g_siteinfo
union all
select '4G_backup' as tb,count(*) from rpt_0_4g_siteinfo_backup
union all
select '5G_backup' as tb,count(*) from rpt_0_5g_siteinfo_backup


select sdate,count(*) from o2p.sdr_1_5g_flow_cell_day group by sdate order by sdate
select sdate,count(*) from o2p.sdr_1_5g_flow_enb_day group by sdate order by sdate

select sdate,count(*) from o2p.rpt_1_5g_flow_gnb_7d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_flow_site_7d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_flow_cell_7d_day group by sdate order by sdate

select sdate,count(*) from o2p.rpt_1_5g_flow_gnb_2d_day group by sdate order by sdate
--select sdate,count(*) from o2p.rpt_1_5g_flow_site_2d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_flow_cell_2d_day group by sdate order by sdate

select sdate,count(*) from o2p.rpt_1_5g_flow_gnb_1d_day group by sdate order by sdate
--select sdate,count(*) from o2p.rpt_1_5g_flow_site_1d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_flow_cell_1d_day group by sdate order by sdate

select sdate,count(*) from o2p.rpt_1_5g_lowflow_gnb_2d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_lowflow_cell_2d_day group by sdate order by sdate
select sdate,count(*) from o2p.rpt_1_5g_lowflow_site_2d_day group by sdate order by sdate

--select data_time,count(*) from o2p.rpt_0_5g_flow_no_siteinfo group by data_time order by data_time
select data_time,count(*) from o2p.rpt_1_5g_flow_no_siteinfo group by data_time order by data_time


--select distinct sdate from o2p.sdr_1_5g_flow_base_day sgfbd order by sdate desc

select * from rpt_1_5g_flow_site_2d_day where sdate = to_date('20220811','yyyymmdd');
--delete from rpt_1_5g_flow_site_2d_day where sdate = to_date('20220811','yyyymmdd');
--insert into rpt_1_5g_flow_site_2d_day
select distinct SDATE, CITY_CODE, CITY_NAME, PHYSTATION_ADDRESS, GNBID
      from (select to_date('20220811', 'yyyymmdd') sdate,
                   a.CITY_CODE,
                   a.CITY_NAME,
                   a.PHYSTATION_ADDRESS,
                   a.GNBID,
                   b.GNBID bgnbid
              from (SELECT distinct CITY_CODE,
                                    CITY_NAME,
                                    PHYSTATION_ADDRESS,
                                    GNBID
                      FROM cfg_0_5g_siteinfo
                     where sdate = (select max(sdate) from cfg_0_5g_siteinfo)) a
              left join (SELECT d.city_name, d.GNBID, d.PHYSTATION_ADDRESS,sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M, 0)) THROUGHPUT
                            FROM sdr_1_5g_flow_base_day t, cfg_0_5g_siteinfo d
                           where t.sdate between
                                 to_date('20220810', 'yyyymmdd') and
                                 to_date('20220811', 'yyyymmdd') --29 时间参数
                             and d.sdate = (select max(sdate) from cfg_0_5g_siteinfo)
                             and t.gnbid = d.gnbid
                             and t.cell_id = d.cell_id
                           group by d.city_name, d.GNBID, d.PHYSTATION_ADDRESS
                          having sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M, 0)) > 0
                        ) b
                on a.gnbid = b.gnbid
               and a.city_name = b.city_name
               and a.PHYSTATION_ADDRESS = b.PHYSTATION_ADDRESS) t
     where bgnbid is null;


select * from rpt_1_5g_flow_site_1d_day where sdate = to_date('20220811','yyyymmdd');
--delete from rpt_1_5g_flow_site_1d_day where sdate = to_date('20220811','yyyymmdd');
--insert into rpt_1_5g_flow_site_1d_day
select distinct SDATE, CITY_CODE, CITY_NAME, PHYSTATION_ADDRESS, GNBID 
      from (select to_date('20220811', 'yyyymmdd') sdate,
                   a.CITY_CODE,
                   a.CITY_NAME,
                   a.PHYSTATION_ADDRESS,
                   a.GNBID,
                   b.GNBID bgnbid
              from (SELECT distinct CITY_CODE,
                                    CITY_NAME,
                                    PHYSTATION_ADDRESS,
                                    GNBID
                      FROM cfg_0_5g_siteinfo
                     where sdate = (select max(sdate) from cfg_0_5g_siteinfo)) a
              left join (SELECT d.city_name, d.GNBID, d.PHYSTATION_ADDRESS,sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M, 0)) THROUGHPUT
                            FROM sdr_1_5g_flow_base_day t, cfg_0_5g_siteinfo d
                           where t.sdate between
                                 to_date('20220811', 'yyyymmdd') and
                                 to_date('20220811', 'yyyymmdd') --29 时间参数
                             and d.sdate = (select max(sdate) from cfg_0_5g_siteinfo)
                             and t.gnbid = d.gnbid
                             and t.cell_id = d.cell_id
                           group by d.city_name, d.GNBID, d.PHYSTATION_ADDRESS
                          having sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M, 0)) > 0
                        ) b
                on a.gnbid = b.gnbid
               and a.city_name = b.city_name
               and a.PHYSTATION_ADDRESS = b.PHYSTATION_ADDRESS) t
     where bgnbid is null;


    --truncate table rpt_1_5g_flow_no_siteinfo;
    --select * from rpt_1_5g_flow_no_siteinfo;
    select '20220805' ||'-'||'20220811' data_time,
             GNBID || '-' || CELL_ID site_cell,
             GNBID gnbid,
             CELL_ID cell_id,
             CITY city,
             VENDER vendor,
             THROUGHPUT/1024/1024/1024 flow_gb,
             case
               when THROUGHPUT > 0 then
                '是'
               else
                '否'
             end is_flow ,
             case
               when bgnbid is null then
                '否'
               else
                '是'
             end is_cfg_cell ,
             CELL_NAME cfg_cellname ,
             city_name cfg_cityname ,
             CONSTRUCTION cfg_contractor ,
             case when gnbidc is null then '否' else '是' end is_err
        from (select a.GNBID,
                     a.CELL_ID,
                     max(a.CITY) CITY,
                     max(a.VENDER) VENDER,
                     sum(a.THROUGHPUT) THROUGHPUT,
                     max(b.gnbid) bgnbid,
                     max(b.cell_name) cell_name,
                     max(b.city_name) city_name,
                     max(CONSTRUCTION) CONSTRUCTION,
                     max(c.gnbid) gnbidc
                from (SELECT GNBID,
                             CELL_ID,
                             max(CITY) CITY,
                             max(VENDER) VENDER,
                             sum(coalesce(UL_THROUGHPUT_M, 0) + coalesce(DL_THROUGHPUT_M, 0)) THROUGHPUT
                        FROM sdr_1_5g_flow_base_day
                       where sdate between to_date('20220805', 'yyyymmdd') and
                             to_date('20220811', 'yyyymmdd')
                       group by GNBID, CELL_ID) a
                left join (select GNBID,
                                 CELL_ID,
                                 max(CITY_name) CITY_name,
                                 max(cell_name) cell_name,
                                 max(CONSTRUCTION) CONSTRUCTION
                            from cfg_0_5g_siteinfo
                           where sdate = (select max(sdate) from cfg_0_5g_siteinfo)
                           group by GNBID, CELL_ID) b
                  on (a.gnbid = b.gnbid and a.cell_id = b.cell_id)
                 left join (SELECT distinct gnbid,cell_id FROM rpt_0_5g_siteinfo_error) c
                 on (a.gnbid = c.gnbid and a.cell_id = c.cell_id)
                 group by a.GNBID, a.CELL_ID) g;
                 
                
	--truncate table rpt_0_5g_err_flag;
	--insert into rpt_0_5g_err_flag
        with a_2d as (   
        select distinct a.gnbid,a.cell_id
          from cfg_0_5g_siteinfo a,rpt_1_5g_flow_cell_2d_day b
         where a.sdate = (select max(sdate) from cfg_0_5g_siteinfo)
           and a.is_alive = 1
           and b.sdate = '2022-08-11'--(select max(sdate) from rpt_1_5g_flow_cell_2d_day)
           and a.gnbid = b.gnbid
           and a.cell_id = b.cell_id),
        b_err as (
        SELECT distinct gnbid, CELL_ID
          FROM rpt_0_5g_siteinfo_error
         where sdate = (select max(sdate) from rpt_0_5g_siteinfo_error)
           and is_alive = 1)
        select '5G小区2天无业务|工参不全不准' FLAG, t.* from (select * from a_2d INTERSECT select * from b_err) t
        union all
        select '5G小区2天无业务', t.* from (select * from a_2d except select * from b_err) t
        union all
        select '工参不全不准', t.* from (select * from b_err except select * from a_2d) t;

       
       
       
       
select sdate,count(*) from o2p.rpt_1_5g_lowflow_site_2d_day rglsdd group by sdate order by sdate
       
delete from rpt_1_5g_lowflow_site_2d_day where sdate = to_date('20220811','yyyymmdd');
      --insert into rpt_1_5g_lowflow_site_2d_day
        select to_date('20220811', 'yyyymmdd') sdate,
               a.CITY_CODE,
               a.CITY_NAME,
               a.PHYSTATION_ADDRESS,
               a.GNBID,
               sum(b.THROUGHPUT) THROUGHPUT
          from (SELECT distinct CITY_CODE,
                                CITY_NAME,
                                PHYSTATION_ADDRESS,
                                GNBID,
                                CELL_ID
                  FROM cfg_0_5g_siteinfo
                 where sdate = (select max(sdate) from cfg_0_5g_siteinfo)) a
         inner join (SELECT GNBID,
                            CELL_ID,
                            sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M, 0)) THROUGHPUT
                       FROM sdr_1_5g_flow_base_day
                      where sdate between to_date('20220811', 'yyyymmdd') - 6 and to_date('20220811', 'yyyymmdd') --29 时间参数
                      group by GNBID, CELL_ID) b
            on a.gnbid = b.gnbid
           and a.cell_id = b.cell_id
         group by to_date('20220811', 'yyyymmdd'),
                  CITY_CODE,
                  CITY_NAME,
                  PHYSTATION_ADDRESS,
                  a.GNBID
        having sum(coalesce(THROUGHPUT, 0)) > 0 and sum(coalesce(THROUGHPUT, 0)) < 7168;

       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       select '[基站ID-扇区ID-经纬度-方位角]-归属多个物理站' ERROR_CLASS,
               SDATE,
               CITY_CODE,
               CITY_NAME,
               DISTRICT_CODE,
               DISTRICT_NAME,
               NETWORK_NAME,
               PHYSTATION_ADDRESS,
               STATION_NAME,
               GNBID,
               CELL_NAME,
               CELL_ID,
               LON,
               LAT,
               DIRECTION,
               HEIGHT,
               M_DOWNTILT,
               E_DOWNTILT,
               STATION_TYPE,
               ISDIGITALINDOOR,
               DOWN_FREQ,
               VENDER,
               OWN_SCHOOLYARD,
               TOWERADDRESS_CODE,
               PROPERTY,
               SCENE,
               IS_SCENESITE,
               MARKETING_NETWORK,
               TERMINALAMOUNT_5G,
               SECTOR_INCOMING,
               IS_BUSY,
               IS_ALIVE,
               IS_ALIVE_UPDATE_TIME,
               CONSTRUCTION,
               IS_SAME_ADDRESS,
               SAME_ADDRESS_SITES,
               IS_SAME_ADDRESS_CT
          from cfg_0_5g_siteinfo t,
               -- 20210707 算法更新，加入基站id-小区id-经纬度-方位角 一起检测，用于支持拉远或合并基站
               (select GNBID     GNBIDh,
                       CELL_ID   CELL_IDh,
                       LON       LONh,
                       LAT       LATh,
                       DIRECTION DIRECTIONh
                  from (SELECT distinct PHYSTATION_ADDRESS,
                                        GNBID,
                                        CELL_ID,
                                        LON,
                                        LAT,
                                        DIRECTION
                          FROM cfg_0_5g_siteinfo
                         --where SDATE = TO_DATE(istart, 'YYYYMMDD')
                           where CONSTRUCTION like '%联通%') g
                 group by GNBID, CELL_ID, LON, LAT, DIRECTION
                having count(*) > 1) h
         where t.gnbid = h.GNBIDh
           and t.cell_id = h.CELL_IDh
           and t.LON = h.LONh
           and t.LAT = h.LATh
           and t.DIRECTION = h.DIRECTIONh
           --AND T.SDATE = TO_DATE(istart, 'YYYYMMDD')
           and T.CONSTRUCTION like '%联通%'
           and t.is_alive = 1
       
           
           
           
           
           
           select a.CITY_NAME "地市名称",
           a.DISTRICT_NAME "行政区名称",
           a.CITY_CODE "地市编码",
           a.DISTRICT_CODE "行政区编码",
           a.PHYSTATION_ADDRESS "物理站名称",
           a.BBU_NAME "BBU名称",
           cast(a.ENBID as varchar) "基站enbid",
           a.vender "厂家",
           round(b.THROUGHPUT / 1024 / 1024 / 1024, 2) "业务量GB" --THROUGHPUT 单位是byte，需要除以1024*1024*1024
           ,
           CONSTRUCTION "承建方",
           a.lon "物理站经度(max)",
           a.lat "物理站纬度(max)"
      from (select distinct CITY_NAME,
                            DISTRICT_NAME,
                            CITY_CODE,
                            DISTRICT_CODE,
                            PHYSTATION_ADDRESS,
                            BBU_NAME,
                            ENBID,
                            vender,
                            max(lon) lon,
                            max(lat) lat,
                            first_value(construction) over(partition by district_name, district_code, enbid) construction
              from cfg_0_4g_siteinfo
             where sdate = (select max(sdate) from cfg_0_4g_siteinfo)
               and is_alive = 1
              group by CITY_NAME,DISTRICT_NAME,CITY_CODE,DISTRICT_CODE,PHYSTATION_ADDRESS,BBU_NAME,ENBID,vender,construction) a,
           (select distinct city_name, enbid, THROUGHPUT
              from rpt_0_4g_flow_enb_7d_day
             where sdate =
                   (select max(sdate) from rpt_0_4g_flow_enb_7d_day)) b
     where /*a.city_name=b.city_name and*/
     a.enbid = b.enbid
     
     
     
     
     
     
     
     
     
     
     select distinct a.city_name "地市名称",
                    a.DISTRICT_NAME "行政区名称",
                    a.CITY_CODE "地市编码",
                    a.DISTRICT_CODE "行政区编码",
                    a.PHYSTATION_ADDRESS "物理站名称",
                    a.STATION_NAME "基站名称",
                    cast(a.gnbid as varchar) "基站GNBID",
                    a.vender "厂家",
                    round(b.THROUGHPUT / 1024 / 1024 / 1024, 2) "业务量GB" --THROUGHPUT 单位是byte
                    ,
                    CONSTRUCTION "承建方",
                    a.lon "物理站经度(max)",
                    a.lat "物理站纬度(max)"          
      from (select distinct city_name,
                            DISTRICT_NAME,
                            CITY_CODE,
                            DISTRICT_CODE,
                            PHYSTATION_ADDRESS,
                            STATION_NAME,
                            gnbid,
                            vender,
                            max(lon) lon,
                            max(lat) lat,
                            first_value(construction) over(partition by district_name, district_code, gnbid) construction
              from cfg_0_5g_siteinfo
             where sdate = (select max(sdate) from cfg_0_5g_siteinfo)
               --and is_alive = 1
               group by city_name,DISTRICT_NAME,CITY_CODE,DISTRICT_CODE,PHYSTATION_ADDRESS,STATION_NAME,gnbid,vender,construction) a,
           (select distinct city_name, PHYSTATION_ADDRESS, gnbid, THROUGHPUT
              from rpt_1_5g_flow_site_7d_day
             where sdate =
                   (select max(sdate) from rpt_1_5g_flow_site_7d_day)) b
     where /* a.city_name=b.city_name and*/
     a.gnbid = b.gnbid
     and a.PHYSTATION_ADDRESS = b.PHYSTATION_ADDRESS
     
     
     select * 
     from cfg_0_5g_siteinfo,(select distinct gnbid, cell_id from rpt_1_5g_flow_cell_7d_day
              where sdate = (select max(sdate) from rpt_1_5g_flow_cell_7d_day) ) b
      where cfg_0_5g_siteinfo.gnbid = b.gnbid 
      and cfg_0_5g_siteinfo.cell_id = b.cell_id
      and cfg_0_5g_siteinfo.sdate = (select max(sdate) from cfg_0_5g_siteinfo)
      --20210721 新增，地市名称不合法的不输出，否则亚新侧无法正常按照报表汇总
      and cfg_0_5g_siteinfo.city_name in ('承德市','邯郸市','廊坊市','石家庄市','秦皇岛市','张家口市','邢台市','保定市','沧州市','衡水市','唐山市','雄安新区');
       
     
     select to_date('20220811', 'yyyymmdd') sdate,
               null CITY_CODE,
               null CITY_NAME,
               GNBID,
               cell_id,
               sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M,0)) THROUGHPUT
          FROM sdr_1_5g_flow_base_day
            where sdate between to_date('20220811', 'yyyymmdd') - 6 and to_date('20220811', 'yyyymmdd') --29 时间参数
            group by GNBID, CELL_ID
            having sum(coalesce(UL_THROUGHPUT_M+DL_THROUGHPUT_M,0)) > 0;