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.
Seeded Random
Moderator: General Moderators
Re: Seeded Random
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.
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
Re: Seeded Random
This is very sad. Random is a tough oneBenjamin wrote:...ORDER BY RAND() doesn't work worth a damn.
Re: Seeded Random
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
*plays^2 would skew a lot more. More complex would be to (track and) include the last time the track was played, like
(+1 so tracks played today/now won't cause a division by zero)
If you want it weighted by the number of plays then it's just something like
Code: Select all
ORDER BY RAND() * playsCode: Select all
RAND() * plays / (DATEDIFF(NOW() - lastPlayed) + 1)
RAND() * plays / (UNIX_TIMESTAMP() - lastPlayed + 1)- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
Re: Seeded Random
Cool. How exactly does that work though? I dont see how multiplying by plays works for this.requinix wrote:If you want it weighted by the number of plays then it's just something likeCode: Select all
ORDER BY RAND() * plays
Re: Seeded Random
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.
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
Re: Seeded Random
ah, makes sense. thanks!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.