ORACLE Top/Bottom N、First/Last、NTile

最新推荐文章于 2021-05-27 18:28:14 发布

wh62592855

最新推荐文章于 2021-05-27 18:28:14 发布

阅读量1.3k

点赞数

分类专栏： Oracle Dev 文章标签： oracle sql user null 工作

Oracle Dev 专栏收录该内容

71 篇文章 0 订阅

订阅专栏

目录
==================================================================
1.带空值的排列
2.Top/Bottom N查询
3.First/Last排名查询
4.按层次查询

一、带空值的排列：

假如被排列的数据中含有空值呢？

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, custom

er_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
         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的大客户：
SQL> select *
SQL>   from (select region_id,
SQL>                customer_id,
SQL>                sum(customer_sales) cust_total,
SQL>                rank() over(order by sum(customer_sales) desc NULLS LAST) rank
SQL>           from user_order
SQL>          group by region_id, customer_id)
SQL> where rank <= 3;

REGION_ID CUSTOMER_ID CUST_TOTAL       RANK
---------- ----------- ---------- ----------
          9          25    2232703          1
          8          17    1944281          2
          7          14    1929774          3

SQL>

【2】找出每个区域订单总额排名前3的大客户：
SQL> select *
   2    from (select region_id,
   3                 customer_id,
   4                 sum(customer_sales) cust_total,
   5                 sum(sum(customer_sales)) over(partition by region_id) reg_total,
   6                 rank() over(partition by region_id
                                order by sum(customer_sales) desc NULLS LAST) rank
   7            from user_order
   8           group by region_id, customer_id)
   9   where rank <= 3;

REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
          5           4    1878275    5585641          1
          5           2    1224992    5585641          2
          5           5    1169926    5585641          3
          6           6    1788836    6307766          1
          6           9    1208959    6307766          2
          6          10    1196748    6307766          3
          7          14    1929774    6868495          1
          7          13    1310434    6868495          2
          7          15    1255591    6868495          3
          8          17    1944281    6854731          1
          8          20    1413722    6854731          2
          8          18    1253840    6854731          3
          9          25    2232703    6739374          1
          9          23    1224992    6739374          2
          9          24    1224992    6739374          2
         10          26    1808949    6238901          1
         10          27    1322747    6238901          2
         10          30    1216858    6238901          3

18 rows selected.

三、First/Last排名查询：

想象一下下面的情形：找出订单总额最多、最少的客户。按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名，第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦？因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。

幸好Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题。还是用实例说话：
SQL> select min(customer_id)
   2         keep (dense_rank first order by sum(customer_sales) desc) first,
   3         min(customer_id)
   4         keep (dense_rank last order by sum(customer_sales) desc) last
   5    from user_order
   6   group by customer_id;

      FIRST       LAST
---------- ----------
         31          1

这里有几个看起来比较疑惑的地方：

①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能换成rank吗？

首先解答一下第一个问题：min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢？
SQL> select keep (dense_rank first order by sum(customer_sales) desc) first,
   2             keep (dense_rank last order by sum(customer_sales) desc) last
   3    from user_order
   4   group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first,
                         *
ERROR at line 1:
ORA-00907: missing right parenthesis

接下来看看第2个问题：keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。

那么什么才是符合条件的记录呢？这就是第3个问题了。dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。

第4个问题：如果我们把dense_rank换成rank呢？
SQL> select min(region_id)
   2          keep(rank first order by sum(customer_sales) desc) first,
   3         min(region_id)
   4          keep(rank last order by sum(customer_sales) desc) last
   5    from user_order
   6   group by region_id;
select min(region_id)
*
ERROR at line 1:
ORA-02000: missing DENSE_RANK

四、按层次查询：
现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N，第一个，最后一个记录。有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。

很熟悉是不？我们马上会想到第二点中提到的方法，可是rank函数只为我们做好了排名，并不知道每个排名在总排名中的相对位置，这时候就引入了另外一个分析函数NTile，下面我们就以上面的需求为例来讲解一下：
SQL> select region_id,
   2         customer_id,
   3         ntile(5) over(order by sum(customer_sales) desc) tile
   4    from user_order
   5   group by region_id, customer_id;

REGION_ID CUSTOMER_ID       TILE
---------- ----------- ----------
         10          31          1
          9          25          1
         10          26          1
          6           6          1
          8          18          2
          5           2          2
          9          23          3
          6           9          3
          7          11          3
          5           3          4
          6           8          4
          8          16          4
          6           7          5
         10          29          5
          5           1          5