ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE函数介绍第六篇 著名函数之分析函数

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

原创 Linux操作系统 作者:junsansi 时间:2007-08-30 00:00:00 0 删除 编辑
oracle函数介绍(6) 著名函数之分析函数(含如下函数介绍):
AVG
COUNT
FIRST
FIRST_VALUE
LAST
LAST_VALUE
LAG
LEAD
MAX
MIN
SUM
DENSE_RANK
RANK
ROW_NUMBER

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;

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;

COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查询分组序列中各组行数。
例如:
--分组查询col的数量
SELECT col,count(0) over(partition by col order by col) ct FROM tmp1;

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则是每一行都有返回。

LAST()与上同,不详述。
例如:见上例。

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;

LAST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的最后一条记录
例如:见上例。

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;

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

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;
MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。
例如:见上例。

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;

DENSE_RANK () OVER([partition_clause] order_by_clause)
例如:见上例。

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7607759/viewspace-22283/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-21

  • 博文量
    640
  • 访问量
    4090935