Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)
一、带空值的排列:在前面《》一文中,我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢? 代码如下: select region_id,customer_id, REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK 我们看到这里有一条记录的CUST_TOTAL字段值为NULL,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句: 代码如下: select region_id, REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK 绿色高亮处,NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
二、Top/Bottom N查询:在日常的工作生产中,我们经常碰到这样的查询:找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示: 【1】找出所有订单总额排名前3的大客户: 代码如下: select * REGION_ID CUSTOMER_ID CUST_TOTAL RANK SQL> 【2】找出每个区域订单总额排名前3的大客户: 代码如下: select * REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK 18 rows selected. 三、First/Last排名查询:想象一下下面的情形:找出订单总额最多、最少的客户。按照前面我们学到的知识,这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名,第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦?因为Rank函数只告诉我们排名的结果,却无法自动替我们从中筛选结果。 幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话: 代码如下: select min(customer_id) FIRST LAST 这里有几个看起来比较疑惑的地方: ①为什么这里要用min函数 首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢? 代码如下: select keep (dense_rank first order by sum(customer_sales) desc) first, ERROR at line 1: 接下来看看第2个问题:keep是干什么用的?从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据,这就是keep的作用。告诉Oracle只保留符合keep条件的记录。 那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。 第4个问题:如果我们把dense_rank换成rank呢? 代码如下: select min(region_id) ERROR at line 1: 四、按层次查询:现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N,第一个,最后一个记录。有时我们会收到类似下面这样的需求:找出订单总额排名前1/5的客户。 很熟悉是不?我们马上会想到第二点中提到的方法,可是rank函数只为我们做好了排名,并不知道每个排名在总排名中的相对位置,这时候就引入了另外一个分析函数NTile,下面我们就以上面的需求为例来讲解一下: 代码如下: select region_id, REGION_ID CUSTOMER_ID TILE Ntil函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。 以上就是Oracle中前几名、后几名、最多、最少以及按层次查询的全部内容,希望能给大家一个参考,也希望大家多多支持编程之家。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- ruby-on-rails-3 – 在Ruby on Rails 3 / Postgres / Apach
- OS X环境下如何搭建编译Cocos2D-X v3.x的Android Studio工程
- 谈一谈自己对依赖、关联、聚合和组合之间区别的理解 .
- Flutter 系统是如何实现ExpansionPanelList的
- cocos2dx中setPosition的坐标位置
- XPath总结
- ruby-on-rails – Rails Carrierwave Base64图像上传
- Swift编译器在@objc协议中的关联类型上崩溃
- reactjs – react-router browserHistory removeListen?
- 【cocos2d-x游戏开发】cocos中的三种缓存类