資料表欄位中權重與機率的選擇

依據資料表中權重(Weight)的欄位,來決定此筆資料出現的頻率,如網頁的廣告…之類的應用,這是相當相當常見的應用。
實際的條件為:
條件1. 權重(Weight)的欄位是一個1-10的數字,數字愈大,其出現的頻率就愈大。(也就是權重值為10的資料,其出現的頻率要比權重值為9的要常出現。)

條件2. 權重值愈大,不代表每次都一定會出現,有可能權重值為1的資料出現了,但權重值為10的資料卻沒有出現。

條件3. 若資料表中的資料有3筆資料,但系統固定要取得10筆資料,那麼還是要將此3筆資料(不論其資料的權重值為何)都顯示出來。

條件4. 取出來的資料皆是不重覆的資料。


假設資料表中的資料如下:
ID, Weight
1, 2
2, 8
3, 3
4, 1
5,6

第一次實作的時候,我使用程式來處理這個問題,

Method 1: 將Weight當成Id出現個數,生成一個陣列,再利用程式的random()取得0至Array.size()的數字後,依據這個數字取得陣列中的Id。

上述的方法會生成這樣的陣列DataArray[1,1,2,2,2,2,2,2,2,2,3,3,3,4,5,5,5,5,5,5]
然後取得DataArray[round(random() * DataArray.size())] 的值即符合所有的條件。
但有一個很大缺點,就是如果系統需要取3筆資料在最壞的情況下,random()一直取得相同的值,或是某筆資料有很高的權限值如([1,2,3,4,4,4,4,4,4,4,4,4,4,4]),這樣的話,系統為了完成取3筆資料的條件,而重覆呼叫random(),但一直沒辦法取得不重覆的內容。
另一個很大的缺點是,如果此資料表中的資料量很大,那麼建出來的陣列就相當可怕。


所以不得不改變做法,於是詢問了一位SQL很強的已離職同事,他給我一個SQL


Method 2:
select * , abs(ID - round(rand() * (select max(ID) from TABLE ))) as DISTANCE
from
(
select * from TABLE
where WEIGHT >=  rand()
) t
order by DISTANCE limit 0,2

這個方法的好處就是可以直接取出我要的資料,不需要在程式中額外處理,但在試run後,卻發生了資料表中有資料,但卻查不到任何資料。其原本在於子查詢中,先過濾WEIGHT大於某個亂數值的結果,若資料表中的WEIGHT都為5,但rand()出的值是6 ,那麼所有的資料都會被過濾掉。

因此我以這個方法做為基底改良了如下的Method
Method3:
select ID,  ROUND(WEIGHT + (RAND()*10)) AS NEW_WEIGHT,
ROUND(ABS(10 - (RAND()*10))) AS DISTANCE
from TABLE
ORDER BY NEW_WEIGHT DESC, DISTANCE

首先將Weight加上一個亂數(此亂數需介於Weight最小值及最大值之間),讓
最小的Weight有可能大於最大的Weight。
再來依Weight的最大值減掉一個亂數值作為距離,再以NEW_WEIGHT
DISTANCE進行排序。
這樣即可符合所有條件,目前也看不到什麼不良的副作用。執行效率非常好。

若有網友有更好的處理方法,也請不吝賜教。

留言

熱門文章