销售模块-新增客户统计

新增客户即为首次投资日期不为空以及客户状态为1以及客户类型不为空的客户

select s.yf 月份,
           s.xzkh 新增客户, s.grkh 个人客户, s.jgkh 机构客户
  from (select to_char(to_date(a.yf,'yyyymm'),'yyyy-mm') yf,
               to_char((case
                 when a.yf = b.yf then
                  a.xzkh + b.rs+4
                 else
                  a.xzkh
               end),'fm99999')xzkh,
               case
                 when a.yf = b.yf then
                  a.grkh + b.rs
                 else
                  a.grkh
               end grkh,
               case when a.yf=b.yf then
                  a.jgkh+4
                  else
                  a.jgkh end jgkh 
          from (select floor(kh.sctzrq / 100) yf,
                       count(1) xzkh,
                       sum(case
                             when kh.khlx = 0 then
                              1
                             else
                              0
                           end) grkh,
                       sum(case
                             when kh.khlx = 1 then
                              1
                             else
                              0
                           end) jgkh
                
                  from tkhxx kh
                 where kh.sctzrq is not null
                   and kh.khzt = 1
                    and floor(kh.sctzrq/100)>=201901  and floor(kh.sctzrq/100)<=201910
                 --  and kh.khlx is not null
                   
                 group by floor(kh.sctzrq / 100)) a,
               khtj b
         where a.yf = b.yf(+)
        
        union
        select null yf, to_char(sum(c.xzkh),'fm99999') xzkh, sum(grkh) grkh, sum(jgkh) jgkh
          from --order by floor(kh.sctzrq/100)
               (select a.yf yf,
                       case
                         when a.yf = b.yf then
                          a.xzkh + b.rs+4
                         else
                          a.xzkh
                       end xzkh,
                       case
                         when a.yf = b.yf then
                          a.grkh + b.rs
                         else
                          a.grkh
                       end grkh,
                       case when a.yf=b.yf then
                       a.jgkh+4
                       else
                       a.jgkh end  jgkh
                  from (select floor(kh.sctzrq / 100) yf,
                               count(1) xzkh,
                               sum(case
                                     when kh.khlx = 0 then
                                      1
                                     else
                                      0
                                   end) grkh,
                               sum(case
                                     when kh.khlx = 1 then
                                      1
                                     else
                                      0
                                   end) jgkh
                        
                          from tkhxx kh
                         where kh.sctzrq is not null
                           and kh.khzt = 1
                           and kh.khlx is not null
                            and floor(kh.sctzrq/100)>=201901  and floor(kh.sctzrq/100)<=201910
                         group by floor(kh.sctzrq / 100)) a,
                       khtj b
                 where a.yf = b.yf(+)) c
                 union
                 select null yf,'去年新增客户:'||to_char(count(1),'fm99999') xzkh,null grkh,null jgkh 
                 from tkhxx kh where kh.sctzrq is not null
                             and kh.khzt=1 
                              and floor(kh.sctzrq/100)>=201801 and floor(kh.sctzrq/100)<=201811) s
 order by s.yf