ORACLE數據庫(六)-----開窗函數

開窗函數又名分析函數、窗口函數、OLAP(數據分析)函數

聚合函數：將數據按照一定的規則分組，統一分析各組的某項情況，每個分組返回一行結果

開窗函數：將數據按照一定的規則分組，統一分析各組的某項情況，每行數據返回一行結果

一、開窗函數語法

分析函數名() over(分析子句)

over作為開窗函數的標志

二、分析子句

分組(PARTITION BY) 排序(ORDER BY) 窗口(ROWS)

三、窗口條件

PRECEDING：之前的

FOLLOWING：之后的

CURRENT：當前的

UNBOUNDING：不受限的

ROW： 行

ROWS BETWEEN N PRECEDING AND N FOLLOWING -- 前面N行---- 后面N行

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 第一行 - 當前行

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 當前行 - 最后一行

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 第一行 - 最后一行

三、分析函數

1 聚合類

SUM()、AVG() 、MAX()、 MIN()、 COUNT() 功能和聚合函數一樣

1 PARTITION BY 默認窗口的作用范圍是從每個組的第一行到最后一行

2 ORDER BY 默認窗口作用范圍是從第一條到當前條

3 ORDER BY 分組內部排序，盡可能的按照順序排，但是結果不一定完全按照內部排序結果

用SUM()舉例

SUM(列名) OVER([PARTITION] [ORDER] [ROWS])

--查詢每天的營業額及周每日累計營業額

SELECT DATA_DT,AMT,SUM(AMT)OVER(PARTITION BY WEEK ORDER BY DATA_DT ASC) FROM BUSINESS; -- 各組內的第一條到當前條

注意點

1)分析函數名內必須包含需要分析的內容

2)分析子句沒有硬性要求 --- 出現ROWS時必須伴隨ORDER BY

3)采用默認窗口范圍時，下一個N相同值(用來排序的值相同)會被一并算入

2 排序類

ROW_NUMBER：對于排序值相同的行 排名不并列也不跳躍

RANK：對排序值相同的行 排名并列并且跳躍

DENSE_RANK：對排序值相同的行 并列不跳躍

用法

ROW_NUMBER()OVER([PARTITION BY ] ORDER BY)

查詢每天的營業額并在整月范圍內升序排列

SELECT DATA_DT,AMT,ROW_NUMBER()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 不并列 不跳躍

SELECT DATA_DT,AMT,RANK()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 并列跳躍

SELECT DATA_DT,AMT,DENSE_RANK()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 并列不跳躍

ROW_NUMBER 可以用來去除重復的行 完全重復和不完全重復(一般多條數據中除了日期不同其他均相同的行)

排序類開窗函數注意點：

1)分子函數名內不能包含任何內容

2)分析子句內必須添加ORDER BY，且不能指定窗口

3 偏移類

LAG() : 向前偏移

LEAD(): 向后偏移

用法

用法(以LAG為例)：LAG(COL_NAME,[OFFSET],[DEFVAL])OVER():向前偏移N行取數

COL_NAME:要分析的字段

OFFSET:偏移量 --默認偏移一行

DEFVAL:默認返回值 --默認返回空null 返回的類型要與分析字段的類型一樣

--查詢每天的營業額以及前一天的營業額

SELECT DATA_DT,AMT,

LAG(AMT,1,0)OVER(ORDER BY DATA_DT)

FROM BUSINESS;

偏移類開窗函數注意點：

1)分析函數名內必須包含要分析的內容，其他兩項參數可以默認

2)分析子句內必須添加ORDER BY，且不能指定窗口

3)若不再有可供偏移的行，則返回默認值

4)偏移量不允許寫負數

5)默認返回值數據類型與分析的字段要保持一致

四、總結

1 窗口子句不能單獨出現，必須要有排序子句出現的情況下才能指定窗口范圍

2 若出現排序子句(ORDER BY)，同時未指定窗口范圍，默認的窗口范圍是第一行到當前行；若未出現排序子句，同時未指定窗口范圍，默認的窗口范圍是第一行到最后一行

總結

以上是生活随笔為你收集整理的oracle开窗函数是什么,ORACLE数据库(六)-----开窗函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。