[SQL]計算直通率
這幾天看到一則新聞說目前最火紅的是演算法工程師，剛好也聽到附近有些程式設計師在聊天說，「大學修一堆演算法、資料結構、作業系統等，結果現在寫程式一點用處都沒有」，因此一時興起整理一下幾年前處理的一個案例。
故事是這樣的，當時我在開發一個跟製造現場管理有關的產品，有一天我們需要製作一張直通率的報表，就是產品在製造的每個製程中的良率相乘起來，表示產品一次就做好的機率有多高，一般來說在製造業這個數值要越高越好。舉例來說有個產品，它有五道製程，每道製程的良率都是 99%，那直通率就是 95% ( 0.99*0.99*0.99*0.99*0.99 )。
知道計算原則之後，那麼寫程式就簡單囉，反正只要把產品和良率找出來，那用迴圈跑一下不就知道了 ? 是這樣沒有錯，但如果我們希望直接用 SQL 指令就算出來，那該怎麼做呢 ? 有接觸過 SQL Aggregate 函數的應該都知道，能用的不外乎 SUM , MAX , MIN , COUNT , AVG , STDEV …等，但好像就是沒有連乘起來的函數。因此在接到這樣問題的時候，就看到大家有不同的處理方式，有人說沒有辦法，只能把資料都撈出來計算之後再塞到 Temp Table；也有人利用 Stored Procedure 去處理，裡面去 cursor 去一筆一筆處理；也有人把資料先用字串的方式串起來，在用 EXECSQL 的方式去計算出來，但好像都沒有方法直接計算出結果。
而這個問題就讓我聯想到，當我還在 SJSMIT 念專一的時候，當時社團教 BASIC 的老師出了一個題目考我們，看誰能寫一個 1.01 的 1000 次方，當時很自滿的用迴圈寫了一段程式交出去，但馬上被老師打回來說跑太慢了…接著想辦法把程式改成遞迴，或者是換成組合語言來寫，跑出來的結果還是比老師的慢很多。這個題目讓我從開學第一天練習到學期末，都還是沒有辦法想到好的解法，最後只好乖乖的去問老師，結果老師笑笑的重新教我一次 exp 和 ln ，兩個我一值覺得他只是微積分裡面兩個不起眼的東西，沒想到卻有那麼大的用處。
回到直通率的計算，資料可能會類似下面的狀況
DECLARE @DATA TABLE ( Item varchar(10), Seq int, Acceptability numeric(3,2) ) INSERT @DATA VALUES ( 'A' , 1 , 1),( 'A' , 2 , 0.99), ( 'A' , 3 , 1),( 'A' , 4 , 0.98), ( 'B' , 1 , 1),( 'B' , 2 , 0.98), ( 'B' , 3 , 0.97), ( 'C' , 1 , 1),( 'C' , 2 , 0.93), ( 'C' , 3 , 1),( 'C' , 4 , 0.95), ( 'D' , 1 , 1),( 'D' , 2 , 0.99), ( 'D' , 3 , 1),( 'D' , 4 , 0.97),( 'D' , 5 , 0.98)
因此我們如果要計算直通率的話，那麼配合 Exp & Ln 的特性，就可以在 SQL 內做到連乘的效果了。
SELECT Item,exp( sum( log(Acceptability) ) ) FPY FROM @DATA GROUP BY Item
如果您的系統有類似計算需要去處理的話，不妨可以試試看這樣的方式，可以讓你減少很多的計算時間喔。