How to Randomly Select a Row from a SQL Table with Weighted Probabilities?

Random Row Selection with Weighted Consideration

SQL provides a means to randomly select a row from a table. However, taking into account a weight for each row to influence the selection probability is not straightforward.

One method to achieve this is weighted reservoir sampling, which is an elegant and effective technique for selecting elements from a set with replacement and with probabilities proportional to their weights.

Implementing this in SQL involves:

• Calculating a priority score for each row by dividing the negative logarithm of a random number by its weight.
• Sorting the rows by this priority score in ascending order.
• Selecting the first row in the sorted results.

This query accomplishes this:

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

In this query, LOG(RAND()) generates a uniformly distributed random number between 0 and 1. Taking the negative logarithm of this number effectively inverts its value, resulting in a larger number for smaller random values. By dividing this inverted value by the row's weight, we obtain a smaller priority for rows with higher weights.

As a result, rows with higher weights will have a lower priority score and are more likely to be at the top of the sorted results, thereby increasing their chances of being selected. This approach ensures that the selection probability of each row is proportional to its weight.

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