Page 1 of 1

Seeded Random

Posted: Sat Oct 06, 2012 12:27 pm
by shiznatix
Hi,

So, I had a previous post here: viewtopic.php?f=1&t=136014 about random not being really random.

Now, I have been looking to make this a better random and now I have a setup ready. Basically, I have a table with 2445 rows and I am taking 5 random rows out of it. Simply, I just do:

SELECT...ORDER BY RAND() LIMIT 5

Each row has a "plays" count, and every time a show gets selected, it gets a +1 to its plays count.

Now, I want to use this "plays" field to seed my random select. I would like rows that have the lowest number of "plays" to be more likely (seeded in their favor) to be selected. That is not to say that the higher number rows couldn't be selected, but just that the higher the number, the less likely it is to be selected.

To be honest, I have no idea how to do this. I have only used built in random functions without any weighting. Can anyone point me in the right direction with this? It can be in PHP or in SQL, or a combo of both.

Re: Seeded Random

Posted: Sat Oct 06, 2012 3:30 pm
by Benjamin
Honestly if it doesn't need to be super efficient I would pull some of the least played tracks, say 100, then use array_rand to retrieve a random sample. ORDER BY RAND() doesn't work worth a damn.

Re: Seeded Random

Posted: Sat Oct 06, 2012 4:11 pm
by shiznatix
Benjamin wrote:...ORDER BY RAND() doesn't work worth a damn.
This is very sad. Random is a tough one

Re: Seeded Random

Posted: Sat Oct 06, 2012 5:51 pm
by requinix
There's problems with ORDER BY RAND()? (when you actually do want the entire table shuffled)

If you want it weighted by the number of plays then it's just something like

Code: Select all

ORDER BY RAND() * plays
*plays^2 would skew a lot more. More complex would be to (track and) include the last time the track was played, like

Code: Select all

RAND() * plays / (DATEDIFF(NOW() - lastPlayed) + 1)
RAND() * plays / (UNIX_TIMESTAMP() - lastPlayed + 1)
(+1 so tracks played today/now won't cause a division by zero)

Re: Seeded Random

Posted: Sun Oct 07, 2012 11:37 am
by shiznatix
requinix wrote:If you want it weighted by the number of plays then it's just something like

Code: Select all

ORDER BY RAND() * plays
Cool. How exactly does that work though? I dont see how multiplying by plays works for this.

Re: Seeded Random

Posted: Sun Oct 07, 2012 5:31 pm
by requinix
Try a few examples in your head. RAND() returns a number between 0 and 1: if plays=1 then it'll sort between 0 and 1 and if plays=10 then it'll sort between 0 and 10. Odds are the one with fewer plays will be sorted before the one with more plays.

Re: Seeded Random

Posted: Tue Oct 09, 2012 6:50 am
by shiznatix
requinix wrote:Try a few examples in your head. RAND() returns a number between 0 and 1: if plays=1 then it'll sort between 0 and 1 and if plays=10 then it'll sort between 0 and 10. Odds are the one with fewer plays will be sorted before the one with more plays.
ah, makes sense. thanks!