--排序分析函数中对null的处理

--分析:对于null在分析函数中是升序默认是nulls last，降序默认是nulls first。如果不指定排序，那么是升序

create table test(id number,name varchar2(20));

insert into test values(1,'测试1');

insert into test values(1,'测试2');

insert into test values(1,'测试3');

insert into test values(2,'测试4');

insert into test values(2,'测试5');

insert into test values(3,'测试6');

insert into test values(3,'测试7');

insert into test values(4,'测试8');

insert into test values(null,'测试9');

insert into test values(null,'测试10');

commit;

--null默认是按nulls last升序

SQL> select id,

  2         name,

  3         row_number()over(order by id) rn,

  4         dense_rank()over(order by id) drk,

  5         rank()over(order by id) rk

  6    from test;

        ID NAME                         RN        DRK         RK

---------- -------------------- ---------- ---------- ----------

         1 测试1                         1          1          1

         1 测试2                         2          1          1

         1 测试3                         3          1          1

         2 测试4                         4          2          4

         2 测试5                         5          2          4

         3 测试6                         6          3          6

         3 测试7                         7          3          6

         4 测试8                         8          4          8

           测试9                         9          5          9

           测试10                       10          5          9

10 rows selected

--最后的执行结果对null值进行了处理，nulls first(降序)

SQL> select id,

  2         name,

  3         row_number()over(order by id nulls first) rn,

  4         dense_rank()over(order by id) drk,

  5         rank()over(order by id desc nulls first) rk

  6    from test;

        ID NAME                         RN        DRK         RK

---------- -------------------- ---------- ---------- ----------

           测试10                        1          5          1

           测试9                         2          5          1

         1 测试2                         3          1          8

         1 测试1                         4          1          8

         1 测试3                         5          1          8

         2 测试4                         6          2          6

         2 测试5                         7          2          6

         3 测试6                         8          3          4

         3 测试7                         9          3          4

         4 测试8                        10          4          3

10 rows selected