chengben/Scripts/工参治理临时.sql

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