oracle function6,ORACLE函数介绍(6)-分析函数-ZT

ORACLE函数介绍

第六篇著名函数之分析函数2007.8.28

1、AVG([DISTINCT|ALL]

expr) OVER(analytic_clause)计算平均值。

例如：

--聚合函数

SELECTcol,AVG(value)FROMtmp1GROUPBYcolORDERBYcol;

--分析函数

SELECTcol,AVG(value) OVER(PARTITIONBYcolORDERBYcol)

FROMtmp1

ORDERBYcol;

2、SUM ( [

DISTINCT | ALL ] expr ) OVER ( analytic_clause )

例如：

--聚合函数

SELECTcol,sum(value)FROMtmp1GROUPBYcolORDERBYcol;

--分析函数

SELECTcol,sum(value) OVER(PARTITIONBYcolORDERBYcol)

FROMtmp1

ORDERBYcol;

3、COUNT({* |

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

例如：

--分组查询col的数量

SELECTcol,count(0) over(partitionbycolorderbycol) ctFROMtmp1;

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

例如：

--聚合函数

SELECTcol,

MIN(value)KEEP(DENSE_RANKFIRSTORDERBYcol) "Min Value",

MAX(value)KEEP(DENSE_RANKLASTORDERBYcol) "MaxValue"

FROMtmp1

GROUPBYcol;

--分析函数

SELECTcol,

MIN(value)KEEP(DENSE_RANKFIRSTORDERBYcol) OVER(PARTITIONBYcol),

MAX(value)KEEP(DENSE_RANKLASTORDERBYcol) OVER(PARTITIONBYcol)

FROMtmp1

ORDERBYcol;

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

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

例如：见上例。

6、FIRST_VALUE

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

例如：

insertintotmp1values('test6','287');

SELECTcol,

FIRST_VALUE(value) over(partitionbycolorderbyvalue) "First",

LAST_VALUE(value) over(partitionbycolorderbyvalue) "Last"

FROMtmp1;

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。

例如：

SELECTcol,

value,

LAG(value) over(orderbyvalue) "Lag",

LEAD(value) over(orderbyvalue) "Lead"

FROMtmp1;

9、LEAD(col[,n][,n])

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

例如：见上例

10、MAX (col)

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

例如：

--聚合函数

SELECTcol,

Max(value) "Max",

Min(value) "Min"

FROMtmp1

GROUPBYcol;

--分析函数

SELECTcol,

value,

Max(value) over(partitionbycolorderbyvalue) "Max",

Min(value) over(partitionbycolorderbyvalue) "Min"

FROMtmp1;

11、MIN (col)

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

例如：见上例。

12、RANK()

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

例如：

insertintotmp1values('test2',120);

SELECTcol,

value,

RANK()

OVER(orderbyvalue) "RANK",

DENSE_RANK() OVER(orderbyvalue) "DENSE_RANK",

ROW_NUMBER() OVER(orderbyvalue) "ROW_NUMBER"

FROMtmp1;

13、DENSE_RANK ()

OVER([partition_clause] order_by_clause)

例如：见上例。

14、ROW_NUMBER ()

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

例如：见上例。