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

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

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

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

NULLS LAST/FIRST告诉Oracle让空值排名最后。注意是NULLS，不是NULL

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);


【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

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

Keep这个东西是干什么的

fist/last是干什么的

dense_rankrank()有什么不同，能换成rank吗？

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

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

• 本文已收录于以下专栏：

举报原因： 您举报文章：带空值的排列 (Top/Bottom N、First/Last、NTile) ——分析函数3 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)