這個做統計分析的時候就用的比較多了,咱們來看一下ORACLE的分析函數,就是OVER函數,OVER PARTITION BYOVER PARTITION BY ORDER BY,還有row_number,還有rollup,然后還有cube,還有grouping,這些語法結合去使用的一種,主要是去做統計分析的,你可能要去做一些財務的報表啊,舉個列子,這里有幾條數據,然后這邊有個小計,小計就是把這幾列做一下匯總,然后還有一個合計,或者是總計,把幾個小計再做最后一個匯總,就是做一些表格的時候需要做的一些東西,可能你如果不做分析函數的話,你只能用JAVA去實現了,好幾個DAO,做統計,自己去查,一個一個去查,然后去拼成一個統計的表格,那如果要用ORACLE的話,你可以去采用他的分析函數了,咱們首先看最簡單的

我現在這一塊,咱們先查出來,來看一下這個結果集

那這個結果集是啥意思呢,先跟著我的思路走,把EMP表的14條數據,做一下這個統計分析,做什么分析呢,我們這里做一個連續的求和,這怎么做連續求和的呢,你會發現他有個特點是,部門10的都是8750,是這3個薪水之和,然后部門20的都是19625,就是部門20的這些人,薪水加起來就是19625,要不然就是部門30的,這些薪水加起來就是29025,第四列統計的都是總和,總和是什么意思呢,總和就是從頭到尾所有的薪水,就是這些SAL都加起來,薪水是29025這個數,你發現這個東西還是有增長的,它是按照部門連續求和的,你發現他有什么區別呢,這前3個字段沒變,然后從第4個字段就要連續求和了,這邊sum(sal),把這個sal進行匯總,然后進行一個over,over表示一個函數,然后over里面寫的是order by deptno那首先我們不看這個,我們看這個over然后什么也不加,改成總和,這是什么意思,這個over我可以不可以把它去掉,其實就相當于去掉他就跟sum相等,這個就是所有員工的匯總,就是29025,我們現在這里面是ORDER BY,用什么去連續求和,我根據的是deptno,那我就把第一個部門就是8750,然后第二個部門的是,就是2975+3000+1100+800+3000,這是第二個部門的,你算一下這個結果等于多少啊,結果是10875,就是再加上8750就等于19625,就是這個數,它是把第一個部門的和8750,然后加上第二個部門薪水的和,加起來,等于的是19625,然后把19625再加上第三個部門的總和就等于29625,這是部門的連續求和,這是按照部門去分別求和,如果你看不明白,你可以把它換成姓名,就可以看清楚了,比如我這里改成ORDER BY ENAME

你可以看到這個薪水是一點點增長的,然后加上1600,等于2700,2700加上2850,就等于5550,然后5500再加上2450就等于8000,然后繼續往下加,我現在按照的是名字,連續求和,就是一條記錄一條記錄連續的求和,最后得到的結果,剛才我是按照部門,按照DEPNO連續求和的,而后面這個就是無所謂了,就是一個四舍五入的一個份額了,這個什么意思呢,拿你當前的SAL,然后SUM(SAL),總數進行一個除法運算,SAL/SUM(SAL),然后結果保留5個有效數字,然后進行乘以100,就是拿我的1100,就是和我的29025進行除,除完了之后呢,肯定是0.0幾,然后我要保留5位有效數字,然后再乘以100,然后每個人占總薪水的份額,這個應該很簡單吧,剛才那個部門可能看著比較別扭,但是換成SAL就比較簡單吧,這是一個最簡單的OVER的用法,OVER加上ORDER BY的用法 咱們再看第二個,可能是比較長,咱們看結果

