Choose a random row, but with a coefficient

Question

I have a data set, each of which has a number of "chances" from 1 to 100. I try to do this in the most efficient way. Odds are not necessarily up to 100.

I had a few ideas.

a) Select the entire data set, and then add all the coefficients and create a random number between 1 and that number. Then cycle through the data set by subtracting the coefficients from the number until 0.

I was hoping to minimize the impact on the database, so I wondered if I could only select the rows I needed.

b)

SELECT * FROM table WHERE (100*RAND()) < odds

I considered LIMIT 0,1

But if the elements have the same probability, then only one of them will be returned

As an alternative, take the entire dataset and select a random one ... but then the chances will be affected, because it becomes random with chances, and then random with no chances, so the chances are tilted in favor of higher odds (even more so).

I assume that order by oddsASC could take the entire data set, and then with PHP take a random one from the lines with the same chances as the first record (the lowest).

Sounds like clumsy solutions.

Does anyone have an excellent solution? If not, which of the above options is better?

Answer 1

, , . , ,

 X  2  
 Y  3
 Z  1

 Key Odds Start  End 
 X    2     0     1      // range 0->1, 2 values == odds
 Y    3     2     4      // range 2->4, 3 values == odds
 Z    1     5     5      // range 5->5, 1 value == odds

. . , . - (Start + Odds-1).

R 0 Max (End)

Select * from T where R >= T.Start and R <= T.End

,

 Select * from T where R >= T.Start and R <= (T.Start + T.Odds - 1)

, End . , Max (End), , - , .

, Start/End. ,

• , .

Answer 2

, - (? 0 SUM(odds) - 1)?

SET @prob := 0;

SELECT
  T.*,
  (@prob := @prob + T.odds) AS prob
FROM table T
WHERE prob > ?
LIMIT 1

, a), (, , ) SQL.

Answer 3

, :

SELECT id, odds FROM table WHERE odds > 0

, , .

1 .

.

, , :

SELECT * FROM table WHERE id = ?

.

---

.

Odds
ID     odds
1      4
2      9
3      56
4      12

ID .

. . :

SELECT MAX(ID) FROM Odds

1 .

.

SELECT * FROM table
JOIN Odds ON Odds.ID = table.ID
WHERE Odds.ID >= ?
LIMIT 1

, Odds , .

SQL Antipatterns.

Answer 4

, ORDER BY RAND() LIMIT 1?

SELECT * FROM table WHERE (100*RAND()) < odds ORDER BY RAND() LIMIT 1

, , , .

Answer 5

select * from table 
where id between 1 and 100 and ((id % 2) <> 0) 
order by NewId() 

Answer 6

. , , , . , , :

. :

Odds
====
   1
   2
   2
   3
   3
   3
   4
   4
   4
   4
etc, 
etc.

. , .

.

Answer 7

, O (log (n)), :

• Store objects as leaves of a (balanced) tree.
• In each node branch, keep the weight of all objects under it.
• When adding, removing, or modifying nodes, update the weight of your parents.

Then select a number from 0 to (total weight - 1) and go down the tree until you find the desired object.

Since you do not care about the order of things in the tree, you can save them as an array of N pointers and N-1 numbers.