How Can I Randomly Select a Row from a Table with Weighted Probabilities?

Random Row Selection with Weighted Probability

Problem:

Imagine having a table with columns such as 'id,' 'content,' and 'weight.' You want to randomly select a row from this table, considering the 'weight' value. For instance, if three rows exist:

id	content	weight
1	some content	60
2	other content	40
3	something	100

The first row has a 30% chance of selection, the second row has a 20% chance, and the third row has a 50% chance.

Solution:

One approach is to apply weighted reservoir sampling:

SELECT
  id,
  -LOG(RAND()) / weight AS priority
FROM
  your_table
ORDER BY priority
LIMIT 1;

This method allows you to select one out of many elements with probabilities proportional to their weights. It works effectively for selecting a single element.

The technique is elaborated upon in this article: [Weighted Reservoir Sampling](https://bit.ly/weighted-res-sampling). Note that the article selects the largest values of POW(RAND(), 1/weight), which is equivalent to choosing the smallest values of -LOG(RAND()) / weight.

The above is the detailed content of How Can I Randomly Select a Row from a Table with Weighted Probabilities?. For more information, please follow other related articles on the PHP Chinese website!