SQLPlayList - How to add random bias?

[barcar]

I've got a few playlists defined in the excellent SQLPlayList plugin.

For example:

SELECT t.url /*, t.title, g.name, t.playCount, t.rating */
FROM tracks t
LEFT JOIN genre_track gt ON t.id = gt.track
LEFT JOIN genres g ON gt.genre = g.id
WHERE g.name NOT IN ('Classical','Books & Spoken','Podcast','Podcasts','Religious','Musicals ')
AND g.name is not null
AND (t.rating is null OR t.rating > 20)
ORDER by random() limit 10;

I'd like to pick the community's brains about how to emulate the iTunes Party Shuffle "play higher rated songs more often" feature.

This feature allows you to set a value (via a slider) that biases the randomness of the Party Shuffle in favour of songs with a higher rating value.

I realise this is more of a mathematical algorithm and SQL programming question (rather than than a plugin/slimserver one) but I'd be interested to hear if others have any suggestions about how to bias randomness in a SQL query.

Thanks.

[erland]

I'm not sure this solves the problem since I don't know exactly how the iTunes party mix works. Also there might be better solutions that doesn't create temporary tables.

This should get you a playlist based on your query that get you:
10% tracks unrated or rated 1-2
90% tracks rated 3-5

CREATE TEMPORARY TABLE mylowratedtracks AS 
  SELECT t.url /*, t.title, g.name, t.playCount, t.rating */
  FROM tracks t
  LEFT JOIN genre_track gt ON t.id = gt.track
  LEFT JOIN genres g ON gt.genre = g.id
  WHERE g.name NOT IN ('Classical','Books & Spoken','Podcast','Podcasts','Religious','Musicals ')
    AND g.name is not null
    AND (t.rating is null OR t.rating < 60)
  ORDER by random() limit 10;

CREATE TEMPORARY TABLE myhighratedtracks AS SELECT t.url /*, t.title, g.name, t.playCount, t.rating */
  FROM tracks t
  LEFT JOIN genre_track gt ON t.id = gt.track
  LEFT JOIN genres g ON gt.genre = g.id
  WHERE g.name NOT IN ('Classical','Books & Spoken','Podcast','Podcasts','Religious','Musicals ')
    AND g.name is not null
    AND (t.rating >= 60)
  ORDER by random() limit 90;

CREATE TEMPORARY TABLE mycombinedtracks AS 
  SELECT * FROM mylowratedtracks 
  UNION 
  SELECT * from myhighratedtracks;

SELECT * from mycombinedtracks 
  ORDER BY random() limit 10;

DROP TABLE mylowratedtracks;
DROP TABLE myhighratedtracks;
DROP TABLE mycombinedtracks;

Note that this query will only work on a slimserver 6.5 using SQLite. The reason is that it depends on the rating column in the tracks table and the random(), with MySQL rand() is used instead and in slimserver 6.2 the ratings will not be placed in the tracks table (at least not by TrackStat). So using slimserver 6.2 or slimserver 6.5 using MySQL would require some minor changes.

[barcar]

Thanks Erland - that's a very elegant solution.

That's pretty much what iTunes does except it can tune the 90/10 split using a slider.

Much appreciated.

[James]

BTW The party shuffle slider is actually for the chance of getting multiple songs by the same artist in a row.
'Play higher rated songs more often' is either off or on.
There are some papers on the net that discuss the weighting algorithm behind it, if you really want to know!

James