How Can I Randomly Select a Row from a Table with Weighted Probability Using SQL?

Random Row Selection with Weighted Probability

Considering a table with columns id, content, and weight, the task is to randomly select a row while taking into account the weight. In a scenario where three rows exist with weights of 60, 40, and 100, respectively, the challenge is to compute the probability of selecting each row as follows:

• Row 1: 30% chance
• Row 2: 20% chance
• Row 3: 50% chance

Weighted Reservoir Sampling

The optimal approach for this problem is weighted reservoir sampling, which can effectively select items with probabilities proportional to their weights. Here's how to apply it:

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

This SQL query employs the following logic:

• It pairs each row with its priority, calculated as the negative logarithm of a random number divided by the row's weight.
• Sorting the rows by priority ensures that rows with higher weights have a greater chance of appearing at the top of the result set.
• Limiting the result to a single row yields the randomly chosen row with weighted probability.

This weighted reservoir sampling technique can be used to select multiple rows or even the entire table with weighted probabilities, making it a versatile solution for various data selection scenarios.

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