本周流行 - 本月流行 - PHP功能

Question

Suppose a table videos

id        | name         | views
----------+--------------+-----------
1         | Video1       | 52
2         | Video2       | 150
...

For getting the video which is popular/most viewed this week, I could create another table: videoviews

id        | foreign_key  | viewed_on
----------+--------------+-----------
1         | 1            | 10/12/2018
2         | 1            | 09/12/2018
...

From this table, I can easily get the data for last week/last month etc. That's not an issue.

Problem: Suppose I have 1000 Videos and Each video gets 100 Views per day. My videoviews table will have 100000 records each day.

I know this is not the best way to achieve this functionality. Just wondering what is?

I found these on SO but..

How to get most visited posts of the week?

Popular Today, This Week, This Month - Design Pattern

Answer 1

Problem: Suppose I have 1000 Videos and Each video gets 100 Views per day. My videoviews table will have 100000 records each day.

Do you need a complete record of each individual view?

You could, instead, use a counter approach, where you store one row per video per day, and simply increment its value when a new row comes in. This is granular enough to provide useful per-day analytics, without having to store a million rows for a million video views.