上面就是over 加上 order by,我們這里加上一個partition,partition是什么意思呢,就是分區,分區的意思,按照什么區域給我做什么事,那你看他有什么效果呢,你沒發現當我按照分區了以后呢,部門連續求和了,咱們觀察一下這個東西,部門這個值是8750,第二個部門的是10875,第三個部門的總和是多少,是9400,那這三個值加在一起等于29025,這個時候你會發現我是按照一個部門一個部門的去求總和的,這個就表示部門連續求和,這是怎么做的呢,注意看第二條語句,這里多了一個PARTITION BY DEPTNO,這個就是分區,我就把它們當成一個區間,一個區域單獨去計算,連續求和,按照ENAME連續求和,去給我遞增連續求和,算完了之后就完事了,第二個部門就是20部門這個,給我按照這個部門這個區域連續求和,求出結果就可以了,同理第三個部門也是這樣的,這個DEPTNO給我連續求和,這個分區就是PARTITION BY,分區的意思,下面還有一塊就是這樣的,partition by deptno,就是部門的總和,你現在就是按照分區給我計算,over如果什么都不加,就是求最終的總和,如果加上按照部門分區,你發現這是8750,這是第二個部門的總和,這是第三個部門的總和,然后這個就是占部門的份額了,你當前第一個人的薪水,占我整個部門8750的比例,28%,57.14%,14.86%,然后再往下看就是連續求和了,剛才我寫的這個,我剛才寫的是sum(sal) over (order by ename) 連續求和,直接按照這個求和也是一樣的,像這種寫法就是等于這個,就是一個一個加的,其實也可以寫成這種,我按照order by,按照部門,然后再按照名稱,部門一次,然后再按照名稱來一次, sum(sal) over (order by deptno,ename) 連續求和,然后就是over什么也不加,sum(sal) over () 總和,然后就是求總的份額,partition by分區的概念,這個其實在真正的統計分析中還是非常有用的,這是ORACLE特殊的語法,這個東西應該是不難的,其實就是把上面的簡寫

partition by deptno order by ename,部門分區了以后,再按照部門的名字繼續給我求和,那這個結果就是DEPT_SUM,分完區以后再按照名字給我累加,這個SUM就是求總和了

我們在加一個表EMPLOYEE,我加完EMPLOYEE這張表以后呢,我們來看看EMPLOYEE表有啥數據

數據很簡單,首先是EMPID,567屬于40部門,然后89屬于50部門,總之就是一個部門的編號,這里有一個每個人的薪水SALARY,大體上我們可以看到,有些人的薪水是相同的,比如第8是6500,咱們現在要做一個函數就是,ROW_NUMBER(),每個部門進行一個大小的比較,舉個例子

執行完這個SQL以后,結果是這樣的,也就是排個序嗎,10部門他的薪水給我排了一個序,550排在第一位,4500排在第二位,然后20部門呢,4800排在第一位,1900排在第二位,40部門肯定是44500排在第一,然后14500排在第二,然后6500排在第三,然后50部門的7500排在第一,6500的排在第二,把一個字段進行大小的比較,那怎么做的呢,其實跟之前的區別就是多了一個函數,Row_Number()函數,就是Row_Number() over 然后不變,partition by deptid,先按部門分一個區,10部門的分一個區,20部門的分一個區,40,50再給我分區,分區之后再按照薪水給我desc,給我進行一個排序,降序排序,ORDERY BY salary desc,薪水高的在最前面,薪水低的在最下面,然后這里起一個別面叫rank,as "排名",select employee.*, Row_Number() OVER (partition by deptid ORDER BY salary desc) as "排名" from employee;也是一樣的,這個就是關于Row_Number的用法

?

然后我們看另外一個小例子,可能有點復雜,建表然后插數據,這個表的數據稍微有點多,首先這個表里面有兩個特殊的關鍵字,EARNMONTH,月份就是09年的12月份,和10年的1月份,一共就這兩個不同的數據,整個月份就兩個月份,一個是09年的12月份,一個是10年的1月份,然后區域也就兩個,要么就是北平,要么就是金陵,然后SNO有相同的也有不相同的,然后就是SNAME名稱,然后就是TIMES次數,打工的次數,SINGLEINCOME就是一次多少錢,打工一次賺30塊錢,那11次就是30*11就是330塊錢,就是這個意思,這就是這幾個字段的一個介紹,那咱們就是用這個數據做一個分析

