Querying aggregate data in different ways

There are four different ways you can retrieve summary data, such as an average, using the Historian.

• Using the SQL Server average function. This is appropriate for discrete samples. For example, a check weigher, where you are measuring individual units against a target weight.

• Using the average retrieval mode. This is appropriate for most situations where you want to find an average, as it is weighted according to time. For example, if you want to find the average for a flow rate or a temperature.

• Setting up summary replication and then querying the AnalogSummaryHistory table. Replication uses the average retrieval mode to do the calculations.

• Setting up a summary event and then querying the SummaryData table. The Event subsystem uses the SQL Server average function.

The following examples show how you can get the same data using these different methods. All examples use the SysTimeSec system tag, which has a range of 0 to 59.

Query 1

The following query uses the SQL Server average function to return the average value of the SysTimeSec tag over the span of one minute.

SELECT AVG(Value) as 'SysTimeSec AVG'

FROM History

WHERE TagName = 'SysTimeSec'

AND DateTime > '2009-11-15 6:30:00'

AND DateTime < '2009-11-15 6:31:00'

AND wwRetrievalMode = 'Full'

The results are:

Query 2

The following query uses the historian time-weighted average retrieval mode to return the average for the same time period. Because the cycle count is set to 2, a first row is returned for the "phantom"cycle leading up to the query start time. The StartDateTime column shows the time stamp at the start of the data sampling, which is the start time of the phantom cycle. The second row returned reflects is the actual data that you expect. The time stamp for the data value is 2009-11-15 06:31:00 because the default time stamping rule is set so that the ending time stamp for the cycle is returned. For more information about the phantom cycle, see About phantom cycles.

SELECT StartDateTime, DateTime, TagName, Value

FROM History

WHERE TagName = 'SysTimeSec'

AND DateTime >= '2009-11-15 6:30:00'

AND DateTime <= '2009-11-15 6:31:00'

AND wwRetrievalMode = 'Average'

AND wwCycleCount = 2

AND wwTimeStampRule = 'end'

The results are:

Query 3

For the following query, local replication has been set up so that the average of the SysTimeSec tag is calculated every minute and stored to the SysTimeSec.1M analog summary tag. The query returns the value of the SysTimeSec.1M tag for the time period specified.

SELECT TagName, StartDateTime, EndDateTime, Average as AVG

FROM AnalogSummaryHistory

WHERE TagName = 'SysTimeSec.1M'

AND StartDateTime >= '2009-11-15 6:30:00'

AND EndDateTime <= '2009-11-15 6:31:00'

The results are:

Query 4

The following query, the History table is used instead of the AnalogSummaryHistory table. Because the cycle count is set to 2, this query returns a row for the phantom cycle. The time stamp for the data value is 2009-11-15 06:31:00 because the default time stamping rule is set so that the ending time stamp for the cycle is returned.

SELECT TagName, DateTime, Value

FROM History

WHERE TagName = 'SysTimeSec.1M'

AND DateTime >= '2009-11-15 6:30:00'

AND DateTime <= '2009-11-15 6:31:00'

AND wwRetrievalMode = 'avg'

AND wwCycleCount = 2

The results are:

Query 5

The following query returns five minutes of summary data for an event tag that has been configured to store the average value of the SysTimeSec tag every minute.

SELECT TagName, CalcType, SummaryDate, Value

FROM v_SummaryData

WHERE TagName = 'SysTimeSec'

AND SummaryDate >= '2009-11-15 18:30:00'

AND SummaryDate <= '2009-11-15 18:31:00'

The results are: