文档章节

oracle -分析函数

l
 leanee
发布于 2016/03/21 09:01
字数 1991
阅读 4
收藏 0

row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序)。
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
dense_rank()也是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。

oracle 分析函数 row_number(),返回一个整数值(>=1);

 

 

Oracle常用分析函数介绍:

评级函数

常见评级函数如下:

  • RANK():返回数据项在分组中的排名,在排名相等时会在名次中留下空位,造成排名不连续。

  • DENSE_RANK():同样返回数据项在分组中排名,不过在排名相等时不会留下名位空位。

  • CUME_DIST():返回特定值相对于一组值的位置,是累积分布(cumulative distribution)的简写。

  • PERCENT_RANK():返回某个值相对于一组值的百分比排名。

  • NTILE():返回n分片后的值,如三分片、四分片等。

  • ROW_NUMBER():为每一条分组记录返回一个数字,注意不同于rownum伪列。

RANK()和DENSE_RANK()

rank()和dense_rank()函数都可用于计算数据项在分组中(在不使用partition by时以所有数据为一个分组)的排名。它们的区别在于rank()在排名相等时,如:有3个第1名时,则下一个排名为第4名,没有2、3名;而dense_rank()则在有3个第1名时,下一个排名为第2名。即,rank()会出现排名间隔,而dense_rank()则不会出现排名间隔。

这两个函数多用于select子句中,在不进行分组的情况下,可以不使用partition by子句。其使用举例如,找出公司所有人工资排名:

select ename,

rank() over (order by sal desc) rank,

dense_rank() over (order by sal desc) dense_rank

from emp;

从语句中可以看出,rank()函数需要有关键字over和order by。而且rank()是一个单值函数,而不是聚合函数。若需要找出每种工作的最高工资在所有工作最高工资中的排名:

select job,

rank() over (order by max(sal) desc) rank,

dense_rank() over (order by max(sal) desc) dense_rank

from emp

group by job;

在排名中,会出现NULL值在前在后的问题,可以在ORDER BY子句之后使用关键字NULLS FIRST/LAST来控制。

PARTITION BY子句

当需要进行获得分组后各组内的排名,则需要使用partition by子句。它不同于group by的分组,这种分组不“合并聚合”,它相当于把值分组后计算,然后重复每个值。

最常见的例子如:在table表中有name(姓名)、class(班级)和score(分数)三个字段,求每个班级里前三名姓名、班级及分数,SQL语句为:

select name,class,score

from (select name,

class,

score,

rank() over(partition by class order by score desc) rank

from table)

where rank <= 3;

在SCOTT用户中测试,求每个部门工资前3名的人姓名、部门、工作和工资,如:

select *

from (select ename,

deptno,

job,

sal,

dense_rank() over(partition by deptno order by sal desc) rank

from emp)

where rank <= 3;

ROW_NUMBER()

row_number为每一行返回一个数字,在分组中较常用(rownum在非分组中常用)。如,给emp表中每种工作工资由高到低进行排序:

select ename,job,sal,row_number() over (partition by job order by sal desc) from emp;

窗口函数(累计和、移动平均值等)

窗口函数可用来计算累计和、移动平均值和中心平均值等,具体如下:

计算累计和

查询从2003年1月到12月的累计销量,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对于累计部分SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)解析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于计算月销量总和,外部的SUM()用于计算累计销量。

  • ORDER BY month 按月份对查询读取的记录进行排序。

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义了窗口的起点和终点,起点为UNBOUNDED PRECEDING,意味着起点为固定的查询结果集的第一行;终点为CURRENT ROW表示终点为处理结果集的当前行。当外部SUM函数计算返回当前的累计销量后,窗口的终点便向下移动一行。PRECEDING表示向上累计数,若将UNBOUNDED换成数字如1,则表示跟之前一条记录做累积;同时还可以向后,使用关键字FOLLOWING,指定向后累积数只需要在该关键字前加数字即可,该数字为向后累积的行数(从这里也可以看出排序的重要性)。

如:

若要计算指定月份如6月到12月的累积销量,则只需要在where子句中再增加条件month between 6 and 12即可。

计算当月跟前3个月累积销量,窗口语句:

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount

计算前一个月和后一个月累积销量,窗口语句:

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount

计算移动平均值