我現在想做group by這個分組,我代碼寫完以后你就知道什么意思了,直接看group by 語句,先按月份分組,這個屬于分組了,我先按月份分組,月份分組之后再按區域分組,這個結構就相當于這樣的了,我們看到了這個表里面兩個字段,如果我單純的按照月份分組,那肯定就是兩條記錄,要么就是09年的12月份,要么就是10年的1月份,那肯定就是兩條記錄,如果這么分組的話,但是我現在按照這個分組之后,再按照區域分組,所以區域也有分組的,這就產生四條記錄,0912 北平,0912金陵,1001北平,1001金陵,就是說兩次分組嗎,我們看到的group by就是這樣的

這個分組應該很簡單吧,按照前兩個字段一個簡單的分組,我現在想要用rollup,做一個分組,那這個rollup是怎么概念呢,rollup的概念是在group by 后面接rollup,這個是他的語法,就是在group by后面直接接rollup,他能做什么啊,他能夠在group by的分組上再加上earnmonth的匯總統計,在分組,再進行排序,加入我們現在這個是一個結果集,是進行group by的一個結果集,group by兩個,一個是按照時間月份,還有一個是按照AREA,還有一個是區域分組,第一次就是最簡單的分組結果,要是我如果加上rollup的話,我會在這個結果集的基礎之上,我再次進行一個分組,這就是rollup的用法,先執行這個結果,你發現這個東西就變成這樣了,首先按照12月份統一給我來一個匯總,那就是12月份是1179.5+975.83=2155.33,這樣的結果就是這樣的一個數,他兩之和就是12月份,然后按照10年的1月份又給我匯總,他就這樣再分組,然后把這兩個結果相加等于最后這個數

就是做月份的小計,這塊是做這個月份的小計,然后這塊就是總計,是不是可以做這樣的,如果你的界面需要這種統計分析的形式的話,那你就需要寫這樣的SQL語句,那現在你是利用ORACLE的分析函數,可以很清晰的做一個統計分析的結果集,看他怎么寫的呢,還是前面的是不變的,分組也不變,只是在group by 后面接了一個rollup,然后把rollup就把earnmonth和area包裹起來,那正常來講我們是這么來寫的,正常來講我們是group by earnmonth,area,現在我們的寫法變量,group by rollup(earnmonth,area),然后把這個月份和區域包裹起來,他肯定是按照earnmonth分組的,如果我把兩個換一下,我把它調換過來,group by rollup(area,earnmonth),就按照AREA進行再分組

不是按照時間,而是按照北平,然后北平再次分一下組,然后金陵的城市有多少,那我能不能只寫一個,這肯定不行,說不是group by的表達式,他其實也是一個group表達式的一個升級版,group by的一個升級版,group by的要求就是分組,分組的一個數值,分組的字段,area,要么就是類似一個SUM形式的,你沒法去弄一個,只能兩個都得寫上,你既然要查AREA,那AREA必須是一個分組字段,那咱們繼續往下看 還有一個更復雜的ORACLE的函數,這個cube函數什么意思呢,咱們先執行一下

你會發現這是另一種需求了,第一次分組之后先按月份分組,再次按照區域再次分一下組,那你發現按照月份分組,計算的兩個值,在按照區域分兩個值,給我統計一下他們之間的總和,cube是有這個功能,就是兩個字段分別計算,這個cube寫法也很簡單,group by cube(earnmonth,area),只是rollup改成了cube,就是這兩個字段需要再次去分組,那你就用這個cube,我們剛剛學了OVER,然后PARTITION,然后還有ORDER BY,OVER函數里寫了PARTITION,ORDER BY,然后group里面有group by,也rollup,cube函數 然后就是grouping,這個函數挺簡單的,你比如我們剛剛看的這個例子,rollup這個例子,我現在就是要這塊小計,然后最后的空格變成小計,這怎么辦呢,有人說在JAVA里循環,然后判斷AREA為空的時候再判斷,然后用一個JAVA區實現一個小計,然后這塊也是用JAVA來實現一個小計,其實ORACLE在設計的時候也考慮到了這一點,所以他有了一個grouping函數,就是為了用rollup和cube對結果集都會產生一個空值null,他匯總完以后就是一個空值,他不是北平也不是金陵,按的是月份分組的,所以這塊不知道寫什么了,那你可以做什么事啊,就是這個語法嗎,我可以用grouping函數,最終我執行完了就變成這樣了,這可能是我們統計分析里面經常用到的,這邊就是月份小計,然后這塊也是月份小計,然后這邊是一個總計,就是grouping可以帶一個參數,如果是本身結果就返回0,如果是合計的結果就返回1,我這邊就是case when了,這個是我們SQL通用的表達式,如果grouping(area)如果是等于1的話,表示是合計的結果,我統計的肯定是月份,如果是不加grouping的話,那肯定是一個空值,并且grouping(area)等于0,等于0是什么意思呢,是本身統計的一個結果,0912這塊本身沒有沖突,這塊是0912沒問題的,這塊是空值,空的部分我就用月份小計替換一下,如果是匯總的就返回1,并且grouping(earnmonth)也是匯總,兩個都返回1,表示兩邊的值都是合計,最后一塊他不知道寫12月份還是1月份了,經過匯總之后的一個空值,如果兩個都是空值的話,那我就寫成總計,當然我設置的是AREA這一個列,else就是原封不動的輸出AREA

這個就是grouping函數的用法,可以去把空值替換成你要的結果,能理解這個意思嗎,其實不難,你不用ORACLE可能會覺得沒有多大的作用,沒關系的,我覺得再過一段時間吧,ORACLE一般第二個版本是屬于穩定的版本,11G第一個版本也是不穩定的,11g的第二個版本才是穩定的,12C現在是第一個版本出了,12C如果第二個版本出了以后,集群肯定就特別特別的穩定了,而且他那個集群是免費的,所有的互聯網公司就考慮用12C吧,然后這個MYSQL肯定會被替代的,所以你可以提前當做復習了,就是一個知識的補充,當額外學習了,總之我們第一天講SQL的時候,這個跟整個的設計脫離的有一點點遠,但是是純ORACLE方向的 這三個函數,一個叫RANK,一個叫dense rank,還有一個叫row_number,其實咱們可以看一下,我把這幾個直接運行了一下

主要觀察Rank排名,你會發現其他的沒變,還是rank() over (partition by earnmonth,area order by personincome desc) 排名,只不過這塊變成rank()了,他這里又什么功效呢,排名第一個的他們兩個值都是第一,330都相等,這兩人并列第一,然后前面有兩個占位了,那么第三個這個就排名第三了,本身來說這個是1,1,2,但是因為前面有個位置了,所以從第三個開始數了,然后就是4,5

然后我們看一下dense_rank(),它是什么區別呢,他就是無論你前面有幾個人,你10個人并列第一,這個就給統計打分了 然后再往下看,第三種方式就是咱們的row_number,這是啥意思呢,我們不管你這個值薪水是不是相等,我都是按照順序123456這么往下排的,那我也給你分成1234,這個也是在統計分析的時候,總是要前幾名,以前我們做一些分析的時候,一個數據,一個指標最高,然后給我排一個名,排一個序

區別我們這里都有,我們這里還有一個累積求和,這個應該是之前的內容了

這個東西如果有時間的話,可以讓你寫的,這個語句啥意思呢,我查詢了這么多東西,然后主要是看第二行,sum(personincome),最后一個數據,12月份第一個人叫大魁,11次,打工一次30塊錢,一共330塊錢,12月份他一共賺了330塊錢,那就是薪水當月的一個匯總,over (partition by sname order by personincome asc),那這個是啥意思呢,按照sname給我分區,sname我記得每個人都有兩個,然后1月份也有一個,我把它兩個劃為一個區間,然后我按照薪水分組,asc,他兩就合并成一個人了,大魁就合并成一個人了,這是330,這是0元,就是330+0,一共就是330

這里有個大凱,一開始是200,再加上350就是550了,1月份是0,然后加上330,大亮57.5+132=189.75,就可以利用over (partition by sname order by personincome asc),反正這三個是分析函數最常用的,組合over加上partition,加上order by,統計分析來回用這三個關鍵字,然后去做一些事情 還有就是做總和運算,這塊有一個綜合運算,我可以去求這些值里面的最高值,剛才我所有的都是用的sum,就包括我之前的連續求和,用的group by必須用的都是用的sum,其實你不僅可以用SUM,還可以用其他的,比如說max最大,min最小,平均值,SUM,都可以去用,這里面的最高值,我進行分組了以后,分組是按照earnmonth,area分的組,按照月份和AREA分的組,然后就是最高值,就是201912 金陵算唯一的一個數據,分4組,兩個是唯一的一條記錄,然后給我區分一下最高值,區分一下最低值,平均值,然后總額

基本上ORACLE的分析函數,基本上用法就這些,其實你做什么軟件都離不開統計分析的,可能開始要實現功能,總的做統計分析,你最終得做什么統計分析,精確的統計分析,你像我們的互聯網行業要求這種精確的統計分析,要求大概的范圍,海量數據,你比如說有幾億條數據,要我做這種分析,分析兩行數據是無所謂的,就看一個大概的比值,如果你要涉及到一些真實的數據的話,有的時候他要求的是精確,要求精確的話就得你自己的去做SQL的分析,不能依賴于hadoop,storm這些東西,你要做精確這事還得靠數據庫,如果說你自己寫分析呢,用JAVA些腳本,用JAVA寫代碼,然后查詢數據自己去寫業務邏輯,寫分析的規則,那這個也是挺麻煩的,ORACLE反正有這些分析函數,SQL肯定是不支持,我個人覺得,MYSQL在過一兩個版本,就是過一兩年嗎,現在是5.6,MYSQL如果再過幾個版本,他肯定會出分析函數,因為MYSQL就屬于ORACLE下面的了,你想一下,MYSQL之前是沒有sysdate的,我印象中是沒有sysdate()函數的,肯定是沒有DAUL這張表,現在他都加上了,包括其實很多特性,以前都是ORACLE獨有的,現在MYSQL也都加上了,MYSQL里面有沒有分區,就是create table 后面寫partition,然后range,這種分區,還有hash分區,然后list分區,MYSQL有分區嗎,你們用MYSQL的數據庫,有吧,我記得也是有的,也是5.幾版本才有的,但是MYSQL5.0以下是沒有分區這個東西的,他不存在分區,那個時候都得用分表分庫,去做,包括現在也得用分表分區去做,是有區別的,肯定是有區別的,MYSQL正常來講是有分區的,也就什么意思呢,MYSQL收費的肯定和免費的不一樣,版本再往前靠一靠,ORACLE這些東西可能都有,但是都有的目的就是發現你使用MYSQL和你使用ORACLE,基本的用法都一樣,MYSQL的存儲過程的語法以后也得變一變,也得和ORACLE比較像,因為畢竟都差不多了,到最后兩個技術點都很像了,一個是MSYQL性能是很低的,一般情況下是不高的,你只能用集群,MYSQL就是做一個持久化,要不然你就用ORACLE,他的性能高,而且還有集群,然后這個時候ORACLE就讓你去選,你是ORACLE還是MYSQL,到時候都用ORACLE了,今天就講這么多東西,主要是做一個SQL語句的復習,然后不了解ORACLE的熟悉一下ORACLE,技術點不同,但是概念都是想通的,就是一些技術,比如事務,鎖啊,還有索引啊

?

總結

以上是生活随笔為你收集整理的Oracle之SQL分析函数的全部內容，希望文章能夠幫你解決所遇到的問題。

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