程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 分段統計與Oracle的分析函數等知識點的綜合運用

分段統計與Oracle的分析函數等知識點的綜合運用

編輯:Oracle教程

 

項目要求:

統計每個巡檢員(USER_ID)當前月的簽到率及查詢相關字段

簽到率公式:以巡檢員為單位,

(當月至今天為止簽到的所有點/該月巡檢點的總個數)=(b.Point/a.Total)

TOTAL相關更多要求:

①在使用過程中,巡檢點個數會根據實際情況進行變更,例如2014年1月15日給某一位巡檢員安排5個點,10天後增加了2個點,即2014年1月24日以後按照7個點來統計,所以,如果當月更改多次的話,這個月會分成許多段。

②每一次更改有一個批次號,對應表T_SIGN_POINTS的POINT_DATE字段,這個字段時更改的第二天,即今日更改,明天生效。

③TOTAL公式:

假設有以下已知條件:

假設今天是2014年4月25日,USER_ID=1249,統計2014年4月的該巡檢員應該巡查的點的個數,T_SIGN_POINTS中相關批次有Batch2014.3.29=6個、Batch2014.4.5=8個,Batch2014.4.12=11個、Batch2014.4.26日=5個

則TOTAL的數學計算應該為:6個×4天+8個×11天+11個×13天=255個(另有其它一些邊界情況類似)

這裡需要考慮到多種可能性,即便只使用最理想的情況,將上面這個公式完全使用SQL語言完成,也是一個挺復雜的過程,相比之下,查詢需要的字段後在後台做計算難度會降低,不過這種寫法可以大幅提升對Oracle或者SQL語言的運用和理解,也有可能Oracle有更合適的函數來解決類似問題。 

相關表及字段情況:

表T_SIGN_RECORDS:記錄該巡檢員已簽到的點

表T_SIGN_POINTS:記錄給每個巡檢員安排的巡檢點

完整SQL語句:

         
    ( a.                                    
                   
                    
                   
                  
                   
                                                     
                   
                    
                   
                  
                   
                                 b.name                   c.name                         a.dep_id              a.role_id              IS_PATROL       ( a.                     (
                        b.point   
                        
                       
                      )    (a.total)                  ( , (PartNum)  total   
                         (                                           decode(                                                                                        decode(greatest(batchday, tday), tday, (tday  batchday  )  batchnum, batchday, ,                                                                                                  (seg_next  batchday)                                                     (tday  batchday  )                                                                                                )                                  ( a.,                     
                                                 lead(a.batchDay, , ) (PARTITION  a.   a.batchDay, a.batchmon)                                          ( t.,              
                                                     to_char(( EXTRACT(  sysdate)  DUAL))                                                      to_char(( EXTRACT(  sysdate)  DUAL))                                                      decode((to_CHAR(TO_DATE(t.points_date, ),                                                             (to_CHAR(sysdate,                                                             ( (trunc(TO_DATE(t.points_date, ), )  )                                                             ( trunc(sysdate, )  dual))                                                      decode((to_CHAR(TO_DATE(t.points_date, ),                                                             (to_CHAR(sysdate,                                                             to_number(to_CHAR(TO_DATE(t.points_date, ),                                                             )                                                      (t.)                                                                                                 (to_date(t.points_date, )  ( trunc(sysdate, )  dual)                                                     (t.points_date  ( (points_date)                                                                                                                                                     t.
                                                                           (to_date(points_date, ) 
                                                                              ( trunc(sysdate, )                                                                                                                            t.                                                 t. , batchDay , batchmon ) A  
                                          , batchday , batchmon ))   
                         
                          ) a                  
                  ( ()  point,               
                                                        to_char(sign_time, )  
                             
                   a.  b.      x.id  y.
    order_index, 

注釋:

【外圍層】連接到:用戶表(T_USER)、部門表(T_DEPARTMENT)、角色表(T_ROLE)查詢一些字段 ,值得注意的是CASE WHEN THEN ELSE END和顯示格式“|| '/' ||”的使用

【POINT層】一個簡單的count函數的使用

【TOTAL層】是重點,由多層嵌套而成,接下來是各層的結果圖片和知識點:

【TOTAL第0層】

結果圖片:

SQL功能:查詢並統計與當月相關的批次即各批次即該批次的點的個數

BatchNum為該批次的點的個數,

TMon為當月的月份,

BatchMon為批次號所在的月份,這個字段的值可以區分批次號是當月的還是本月之前的最大批次號,為的是輔助lead函數進行排序,如果批次號在當月,結果為當月的第2天,如果在這個月之前,返回當月的第1天

【TOTAL第1層】

結果圖片:

SQL功能:主要增加SEG_NEXT字段,將下一個批次號的日期做為SEG_NEXT

lead(a.batchDay, , ) (PARTITION  a.   a.batchDay, a.batchmon)  seg_Next

PARTITION以a.user_id為區域執行lead函數,按照a.batchDay和a.batchmon排序,這裡可看出batchmon字段的意義:如果本月在2014年4月1號有批次號,就把小於本月的最大批次號放在4月1號批次號的下面,這樣,結果圖片中第9行batchDay和SEG_NEXT就都是都是1,兩段的差值是0,不會影響後續的結果。

 

【TOTAL第2層】

結果圖片

SQL知識點:

Decode函數:

Decode函數可以與sigh函數和greatest函數結合使用,其中與greatest函數結合使用的時候,要注意參數的順序和if_value和value的順序,因為greatest的參數存在相等的情況,當參數相等的時候,會執行第一個if_value的value

 

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved