How can I aggregate rows to reduce the resolution of data in a SQL query?

Question

I'm working with an MSSQL 2000 database containing large amounts of Windows perfmon data collected for all servers in the environment. I'm using SSRS 2005 to build a custom report chart to visualize the metrics over time.

If I wanted to view, say, the last month the extensive number of data points would create an ugly report with unreadable labels on the X axis. I would like to reduce the aggregate the data by time down to n data points so to give the average value over the grouped time spans.

I've tried building a query with fancy GROUP BY clauses, haven't been able to build something that executes. I figured this ought to be a common task for SQL, but I haven't found any answers online.

The table structure basically looks like below. This is actually the MOM 2005 OnePoint database, but I think the application is irrelevant.

CREATE TABLE PerfTable (
   [time] datetime,
   value float,
   Server nvarchar(356),
   ObjectName nvarchar(225),
   CounterName nvarchar(225),
   InstanceName nvarchar(225),
   Scale float
);

Answer 1

Do you really need to reduce the number of records returned from SQL, or just the data rendered by the chart?

It might be easier to get all the values from SQL, and then massage the data into something more usable later. Changing the query will reduce the network usage as less data will be sent, but if that's not a concern then maybe the query isn't the best place to do it.

Answer 2

Might be worth building a View to look at a months worth of data and work with the SQL behind that to reduce the amount of data.

Then you can run the report from that View.

Also, it might be worth you giving us an idea of the table structure involved and SQL you've currently using to get the results.

Answer 3

You can use DATEPART function to get chunks of data filtered by specific day, hour or minute (or several others). You should be able to group by these and get averages/aggregates you need.

OK, here's the solution to get n aggregates (as far as there's data in each chunk of time):

declare @points as int
declare @start as float
declare @period as float

set @points = 20

select 
@start=cast(min(time) as float), 
@period=cast(max(time)-min(time) as float) 
from perftable

select avg(value),
round((cast(time as float)-@start)/(@period/@points),0,1)
from perftable
group by
round((cast(time as float)-@start)/(@period/@points),0,1)

@points variable is a number od aggregates you want to get. @start is time of the first record in report casted to float @period is difference between begin and end dates in report

The rest is pretty much linear scaling of dates to range [0;@points], truncating results to integers and grouping by truncated results.

Answer 4

Say we want to have 3 timespand and the average of "value" in that span.

First we determine the periods.. start-end,start-end,start-end etc This you can do in your own code so I use parameters.

In this example we also group by 'server' but you can add extra columns or remove it.

DECLARE @startdate1 as DateTime
DECLARE @enddate1 as DateTime
DECLARE @startdate2 as DateTime
DECLARE @enddate2 as DateTime
DECLARE @startdate3 as DateTime
DECLARE @enddate3 as DateTime
SELECT 
  CASE WHEN time >= @startdate1 AND time < @enddate1 THEN 'PERIOD1'
    ELSE CASE WHEN time >= @startdate2 AND time < @enddate2 THEN 'PERIOD2'
     ELSE CASE WHEN time >= @startdate3 AND time < @enddate3 THEN 'PERIOD3' 
     END 
    END
  END as Period,
  AVG(p.[value]),
  p.[Server]
FROM PerfTable p
GROUP BY 
  CASE WHEN time >= @startdate1 AND time < @enddate1 THEN 'PERIOD1'
    ELSE CASE WHEN time >= @startdate2 AND time < @enddate2 THEN 'PERIOD2'
     ELSE CASE WHEN time >= @startdate3 AND time < @enddate3 THEN 'PERIOD3' 
     END 
    END
  END,
  p.[Server]

Answer 5

I have a solution that works closely to what I was asking for. If I wanted to group by a time unit, it's pretty simple:

Group by hour:

select
   dateadd(hh, datediff(hh, '1970-01-01', [time]), '1970-01-01'),
   Server, ObjectName, CounterName, InstanceName, avg(value)
from PerfTable
group by
   dateadd(hh, datediff(hh, '1970-01-01', [time]), '1970-01-01'),
   ComputerName, ObjectName, CounterName, InstanceName
order by
   dateadd(hh, datediff(hh, '1970-01-01', [time]), '1970-01-01') desc,
   ObjectName, CounterName, InstanceName, ComputerName

This just doesn't address the need to scale down to n data points.