oracle 分组排序

直接在你原来的select里面加排序就好了，

select  (case when c.auditscore is not null then
                                  rank() over(partition  by (case when c.auditscore is not null then 1 else 2 end) order by
                                       c.auditscore desc )
                            else
                                         null
                            end )     ranking ，

                              (case
                                 when c.auditscore is not null then
                                  count(*) over(partition  by (case when c.auditscore is not null then 1 else 2 end) )
                                 else
                                null
                               end) count1,
                              (case
                                 when c.auditscore is not null then
                                  avg(c.auditscore) over(partition  by (case when c.auditscore is not null then 1 else 2 end) )
                                 else
                                null
                               end) avg1

from table c

解析：

   rank() over(partition  by (case when c.auditscore is not null then 1 else 2 end) order by c.auditscore desc )

看条件就是了 将auditscore字段分成2组（空和非空），然后分别 rank() over 排序（2组排序），接着再在外面筛选一层，将空的置成null。

补充partition  by deptid,channelid可以多字段分组比如根据部门编号和渠道分组

同理count(*) avg 等都可以用