一、带空值的排列：
在前面《Oracle开发之分析函数(Rank、Dense_rank、row_number)》一文中，我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢？
sum(customer_sales) cust_sales,
sum(sum(customer_sales)) over(partition by region_id) ran_total,
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
from user_order
group by region_id, customer_id;</div>
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK
---------- ----------- ---------- ---------- ----------
10 31 6238901 1
10 26 1808949 6238901 2
10 27 1322747 6238901 3
10 30 1216858 6238901 4
10 28 986964 6238901 5
10 29 903383 6238901 6
我们看到这里有一条记录的CUST_TOTAL字段值为NULL，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：
sum(customer_sales) cust_total,
sum(sum(customer_sales)) over(partition by region_id) reg_total,
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id;</div>
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
10 28 986964 6238901 4
10 29 903383 6238901 5
10 31 6238901 6
绿色高亮处，NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
注意是NULLS，不是NULL。
二、Top/Bottom N查询：
在日常的工作生产中，我们经常碰到这样的查询：找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示：
【1】找出所有订单总额排名前3的大客户：
from (select region_id,
customer_id,
sum(customer_sales) cust_total,
rank() over(order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id)
where rank <= 3;</div>
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
---------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3
SQL>
【2】找出每个区域订单总额排名前3的大客户：
from (select region_id,
customer_id,