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

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

1、CUME_DIST() OVER([partition_clause] order_by_clause返回该行在分组序列中的相对位置,返回值介于0到1之间。注意哟,如果order by的列是desc,则该分组内最大的行返回列值1,如果order by为asc,则该分组内最小的行返回列值1。

例如:SELECT col, value, CUME_DIST() OVER(ORDER BY value DESCFROM tmp1;

2、NTILE(n) OVER([partition_clause] order_by_clause)  

ntile是个很有意思的统计函数。它会按照你指定的组数(n)对记录做分组 

例如:SELECT t.*,ntile(5) over(order by value descFROM tmp1 t;

3、PERCENT_RANK() OVER([partition_clause] order_by_clause与CUME_DIST类似,本函数返回分组序列中各行在分组序列的相对位置。其返回值也是介于0到1之间,不过其起始值始终为0而终结值始终为1。

例如:SELECT col, value, PERCENT_RANK() OVER(ORDER BY valueFROM tmp1;

4、PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同,只是一个是聚合函数,一个是分析函数。

例如:

--聚合函数

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;

--分析函数

SELECT col,

       value,

       sum(value) over(partition by col) "Sum",

       PERCENTILE_CONT(0.5) WITHIN GROUPORDER BY value) OVER(PARTITION BY col) "CONTa",

       PERCENTILE_CONT(0.8) WITHIN GROUPORDER BY value) OVER(PARTITION BY col) "CONTb"

  FROM TMP1;

5、PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同,只是一个是聚合函数,一个是分析函数。

例如:

--聚合函数

SELECT col, max(value), min(value), sum(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;

--分析函数

SELECT col,

       value,

       sum(value) over(partition by col) "Sum",

       PERCENTILE_DISC(0.5) WITHIN GROUPORDER BY value) OVER(PARTITION BY col) "CONTa",

       PERCENTILE_DISC(0.8) WITHIN GROUPORDER BY value) OVER(PARTITION BY col) "CONTb"

  FROM TMP1;

6、RATIO_TO_REPORT(col) over ([partition_clause]) 本函数计算本行col列值在该分组序列sum(col)中所占比率。如果col列为空,则返回空值。

例如:

SELECT col, value,

       RATIO_TO_REPORT(value) OVER(PARTITION BY col) "RATIO_TO_REPORT"

  FROM TMP1

7、STDDEV ([distinct|all] col) OVER (analytic_clause返回列的标准偏差。

例如:

--聚合函数

SELECT col, STDDEV(valueFROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV"

  FROM TMP1;

8、STDDEV_SAMP(col) OVER (analytic_clause功能与上相同,与STDDEV不同地方在于如果该分组序列只有一行的话,则STDDEV_SAMP函数返回空值,而STDDEV则返回0。

例如:

--聚合函数

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

--分析函数

SELECT col, value,

       STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV",

       STDDEV_SAMP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_SAMP"

  FROM TMP1;

9、STDDEV_POP(col) OVER (analytic_clause返回该分组序列总体标准偏差

例如:

--聚合函数

SELECT col, STDDEV_POP(valueFROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       STDDEV_POP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_POP"

  FROM TMP1;

10、VAR_POP(col) OVER (analytic_clause返回分组序列的总体方差,VAR_POP进行如下计算:(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

例如:

--聚合函数

SELECT col, VAR_POP(valueFROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       VAR_POP(value) OVER(PARTITION BY col ORDER BY value) "VAR_POP"

  FROM TMP1;

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

例如:

--聚合函数

SELECT col, VAR_SAMP(valueFROM TMP1 GROUP BY col;

--分析函数

SELECT col, value,

       VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP"

  FROM TMP1;

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

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

例如:

--聚合函数

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

--分析函数

SELECT col, value,

       VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP",

       VARIANCE(value) OVER(PARTITION BY col ORDER BY value) "VARIANCE"

  FROM TMP1;

 

Oracle 11G函数整理(聚合函数)

聚合函数就是基于多行数据返回一行结果,下面就是Oracle提供的一些列聚合函数: AVG COLLECT CORR CORR_* COUNT COVAR_POP ...
  • zhangliao613
  • zhangliao613
  • 2015年03月10日 15:31
  • 802

Oracle的LAG和LEAD分析函数

Oracle的LAG和LEAD分析函数 Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。 Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。 这...
  • haiross
  • haiross
  • 2015年02月11日 15:26
  • 5659

oracle分析函数技术详解(配上开窗函数over())

一、Oracle分析函数入门 分析函数是什么? 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个...
  • haiross
  • haiross
  • 2013年11月11日 11:36
  • 32070

mysql实现oracle分析函数功能 over

部分内容来源:http://blog.csdn.net/jgmydsai/article/category/3139929 Mysql的if函数 格式:IF(Condition,A,B) 意...
  • mengtianyalll
  • mengtianyalll
  • 2015年05月16日 13:03
  • 11565

oracle函数介绍(7) 非著名函数之分析函数.doc

  • 2016年09月07日 17:13
  • 43KB
  • 下载

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

1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。例如:--聚合函数SELECT col, AVG(value) FROM tmp1 GRO...
  • laichangbo
  • laichangbo
  • 2011年01月13日 23:56
  • 197

oracle分析函数

  • 2010年03月25日 10:45
  • 516KB
  • 下载

ORACLE分析函数详解

  • 2007年11月28日 10:17
  • 125KB
  • 下载

oracle10g分析函數

  • 2012年02月29日 16:59
  • 831KB
  • 下载

Oracle分析函数

  • 2011年09月06日 14:13
  • 148KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE函数介绍第七篇 非著名函数之分析函数
举报原因:
原因补充:

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