Oracle应用专题之：分析函数3(Top/Bottom N、First/Last、NTile)
一、带空值的排列：

在前面《Oracle开发专题之：分析函数2(Rank、Dense_rank、row_number)》一文中，我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢？

SQL> select region_id, customer_id,
  2         sum(customer_sales) cust_sales,
  3         sum(sum(customer_sales)) over(partition by region_id) ran_total,
  4         rank() over(partition by region_id
  5                  order by sum(customer_sales) desc) rank
  6    from user_order
  7   group by region_id, customer_id;

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，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：
SQL> select region_id, customer_id,
  2         sum(customer_sales) cust_total,
  3         sum(sum(customer_sales)) over(partition by region_id) reg_total,
  4         rank() over(partition by region_id
                        order by sum(customer_sales) desc NULLS LAST) rank
  5        from user_order
  6       group by region_id, customer_id;

REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
        10          26    1808949     6238901           1
        10          27    1322747    6238901           2
        10          30    1216858    6238901           3