Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)
来源:互联网 发布:淘宝买兔子炒了 编辑:程序博客网 时间:2024/06/07 03:46
一、带空值的排列:
在前面《Oracle开发之分析函数(Rank、Dense_rank、row_number)》一文中,我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢?
SQL> select region_id, customer_id, sum(customer_sales) cust_sales, sum(sum(customer_sales)) over(partition by region_id) ran_total, rank() over(partition by region_id order by sum(customer_sales) desc) rank 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
我们看到这里有一条记录的CUST_TOTAL字段值为NULL,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句:
SQL> select region_id, customer_id, sum(customer_sales) cust_total, 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; 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 * from (select region_id, customer_id, sum(customer_sales) cust_total, rank() over(order by sum(customer_sales) desc NULLS LAST) rank from user_order group by region_id, customer_id) where rank <= 3; REGION_ID CUSTOMER_ID CUST_TOTAL RANK---------- ----------- ---------- ---------- 9 25 2232703 1 8 17 1944281 2 7 14 1929774 3SQL>
【2】找出每个区域订单总额排名前3的大客户:
SQL> select * from (select region_id, customer_id, sum(customer_sales) cust_total, 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) 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 318 rows selected.
三、First/Last排名查询:
想象一下下面的情形:找出订单总额最多、最少的客户。按照前面我们学到的知识,这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名,第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦?因为Rank函数只告诉我们排名的结果,却无法自动替我们从中筛选结果。
幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话:
SQL> select min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first, min(customer_id) keep (dense_rank last order by sum(customer_sales) desc) last from user_order 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, keep (dense_rank last order by sum(customer_sales) desc) last from user_order 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) keep(rank first order by sum(customer_sales) desc) first, min(region_id) keep(rank last order by sum(customer_sales) desc) last from user_order 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, customer_id, ntile(5) over(order by sum(customer_sales) desc) til from user_order 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
Ntil函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
- Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)
- 分析函数2之(Top/Bottom N、First/Last、NTile)
- Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
- Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
- Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
- Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
- Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
- ORACLE Top/Bottom N、First/Last、NTile
- oracle分析函数系列之Top/Bottom N、min() keep First/Last、NTile:排名空处理,查询第一名,最后一名
- 带空值的排列 (Top/Bottom N、First/Last、NTile) ——分析函数3
- oracle分析函数 rank函数,dense_rank函数,first函数,last函数,ntile函数求四分位数
- Oracle分析函数之first()和last()函数
- oracle 分析函数 NTILE
- Oracle分析函数ntile
- oracle 分析函数 keep(dense_rank first/last)
- Oracle分析函数——函数RANK,DENSE_RANK,FIRST,LAST
- 65.Oracle数据库SQL开发之 高级查询——使用FIRST和LAST函数
- ORACLE分析函数(4)---FIRST/LAST排名查询
- C和C++混合编译
- Android Studio教程从入门到精通
- Valid Palindrome
- 文章标题
- 手机浏览器调用手机qq客户端的js
- Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)
- 0531 linux系统command shell(2)
- 准备使用MyBatis进行开发
- c#日期操作
- nginx下无法打开php,报错[error] 5040#0: *1 connect() failed (111: Connection ref ...
- Java中正则表达式的分割案例
- Java常用编码规范
- scrollview + listview 只显示一行的解决办法
- 图像处理珍藏版本汇总