oracle函数介绍著名函数之分析函数.docVIP

[三思笔记]-Oracle函数介绍--著名及非著名函数 / QQ：5454589君三思 ORACLE函数介绍 第六篇 著名函数之分析函数 2007.8.28 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,