oracle函數(shù)介紹(6) 著名函數(shù)之分析函數(shù)

1、ORACLE函數(shù)介紹第六篇 著名函數(shù)之分析函數(shù) 2007.8.281、 AVG(DISTINCT|ALL expr) OVER(analytic_clause) 計算平均值。例如：-聚合函數(shù)SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;-分析函數(shù)SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col;2、 SUM ( DISTINCT | ALL expr ) OVER ( analytic_clause )例如：-

2、聚合函數(shù)SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;-分析函數(shù)SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col;3、 COUNT(* | DISTINCT | ALL expr) OVER (analytic_clause) 查詢分組序列中各組行數(shù)。 例如：-分組查詢col的數(shù)量SELECT col,count(0) over(partition by col order by col) ct FROM tm

3、p1;4、 FIRST() 從DENSE_RANK返回的集合中取出排在第一的行。例如：-聚合函數(shù)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;-分析函數(shù)SELECT col, MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col), MAX(value) KEEP

4、(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col) FROM tmp1 ORDER BY col;可以看到二者結(jié)果基本相似，但是ex1的結(jié)果是group by后的列，而ex2則是每一行都有返回。5、 LAST()與上同，不詳述。例如：見上例。6、 FIRST_VALUE (col) OVER ( analytic_clause ) 返回over()條件查詢出的第一條記錄例如：insert into tmp1 values (test6,287);SELECT col, FIRST_VALUE(value) over(partition b

5、y col order by value) First, LAST_VALUE(value) over(partition by col order by value) Last FROM tmp1;7、 LAST_VALUE (col) OVER ( analytic_clause ) 返回over()條件查詢出的最后一條記錄例如：見上例。8、 LAG(col,n,n) over(partition_clause order_by_clause) lag是一個相當有意思的函數(shù)，其功能是返回指定列col前n1行的值(如果前n1行已經(jīng)超出比照范圍，則返回n2，如不指定n2則默認返回null)，如

6、不指定n1，其默認值為1。例如：SELECT col, value, LAG(value) over(order by value) Lag, LEAD(value) over(order by value) Lead FROM tmp1;9、 LEAD(col,n,n) over(partition_clause order_by_clause) 與上函數(shù)正好相反，本函數(shù)返回指定列col后n1行的值。例如：見上例10、 MAX (col) OVER (analytic_clause) 獲取分組序列中的最大值。例如：-聚合函數(shù)SELECT col, Max(value) Max, Min(va

7、lue) Min FROM tmp1 GROUP BY col;-分析函數(shù)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;11、 MIN (col) OVER (analytic_clause) 獲取分組序列中的最小值。例如：見上例。12、 RANK() OVER(partition_clause order_by_clause) 關(guān)于RANK和DENSE_RANK前面聚合

8、函數(shù)處介紹過了，這里不廢話不，大概直接看示例吧。例如：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;13、 DENSE_RANK () OVER(partition_clause order_by_clause) 例如：見上例。14、 ROW_NUMBER () OVER(partition_clause order_by_clause) 這個函數(shù)需要多說兩句，通過上述的對比相信大家應(yīng)該已經(jīng)能夠看出些端倪。前面講過，dense_rank在做排序時如果遇到列有重復(fù)值，則重復(fù)值所在行的序列值相同，而其后的序列值依舊遞增，rank