接PART4:http://blog.chinaunix.net/uid-7655508-id-5834840.html
1.3.2.2 NULL的处理
分析函数中的分析子句的order by默认升序,则默认是nulls last,降序默认是nulls first。这和普通order by一样,如果不指定排序,那么是升序。
--script
droptabletest;
createtabletest(idnumber,namevarchar2(10));
insertintotestvalues(1,'dj');
insertintotestvalues(1,'dj');
insertintotestvalues(1,'dj1');
insertintotestvalues(2,'dj1');
insertintotestvalues(3,'dj2');
insertintotestvalues(4,'dj3');
insertintotestvalues(null,'dj');
insertintotestvalues(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: