[資料庫] Oracle單表查詢總數及百分比和資料橫向縱向串連

標籤：

        這是最近項目關於SQL語句的，本文簡單記錄並總結以下幾個知識點：
        1.如何統計一張表中某個欄位的總數，如不同"專業"的學生數及所佔百分比；
        2.如何聯絡另一張表進行查詢某個欄位的總數及百分比；
        3.簡單介紹decode防止分母為0和trunc保留小數位元等函數；
        4.通常複雜的SQL語句會涉及到查詢結果橫向串連和縱向串連，這裡進行介紹。
        最近買了本《Oracle查詢最佳化改寫技巧與案例·有教無類 落落》，推薦大家也閱讀下。後面我也會補充一些相關資料的知識，希望對大家有所協助吧！文章還是以基礎知識為主，同時主要是解決實際的問題。同時我採用自問自答的新敘述方法進行介紹~


0. 前言
        假如現在存在如所示的一張表，這種表在資料庫中是很常見，主要包括欄位（序號，教師姓名，單位名稱，性別，學曆）。該表的資訊為：TEST_TEACHER（id，name，dw_name，sex，degree）。



1. 問題一： 如何在單表中統計總數及比例
        現在需要統計各個單位的教師人數，及該單位的不同學曆的、性別的人數。這是非常常見的問題，當時項目中統計的內容很多，包括：年齡、學緣、職稱、專業等等。
        這很簡單並且方法很多，常見的主要使用子查詢或group by分組。
        group by：

select DW_NAME, COUNT(DW_NAME) as 人數 from TEST_TEACHERgroup by DW_NAMEorder by DW_NAME;

        輸出結果如所示：其中order by是按漢語拼音排序輸出。

        如果現在需要統計各個學院的男性、女性教師人數，博士、碩士、學士的教師人數，需要怎麼辦呢？因為它都是同一張表的資訊，一種方法是使用子查詢，這裡介紹另外一種方法，通過CASE WHEN THEN實現。
         CASE WHEN THEN：

select DW_NAME, COUNT(DW_NAME) as SUM,     COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Women,    COUNT(CASE WHEN DEGREE='博士' THEN 1 END) as BS, COUNT(CASE WHEN DEGREE='碩士' THEN 1 END) as SSfrom TEST_TEACHER  group by DW_NAME order by DW_NAME; 

        輸出結果如所示：表示如何SEX為"男"，統計加1。

 
        現在如果需要統計每個學院所佔所有人數的比例，怎麼計算呢？
        傳統方法該步驟是分別統計兩個數，再通過後台Java或C++進行處理的，或者是使用除法: 學院總數/老師表總數，但是已經使用了分組group by，如果再使用一遍統計TEST_TEACHER總數就會報錯。
        這裡使用的是Oracle的函數ratio_to_report() over()實現。
         ratio_to_report：

select DW_NAME, COUNT(DW_NAME) as SUM,     ratio_to_report(COUNT(1)) OVER(),    COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Womenfrom TEST_TEACHER  group by DW_NAME order by DW_NAME; 

        輸出結果如所示：其中電腦3個老師，總數10個老師，比例佔0.3。

     
2.問題二： 聯絡另一張表統計總數及比例
       在設計資料庫過程中，通常會將教師的資訊設定為一張表，學院資訊會存在另一張表中，同理授課資訊、發表論文資訊都會存在單獨的表，再通過教師姓名（或教師編號）串連查詢。
       所以這裡講述第二種統計總數及比例的方法，假設還存在一張學院表，如所示：該表TEST_DEP結構為（DW_NAME，DW_CODE，YEAR，ADDR）。

       通常是遍曆該單位表，然後子查詢串連教師表，統計不同單位的人數資訊。同樣統計專業教師資訊、教師發表論文、學院發表論文都是這種方法。


        兩表串連子查詢 ：統計總數

