ORACLE函式介紹第六篇 著名函式之分析函式

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/，如需轉載，請註明出處，否則將追究法律責任。