[Oracle]Mutil Row To One Row應用(一)

我有一張Log Table，會紀錄著procedure開始執行與結束的時間，該Log Table的紀錄方式為procedure Begin時寫入一筆資料，procedure End時寫入一筆資料，所以每次procedure執行只要是成功的狀態，就會有雙雙成對的Begin/End的資料，我期許可以算出每次procedure執行所需要的時間，於是就有把每兩筆Log Data變成一筆後去做時間運算的需求。

select * from log_table t

呈現上圖結果，利用兩個欄位相減去運算出每次Loader所執行花費的時間。

以上這個題目拋出請同事們一起動動腦，加上我個人的解法共募集了三種解法。

解法一：

select a.execute_time begin_time,
       b.execute_time end_time,
       round((b.execute_time - a.execute_time) * 24 * 60, 2) cost_time
  from (select rownum begin_rn, execute_time
          from (select *
                  from log_table t
                 where t.procedure_name = 'Loader1'
                   and t.code = 'Begin'
                 order by t.execute_time)) a,
       (select rownum end_rn, execute_time
          from (select *
                  from log_table t
                 where t.procedure_name = 'Loader1'
                   and t.code = 'End'
                 order by t.execute_time)) b
 where a.begin_rn = b.end_rn

上列解法一運用兩份sub query的join去做資料結合，
將Begin的sub query與End的sub query 利用rownum來join。

不過解法一對log_table做了兩次資料處理，在大量資料的狀況下，想必效能應該會稍差。

解法二：

select max(decode(code, 'Begin', execute_time)) begin_date,
       max(decode(code, 'End', execute_time)) end_date,
       round((max(decode(code, 'End', execute_time)) -
             max(decode(code, 'Begin', execute_time))) * 24 * 60,
             2) cost_time
  from (select rn,
               mod(rn, 2) rn_1,
               ceil(rn / 2) rn_2,
               b.code,
               b.execute_time
          from (select rownum rn, a.code, a.execute_time
                  from (select *
                          from log_table t
                         where t.procedure_name = 'Loader1'
                         order by t.execute_time) a) b)
 group by rn_2

上列解法二運用rownum的數學運算後，再執行group by去做資料結合，
先利用rownum將每一筆資料依照時間排序編列流水號，
mod為取餘數，目前在分組運算中並沒利用到，
ceil為無條件進入，解法二中就是將每一筆資料除以2後取無條件進入，這樣就將每兩筆變成兩兩一組，有同樣的編號(rn_2)，
當我們已經順利的將資料分成兩兩一組後，再利用group by rn_2，搭配decode與群組函數max就可以轉秩資料(row to column)。

解法三：

select b.begin_time,
       b.end_time,
       round((b.end_time - b.begin_time) * 24 * 60, 2) cost_time
  from (select a.*, rownum rn
          from (select t.execute_time begin_time,
                       lead(execute_time, 1) over(ORDER BY execute_time) AS end_time
                  from log_table t
                 where t.procedure_name = 'Loader1'
                 order by t.execute_time) a) b
 where mod(rn, 2) = 1

解法三主要是應用oracle特有的function lead去做資料結合，
lead主要是把每一筆資料的下一筆資料的某的欄位抓上去擺在隔壁當鄰居欄位，
所以在a的sub query中Begin的execute_time就會有一個鄰居欄位為End 的execute_time
當然End 的execute_time也會出現一個鄰居欄位為Begin的execute_time，
所以我們就應用解法二裡面的mod餘數，只去取Begin的那一筆來計算，Begin那筆的rownum 取2的餘數都是1，End那筆的rownum可被2整除，所以餘數都是0。

解法二、三針對來源Table:log_table都只做一次資料處理，所以可想而知的效能應該會比較好才是，相關那個解法的效能較佳，數據會說話，等我的後續報導啦~~