oracle分析函數學習
一、分析函數的語法</span>
FUNCTION_NAME（<參數>，…）
OVER （<PARTITION BY 表達式，…> <ORDER BY 表達式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>）
1、FUNCTION_NAME（<參數>，…）
在后面介紹各個分析函數的用途。
2、OVER關鍵字
over只是一個關鍵字，標識這是一個分析函數。
3、PARTITION子句
分析函數以什么條件分組計算，相當于group by的作用，但是分析函數不會把結果集聚合，而是以原始記錄方式顯示每行的計算結果。缺省該子句表示整個記錄集作為一組計算。
4、ORDER BY子句
分析函數中的order by 子句和標準SQL中的order by 子句類似，表示組內以什么條件排序，asc和desc表示排序的方向，nulls first和nulls last表示空值的排序位置。
5、WINDOWING子句
默認的窗口是：當有ORDER BY子句的時候表示從當前分區的第一行到當前行；當沒有ORDER BY子句的時候表示整個分組。
窗口函數有2種方式，但是必須有ORDER BY子句時才能使用窗口函數。
a、 值域窗（RANGE WINDOW），邏輯偏移
RANGE 表達式 PRECEDING ，當前組中當前行的前N行開始到當前行的記錄集。排序列和表達式都只能是數值或間隔日期，選定窗為排序后當前行之前，排序列（使用這種窗口函數時只能有一個排序列）值大于/小于（當前行該列值 –/+表達式）的所有行，因此與ORDER BY子句有關系。是以排序列計算窗口范圍。
以下2種情況可以有多個排序列：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
b、 行窗（ROW WINDOW），物理偏移
ROWS 表達式 PRECEDING，表達式必須是一個正的數字類型。以排序的結果順序計算偏移當前行的起始行。
除了上面的PRECEDING關鍵字外，還有CURRENT ROW表示當前行，FOLLOWING表示當前行之后N行，還可以用BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING表示前m行到后n行的記錄集計算。如果不是BETWEEN AND形式，則oracle會認為窗口函數只寫了起始行，而當前行默認是終止行。所以FOLLOWING關鍵字只能用在BETWEEN AND中。
二、分析函數的簡介
| AVG
（<distinct | all> expr）
|
一組或選定窗中表達式的平均值,添加distinct去重取平均值。
| </tr>
| CORR
（expr， expr）
|
即COVAR_POP（exp1，exp2） / （STDDEV_POP（expr1） * STDDEV_POP（expr2）），兩個表達式的互相關，-1（反相關） ~ 1（正相關），表示不相關
|
| COUNT
（<distinct> <*> <expr>）
|
計數,添加distinct去重計數。
|
| COVAR_POP
（expr， expr）
|
總體協方差
|
| COVAR_SAMP
（expr， expr）
|
樣本協方差
|
| CUME_DIST
|
累積分布，即行在組中的相對位置，返回0 ~ 1
|
| DENSE_RANK
|
行的相對排序（與ORDER BY搭配），相同的值具有一樣的序數（NULL計為相同），并不留空序數
|
| FIRST_VALUE
|
一個組的第一個值
|
| LAG
（expr, <offset>，<default>）
|
訪問之前的行，OFFSET是缺省為1 的正數，表示相對行數，DEFAULT是當超出選定窗范圍時的返回值（如第一行不存在之前行）
|
| LAST_VALUE
|
一個組的最后一個值
|
| LEAD
（expr，<offset>，<default>）
|
訪問之后的行，OFFSET是缺省為1 的正數，表示相對行數，DEFAULT是當超出選定窗范圍時的返回值（如最后行不存在之前行）
|
| MAX
(expr）
|
最大值
|
| MIN
（expr）
|
最小值
|
| NTILE
（expr）
|
按表達式的值和行在組中的位置編號，如表達式為4，則組分4份，分別為1 ~ 4的值，而不能等分則多出的部分在值最小的那組
|
| PERCENT_RANK
|
類似CUME_DIST，1/（行的序數 - 1）
|
| RANK
|
相對序數，允許并列，并空出隨后序號
|
| RATIO_TO_REPORT
（expr）
|
表達式值 / SUM（表達式值），當前值占當前分組的比例。
|
| REGR_ xxxx
（expr， expr）
|
線性回歸函數
|
| ROW_NUMBER
|
排序的組中行的偏移
|
| STDDEV
（expr）
|
標準差
|
| STDDEV_POP
（expr）
|
總體標準差
|
| STDDEV_SAMP
（expr）
|
樣本標準差
|
| SUM
（expr）
|
合計
|
| VAR_POP
（expr）
|
總體方差
|
| VAR_SAMP
（expr）
|
樣本方差
|
| VARIANCE
（expr）
|
方差
|
</tbody>
</table>
三、聚合函數的特殊關鍵字KEEP
聚合函數 MIN, MAX, SUM, AVG, COUNT, VARIANCE,和 STDDEV, 當使用 KEEP 時和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，獲取一組中排名第一或者排名最后的記錄。必須有order by 子句用來排序。后面也可以接over（）分析函數部分。
Min（col2）keep（dense_rank first order by col1）保留按col1排名第一的col2的最小值。
Min（col2）keep（dense_rank first order by col1）over (partition by col3) 按col3分組保留按col1排名各組第一的col2的最小值。
本文由用戶
jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
一、分析函數的語法 FUNCTION_NAME （ < 參數 > ，…） OVER （ <PARTITION BY 表達式，… > <ORDER BY 表達式 <ASC | DESC> <NUL...
Oracle必讀好書推薦 -------------------------------------------------------------------------- tkyte的書 1...
. ASCII 返回與指定的字符對應的十進制數; SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual...
數據庫全名是： ORACLEDB ； Net 本地服務名： ORACLEDB01 ；用戶名： haha ；密碼 haha 。 在數據庫里有一個測試用的表： student 一：查詢語句 sele...
sesese色
jopen 
baojian322 
wf1006 
lyz