I have a table/collection called scores and it has 3 relevant identifiers based on which calculation need to be done.
score : Number,
company : String,
zone : String,
unit : String,
timestamp : Number
Everyday we get huge amount of data in this scores table which has 3 identifiers. On client side dashboard we are bound to show progression for company, zone and unit separately for past six weeks.
And weeks are not fixed - they are dynamic instead. For e.g if you are checking the dashboard on 15th of march then past 6 weeks data from x date to 14th March is shown. Progression in terms of weeks.
The plan is to create a system which at midnight will take scores from records for past 42 days from that time and calculate weekly score and then save it for every company, zone and unit separately.
I don't know if this approach is correct or not, probably not. Let's say there are 500 records coming in the system everyday so for 42 days I will have roughly 21000 records. Now calculation has to be done for all three entities separately.
Plus the records that I am going to fetch based on identifier(one from set of three) & timestamp, I will end up with 42 days data. How do I churn them into weeks format? I should fetch 42days data in one go and then distribute via timestamp using conditions or I should just call for 1 week data in one go.
This approach seems really confusing given that the output that I am going to get from this is not that significant. Is this the right approach to solve this issue?
I want to make a system that'll scale in future for more data and not just 21k records.