常见场景下的SQL运用

记录一些常见需求场景下的SQL表达式。

假如有这么一个游戏表game,它有这么四个字段id、user_id、score、created_time,记录的是用户玩某个游戏的得分记录,每个人可以玩多次,游戏只有一款。

假如我们要做一个排行榜,榜单上只展示最高的10个得分,这就很简单:

select score from game order by score desc limit 10

假如我们要做的排行榜,要展示Top 10的得分的同时,还要展示出对应的用户ID,用户ID不重复,比如一个用户玩了三次,得分按理说都上榜,也只取他得分最高的那次,这就有些复杂了:

select user_id,max(score) as max_score from game group by user_id order by max_score desc limit 10

假如需求同上,只是额外要求榜单上还要展示用户玩得该得分的时间戳,也就是created_time,那就变得复杂多了,此时需要用到窗口函数row_number(),SQL如下:

select * from (
  select
    id, user_id, score, ROW_NUMBER() over(PARTITION BY user_id order by score desc) as rid
  from
    game
  group by id,user_id,score
) t where rid=1 order by score desc limit 10

如果这个榜单有点变态,每个用户最多可以上榜三次,而不是只在榜单上出现一次,使用上面的解法就很简单,只需要将rid=1改成rid<=3即可。

假如需求完全变了,是需要统计每个参与者的top 3的得分记录,榜单先按用户ID逆序排,然后每个用户的top 3得分再逆序排:

select * from (
  select
    id, user_id, score, ROW_NUMBER() over(PARTITION BY user_id order by score desc) as rid
  from
    game
  group by id,user_id,score
) t where rid<=3 order by user_id desc,rid

 

发表于 03月12日 21:22   评论:0   阅读:363  



回到顶部

首页 | 关于我 | 关于本站 | 站内留言 | rss
python logo   django logo   tornado logo