I'm trying to count the number of actions per account over a period of time. The period of time may be variable. The way I've thought of doing this is by creating a table for each time period, containing records for each account that get upserted on user action. For example, I'd have a table named '12-05-2013' which would contain a record w/ the following attributes:
"account_id" = uniqueid12345,
"uploads" = 4
The table would basically represent the uploads by all users on that specific day.
I want to be able to find all accounts that had < 2 uploads over the last seven days. This would require me to query over the 7 tables representing the last 7 days. (IE 12-05-2013, 12-04-2013, 12-03-2013, etc). So far I've been unable to find a method of selecting out of these specific tables. Am I modeling the problem wrong? Is there an easy way to do this? I'm new to relational databases so go easy on me :)
Why wouldn't you use a query such as this?
select userid, count(*) as NumActions from ActionTable where ActionDate bewteen @date1 and @date2;
Answer author Gordon-linoff
Tickanswer.com is providing the only single recommended solution of the question Select based on table name w/ SQL for time series tables? under the categories i.e sql , database , relational-database , time-series , . Our team of experts filter the best solution for you.