Page 1 of 1

weighted results in random select query?

Posted: Thu Oct 13, 2005 12:02 pm
by jxn
hey there,
I've got a database table containing mp3/ogg/etc filenames and other data, and I'm using that table to generate random playlists, but I really want certain songs to play at a slightly higher frequency than others. The database has a few thousand (perhaps a few 10,000) tracks listed in it, and many of them have the release date and a 1 digit rating (from 0 - 5, the higher the better) appearing as feilds in the table...and I want to give those tracks priority (so that the best and newest might get, say, 30-40% higher probability of being played over the course of a 10 or 15 hour playlist)... any help/inspiration/ideas?

Posted: Thu Oct 13, 2005 12:51 pm
by timvw
well, i'll try to explain a system ;)

Assume you have the songs a, b, c, d and f. And you give them weights 1, 3, 4, 2, 1

you could compose a virtual (numeric indexed array as)

a b b b c c c c d d f

this way, you end up with 1 + 3 + 4 + 2 + 1 = 11 items.

Now you choose a random number in the range of 1 to 11... This is the index of the random weighted song..

Thus, if you get 1 -> play a, if you get 2, 3 or 4 play b, ...

Posted: Thu Oct 13, 2005 1:11 pm
by jxn
Thanks. I had considered this as an option already, but right now I'm simply selecting the random tracks one at a time by pulling them from the database using "ORDER BY RAND() LIMIT 1". My assumption was that, by only pulling one row at a time out of mysql, I would speed things up and save resources over a method that might pull all 5,000+ tracks into a single, multi-dimensional array...especially one that doubles, triples, etc the weighted tracks. It also doesn't weight songs based on their date (a `date` type mysql field), although that isn't quite as important to me. I'm surprised if there's no fairly easy way to retrieve a weighted random retrieve from mysql, but if I cannot find anything else, perhaps I'll try this solution. Anyone else have any other ideas?

Posted: Fri Oct 14, 2005 1:33 am
by AGISB
I would first add a table row weight to the table and I assume you already have this. Index it. I add a date field and automatically downgrade titles that are in the database for a certain time.

I then would create the before mentioned virtual table but not on songs but on the weight numbers.

Lets say out of 100 40 are highest weight then 25 20 10 5 ... . Then random that and use a random select query with the indexed where of weight.

A played song is placed in a already played table or you add a played row to the initial table to mark the played songs to avoid possible repeats within a short period. Time the repeats for each weight differently so low rated songs e.g. get only played once in a week but high rated once might repeat each hour.

You could add a preferred row to enable certain advertising songs etc.