Сравнение рангов MySQL

Question

То, что я пытаюсь сделать, эквивалентно таблице "музыкальные чарты".

У меня есть таблица воспроизведения видео, и я хочу оценить видео, которые были воспроизведены за последние 24 часа, и сравнить их с рейтингом, который был за 24 часа до этого.

Таким образом, выходные данные должны быть VideoID, текущий ранг идентификатора и ранг за день до ID

Таблица состоит из:

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)

таблица называется video_plays7d.

Это ставит меня в тупик, и я не могу понять это, какие-либо идеи?

Answer 1

Сначала вам понадобится полный список ВСЕХ видео, поэтому, если какое-либо из них будет воспроизведено в один день, а не в другой, вы не потеряете этот экземпляр... Внутренний предварительный запрос должен получить ВСЕ возможные видео и соответствующие им значения на текущую дату. или раньше, но предложение WHERE ограничивается двумя днями. Внутренний запрос также делает предварительный заказ ИТ-ПЕРВОГО прохода по убыванию отсчета "День до" и применяет @var для построения ранга. От этого запрашивайте все эти результаты, но на этот раз сортируйте по убыванию числа "СЕГОДНЯ" и применяйте ранг к набору ТА. Когда все закончено, он должен показывать рейтинги на сегодняшний день от 1 до?, Но за день до ранжирования может быть смещен соответственно их количеству.

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