How to Add "Weights" to a MySQL Table and Select Random Values According to These

How to add weights to a MySQL table and select random values according to these?

I found this nice little algorithm in Quod Libet. You could probably translate it to some procedural SQL.

function WeightedShuffle(list of items with weights):
max_score ← the sum of every item’s weight
choice ← random number in the range [0, max_score)
current ← 0
for each item (i, weight) in items:
current ← current + weight
if current ≥ choice or i is the last item:
return item i

selecting random rows based on weight on another row

I think the difficult part here is getting any individual row to potentially appear more than once. I'd look into doing something like the following:

1) Build a temp table, duplicating records according to their frequency (I'm sure there's a better way of doing this, but the first answer that came to my mind was a simple while loop... This particular one really only works if the frequency values are integers)

create table #dup
(
id int,
nm varchar(10)
)

declare @curr int, @maxFreq int
select @curr=0, @maxFreq=max(freq)
from tbl

while @curr < @maxFreq
begin
insert into #dup
select id, nm
from tbl
where freq > @curr

set @curr = @curr+1
end

2) Select your top records, ordered by a random value

select top 10 *
from #dup
order by newID()

3) Cleanup

drop table #dup

mySQL select random with probability

SELECT
IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour
FROM
(SELECT @rnd:=rand()) AS rndinit;

Gives you the flavour with the requested probabilities.

SELECT * FROM tablename ORDER BY rand() LIMIT 1

gives you a single random row. Now we put it together:

SELECT
tablename.*
FROM
tablename
INNER JOIN (
SELECT
IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour
FROM
(SELECT @rnd:=rand()) AS rndinit
) AS rndview ON rndview.rndflavour=tablename.flavour
ORDER BY rand()
LIMIT 1


Related Topics



Leave a reply



Submit