0

I'm using the following query to select a random campaign:

SELECT * from (  
SELECT campaign_id
FROM campaigns
WHERE campaign_approved = 1
) T ORDER BY RAND() 
LIMIT 1

However, I'd like to select campaigns, still randomly, but with odds of being selected in proportion to their campaign_balance.

So for example, if one campaign has a campaign_balance of 50 and the other has a campaign_balance of 1.....the first one should have 50 / 51 chance of being selected.

How do I do a random selection that is biased like this?

Amy Neville
  • 10,067
  • 13
  • 58
  • 94
  • you need to assign this campaign_balance in a new cumulative column, then ramdomly select between 1 and max cumulative value and find it. – Horaciux Jan 14 '16 at 18:22
  • I'm still learning mysql, this makes no sense to me :) – Amy Neville Jan 14 '16 at 18:23
  • look at this for cumulative column or running total http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql – Horaciux Jan 14 '16 at 18:28
  • Create a new table with a structure like this: campaing_id, campaign_balance, cumulative_chance. Does it make sense to you? – Horaciux Jan 14 '16 at 18:32
  • campaign_balance already exists in the table – Amy Neville Jan 14 '16 at 18:32
  • ok, great, you can create a new table or add a new column, cumulative_chance. This column will be calculated as mentioned in previous link. – Horaciux Jan 14 '16 at 18:35
  • Yes, but I'm trying to run a selection efficiently. Updating a table and then selecting is two queries? – Amy Neville Jan 14 '16 at 18:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100692/discussion-between-horaciux-and-amy-neville). – Horaciux Jan 14 '16 at 18:45

0 Answers0