目录
===============================================
1.带空值的排列
2.Top/Bottom N查询
3.First/Last排名查询
4.按层次查询
一、带空值的排列:
在前面《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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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_SALES字段值为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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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
;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
--
-------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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
ntile函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
转载自:http://www.blogjava.net/pengpenglin/archive/2008/06/27/211019.html