MySQL排名比较 [英] MySQL Rank Comparison

问题描述

我要执行的操作等同于音乐图表表。

我有一张视频播放表，我想对那些在过去24小时内播放过，并将它们与之前24小时内的排名进行比较。

因此，输出应为VideoID，当前排名为ID，以及ID的前一天排名

该表格包含：

  PlayID（播放表的特定ID）
 IP（播放视频的用户的IP地址）
 VIDEOID（特定视频的ID，按等级排序多数情况下）
播放时间（播放时的Linux时间）
  

该表称为video_plays7d。 / p>

这让我很沮丧，我无法弄清楚任何想法？

解决方案

您首先需要所有视频的完整列表，因此，如果有一天（而不是另一天）播放，则不会丢失该实例。 -query应该会在当前日期或之前获取所有可能的视频及其各自的计数，但是WHERE子句的有效期限仅限于两天。内部查询还按前一天计数递减的顺序对IT进行第一遍预定，并应用@var建立排名。从THAT中，重新查询所有这些结果，但是这次，按 TODAY计数递减排序，并将排名应用于THAT集合。完成所有操作后，它应显示从1到今天的排名，但排名的前一天可能会分别错开其数量。

 选择
 AllPlays.VideoID，
 AllPlays.CntToday，
 @RankToday：= @RankToday +1作为TodayRank，
 AllPlays.CntBefore，
 AllPlays从
中选择.BeforeRank 
（选择vp.VideoID，
 sum（if（date（FROM_UNIXTIME（vp.playtime））= curdate（），1，0））作为CntToday，
 sum（if（date（FROM_UNIXTIME（vp.playtime））< curdate（），1，0））作为CntBefore，
 @RankBefore：= @RankBefore +1作为BeforeRank 
从
 video_plays7d vp 
加入（select @RankBefore：= 0）SQLVars 
其中
 date（FROM_UNIXTIME（vp.playtime））> = date_sub（curdate（），间隔1天）
组由
 vp.VideoID 
订单由
 CntBefore DESC 
）作为AllPlays 
的联接（选择@RankToday：= 0）作为SqlVars2 
的订单，由
 AllPlays.CntToday DESC 
  

What I am trying to do is equivalent to a "music charts" table.

I have a table of video plays, and i want to rank the videos that have been played in the last 24 hours, and compare them to the rank they were the 24 hours before that.

So the output should be the VideoID, The current Rank of the ID, and the rank the day previous of the ID

The table consists of:

PlayID (the specific id to the play table)
IP (IP address of the user who played the video)
VIDEOID (ID of the specific video, what needs to be ranked by most occurances)
playtime (linux time when played)

the table is called video_plays7d.

This has been stumping me, and i cannot figure it out, any ideas?

解决方案

You'll first need a full list of ALL videos so if any played on one day and not the other, you won't loose that instance... The inner pre-query should get ALL possible video's and their respective counts on current date or before, but the WHERE clause restricts down to just the two days. Inner query also pre-orders ITs FIRST pass by the "Day Before" count descending and applies @var to build out the rank. From THAT, requery all those results, but this time, sort by the "TODAY" count descending and apply rank to THAT set. When all is finished, it should show the ranks as of today from 1-?, but the day before ranking may be staggered respectively to their count.

select
      AllPlays.VideoID,
      AllPlays.CntToday,
      @RankToday := @RankToday +1 as TodayRank,
      AllPlays.CntBefore,
      AllPlays.BeforeRank
   from
      ( select vp.VideoID,
               sum(if(date(FROM_UNIXTIME(vp.playtime )) = curdate(), 1, 0 )) as CntToday,
               sum(if(date(FROM_UNIXTIME(vp.playtime )) < curdate(), 1, 0 )) as CntBefore,
               @RankBefore := @RankBefore +1 as BeforeRank
           from 
              video_plays7d vp
              join ( select @RankBefore := 0 ) SQLVars
           where
              date( FROM_UNIXTIME(vp.playtime ) ) >= date_sub( curdate(), interval 1 day )
           group by
             vp.VideoID
           order by
              CntBefore DESC
      ) As AllPlays
      join ( select @RankToday := 0 ) as SqlVars2
   order by
      AllPlays.CntToday DESC

这篇关于MySQL排名比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！