My question is more or less theoretical - what is the best approch to get query data by intervals.
I have a system that manage counters (thousands of counters - counts consumption of electricity/water etc...), every 15 minutes the counter is sampled and stored in the DB. there is one table that stores all counters data [id, counterID, Timestamp, CounterValue].
Now, I want to query the data about the Interval (hourly/daily/weekly/monthley) consumption.
The intervaled consumption defined as the first measurment (counting) in the start of the next interval minus the first measurment in the start of the current interval. since all measurments are agregated (counters) the delta between two counters represents the consumption in the interval.
Since there is ALOT of data and I want the queries to be fast I need a better way then just quering the whole table... (I think...) quering the whole data (even with small amount of data - this query is slow...):
SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number FROM counters_data) A WHERE row_number = 1 Order by dateOfData;
I thought about some ways doing this:
- Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
- Add tables for every interval with columns [id, date, counterId, counterDataRecord] so there will be tblDailyCounter, tblWeeklyCounter etc...
is there any other idea that Im missing?
what is the pros&cons of those aproches?
what is the most recomanded / best practice?