实施“多数观看".特征数据库 [英] Implementation of "Most Viewed" feature database

问题描述

我想知道如何在数据库中最好地实现"收视率最高"功能(例如youtube).

I was wondering how to best implement a "most viewed" featured (like youtube) on my database.

让我解释一下"观看次数最多"功能要好一点的地方: 基本上，我想列出这一天/周/月访问量最大的页面/视频/等，请参见 http://www.youtube.com/charts/videos_views 作为示例.

Let me explain the "most viewed" feature a little bit better: Basically I want to list the most visited pages/video/etc from this day/week/month, see http://www.youtube.com/charts/videos_views for an example.

因此，我想知道如何最好地实现此功能，因为我想到了许多实现此功能的方法，但是所有这些方法都有+和-.

So I was wondering how to best implement this feature as I can think of many many ways of doing it but all of them have their + and - to them.

此外，我也很想听听不同程序员对其他程序员想法的评论.我也想就这个话题开始一段很好的对话.

Plus I also would love to hear the comments of various programmers on others programmers ideas. Also i would like to start a good conversation on this topic.

Ps.我正在专门研究如何计算时间，比如说本月访问最多的时间，而没有一个巨大的表可以将每个视图与日期时间一起保存.欢迎提出任何想法.
点子我使用Mysql和PHP，非常欢迎这两个提示.

Ps. I'm looking specially on how to calculate the time, say most viewed in this month, without having a huge table saving every single view with the datetime. Any idea is welcome.
Pps. I use Mysql and PHP, extra tips for those two are very welcome.

推荐答案

有以下表格: 1.意见 2. views_hourly_summary 3. views_daily_summary 4. views_monthly_summary 5. views_alltime_summary

have the following tables : 1. views 2. views_hourly_summary 3. views_daily_summary 4. views_monthly_summary 5. views_alltime_summary

按以下时间间隔运行cron作业:

have a cron job run at the following intervals:

1. 每小时运行一次，并从视图表中预先汇总该小时的视图，并将预先汇总的结果保存在views_hourly_summary表中，并更新views_alltime_summary表

1. run every hour and preaggregate the views for that hour from the views table and save the preaggregated result in the views_hourly_summary table, also update the views_alltime_summary table

每天结束时运行，并从小时表中预先汇总当天的观看次数，并将预先汇总的结果保存在views_daily_summary表中

run at the end of everyday and preaggregate the views for that day from the hours table and save the preaggregated result in the views_daily_summary table

在每个月末运行，并从小时表中预先汇总当天的视图，并将预先汇总的结果保存在views_daily_summary表中

run at the end of everymonth and preaggregate the views for that day from the hours table and save the preaggregated result in the views_daily_summary table

下一步，在获取结果时，您将必须进行一些数学运算，如下所示:

next when fetching results you will have to do some math as follows:

1. 例如，您要获取最近4个小时的视图，则需要从小时表中获取3个整小时数据，而其余数据则可以从视图表中获取，如下所示:

1. For example, you want to fetch the views for last 4 hours, you would fetch the 3 whole hours data from the hourly table and for the remaining data fetch it from the views table as follows:

选择item_id，将sum(views)作为视图 来自views_hourly_summary 从concat(left(now()-间隔3小时，14)，'00:00')和concat(left(now(now，)，14)，'00:00')之间的小时 按item_id分组

select item_id, sum(views) as views from views_hourly_summary where hour between concat(left(now() - interval 3 hour, 14), '00:00') and concat(left(now(), 14), '00:00') group by item_id

联盟

选择item_id，将count(1)作为视图 从观点 其中(now()-间隔4小时)和concat(left(now()-间隔3小时，14)，'00:00')之间的日期时间 或datetime> concat(left(now()，14)，'00:00') 按item_id分组

select item_id, count(1) as views from views where datetime between (now() - interval 4 hour) and concat(left(now() - interval 3 hour, 14), '00:00') or datetime > concat(left(now(), 14), '00:00') group by item_id

这篇关于实施“多数观看".特征数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！