通信基站和通信技术机房

最新推荐文章于 2021-10-27 10:24:00 发布

wyqiang

最新推荐文章于 2021-10-27 10:24:00 发布

阅读量857

点赞数

本文链接：https://blog.youkuaiyun.com/wyqiang/article/details/79254813

版权

该博客内容涉及通信基站和通信机房的能耗统计。通过SQL查询，对比了当前月与上月在同一地区的通信基站和通信机房在总能耗、空调能耗、主设备能耗和发电机能耗上的使用情况，并计算了各项指标的月环比变化率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;