SQL or NOSQL for storing and analyzing logs of player actions. What is the best way to organize an analytics database?

Question

MySQL

Welcome all.
We now store raw player action logs in mongodb, the collection is
{ t :
timestamp a
: action data
: some array
} level_start t: 140000100 player_id: 123456 a: level_start t: 140,000,150 player_id: 123456 a: level_win t: 140,000,200 player_id: 123456 a: level_start t: 140,000,250 player_id: 123456 a: level_win t: 140,000,300 player_id: 123456 a: level_start t: 140,000,350 player_id : 123456 a: level_start t: 140000400 player_id: 123456 a: level_win
etc. with different player_id, etc.
The task is to calculate the average number of attempts among all players before the first pass, i.e. - how many times level_start occurs before the first level_win appears for each player.
This task is taken as an example - we are trying to decide in which subd it is best to store data and in what form to put it there so that such queries can be performed as quickly as possible for large data arrays.
Any subd for the solution is considered - mongodb, mysql, posgresql, etc.
And also welcome any advice on organizing such a database.

Answer 1

I don’t know what to decide, but with the storage of actions, it’s better to store them in codes.
1-win
2-lose, etc.
firstly, the size of the database will be reduced, secondly, the processing time will fall (the speed will increase).
Time / user ID / ID actions
by time I think it will not be difficult to clean the database, but with another it’s easier to make a selection

Answer 2

Much depends on reports. For example, for your task, you do not need to store raw data at all.
If the reports are not yet defined or raw data is still needed, then partitioning can be done, for example, adding all the logs for one player for one day into one document in binary form (time_delta, action) while clamping it in gzip / zlib /
... As a result, 500GB can turn out to be 5GB, and the speed will increase due to light indexes and a smaller number of transmitted data / documents. - did something similar recently.
You can also reset the userFlag to 0 for the collection - save memory, while the monga will not do extra. reservations in each document.