Order database query results along a curve

Question

i have a table of songs and their beats-per-minute values, and i would like to build playlists that follow a curve like this:

  ^       . . .
  |     .       .
b |   .           .
p |  .              .
m | .                .
  x-------------------->
    time

i realize this is probably not possible in a single SQL statement, but i'm interested in hearing solutions - right now, the best approach i see is selecting more tracks than necessary and ordering in my application code (python).

Answer 1

A simple way to do this would be to just order the songs by their BPM in the SQL. Then, once you have the data in your application, build your list from the front and back by placing items with an odd index at the beginning and even index at the end. That would cause the BPM to rise and fall over time. The shape of this would depend on the actual BPMs that are available.

However, if you want to form a specific curve, you'd first have to define the parameters of the curve.

Answer 2

Keeping in mind that sql queries have high fixed costs per query, I'd do it like this:

1. use one sql query to get a list of songs that are ordered by length (for example, a list of all songs between x - y seconds long, up to a max of z songs in total).

2. create a function that generates a playlist from that list of songs, by selecting songs from it in a way that follows the pattern outlined by your curve above.

Maybe something like this?

  function makePlaylist(array songList,int playListLength,function curve)
    int x=songList[0].length;
    array playList=new array();//empty array
    int max=getHighestBpmFromList(songList);//getHighestBpmFromList implementation not shown here
    song closestMatch;

    while (playList.length<playListLength)
      currentLength=song.length
      optimalBPM=-(x-songList[0].length)^2+max;//your curve as described above

      closestMatch=findClosestMatch(optimalBPM);//findClosestMatch would find a song
                                                //in the list whose bpm is as close
                                                //as possible to what the bpm 
                                                //should be at x on the curve
                                                //(maybe binary search, since 
                                                //the list is sorted)
      playList.push(closestMatch);
      x++

    return playList;

Answer 3

My idea is to join a table that has a single field "Time" (that contains integers 1..MAX , you can build this table on the fly) with the song's table, where the appropriate SongID for each "Time" represents the song with bps closest to f(time). f is the function that represents the curve. Then you can order the result by time and get the curve.

This method does not deal with duplicates, and I do not know if it is easy to implement in SQL (I'm not an SQL expert).