本周最受观看的影片 [英] Most watched videos this week

问题描述

我有一个像用户上传和观看视频的网页一样的YouTube。

I have a Youtube like web-page where users upload&watch videos.

我想添加一个本周观看最多的视频视频列表我的页面。但是这个列表不应该只包含上一周上传的视频，而是所有视频。

I would like to add a "most watched videos this week" list of videos to my page. But this list should not contain just the videos that ware uploaded in the previous week, but all videos.

我正在列中记录视图，所以我没有关于视频观看时间的信息。所以现在我正在寻找一个解决方案来记录这些数据。

I'm currently recording views in a column, so I have no information on when a video was watched. So now I'm searching for a solution to how to record this data.

第一个是最明显的（根据我所知）有一个单独的表，您可以在每次要录制新视图（存储视频的ID和时间戳）时插入新行。我很担心我会在这张表中快速获得大量的数据，而使用这个表格的查询速度会非常慢（我们每个月可以获得约300万次观看次数）。

The first is the most obvious (and the correct one, as far as I know): have a separate table in which you insert a new line every time you want to record a new view (storing the ID of the video and the timestamp). I'm worried that I would quickly get huge amounts of data in this table, and queries using this table would be extremely slow (we get about 3 million views a month).

第二个解决方案并不灵活，但在数据库中更容易。我将在视频表中添加7列（每周一个）：views_monday，views_tuesday，views_wednesday，...
根据日期增加正确列中的值。而且我会在当天的日子里重新设置为0。我可以通过总结这7列来轻松获得一周中观看次数最多的视频。

The second solution isn't as flexible but is more easy on the database. I would add 7 columns to the "videos" table (one for each day of the week): views_monday, views_tuesday , views_wednesday, ... And increment the value in the correct column based on the day it is. And I would reset the current day's column to 0 at midnight. I could then easily get the most watched videos of the week by summing this 7 columns.

你觉得我应该怎么处理第一个解决方案呢？我的情况就足够了如果您有更好的解决方案，请分享！

What do you think, should I bother with the first solution or will the second one suffice for my case? If you have a better solution please share!

我正在使用MySQL。

I'm using MySQL.

推荐答案

您还可以创建一个具有日期，视频ID和视图数的表。每次观看视频时，只需增加视图数（如果不存在则创建记录）。这样你每个视频每天只能有一条记录。您可以通过查询此表格轻松地计算最后一周/月/年的计数。

You could also create a table with the date, video id and view count. Each time a video is viewed you just increment that days view count (or create the record if is dose not exist). So that way you just have one record per day per video. You can easily sum the count for the last week / month / year by querying this table.

这篇关于本周最受观看的影片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！