渠道发展统计报表sql(oracle)

select tn,
       channalName,
       decode(sucRate, null, '0%', round(sucRate * 100, 2) || '%') sucRate,
       pointsTotal,
       userTotal,
       recommendTotal,
       recommendSuc,
       validPoints,
       vaildUser,
       activePoints,
       warnPoints,
       onlinePoints,
       RANK() OVER(order by nvl(recommendTotal, 0) desc) as recommendTotalnum,
       RANK() OVER(order by nvl(recommendSuc, 0) desc) as recommendSucnum,
       RANK() OVER(order by nvl(sucRate, 0) desc) as sucRatenum,
       RANK() OVER(order by nvl(pointOnline, 0) desc) as pointOnlinenum,
       RANK() OVER(order by nvl(pointCapacity, 0) desc) as pointCapacitynum,
       round(pointCapacity, 2) pointCapacity,
       round(pointOnline * 100, 2) pointOnline
  from (select rownum tn,
               c.channel_name channalName,
               decode(recommendTotal, 0, 0, recommendSuc / recommendTotal) sucRate,
               a.pointsTotal,
               a.userTotal,
               b.*,
               decode(validPoints, 0, 0, recommendSuc / validPoints) pointCapacity,
               decode(pointsTotal, 0, 0, validPoints / pointsTotal) pointOnline
          from sys_organise_channel c,
               (select p.channel_type,
                       count(distinct p.organise_id) pointsTotal,
                       count(distinct case
                               when u.flag = '0' then
                                u.emp_no
                               else
                                null
                             end) userTotal
                  from sys_organise_info    o,
                       tview_organise_point p,
                       sys_user_info        u
                 where 1 = 1
                   and o.organise_id = 1
                   and instr(p.organise_code, o.organise_Code) = 1
                   and p.organise_id = u.organise_id(+)
                 group by p.channel_type) a,
               (select channel_type,
                       sum(recommendTotal) recommendTotal,
                       sum(recommendSuc) recommendSuc,
                       count(distinct organise_id) onlinePoints,
                       sum(vaildUser) vaildUser,
                       sum(case
                             when recommendSuc > 0 then
                              1
                             else
                              0
                           end) validPoints,
                       sum(case
                             when recommendSuc >= 50 then
                              1
                             else
                              0
                           end) activePoints,
                       sum(case
                             when decode(recommendTotal,
                                         0,
                                         0,
                                         recommendSuc / recommendTotal) <= 0.1 then
                              1
                             else
                              0
                           end) warnPoints
                  from (select channel_type,
                               organise_id,
                               sum(alls) recommendTotal,
                               sum(suc) recommendSuc,
                               sum(case
                                     when suc > 0 then
                                      1
                                     else
                                      0
                                   end) vaildUser
                          from (select r.channel_type,
                                       r.organise_id,
                                       r.emp_no,
                                       sum(r.recommend_total) alls,
                                       sum(r.open_suc_total) suc
                                  from stat_recomend_day r, sys_organise_info o
                                 where 1 = 1
                                   and o.organise_id = 1
                                   and instr(r.organise_code, o.organise_Code) = 1
                                 group by r.channel_type,
                                          r.organise_id,
                                          r.emp_no)
                         group by channel_type, organise_id) t
                 group by channel_type) b
         where c.channel_id = a.channel_type(+)
           and a.channel_type = b.channel_type(+))
 order by tn asc