Oracle分析函式之開窗函式over()詳解
分析函式是什麼?
分析函式是Oracle專門用於
解決複雜報表統計需求
的功能強大的函式,
它可以在資料中進行分組然後計算基於組的某種統計值
,並且每一組的每一行都可以返回一個統計值。
分析函式和聚合函式的不同之處是什麼?
普通的聚合函式用group by分組,
每個分組
返回一個統計值,而分析函式採用partition by分組,並且
每組每行
都可以返回一個統計值。
分析函式的形式
分析函式帶有一個開窗函式over(),包含三個分析子句:分組(partition by), 排序(order by), 視窗(rows) ,他們的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:視窗子句在這裡我只說rows方式的視窗,range方式和滑動視窗也不提
分析函式例子(在scott使用者下模擬)
示例目的:顯示各部門員工的工資,並附帶顯示該部分的最高工資。
--顯示各部門員工的工資,並附帶顯示該部分的最高工資。SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL, LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS --unbounded preceding and unbouned following針對當前所有記錄的前一條、後一條記錄,也就是表中的所有記錄 --unbounded:不受控制的,無限的 --preceding:在...之前 --following:在...之後 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP E;
執行結果:
示例目的:按照deptno分組,然後計算每組值的總和
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal FROM SCOTT.EMP;
執行結果:
示例目的:對各部門進行分組,並附帶顯示第一行至當前行的彙總
SELECT EMPNO, ENAME, DEPTNO, SAL, --注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至當前行的彙總 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal FROM SCOTT.EMP;
執行結果:
示例目標:當前行至最後一行的彙總
SELECT EMPNO, ENAME, DEPTNO, SAL, --注意ROWS BETWEEN current row AND unbounded following 指當前行到最後一行的彙總 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal FROM SCOTT.EMP;
執行結果:
示例目標:當前行的上一行(rownum-1)到當前行的彙總
SELECT EMPNO, ENAME, DEPTNO, SAL, --注意ROWS BETWEEN 1 preceding AND current row 是指當前行的上一行(rownum-1)到當前行的彙總 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal FROM SCOTT.EMP;
執行結果:
示例目標: 當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的彙總
SELECT EMPNO, ENAME, DEPTNO, SAL, --注意ROWS BETWEEN 1 preceding AND 1 following 是指當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的彙總 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal FROM SCOTT.EMP;
執行結果:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14377/viewspace-2638439/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle的分析函式over 及開窗函式Oracle函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- 【函式】oracle視窗函式over()的理解函式Oracle
- oracle over函式 詳解(轉)Oracle函式
- (轉)Oracle語法之OVER(PARTITION BY)及開窗函式Oracle函式
- oracle分析函式,keep and over解說Oracle函式
- Oracle 語法之 OVER (PARTITION BY ..) 及開窗函式 轉載Oracle函式
- 分析函式 over函式
- Oracle分析函式與視窗函式Oracle函式
- Oracle 分析函式詳解Oracle函式
- Spark Streaming--開窗函式over()Spark函式
- 語法:OVER(PARTITION BY)及開窗函式函式
- SQL視窗分析函式使用詳解系列三之偏移量類視窗函式SQL函式
- 常用分析函式開窗講解函式
- Oracle over()函式使用Oracle函式
- Sql Server資料庫開窗函式Over()的使用例項詳解SQLServer資料庫函式
- hive視窗分析函式使用詳解系列二之分組排序視窗函式Hive函式排序
- sum()over()和count()over()分析函式函式
- Oracle 中分析函式用法之--rank(),dense_rank(),partition,over()Oracle函式
- hive視窗分析函式使用詳解系列一Hive函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- Oracle聚合函式/分析函式Oracle函式
- Oracle 聚合函式詳解Oracle函式
- oracle TRANSLATE函式詳解Oracle函式
- ORALCE函式:LAG()和LEAD() 分析函式詳解函式
- Oracle分析函式七——分析函式案例Oracle函式
- 詳解SQL操作的視窗函式SQL函式
- SparkSQL 開窗函式SparkSQL函式
- Spark 開窗函式Spark函式
- SparkSQL開窗函式SparkSQL函式
- sql(oracle)資料處理實用總結開窗函式(over partition)使用SQLOracle函式
- OVER(PARTITION BY)函式用法函式
- Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法Oracle函式
- 【Mysql】Mysql似oracle分析函式sum over的實現MySqlOracle函式
- ROW_NUMBER() OVER() 分析函式的用法函式
- Oracle單行函式詳解Oracle函式
- ORACLE to_char函式詳解Oracle函式
- Oracle 分析函式Oracle函式