項目要求:
統計每個巡檢員(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