How to store periodic data in MySQL?

Question

MySQL

There is a table with ratings that are updated once a month. It looks like this:
August:
--------------
| Ivan | 8 |
| Peter | 4 |
| Dima | 5 |
September (important: new member added):
--------------
| Ivan | 8 |
| KOLYA | 2 |
| Dima | 5 |
What is the best way to organize the storage structure of such monthly tables in order to display charts like "Ivan's rating dynamics by months", "Top-3 maximum rating growth" and others?
Stack - YII2 (php+mysql)

Answer 1

Always keep consolidating tables:
Rating of users for the last 24 hours, for the last 7 days, for the last 14, for a month, for two, for three, for 6, for 12 and by months: Jan, Feb,..., Dec, years.
For arbitrary dates - to summarize the central ready data and make "makeweights" in front and behind through a sample for a period of less than a week (for one or more users).

Answer 2

The member table and the ranking table: id, result, month (from the task you need to understand how to number, at least just a number and know that 1 is 7.2015, you can use a string, in one, from the situation), member_id.
The first request is generally elementary. The second one is in SQL xs how, but select the last three months and sort at the application level. If you think very well, there with several nested selects it can come to something and you can, but here, of course, you need to understand the task, load, and so on. Since you can write a full-fledged function in SQL, but this is already more work for the DBA.

Answer 3

If the division is strictly known for months, what is the problem then?
Let's say there is a table with the current rating of users
| id | rating |
There is a log table for rating
| user_id | month/year | rating |
You make a trigger, when you update the first table, update the second for the current year / month
Well, that's it,
the queries that you want to do seem to be simple,
the first is just a selection by user,
the second: join the table to itself for the second date with ON by user, and sorting by difference between two ratings, + limit