CODEWITHSUNDEEP
sqlpostgresql
jdee
jdee

Reputation: 12080

Complicated Result Distribution Database Query

In my database my users have preferences stored, of what percentage of music they like. So user Billy has the following preferences

id:1 user:Billy music_type:Jazz percentage:50%
id:2 user:Billy music_type:Punk percentage:25%
id:3 user:Billy music_type:Folk percentage:25%

I also have a table that has 1 million jazz,punk and folk music tracks in it

id:1 music_type:Jazz track_name:Bippity Boop
id:2 music_type:Punk track_name:Grrrrrrrrrr!
id:3 music_type:Folk track_name:DeDiDoDeeDoo

I want to show Billy 100 tracks. 50 of them Jazz, 25 Punk, 25 Folk ( as per his preferences stored in the first table ).

How can I construct such a query from the database? Note: This is obviously a vastly simplified version of my real tables and data, but any help will certainly help me solve my real problem.

Upvotes: 2

Views: 107

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61616

With Postgresql 8.4 or higher, you could use a window function to partition the tracks by music type, get a per-partition counter, and then use it to limit the results per-partition according to the preferences. Here is an SQL skeleton:

  select s.track_name,s.id from
   (select id, track_name, music_type, row_number() over (partition by music_type) as r
      from tracks ) s,
    preferences p
    where username='Billy'
    and p.music_type=s.music_type
    and s.r<=p.percentage
    order by s.id

Upvotes: 3

Related Questions