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

原创 2007年09月19日 17:46:00
 

注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。

  聚合函数可被用于select,order by以及having子句中。其运算可以基于group by的结果,也可以直接对所有记录进行运算。当然,这种类型语句相信大家平常也用的比较多,概念性的就不多介绍了,有一点需要注意的是,除了count和grouping之后,其它的统计运算均会忽略值为null的列。

  注意,多数聚合函数都支持DISTINCT子句(distinct是什么?过滤记录集中重复记录用的),如果不指定则默认针对所有计录,举个最简单的例子:select count(col),count(distinct col) from tmp1

  本类函数中部分函数同时也属于分析函数。其做为分析函数时的具体用法将会在后面章节介绍,本节不做描述。

1、CUME_DIST(expr[,expr]...) WITHIN GROUP

  (ORDER BY 

    expr [DESC | ASC] [NULLS {FIRST | LAST}] 

      [,expr [DESC | ASC] [NULLS {FIRST | LAST}]]...)非常有意思的一个函数。查询指定数值在指定分组序列中的相对位置,返回值是介于 0 和 1 之间的小数值。我们后面还会再讲几个相关的函数,均是实现类型功能,只是值的表现形式不同。

例如:SELECT CUME_DIST(120) WITHIN GROUP (ORDER BY valueFROM TMP1

2、GROUP_ID() 该函数必须配合group by子句使用。主要是用来区分group by 生成的记录集中是否是被重复生成的记录,如果该条记录是则返回1,否则返回0。

例如:SELECT t.col, group_id() FROM TMP1 t GROUP BY col, ROLLUP(col)

3、PERCENT_RANK ( expr [, expr]... ) WITHIN GROUP

( ORDER BY 

  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]

  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)本函数语法及形式与cume_dist非常相似,也是返回指定数据在指定分组序列中所占的相对位置,不过与cume_dist不同的是。不管指定值与分组序列中某值是否重复,均将此值视为序列一部分,而cume_dist则不同。如果指定值与分组序列中某值重复,则将二值视为一个值处理(上帝保佑,但愿俺讲明白了,废话不说,大家通过示例来理解吧)。

例如:SELECT PERCENT_RANK(120) WITHIN GROUPORDER BY valueFROM TMP1

4、PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 根据输入值返回该值对应的分组序列中数值。输入值应该是介于0到1之间。原因请参照前面的PERCENT_RANK函数。

如果分组序列中没有存在对应值的话,会根据如下规则来计算返回值:

  if (CRN = FRN = RN) then 

    (value of expression from row at RN)

  else

    (CRN - RN) * (value of expression for row at FRN) +

    (RN - FRN) * (value of expression for row at CRN)

例如:

SELECT col, max(value), min(value), sum(value),

 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,

 PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b

  FROM TMP1

 group by col;

5、PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 语法与形式与上例相同,不过在计算方法上略有差异。如果分组序列中没有对应值的话,那么将会返回该序列中最近最大的一个值。

相对而言,我认为某些情况下本函数被应用的机率还是相当大地,起码比上述几个函数的应用范围会广。

例如:

SELECT col, max(value), min(value),

 PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,

 PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b

  FROM TMP1

 group by col;

6、STDDEV([DISTINCT|ALL] n) 该函数返回样本的标准偏差。

例如:SELECT col, STDDEV(valueFROM TMP1 GROUP BY col;

7、STDDEV_SAMP(n) 语法与形式与上同,其与STDDEV函数最大的区别是,如果该分组序列只有一行的话,则STDDEV_SAMP函数返回空值,而STDDEV则返回0。

例如:SELECT col, STDDEV(value),STDDEV_SAMP(valueFROM TMP1 GROUP BY col;

8、STDDEV_POP(n) 返回该分组序列总体标准偏差。

例如:SELECT col, STDDEV_POP(valueFROM TMP1 GROUP BY col;

9、VAR_POP(n) 该函数返回分组序列的总体方差,VAR_POP进行如下计算:(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)。

例如:SELECT col, VAR_POP(valueFROM TMP1 GROUP BY col;

10、VAR_SAMP(n) 与上类似,该函数返回分组序列的样本方差,,其计算公式为:(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)。

例如:SELECT col, VAR_POP(value),VAR_SAMP(valueFROM TMP1 GROUP BY col;

11、VARIANCE(n) 该函数返回分组序列方差,Oracle计算该变量如下:

如果表达式中行数为1,则返回0,如果表达式中行数大于1,则返回VAR_SAMP

例如:SELECT col, VAR_SAMP(value),VARIANCE(valueFROM TMP1 GROUP BY col;

 

Oracle聚合函数及其扩展使用

  • 2011年04月16日 18:46
  • 69KB
  • 下载

原创:oracle聚合函数介绍 (六)

   聚合函数对一组行中的某个列执行计算执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。    在下面...

Oracle自定义聚合函数-分析函数

  • 2016年11月08日 16:47
  • 29KB
  • 下载

Oracle中使用聚合函数

单行函数和聚合函数的区别: 单行函数操作时,根据函数的功能同时处理一行数据,返回每一行的处理结果; 聚合函数同时对分组后的一组行进行操作,返回分组后各组的处理结果; 聚合函数主要是数字处理函数,...

oracle中聚合函数rank、dense_rank和row_rumber的使用

oracle中聚合函数rank、dense_rank和row_rumber的使用 2008-11-21 10:51 其实现在Oracle对于rank和dense_rank已经支持合...

Oracle聚合函数

1.聚合函数 聚合函数就是同时对一组进行操作,对每组行返回一行输出结果,这就是与单行函数的不同,聚合函数有时候也可以叫分组函数 2.常用的聚合函数 (1)avg(x):返回x的平均值 (2)c...

傅老师课堂:Oracle高级应用之去重聚合函数

本例实现的是连接字符串,并且去掉重复的项。 聚合函数实际上就是一个对象: create or replace type distinct_concat_type as object ( --对象...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE函数介绍第四篇 非著名函数之聚合函数
举报原因:
原因补充:

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