Kawałek Kodu

Jeśli trafiłeś na ten artykuł, to być może miałeś łut szczęścia. Nie musisz mieć jednak szczęścia, aby losować rekordy. W MySQL zazwyczaj używałeś do tego funkcji RAND. Co prawda ta funkcja samodzielnie nie losuje rekordu, ale w połączeniu z klauzulą LIMIT 1, daje to, czego od niej oczekujemy. Tyle, że nie zawsze, bo czasem człowiek chce czegoś więcej. I być może chciałeś losować rekordy z jakąś wagą, czyli niektóre wartości miały pojawiać się w wyniku częściej, a niektóre rzadziej.

Na razie nie przykładajmy uwagi do wagi.

Niech nasza przykładowa tabela ma nastepujące rekordy:

SELECT imie FROM tabela ORDER BY RAND() LIMIT 1

Klauzula ORDER BY w połączeniu z funkcją RAND powoduje, że rekordy w wyniku otrzymują losową pozycję, a LIMIT 1 wybiera po prostu pierwszy rekord z puli. Ponieważ tabela ma 8 rekordów wylosowanie jednego z nich zachodzi z prawdopodobieństwem 1/8, czyli 12,5%. Taką szansę na wybór ma każdy z ośmiu rekordów.

A kiedy rekord miałby większą szansę na łaskawszy wybór przez szajkę RAND+LIMIT?
Jeśli pojawiłby się w tabeli kilkukrotnie.

Zmodyfikujmy naszą tabelę dodając kolumnę waga.

Natomiast zapytanie zbudujemy takie:

SELECT t1.imie FROM tabela AS t1 JOIN tabela AS t2 ON t1.waga>=t2.waga ORDER BY RAND() LIMIT 1

Mamy tu powiązanie tabeli do samej siebie (tzw. self join) poprzez nierówność opartą na kolumnie waga. Myślę, że łatwiej będzie pokazać "co i jak", kiedy na chwilę usuniemy naszą znajomą szajkę, a więc zapytanie będzie takie:

SELECT t1.imie FROM tabela AS t1 JOIN tabela AS t2 ON t1.waga>=t2.waga

Poprzez nierówność waga pojedynczego rekordu z tabeli t1 będzie porównywana z wagą wszytkich rekordów po kolei z tabeli t2. Dla ośmiu rekordów będą to 64 porównania. Rekord będzie spełniał warunek dopóki jego waga będzie mniejsza lub równa od porównywnej. Nawiązując do naszej tabeli Janek pojawi się jeden raz (5>=5, 5>=17, 5>=22, 5>=45, 5>=67, 5>=15, 5>=18, 5>=33), Marek 3 razy (17>=5, 17<=17, 17>=22, 17>=45, 17>=67, 17>=15, 17>=18, 17>=33), Stefan 5 razy, Romek 7, Antoni 8, Waldek 2, Michał 4, a Mirek 6.
Czyli przy takim zapytaniu wagi wcale nie mają wpływu na częstotliwość pojawienia się rekordu. Ich wartości nadają tylko porządek rekordom w wyniku czego dany rekord pojawia się X razy, a jego sąsiad z większą wagą X+1 razy (bo dzieli ich jeden rekord na podstawie porządku wag). Jeśli satysfakcjonuje Cię takie rozwiązanie, to wagom dla lepszej czytelności możesz nadać wartości różniące się o 1.

A jeśli nie satysfakcjonuje?

Należy jakoś zwiększyć występowanie rekordów, tak aby ilość każdego z nich odpowiadała wartości wagi, a nie tylko pozycji wagi. Wykorzystamy tu metodę opisaną we wpisie Plaga mrówek, czyli jak zmultiplikować rekordy zapytania.

SELECT t1.imie FROM tabela AS t1
JOIN
(SELECT a.a + (10 * b.a) AS waga FROM(
 (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
 CROSS JOIN
 (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
 )
) AS wagi ON t1.waga>wagi.waga
ORDER BY RAND() LIMIT 1

Jeśli wygląda przerażająco, to pewnie powinieneś jeszcze raz (lub pierwszy raz) przeczytać wpis, o który wyżej wspominałem. Zapytanie jest bardzo podobne do poprzedniego, lecz nie łączymy tabeli t1 z samą sobą, ale z tabelą liczb od 0-99. Jeśli więc Janek ma przypisaną wagę 5 (chudziak!), to w wyniku znajdzie się właśnie tyle razy, bo spełni 5 warunków (5>0, 5>1, 5>2, 5>3, 5>4, 5>5, itd.). Tu korzystamy z nierówności ostrej, bo zaczynamy numerację od 0, a nie od 1. Gdyby nierówność była nieostra, to rekord powtórzyłby się 6 razy (warunek 5>=5). Tabela liczb powinna zawierać liczby z zakresu 0-maksymalna_waga_rekordu-1. Obecnie może obsłużyć wagę 100.
A jakie prawdopodobieństwo wylosowania ma teraz Janek? Jest to 5/222 (ok. 2,25%), gdzie pierwsza liczba to oczywiście waga rekordu, a druga suma wag wszystkich rekordów.

Życzę Ci odwagi, rozwagi i uwagi w losowaniu rekordów z wagą!

Przydatne linki:
Plaga mrówek, czyli jak zmultiplikować rekordy zapytania.
Funkcja RAND w MySQL