CODEWITHSUNDEEP
sql-servertime-seriesaggregation
Steve Smart
Steve Smart

Reputation: 31

Tricky: SQL Server-side aggregation of time-series data for charting

I have a large time-series data set in a table that contains 5 years of data. The data is very structured; it is clustered/ordered on the time column and there is exactly one record for exactly every 10 minutes over this entire 5 year period.

In my user-side application I have a time-series chart that is 400 pixels wide, and users can set the time scale from 1 hour up to 5 years. Therefore any query to the database by this chart that returns more than 400 records provides data that cannot be physically displayed.

What I want to know is; can anyone suggest an approach such that when the database is queried for a certain time range, the SQL database would dynamically make a suitable averaging aggregation that returns no more than 400 records?

Example 1): if the time range was 5 years, SQL Server would calculate ~1 value for every 4.5 days (5yrs*365days/400records required), so would average all the 10 minute samples for each 4.5 day bin and return a record for each bin. About 400 in total.

Example 2): If the time range was one month, SQL Server would calculate ~1 record for every 1.85 hours (31 days/400records), so would average all the 10 minute samples for each 1.85 hour bin and return a record for each bin. About 400 in total.

Ideally I'd like a solution that from the applications perspective can be queried just like a static Table.

I'd really appreciate any suggested approaches or code snippets.

Upvotes: 3

Views: 3858

Answers (1)

ba__friend
ba__friend

Reputation: 5903

some examples, if you have a datetime column (which is not quite clear from your question, as there is not table schema):

Grouping into interval of 5 minutes within a time range

SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)

They should be quite easy to port to SQL server, use datediff to convert your datetime values into an unix timestamp and use round() with the function parameter <> 0 for the div.

Upvotes: 1

Related Questions