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则不管是否有重复行,(分组内)序列值始终递增
例如:见上例。