接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;

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

可以看出,没有指定排序,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;
ORACLE分析函数实战(Analytical Functions)_PART5-LMLPHP

我们可以看出,最后的执行结果对null值进行了处理,nullsfirst

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

 

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

 

SELECT * from

 (SELECTregion_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_numberbetween 2 and 5;--外层过滤

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

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


未完待续,见PART6:http://chinaunix.net/uid-7655508-id-5834920.html


09-02 02:00
查看更多