计算本月与前3个月之间销量的移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对移动平均值部分AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。

  • ORDER BY month 按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。

  • ROWS BETWEEN 3 PRECEDING AND CURRENT ROW定义了窗口的起点为当前记录的前3条记录,窗口的终点为当前记录。

计算中心平均值

计算当前月份前、后各一个月的销量移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对中心平均值部分AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。

  • ORDER BY month 按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。

  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING定义了窗口的起点是当前记录之前的那条记录,窗口的终点是当前记录之后的那条记录。

窗口第一条和最后一条记录

FIRST_VALUE()和LAST_VALUE()函数可用于获取窗口中的第一行和最后一行数据,如,可用于获取当前月前一个月和后一个月的销量:

SELECT month,

SUM(amount) AS month_amount,

FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS pre_month_amount,

LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS next_month_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

其中,窗口定义了起点为前一个月终点为后一个月,故而first_value(sum(amount))为前一个月销量而last_value()为后一个月销量。

声明:该笔记转载自《Oracle Database 11g SQL开发指南》,仅用于个人学习,若侵犯到您的版权敬请告知!

 

本文转载自:http://www.ecdoer.com/post/oracle-rank-rows.html

l
粉丝 0
博文 6
码字总数 1518
作品 0
宁波
私信 提问
Oracle/Hive/Impala SQL比较

5 Function 指数据库内置的function,不讨论UDF。另外,操作符都不比较了,区别不大。 5.1 数学函数 功能 Oracle Hive Impala ABS 绝对值,有 有 有 SIN/SINH/ASIN/COS/COSH/ACOS/TAN/TANH/A...

hblt-j
2017/10/28
0
0
PostgreSQL Oracle 兼容性 - Analysis函数之keep

标签 PostgreSQL , Oracle , 分析函数 , 窗口函数 , keep 背景 Oracle 分析函数KEEP,类似OVER的语法结构(当然,含义与之不同)。keep可以用于普通的查询,也可以用于分组聚合,同时亦可用于...

德哥
2018/06/21
0
0
【书评:Oracle查询优化改写】第14章 结尾章

【书评:Oracle查询优化改写】第14章 结尾章 一.1 相关参考文章链接 前13章的链接参考相关连接: 【书评:Oracle查询优化改写】第一章 http://blog.itpub.net/26736162/viewspace-1652985/ 【书...

技术小胖子
2017/11/08
0
0
Oracle SQL的优化规则解析

以下的文章主要介绍的是Oracle SQL的优化规则的推荐方案,如果你是Oracle SQL的优化规则实际应用方面的新手,你就可以通过以下的文章对Oracle SQL的优化规则的推荐方案的实际操作有个更好的了...

wersdffg
2015/10/17
211
0
Oracle开窗函数笔记及应用场景

介绍Oracle的开窗函数之前先介绍一下分析函数,因为开窗函数也属于分析函数 分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返...

smileNicky
2018/12/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Jenkins系列_插件安装及报错处理

进入Jenkins之后我们可以进行插件的安装,插件管理位于以下模块: 发现上面报了一堆错误,是因为插件的依赖没有安装好,那么这一节,就先把这些错误解决掉吧。解决完成后,也就基本会使用插件...

shzwork
今天
2
0
mysql mysql的所有查询语句和聚合函数(整理一下,忘记了可以随时看看)

查询所有字段 select * from 表名; 查询自定字段 select 字段名 from 表名; 查询指定数据 select * from 表名 where 条件; 带关键字IN的查询 select * from 表名 where 条件 [not] in(元素...

edison_kwok
昨天
8
0
多线程同时加载缓存实现

import com.google.common.cache.Cache;import com.google.common.cache.CacheBuilder;import java.util.concurrent.ExecutionException;import java.util.concurrent.ExecutorServi......

暗中观察
昨天
2
0
利用VisualVM 内存查看

准备工作,建几个测试类。等下就是要查看这几个类里面的属性 package visualvm;public class MultiObject { private String str; private int i; MultiObject(String str...

冷基
昨天
2
0
组装一台工作游戏两用机

一、配置清单如下: 分类 项目 价格(元) 主板 华硕(ASUS)TUF Z370-PLUS GAMING II 电竞特工 Z370二代 支持9代CPU 1049 CPU 英特尔(Intel) i7 8700K 酷睿六核 盒装CPU处理器 2640 风扇 九...

mbzhong
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部