How to Select a Weighted Row from a MySQL Database Using Reservoir Sampling?

Selecting a Weighted Row from a Database

In data management, randomly selecting rows from a table is a common task. However, there are scenarios where weighting needs to be applied to the selection process, ensuring that certain rows have a higher probability of being chosen.

This article explores a solution to this issue using the weighted reservoir sampling method for MySQL databases. Consider a table with columns: id, content, and weight. We aim to randomly select a row while taking into account the weight.

The SQL query below harnesses the weighted reservoir sampling method to achieve this:

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

This query calculates a priority value for each row by dividing the negative natural logarithm of a random number by its weight. The rows are then sorted in ascending order of priority, with the row with the lowest priority (highest value of priority) selected as the result.

By utilizing this weighted reservoir sampling technique, we can randomly select a row from a table, where the probability of selection is proportional to its weight. This allows us to ensure that rows with higher weights have a greater chance of being chosen.

The above is the detailed content of How to Select a Weighted Row from a MySQL Database Using Reservoir Sampling?. For more information, please follow other related articles on the PHP Chinese website!