ORACLE分析函数实战(Analytical Functions)_PART5

接PART4：http://blog.chinaunix.net/uid-7655508-id-5834840.html

1.3.2.2 NULL的处理

分析函数中的分析子句的order by默认升序，则默认是nulls last，降序默认是nulls first。这和普通order by一样，如果不指定排序，那么是升序。

 

    --script

drop table test;

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

insert into test values(1,'dj');

insert into test values(1,'dj');

insert into test values(1,'dj1');

insert into test values(2,'dj1');

insert into test values(3,'dj2');

insert into test values(4,'dj3');

insert into test values(null,'dj');

insert into test values(null,'dj1');

commit;

下面我们查询一下，看看null对查询结果的影响。

select id,name,row_number() over(order by id) rrank,

dense_rank() over(order by id) drank,
rank() over(order by id) rank from test;



可以看出，没有指定排序，null默认是按nulls last排序。下面看指定排序之后的情况：

select id,name,row_number() over(order by id nulls first) rrank,

dense_rank() over(order by id) drank,

rank() over(order by id desc nulls first) rank from test order by rrank;


我们可以看出，最后的执行结果对null值进行了处理，nulls first。

1.3.2.3 TOP/BOTTOM-N查询
  Ranking分析函数常用于求top/bottom-n问题，这类问题可以使用rownum伪列来实现，但是使用ranking分析函数，可以更加简单，效率更好，而且对于解决复杂的top/bottom-n问题更加有效。

 

由于分析函数不能在where和having中出现，那么我们用等级函数处理这类问题，只能将排名查询出来作为内层查询，然后在外层查询用条件过滤。如：求2001年订单按区域分组的订单总量在第2到第5的四个区域的情况：

 

SELECT * from

 (SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  ROW_NUMBER(  ) OVER ( ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr) inn  --内层查询排名
where inn.sales_number between 2 and 5;--外层过滤

  由上面可以看出，使用分析函数，没有rownum伪列的一些限制，比如使用rownum实现中间几行，必须要使用三重嵌套查询。比如rownum是排名然后排序，如果rownum和order by再一层，很可能排名乱掉，然而分析函数不会有此情况，我们可以对分析子句应用order by。所以，使用分析排名函数解决top-n和bottom-n问题更简单。

未完待续，见PART6：