Page 1 of 1

Weighted Random

Posted: Wed Jan 17, 2007 1:53 pm
by Pezmc
I have a mysql query querying my database using order by RAND() LIMIT 1 to sellect a random coice from the database, however I would like to have the chance of a object being chosen increased for certain values (Weighting?)

In my database I have:

Code: Select all

id  name weight
1  Pezm  1
2  Shog  1.1
3  Ebay  1.2
4  PLp   1.3
5  php  1.4
So follwing the information in my database if a weighted random script is used php shuld be the most frequent with 1.4*the chance to get chosen, compared to Pezm,

How can this be acchieved?

Posted: Wed Jan 17, 2007 1:56 pm
by feyd

Posted: Wed Jan 17, 2007 5:47 pm
by pickle
Since RAND() can accept a variable, I'm wondering if it would be possible to put the `weight` field as the argument. That way, the higher the weight, the higher a possible number could be:

Code: Select all

SELECT
  *
FROM
  myTable
ORDER BY
  RAND(weight)

Posted: Thu Jan 18, 2007 4:54 am
by Mordred
You make an accumulative sum of weights for each item, then normalise the random by multyplying by the total sum of weights.

Here's a quick hack (without the LIMIT 1 which you'll also need), with the accumulative sum and total sum also shown as columns (you don't really need them).

The heart of it is the rand()*total_sum >= accumulated sum. The sum is also accumulated backwards, so we can choose the topmost result instead of the bottommost.

Code: Select all


CREATE TABLE `rand` (
  `id` int(11) NOT NULL auto_increment,
  `item` varchar(255) NOT NULL default '',
  `weight` float NOT NULL default '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `rand` (`id`, `item`, `weight`) VALUES 
(1, 'item 1', 1),
(2, 'item 2', 2.5),
(3, 'item 3', 1.3),
(4, 'item 4', 0);

SELECT * , (

SELECT sum( weight ) 
FROM `rand` AS t_sum
WHERE t_sum.id >

t.id
) AS weight_sum, (
SELECT sum( weight ) 
FROM `rand` 
) AS weight_total
FROM `rand` AS t
WHERE weight !=0
AND rand( ) * ( 
SELECT SUM( weight ) 
FROM `rand` ) > = ( 
SELECT sum( weight ) 
FROM `rand` AS t_sum
WHERE t_sum.id >

t.id ) ORDER BY t.id ASC