设备存量数据来源(综合资源) Mr

/**
数据抽取规则：统计一下各表的数据记录总和为设备数量.
配电柜:rmw2.RMS_HIGHCAB+rmw2.RMS_LOWCAB+rmw2.RMS_DCCAB+rmw2.RMS_ACCAB
蓄电池组:rmw2.RMS_BATTERY
空调:rmw2.RMS_GENERALAIR+rmw2.RMS_DEDICATEAIR
发电设备:rmw2.RMS_MOBILEGENE+rmw2.RMS_GENERATOR
月环比算法上月设备数量/上上月设备数量
年同比算法上月设备数量/去年上月设备数量
**/

select '配电柜' as 设备类型,上月设备数量,
round(上月设备数量/decode(上上月设备数量,0,1,上上月设备数量)*100,2) as 月环比,
round(上月设备数量/decode(去年上月设备数量,0,1,去年上月设备数量)*100,2) as 年同比 from 

--取上月设备总量
(select aaa+bbb+ccc+ddd  as 上月设备数量 from 
(select count(*) as aaa from rmw2.RMS_HIGHCAB where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm') ) a,
(select count(*) as bbb from rmw2.RMS_LOWCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm'))b,
(select count(*) as ccc from rmw2.RMS_DCCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) c,
(select count(*) as ddd from rmw2.RMS_ACCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) d),
--取上上月设备总量
(select aaa+bbb+ccc+ddd  as 上上月设备数量 from 
(select count(*) as aaa from rmw2.RMS_HIGHCAB where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm') ) a,
(select count(*) as bbb from rmw2.RMS_LOWCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm'))b,
(select count(*) as ccc from rmw2.RMS_DCCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) c,
(select count(*) as ddd from rmw2.RMS_ACCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) d),
--取去年上月设备总量
(select aaa+bbb+ccc+ddd  as 去年上月设备数量 from 
(select count(*) as aaa from rmw2.RMS_HIGHCAB where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm') ) a,
(select count(*) as bbb from rmw2.RMS_LOWCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm'))b,
(select count(*) as ccc from rmw2.RMS_DCCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) c,
(select count(*) as ddd from rmw2.RMS_ACCAB
where stateflag=0 
and status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(time_stamp, 'yyyy-mm')) d)
--连接蓄电池组数据
union all
select '蓄电池组' as 设备类型,上月设备数量,
round(上月设备数量/decode(上上月设备数量,0,1,上上月设备数量)*100,2) as 月环比,
round(上月设备数量/decode(去年上月设备数量,0,1,去年上月设备数量)*100,2) as 年同比 from 

(select count(*) as 上月设备数量 from rmw2.RMS_BATTERY a 
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm')),

(select count(*) as 上上月设备数量 from rmw2.RMS_BATTERY a 
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm') ),

(select count(*) as 去年上月设备数量 from rmw2.RMS_BATTERY a 
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm'))
union all
--连接空调数量
select '空调' as 设备类型,上月设备数量,
round(上月设备数量/decode(上上月设备数量,0,1,上上月设备数量)*100,2) as 月环比,
round(上月设备数量/decode(去年上月设备数量,0,1,去年上月设备数量)*100,2) as 年同比 from 

(select count(*) as 上月设备数量 from rmw2.RMS_GENERALAIR a ,rmw2.RMS_DEDICATEAIR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm')),

(select count(*) as 上上月设备数量 from rmw2.RMS_GENERALAIR a ,rmw2.RMS_DEDICATEAIR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm') ),

(select count(*) as 去年上月设备数量 from rmw2.RMS_GENERALAIR a ,rmw2.RMS_DEDICATEAIR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm'))
union all
--连接发电设备
select '发电设备' as 设备类型,上月设备数量,
round(上月设备数量/decode(上上月设备数量,0,1,上上月设备数量)*100,2) as 月环比,
round(上月设备数量/decode(去年上月设备数量,0,1,去年上月设备数量)*100,2) as 年同比 from 

(select count(*) as 上月设备数量 from rmw2.RMS_MOBILEGENE a ,rmw2.RMS_GENERATOR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -1), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm')),

(select count(*) as 上上月设备数量 from rmw2.RMS_MOBILEGENE a ,rmw2.RMS_GENERATOR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -2), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm') ),

(select count(*) as 去年上月设备数量 from rmw2.RMS_MOBILEGENE a ,rmw2.RMS_GENERATOR b
where a.stateflag=0 
and a.status not in ('退网')
and to_char(add_months(sysdate-5, -13), 'yyyy-mm') = to_char(a.time_stamp, 'yyyy-mm'))

/**
下面的类型都在一个表中，那么我们可以利用group by的方式
但是做计算的时候还是要进行分开查询
SCP:rmw2.RMS_AINET_COMMON_NE
SMP:rmw2.RMS_AINET_COMMON_NE
VC:rmw2.RMS_AINET_COMMON_NE
SDP:rmw2.RMS_AINET_COMMON_NE
**/



select 设备类型2 as 设备类型,
       round(上月设备数量 / decode(上上月设备数量, '', 1, 上上月设备数量) * 100,
             2) as 月环比,
       round(上月设备数量 / decode(去年上月设备数量, '', 1, 去年上月设备数量) * 100,
             2) as 年同比
  from (select 设备类型2, 上月设备数量, 上上月设备数量
          from (select t.equ_type as 设备类型1, count(*) as 上月设备数量
                  from rmw2.RMS_AINET_COMMON_NE t
                 where t.equ_type in ('SCP', 'SMP', 'VC', 'SDP')
                   and stateflag = 0
                   and status not in ('退网')
                   and to_char(add_months(sysdate - 5, -1), 'yyyy-mm') =
                       to_char(time_stamp, 'yyyy-mm')
                 GROUP BY t.equ_type) a
         right join (select t.equ_type as 设备类型2,
                           count(*) as 上上月设备数量
                      from rmw2.RMS_AINET_COMMON_NE t
                     where t.equ_type in ('SCP', 'SMP', 'VC', 'SDP')
                       and stateflag = 0
                       and status not in ('退网')
                       and to_char(add_months(sysdate - 5, -2), 'yyyy-mm') =
                           to_char(time_stamp, 'yyyy-mm')
                     GROUP BY t.equ_type) b
            on a.设备类型1 = b.设备类型2) d
  left join (select t.equ_type as 设备类型, count(*) as 去年上月设备数量
               from rmw2.RMS_AINET_COMMON_NE t
              where t.equ_type in ('SCP', 'SMP', 'VC', 'SDP')
                and stateflag = 0
                and status not in ('退网')
                and to_char(add_months(sysdate - 5, -13), 'yyyy-mm') =
                    to_char(time_stamp, 'yyyy-mm')
              GROUP BY t.equ_type) c
    on d.设备类型2 = c.设备类型

其实我们还可以通过case when 的方式简单的实现上边的逻辑：

/**
USE THE CASE WHEN 
SCP:rmw2.RMS_AINET_COMMON_NE
SMP:rmw2.RMS_AINET_COMMON_NE
VC:rmw2.RMS_AINET_COMMON_NE
SDP:rmw2.RMS_AINET_COMMON_NE
**/


select t.equ_type,上月设备数量,
round(上月设备数量/decode(上上月设备数量,0,1,上上月设备数量)*100,2) as 月环比,
round(上月设备数量/decode(去年上月设备数量,0,1,去年上月设备数量)*100,2) as 年同比 from 
(select a.equ_type,
      count(case
               when to_char(add_months(sysdate - 5, -1), 'yyyy-mm') =
                    to_char(a.time_stamp, 'yyyy-mm') then
                1
               else
                null
             end) 上月设备数量,
       
       count(case
               when to_char(add_months(sysdate - 5, -2), 'yyyy-mm') =
                    to_char(a.time_stamp, 'yyyy-mm') then
                1
               else
                null
             end) 上上月设备数量,
       
       count(case
               when to_char(add_months(sysdate - 5, -13), 'yyyy-mm') =
                    to_char(a.time_stamp, 'yyyy-mm') then
                1
               else
                null
             end) 去年上月设备数量

  from rmw2.RMS_AINET_COMMON_NE a
 where a.stateflag = 0
   and a.status not in ('退网')
   and a.equ_type in ('SCP', 'SMP', 'VC', 'SDP')
   group by a.equ_type) t

上边的例子是在where条件中使用case when 当然case  when也可以用在 group by 后

SELECT  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END salary_class, -- 别名命名
COUNT(*)  
FROM    Table_A  
GROUP BY  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END;  

从上边的实现可以看出知识面广的话马上就提高了生产力，而且这个的运行效率方面要比做uoion和left JOIN 的速度快很多，加油吧！