通信基站和通信技术机房

SELECT  nowtime.EC_CITY_NAME as EC_CITY_NAME,
        nowtime.EC_ITEM_TIME as EC_ITEM_TIME,
        nowtime.EC_ARENA_NAME as EC_ARENA_NAME,
        nowtime.TOTAL_USAGE as NOW_TOTAL_USAGE,
        lastmonth.TOTAL_USAGE as YEAR_TOTAL_USAGE,
       
        CASE
        WHEN nowtime.TOTAL_USAGE is null  THEN 0
        WHEN  nowtime.TOTAL_USAGE = 0 THEN 0
        ELSE
            CASE WHEN lastmonth.TOTAL_USAGE is null  THEN 0
            WHEN lastmonth.TOTAL_USAGE = 0 THEN 0
            ELSE round(nowtime.TOTAL_USAGE/lastmonth.TOTAL_USAGE,4)
            END
        END as TOTALUSAGECOMPARELASTMONTH,
        
        
        nowtime.AIRCON_USAGE as NOW_AIRCON_USAGE,
         lastmonth.AIRCON_USAGE as YEAR_AIRCON_USAGE,
         CASE
        WHEN nowtime.AIRCON_USAGE is null  THEN 0
        WHEN  nowtime.AIRCON_USAGE = 0 THEN 0
        ELSE
            CASE WHEN lastmonth.AIRCON_USAGE is null  THEN 0
            WHEN lastmonth.AIRCON_USAGE = 0 THEN 0
            ELSE round(nowtime.AIRCON_USAGE/lastmonth.AIRCON_USAGE,4)
            END
        END as AIRCONUSAGECOMPARELASTMONTH,
         
        nowtime.MAIN_USAGE as NOW_MAIN_USAGE,
        lastmonth.MAIN_USAGE as YEAR_MAIN_USAGE,
        CASE
        WHEN nowtime.MAIN_USAGE is null  THEN 0
        WHEN  nowtime.MAIN_USAGE = 0 THEN 0
        ELSE
            CASE WHEN lastmonth.MAIN_USAGE is null  THEN 0
            WHEN lastmonth.MAIN_USAGE = 0 THEN 0
            ELSE round(nowtime.MAIN_USAGE/lastmonth.MAIN_USAGE,4)
            END
        END as MAINUSAGECOMPARELASTMONTH,
        
        nowtime.GENERATOR_USAGE as NOW_GENERATOR_USAGE,
        lastmonth.GENERATOR_USAGE as YEAR_GENERATOR_USAGE,
                CASE
        WHEN nowtime.GENERATOR_USAGE is null  THEN 0
        WHEN  nowtime.GENERATOR_USAGE = 0 THEN 0
        ELSE
            CASE WHEN lastmonth.GENERATOR_USAGE is null  THEN 0
            WHEN lastmonth.GENERATOR_USAGE = 0 THEN 0
            ELSE round(nowtime.GENERATOR_USAGE/lastmonth.GENERATOR_USAGE,4)
            END
        END as GENERATORCOMPARELASTMONTH

            FROM
            (SELECT tab1.EC_CITY_NAME as EC_CITY_NAME, tab1.EC_ITEM_TIME as EC_ITEM_TIME,tab1.EC_ARENA_NAME as EC_ARENA_NAME,
            SUM(TOTAL_USAGE) as TOTAL_USAGE,
            SUM(AIRCON_USAGE) as AIRCON_USAGE,
            SUM(MAIN_USAGE) as MAIN_USAGE,
            SUM(GENERATOR_USAGE) as GENERATOR_USAGE
            FROM
            (SELECT
                sr.NAME as EC_CITY_NAME,
                to_char(rcm.READTIME,'yyyymm') as EC_ITEM_TIME,
                '通信机房' as EC_ARENA_NAME,
                SUM(rcm.TOTAL_USAGE) as TOTAL_USAGE,
                SUM(round(rcm.AIRCON_USAGE,2)) as AIRCON_USAGE,
                SUM(rcm.MAIN_USAGE) as MAIN_USAGE,
                SUM(rcm.GENERATOR_USAGE) as GENERATOR_USAGE
                FROM  SYS_REGION sr
                full join RES_MACHROOM rb
                ON  sr.CODE = rb.CITYID
                join  RPT_CAP_MACHROOM rcm
                ON  rcm.MACHROOMID = rb.ZGID
                GROUP BY sr.NAME, rcm.READTIME
          UNION
                      SELECT
               sr.NAME as EC_CITY_NAME,
               to_char(rcs.READTIME,'yyyymm') as EC_ITEM_TIME,
               '通信基站' as EC_ARENA_NAME,
               SUM(rcs.TOTAL_USAGE) as TOTAL_USAGE,
               SUM(round(rcs.AIRCON_USAGE,2)) as AIRCON_USAGE,
               SUM(rcs.MAIN_USAGE) as MAIN_USAGE,
               SUM(rcs.GENERATOR_USAGE) as GENERATOR_USAGE
              FROM  SYS_REGION sr
              full join RES_BTS rb
              ON  sr.CODE = rb.CITYID
              join  RPT_CAP_BTS rcs
              ON  rcs.BTSID = rb.ZGID
              GROUP BY sr.NAME, rcs.READTIME) tab1
          GROUP BY tab1.EC_CITY_NAME, tab1.EC_ITEM_TIME, tab1.EC_ARENA_NAME
          ) nowtime
          left join
        (SELECT tab1.EC_CITY_NAME as EC_CITY_NAME, to_char(add_months(to_date(tab1.EC_ITEM_TIME, 'yyyymm'),1),'yyyymm') as EC_ITEM_TIME,
            tab1.EC_ARENA_NAME as EC_ARENA_NAME,
            SUM(TOTAL_USAGE) as TOTAL_USAGE,
            SUM(AIRCON_USAGE) as AIRCON_USAGE,
            SUM(MAIN_USAGE) as MAIN_USAGE,
            SUM(GENERATOR_USAGE) as GENERATOR_USAGE
            FROM
            (SELECT
                sr.NAME as EC_CITY_NAME,
                to_char(rcm.READTIME,'yyyymm') as EC_ITEM_TIME,
                '通信机房' as EC_ARENA_NAME,
                SUM(rcm.TOTAL_USAGE) as TOTAL_USAGE,
                SUM(round(rcm.AIRCON_USAGE,2)) as AIRCON_USAGE,
                SUM(rcm.MAIN_USAGE) as MAIN_USAGE,
                SUM(rcm.GENERATOR_USAGE) as GENERATOR_USAGE
                FROM  SYS_REGION sr
                full join RES_MACHROOM rb
                ON  sr.CODE = rb.CITYID
                join  RPT_CAP_MACHROOM rcm
                ON  rcm.MACHROOMID = rb.ZGID
                GROUP BY sr.NAME, rcm.READTIME
          UNION
             SELECT
               sr.NAME as EC_CITY_NAME,
               to_char(rcs.READTIME,'yyyymm') as EC_ITEM_TIME,
               '通信基站' as EC_ARENA_NAME,
               SUM(rcs.TOTAL_USAGE) as TOTAL_USAGE,
               SUM(round(rcs.AIRCON_USAGE,2)) as AIRCON_USAGE,
               SUM(rcs.MAIN_USAGE) as MAIN_USAGE,
               SUM(rcs.GENERATOR_USAGE) as GENERATOR_USAGE
              FROM  SYS_REGION sr
              full join RES_BTS rb
              ON  sr.CODE = rb.CITYID
              join  RPT_CAP_BTS rcs
              ON  rcs.BTSID = rb.ZGID
              GROUP BY sr.NAME, rcs.READTIME) tab1
          GROUP BY tab1.EC_CITY_NAME, tab1.EC_ITEM_TIME, tab1.EC_ARENA_NAME
          )  lastmonth
        
          ON
           lastmonth.EC_CITY_NAME = nowtime.EC_CITY_NAME
             AND lastmonth.EC_ARENA_NAME = nowtime.EC_ARENA_NAME
             AND lastmonth.EC_ITEM_TIME = nowtime.EC_ITEM_TIME;