关闭

带空值的排列 (Top/Bottom N、First/Last、NTile) ——分析函数3

667人阅读 评论(0) 收藏 举报

一、带空值的排列:

1、测试环境

SQL> desc user_order;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                          NUMBER(2)
 CUSTOMER_ID                                        NUMBER(2)
 CUSTOMER_SALES                                     NUMBER

SQL> select * from user_order;

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
        10          29         903383
        10          28         986964
        10          30        1216858
        10          27        1322747
        10          26        1808949
        10          31

已选择6行。


我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢?

SQL> select region_id,
  2         customer_id,
  3         sum(customer_sales) cust_sales,
  4         sum(sum(customer_sales)) over(partition by region_id) ran_total,
       rank() over(partition by region_id order by sum(customer_sales) desc) rank
  6    from user_order 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

已选择6行。

我们看到这里有一条记录的CUST_TOTAL字段值为NULL,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句:

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

 REGION_ID CUSTOMER_ID CUST_SALES  RAN_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

已选择6行。
NULLS LAST/FIRST告诉Oracle让空值排名最后。注意是NULLS,不是NULL

二、Top/Bottom N查询:

1、测试环境

create table user_order(
  EGION_ID NUMBER(2),
  CUSTOMER_ID NUMBER(2),
  CUSTOMER_SALES NUMBER
);

2、测试数据
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,1,151162);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,29,903383);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,7,971585);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,28,986964);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,21,1020541);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,22,1036146);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,16,1068467);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,8,1141638);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,3,1161286);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,5,1169926);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,19,1174421);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,12,1182275);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,11,1190421);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,10,1196748);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,9,1208959);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,30,1216858);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,2,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,24,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,23,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,18,1253840);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,15,1255591);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,13,1310434);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,27,1322747);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,20,1413722);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,6,1788836);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,26,1808949);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,4,1878275);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,14,1929774);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,17,1944281);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,25,2232703);

在日常的工作生产中,我们经常碰到这样的查询:找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示:

【1】找出所有订单总额排名前3的大客户:

SQL> select *
  2    from (select region_id,
  3                 customer_id,
  4                 sum(customer_sales) cust_total,
               rank() over(order by sum(customer_sales) desc NULLS LAST) rank
  6           group by region_id, customer_id)
          from user_order
  7           group by region_id, customer_id)
  8   where rank <= 3;

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

【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,
               rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank
          from user_order
         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

 REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
         8          18    1253840    6854731          3
         9          25    2232703    6739374          1
         9          24    1224992    6739374          2
         9          23    1224992    6739374          2
        10          26    1808949    6238901          1
        10          27    1322747    6238901          2
        10          30    1216858    6238901          3

已选择18行。

三、First/Last排名查询:

想象一下下面的情形:找出订单总额最多、最少的客户。按照前面我们学到的知识,这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名,第二个查询按照订单总额降序排列以期拿到最后一名。是不是很烦?因为Rank函数只告诉我们排名的结果,却无法自动替我们从中筛选结果。幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话:

select min(customer_id) keep(dense_rank first order by sum(customer_sales) desc) first,
  2         min(customer_id) keep(dense_rank last order by sum(customer_sales) desc) last
  3    from user_order
  4   group by customer_id;

     FIRST       LAST
---------- ----------
        25          1

这里有几个看起来比较疑惑的地方:
为什么这里要用min函数

首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢?
Keep这个东西是干什么的

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

那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。
dense_rankrank()有什么不同,能换成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) til
  4    from user_order
  5   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID        TIL
---------- ----------- ----------
         9          25          1
         8          17          1
         7          14          1
         5           4          1
        10          26          1
         6           6          1
         8          20          2
        10          27          2
         7          13          2
         7          15          2
         8          18          2

 REGION_ID CUSTOMER_ID        TIL
---------- ----------- ----------
         9          23          2
         9          24          3
         5           2          3
        10          30          3
         6           9          3
         6          10          3
         7          11          3
         7          12          4
         8          19          4
         5           5          4
         5           3          4

 REGION_ID CUSTOMER_ID        TIL
---------- ----------- ----------
         6           8          4
         8          16          4
         9          22          5
         9          21          5
        10          28          5
         6           7          5
        10          29          5
         5           1          5

已选择30行。
Ntil函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1008177次
    • 积分:15766
    • 等级:
    • 排名:第665名
    • 原创:568篇
    • 转载:91篇
    • 译文:2篇
    • 评论:67条