Efficent way to calculate Top 10, or Top X, list, across multiple time periods
The short answer is No.
There is no guarantee that a Top-Ten-Of-Last-Year song was ever on a Top-Ten-Daily list (it's highly likely, but not guaranteed).
The only way to get an absolutely-for-sure Top Ten is to add up all the votes over the specified time period, then select the Top Ten.
What I want to do: Calculate the most popular search queries for: past day, past 30 days, past 60 days, past 90 days, each calendar month, and for all time.
My raw data is a list of timestamped search queries, and I'm already running a nightly cron job for related data aggregation so I'd like to integrate this calculation into it. Reading through every query is fine (and as far as I can tell necessary) for a daily tally, but for the other time periods this is going to be an expensive calculation so I'm looking for a way to use my precounted data to save time.
What I don't want to do: Pull the records for every day in the period, sum all the tallies, sort the entire resulting list, and take the top X values. This is going to be inefficient, especially for the "all time" list.
I considered using heaps and binary trees to keep realtime sorts and/or access data faster, reading words off of each list in parallel and pushing their values into the heap with various constraints and ending conditions, but this always ruins either the lookup time or the sort time and I'm basically back to looking at everything.
I also thought about keeping running totals for each time period, adding the latest day and subtracting the earliest (saving monthly totals on the 1st of every month), but then I have to save complete counts for every time period every day (instead of just the top X) and I'm still looking through every record in the daily totals.
Is there any way to perform this faster, maybe using some other data structure or a fun mathematical property that I'm just not aware of? Also, tn case anyone needs to know, this whole thing lives inside a Django project.