select t2.DW_NAME,     (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女from TEST_DEP t2;

        輸出結果如所示：這種方法是非常常見的一種統計方法，而不是僅僅通過單表，因為資料庫往往都會通過外鍵聯絡其他表。

        這種兩表聯絡的另一個優勢是更方便計算百分比、比例等，因為同一張表使用兩次時很麻煩的，如 "select (select * from t1) from t1;"這裡使用子查詢計算比例代碼如下。
        兩表串連子查詢：統計比例

select t2.DW_NAME,     (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數,    ((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME)     / (select COUNT(*) from TEST_TEACHER)) as 總數比例,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,    ((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')    / (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME)) as 男教師比例from TEST_DEP t2;

        輸出結果如所示：

        其中總共10個教師，電腦學院3人，所佔比例=3/10=0.4，同時男教師比例為電腦學院男教師2人，女教師1人，故比例=2/3=0.666，這裡使用的方法是子查詢的除法。其實更常見的方法是擷取總數，然後後台商務邏輯進行計算。

 
        
3.問題三： 除法防止分母為0及保留有效位元
        如輸出為0.6666667，此時需要保留有效位元，這裡的方法可以是使用trunc()函數，整數可用floor()函數。
        TRUNC(x[,y])功能: 計算截尾到y位小數的x值，y預設為0，結果變為一個整數值。
        trunc()是截斷操作，floor(x)是小於或等於x的最大整數。
        TRUNC() FLOOR()：

select t2.DW_NAME,     (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,    trunc(((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')    / (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME))*100,2) as 百分比from TEST_DEP t2;

        輸出結果如所示：

        其中輸出為66.66，表示電腦學院男教師所佔百分比，其中保留2位有效數位方法為：trunc( 統計男教師子查詢 / 統計教師子查詢*100，2)。

        Oracle中通常需要統計如男生佔全班總人數比例等用法，此時如果分母為0，它會報錯"[Err] ORA-01476: divisor is equal to zero"。那怎麼辦呢？
        解決方案：使用函數decode，當分母為0時直接返回0，否則進行除法運算。
       select a/b from c; 修改成如下即可：select decode(b, 0, 0, a/b) from c;
        例如：decode(XF_ALL, 0, 0,trunc(XF_MATH / XF_ALL * 100, 1)) as BL
        上面子查詢除法使用decode的並結合trunc保留2位有效數位SQL如下。
        decode()：

select t2.DW_NAME,     (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME) as 總數,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男') as 男,    (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='女') as 女,    trunc(decode((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME),    0, 0,     ((select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME and SEX='男')    / (select COUNT(*) from TEST_TEACHER t1 where t1.DW_NAME=t2.DW_NAME)))*100,2) as 百分比from TEST_DEP t2;

        輸出結果如所示，核心方法：trunc(decode(b,0,0,a/b)*100, 2)。



4.問題四： 查詢橫向串連和縱向串連
        複雜的SQL語句通常包括橫向串連和縱向串連。
        橫向串連：使用自訂子查詢
        這裡使用的方法是子查詢自訂命名的方法，該方法只返回一行資料，通常通過Json格式給後台，後台只需要顯示即可。它的優勢是不論表多複雜或表之間沒有關係，而是只需要把值統計在一行，都能聯絡返回結果。
        如所示，這就是橫向串連返回的結果，但是需要知道具體的學院名稱。

        在統計不同表的總數資訊時，使用該方法比較好。

select t1.DW_NAME, t2.ZS, t3.BS, t4.DW_NAME, t5.ZS, t6.BSfrom (select DW_NAME as DW_NAME from TEST_TEACHER where DW_NAME='軟體學院' group by DW_NAME) t1,(select COUNT(*) as ZS from TEST_TEACHER where DW_NAME='軟體學院') t2,(select COUNT(*) as BS from TEST_TEACHER where DW_NAME='軟體學院' and DEGREE='博士') t3,(select DW_NAME as DW_NAME from TEST_TEACHER where DW_NAME='電腦學院' group by DW_NAME) t4,(select COUNT(*) as ZS from TEST_TEACHER where DW_NAME='電腦學院') t5,(select COUNT(*) as BS from TEST_TEACHER where DW_NAME='電腦學院' and DEGREE='博士') t6;

        縱向串連：使用UNION ALL串連
        假設現在只需要統計"軟體學院"、"電腦學院"的資訊，這裡需要縱向串連，則使用UNION ALL自然串連。
        Union：對兩個結果集進行並集操作，不包括重複行，同時進行預設規則的排序；
        Union All：對兩個結果集進行並集操作，包括重複行，不進行排序。
        注意：COUNT需要使用GROUP BY，這種方法的優勢是如果表中存在很多NULL的單位資訊或只需要統計幾個固定的學院資訊，此時使用該方法就比較適合。
        總之，特定的場合需要特定的處理方法。

select DW_NAME, COUNT(DW_NAME) as SUM,     ratio_to_report(COUNT(1)) OVER() as BL,    COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Womenfrom TEST_TEACHER WHERE DW_NAME='軟體學院' GROUP BY DW_NAME    UNION ALLselect DW_NAME, COUNT(DW_NAME) as SUM,     ratio_to_report(COUNT(1)) OVER() as BL,    COUNT(CASE WHEN SEX='男' THEN 1 END) as Man, COUNT(CASE WHEN SEX='女' THEN 1 END) as Womenfrom TEST_TEACHER WHERE DW_NAME='電腦學院' GROUP BY DW_NAME; 

        輸出結果如所示：

        相關資料：
        [資料庫] SQL語句select簡單記錄總結
        [資料庫] SQL查詢語句表行列轉換及一行資料轉換成兩列
        [資料庫] Navicat for Oracle基本用法圖文介紹
        [資料庫] Navicat for Oracle設定唯一性和遞增序列實驗

       最後希望文章對你有所協助，這是一篇我的線上筆記，同時後面結合自己實際項目和SQL效能最佳化，將分享一些更為專業的文章~還有20多天就要畢業當老師了，哈哈！
       (By:Eastmount 2016-06-14 深夜3點   http://blog.csdn.net//eastmount/ )

[資料庫] Oracle單表查詢總數及百分比和資料橫向縱向串連