首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)

2013-07-09 
Oracle开发专题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)目录

Oracle开发专题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)

目录
===============================================

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

一、带空值的排列:

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

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?region_id,?customer_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2?????????sum(customer_sales)?cust_sales,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????sum(sum(customer_sales))?over(partition?by?region_id)?ran_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4?????????rank()?over(partition?by?region_id
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5??????????????????order?by?sum(customer_sales)?desc)?rank
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??6????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??7???group?by?region_id,?customer_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?REGION_ID?CUSTOMER_ID?CUST_SALES??RAN_TOTAL???????RANK
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?-----------?----------?----------?----------?
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????31??????????????????? 6238901??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????26????1808949????6238901??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????27????1322747????6238901??????????3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????30????1216858????6238901??????????4
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????28?????986964????6238901??????????5
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????29?????903383????6238901??????????6

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

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?region_id,?customer_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2?????????sum(customer_sales)?cust_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????sum(sum(customer_sales))?over(partition?by?region_id)?reg_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4?????????rank()?over(partition?by?region_id?
????????????????????????order?by?sum(customer_sales)?desc?NULLS?LAST)?rank
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5????????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??6???????group?by?region_id,?customer_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?REGION_ID?CUSTOMER_ID?CUST_TOTAL??REG_TOTAL???????RANK
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?-----------?----------?----------?----------
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????26????1808949??? ?6238901?????????? 1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????27????1322747????6238901?????????? 2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????30????1216858????6238901?????????? 3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????28?????986964???? 6238901?????????? 4
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????29?????903383???? 6238901?????????? 5
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????31???????????????????? 6238901???????????6

绿色高亮处,NULLS LAST/FIRST告诉Oracle让空值排名最后或第一。

注意是NULLS,不是NULL。

?

二、Top/Bottom N查询:

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

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

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?*
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>???from?(select?region_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>????????????????customer_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>????????????????sum(customer_sales)?cust_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>????????????????rank()?over(order?by?sum(customer_sales)?desc?NULLS?LAST)?rank
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>???????????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>??????????group?by?region_id,?customer_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>??where?rank?<=?3;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?REGION_ID?CUSTOMER_ID?CUST_TOTAL???????RANK
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?-----------?----------?----------
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????25????2232703??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????17????1944281??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????7??????????14????1929774??????????3

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?


【2】找出每个区域订单总额排名前3的大客户:

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?*
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2????from?(select?region_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????????????customer_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4?????????????????sum(customer_sales)?cust_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5?????????????????sum(sum(customer_sales))?over(partition?by?region_id)?reg_total,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??6?????????????????rank()?over(partition?by?region_id
??????????????????????????????? order?by?sum(customer_sales)?desc?NULLS?LAST)?rank
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??7????????????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??8???????????group?by?region_id,?customer_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??9???where?rank?<=?3;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?REGION_ID?CUSTOMER_ID?CUST_TOTAL??REG_TOTAL???????RANK
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?-----------?----------?----------?----------
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????5???????????4????1878275????5585641??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????5???????????2????1224992????5585641??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????5???????????5????1169926????5585641??????????3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????6????1788836????6307766??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????9????1208959????6307766??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6??????????10????1196748????6307766??????????3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????7??????????14????1929774????6868495??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????7??????????13????1310434????6868495??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????7??????????15????1255591????6868495??????????3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????17????1944281????6854731??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????20????1413722????6854731??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????18????1253840????6854731??????????3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????25????2232703????6739374??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????23????1224992????6739374??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????24????1224992????6739374??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????26????1808949????6238901??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????27????1322747????6238901??????????2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????30????1216858????6238901??????????3

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)18?rows?selected.


三、First/Last排名查询:

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

幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话:

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?min(customer_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2?????????keep?(dense_rank?first?order?by?sum(customer_sales)?desc)?first,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????min(customer_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4?????????keep?(dense_rank?last?order?by?sum(customer_sales)?desc)?last
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??6???group?by?customer_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????FIRST???????LAST
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?----------
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????31??????????1


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

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

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

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?keep?(dense_rank?first?order?by?sum(customer_sales)?desc)?first,?
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2???????????? keep?(dense_rank?last?order?by?sum(customer_sales)?desc)?last
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4???group?by?customer_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)select?keep?(dense_rank?first?order?by?sum(customer_sales)?desc)?first,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????????????????????*
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)ERROR?at?line?1:
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)ORA-00907:?missing?right?parenthesis


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

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

第4个问题:如果我们把dense_rank换成rank呢?

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?min(region_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2??????????keep(rank?first?order?by?sum(customer_sales)?desc)?first,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????min(region_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4??????????keep(rank?last?order?by?sum(customer_sales)?desc)?last
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??6???group?by?region_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)select?min(region_id)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)*
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)ERROR?at?line?1:
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)ORA-02000:?missing?DENSE_RANK


四、按层次查询:

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

很熟悉是不?我们马上会想到第二点中提到的方法,可是rank函数只为我们做好了排名,并不知道每个排名在总排名中的相对位置,这时候就引入了另外一个分析函数ntile,下面我们就以上面的需求为例来讲解一下:

Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)SQL>?select?region_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??2?????????customer_id,
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??3?????????ntile(5)?over(order?by?sum(customer_sales)?desc)?tile
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??4????from?user_order
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)??5???group?by?region_id,?customer_id;
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?REGION_ID?CUSTOMER_ID???????TILE
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)----------?-----------?----------
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????31??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????25?????????? 1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????26??????????1
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????6??????????? 1?????????
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????18?????????? 2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????5???????????2??????????? 2
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????9??????????23?????????? 3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????9??????????? 3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????7??????????11?????????? 3
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????5???????????3??????????? 4
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????8??????????? 4
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????8??????????16?????????? 4
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????6???????????7??????????? 5
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)????????10??????????29??????????5
Oracle开发课题之:分析函数3(Top/Bottom N、First/Last、NTile) (转载)?????????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

?

热点排行