Question

我正试图写一个 s子,但我发现一些困难。想要的是:

Lets suppose we have a table where we store the times that a video have been watched, per day. So for example:

day: 14/11/2011, video_id: 3539, timeswatched: 125
and: 15/11/2011, video_id: 3539, timeswatched: 162

(so the table stores video_id,day,timeswatched)

What i want is to make a billboard/chart type which show the percentage change of the timeswatched of the 8 top viewed (for today) videos between the dates: today and yesterday. (note that a video may have not been watched at all today or yesterday - should this be a problem or not?)

因此,结果应当像这样的:录像:3539人+23%或-44%(或)今天更多的/无观察(我们需要知道如何找到23%)

感谢!

Answer 1

Select Top8.idvideo, (vtoday.times - vyesterday.times) / vtoday.times
From 
  (select 
     Idvideo from Nviews 
     Where day = Curdate()
   order by watches desc limit 8) top8 
  Inner join
   NViews vtoday 
       on vtoday.day = curdate()
       and vtoday.idvideo=top8.idvideo
  Left outer join
    NViews vyesterday
       On vyesterday.day= Date_add( curdate(), interval -1 day) and
           Vyesterday.idvideo = top8.idvideo

Answer 2

SELECT
    videoWatchCountToday.timeswatched AS timeswatchedtoday,
    videoWatchCountYesterday.timeswatched AS timeswatchedyesterday,
    CASE
        WHEN COALESCE(videoWatchCountYesterday.timeswatched, 0) = 0 THEN NULL
        ELSE ROUND(100 * (videoWatchCountToday - videoWatchCountYesterday) / videoWatchCountYesterday, 0)
    END AS change
FROM
    videoWatchCount AS videoWatchCountToday
    LEFT JOIN videoWatchCount AS videoWatchCountYesterday ON
        videoWatchCountToday.video_id = videoWatchCountYesterday.video_id AND
        videoWatchCountYesterday.day =  2011-11-14 
WHERE
    videoWatchCountToday.video_id = 3539 AND
    videoWatchCountToday.day =  2011-11-15

Answer 3

我愿做一些事情(假装法):

yesterday = sql( SELECT timeswatched  FROM videos WHERE DATE(date_field) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
today = sql( SELECT timeswatched  FROM videos WHERE DATE(date_field) = CURRENT_DATE() )

if (today == 0 || yesterday == 0)
  // Special handling... 0 -> 10 views is an infinite increase.
else
  return (100 * yesterday / today) - 100;

友情链接