在Rails中使用Postgres限制SQL结果

Question

在Rails中使用Postgres限制SQL结果

3

我有一个应用程序，其中包含音乐排行榜以展示热门曲目（显示前十名）。

然而，我想限制排行榜，使得任何特定用户不能同时拥有超过2首歌曲在排行榜上。如果艺术家A通常会有排行榜中的4个前十名位置，那么只会显示艺术家A的前两首热门曲目（假设它们当然不是由艺术家A创作的）。此时，列表中的第11和第12项将各上升2个位置。

所以，假设这是当前的热门曲目部分：

艺术家A的歌曲A
艺术家B的歌曲B
艺术家A的歌曲C
艺术家C的歌曲D
艺术家D的歌曲E
艺术家E的歌曲F
艺术家F的歌曲G
艺术家A的歌曲H
艺术家A的歌曲I
艺术家G的歌曲J

我想限制SQL查询结果，使得#8和#9不包括在内（因为查询结果中只允许每个艺术家最多有2首热门曲目），列表将变成：

艺术家A的歌曲A
艺术家B的歌曲B
艺术家A的歌曲C
艺术家C的歌曲D
艺术家D的歌曲E
艺术家E的歌曲F
艺术家F的歌曲G
艺术家G的歌曲J
艺术家H的歌曲K（之前是#11）
艺术家I的歌曲L（之前是#12）

顺便说一下，我正在使用Postgres，这是我现在拥有的。它计算最近14天内每个曲目的播放次数以生成前10名列表。我想修改它以获得上述所需的限制。

def self.top_tracks_past14(max=3)
  Track.find_by_sql(["select COALESCE(sum(plays.clicks), 0), tracks.*
    from tracks
    left join plays
    on tracks.id = plays.track_id
    and plays.created_at > now() - interval '14 days'
    inner join albums
    on tracks.album_id = albums.id
    inner join users
    on albums.user_id = users.id
    group by tracks.id
    order by 1 desc limit ?", max])
end

- Andrew

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vamsi Prabhala · Accepted Answer

select trackid, userid, totalclicks from 
(
select *, 
row_number() over(partition by userid order by totalclicks desc) as rn
from
 (
select COALESCE(sum(plays.clicks), 0) as totalclicks,plays.track_id as trackid,
users.id as userid
from tracks
left join plays
on tracks.id = plays.track_id
and plays.created_at > now() - interval '14 days'
inner join albums
on tracks.album_id = albums.id
inner join users
on albums.user_id = users.id
group by plays.track_id, users.id
  ) t
) t1
where t1.rn <= 2
order by 1 desc
limit 10;

你可以使用row_number函数，在顶部曲目中每个用户仅选择2行。

编辑：根据OP的要求，来自tracks、albums、users、plays的所有列将在外部查询中可用。如果您需要从这些表中排除计算得出的rn，请确保从这些表中选择所需的列。

def self.top_tracks_past14(max=3)
Track.find_by_sql(["select t1.trackid, t1.userid, t1.totalclicks from 
(
select t.trackid, t.userid, t.totalclicks, 
row_number() over(partition by t.userid order by t.totalclicks desc) as rn
from
 (
select COALESCE(sum(plays.clicks), 0) as totalclicks,plays.track_id as trackid
,users.id as userid
from tracks
left join plays
on tracks.id = plays.track_id
and plays.created_at > now() - interval '14 days'
inner join albums
on tracks.album_id = albums.id
inner join users
on albums.user_id = users.id
group by plays.track_id, users.id
  ) t
) t1
where t1.rn <= 2
order by t1.totalclicks desc limit ?", max])
end