ORACLE函数介绍第六篇 著名函数之分析函数

转载 2011年01月13日 23:56:00

1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。

例如:

--聚合函数

SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函数

SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col)

  FROM tmp1

 ORDER BY col;


2、SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )

例如:

--聚合函数

SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函数

SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col)

  FROM tmp1

 ORDER BY col;


3、COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查询分组序列中各组行数。

例如:

--分组查询col的数量

SELECT col,count(0) over(partition by col order by col) ct FROM tmp1;


4、FIRST() 从DENSE_RANK返回的集合中取出排在第一的行。

 

例如:

--聚合函数

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"

  FROM tmp1

 GROUP BY col;

--分析函数

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col),

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col)

  FROM tmp1

 ORDER BY col;

可以看到二者结果基本相似,但是ex1的结果是group by后的列,而ex2则是每一行都有返回。


5、LAST()与上同,不详述。

例如:见上例。


6、FIRST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的第一条记录

例如:

insert into tmp1 values ('test6','287');

SELECT col,

       FIRST_VALUE(value) over(partition by col order by value) "First",

       LAST_VALUE(value) over(partition by col order by value) "Last"

  FROM tmp1;


7、LAST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的最后一条记录

例如:见上例。


8、LAG(col[,n][,n]) over([partition_clause] order_by_clause) lag是一个相当有意思的函数,其功能是返回指定列col前n1行的值(如果前n1行已经超出比照范围,则返回n2,如不指定n2则默认返回null),如不指定n1,其默认值为1。

例如:

SELECT col,

       value,

       LAG(value) over(order by value) "Lag",

       LEAD(value) over(order by value) "Lead"

  FROM tmp1;


9、LEAD(col[,n][,n]) over([partition_clause] order_by_clause) 与上函数正好相反,本函数返回指定列col后n1行的值。

例如:见上例


10、MAX (col) OVER (analytic_clause) 获取分组序列中的最大值。

例如:

--聚合函数

SELECT col,

       Max(value) "Max",

       Min(value) "Min"

  FROM tmp1

 GROUP BY col;

--分析函数

SELECT col,

       value,

       Max(value) over(partition by col order by value) "Max",

       Min(value) over(partition by col order by value) "Min"

  FROM tmp1;

11、MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。

例如:见上例。


12、RANK() OVER([partition_clause] order_by_clause) 关于RANK和DENSE_RANK前面聚合函数处介绍过了,这里不废话不,大概直接看示例吧。

例如:

insert into tmp1 values ('test2',120);

SELECT col,

       value,

       RANK() OVER(order by value) "RANK",

       DENSE_RANK() OVER(order by value) "DENSE_RANK",

       ROW_NUMBER() OVER(order by value) "ROW_NUMBER"

  FROM tmp1;


13、DENSE_RANK () OVER([partition_clause] order_by_clause)

例如:见上例。


14、ROW_NUMBER () OVER([partition_clause] order_by_clause) 这个函数需要多说两句,通过上述的对比相信大家应该已经能够看出些端倪。前面讲过,dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值依旧递增,rank则是重复值所在行的序列值相同,但其后的序列值从+重复行数开始递增,而row_number则不管是否有重复行,(分组内)序列值始终递增

例如:见上例。


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/junsisi/archive/2007/09/19/1791742.aspx

ORACLE函数介绍第六篇 著名函数之分析函数

 1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。例如:--聚合函数SELECT col, AVG(value) FROM tmp1 GR...
  • junsisi
  • junsisi
  • 2007年09月19日 17:47
  • 618

ORACLE函数介绍第七篇 非著名函数之分析函数

 1、CUME_DIST() OVER([partition_clause] order_by_clause) 返回该行在分组序列中的相对位置,返回值介于0到1之间。注意哟,如果order by的列是...
  • junsisi
  • junsisi
  • 2007年09月19日 17:48
  • 888

Oracle 11g学习笔记--分析函数

数据库中有很多内置的分析函数,能够执行复杂的计算。
  • E_xiake
  • E_xiake
  • 2016年10月16日 15:57
  • 1386

oracle高级分析函数使用实例

ORACLE的分析函数,发现大家写SQL的时候有些功能写的比较麻烦或者不知道复杂的功能怎么通过SQL实现,ORACLE自带的分析函数有很多相应的功能:   它是Oracle分析函数专门针对类似于"...
  • zhou689689
  • zhou689689
  • 2014年11月26日 10:26
  • 2169

ORACLE函数介绍第三篇 著名函数之聚合函数

 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。  聚合函数可被用于select,order by以及having子句中。其运算可以基于group by的结果...
  • junsisi
  • junsisi
  • 2007年09月19日 17:43
  • 626

ORACLE函数 非著名函数之单值函数

注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。    单值函数在查询中返回单个值,可被应用到select,where子句,start with以及con...
  • u012113911
  • u012113911
  • 2014年07月30日 21:50
  • 321

ORACLE函数介绍第一篇 著名函数之单值函数

注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。  单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect ...
  • junsisi
  • junsisi
  • 2007年09月19日 17:41
  • 654

ORACLE函数介绍第一篇 著名函数之单值函数

注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。  单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect ...
  • matrixzero
  • matrixzero
  • 2008年06月05日 12:25
  • 496

ORACLE函数介绍第五篇 分析函数简述

 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。  分析函数计算基于group by的列,分组查询出的行被称为"比照(window)",在根据over()执行...
  • junsisi
  • junsisi
  • 2007年09月19日 17:46
  • 602

ORACLE函数介绍第二篇 非著名函数之单值函数

注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。  单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect ...
  • junsisi
  • junsisi
  • 2007年09月19日 17:42
  • 659
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE函数介绍第六篇 著名函数之分析函数
举报原因:
原因补充:

(最多只允许输入30个字)