EpmQueryAggregateFunction

Returns data from one or more variables, comma-separated, informed in the @node parameter, according to the format defined by the aggregation function indicated by the @aggregateFunction parameter.

Syntax

SELECT Name, Timestamp, Quality, Value FROM EpmQueryAggregateFunction(@timeZoneOffset, @startTime, @endTime, @sampleInterval, @aggregateFunction, @node)

Parameters

•@timeZoneOffset: Timezone to consider. Possible values range between -12 and 14

•@startTime: Initial date of the period to query. Users must use a String in the format yyyy-MM-dd HH:mm:ss

•@endTime: Final date of the period to query. Users must use a String in the format yyyy-MM-dd HH:mm:ss

•@sampleInterval: Time interval used to calculate aggregation, in milliseconds

•@aggregateFunction: Name of the aggregation function to use. Possible values for this parameter are described on the next table

•@node: Variable or variables to query. Users must inform the full name or names between single quotes and comma-separated

Possible values for the @aggregateFunction parameter

Value	Description
AnnotationCount	Informs how many annotations a variable has inside the sampling interval
Average	Calculates the average of data values with good quality inside the sampling interval defined in the @sampleInterval parameter
Count	Returns the number of data present in the sampling interval defined in the @sampleInterval parameter
Delta	Returns the difference between the first and last values with good quality inside each sampling interval
DeltaBounds	Returns the difference between the first and last values with good quality inside each sampling interval. It considers the Simple Bounding Values
DurationBad	Returns the time, in milliseconds, in which data had bad quality inside the sampling interval. Quality is defined based on the quality of data from interval's edges
DurationGood	Returns the time, in milliseconds, in which data had good quality inside the sampling interval. Quality is defined based on the quality of data from interval's edges
DurationInStateNonZero	Returns the total time, in milliseconds, in which a value remained different from 0 (zero) inside the sampling interval
DurationInStateZero	Returns the total time, in milliseconds, in which a value remained in 0 (zero) inside the sampling interval
End	Equivalent to the Interpolative function, but uses the original timestamp from the last value of the sampling interval
EndBounds	Equivalent to the Interpolative function, but uses the original timestamp from the last value of the sampling interval. It considers the Simple Bounding Values
Interpolative	Interpolates data from the queried interval, returning points according to the interval defined in the @sampleInterval parameter
Maximum	Informs the greatest value found on data inside the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the starting time of each sampling interval
Maximum2	Informs the greatest value found on data inside the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the starting time of each sampling interval. It considers the Simple Bounding Values
MaximumActualTime	Informs the greatest value found on data inside the sampling interval defined in the @sampleInterval parameter, except that the timestamp corresponds to the moment the greatest value found occurred
MaximumActualTime2	Informs the greatest value found on data inside the sampling interval defined in the @sampleInterval parameter, except that the timestamp corresponds to the moment the greatest value found occurred. It considers the Simple Bounding Values
Minimum	Informs the smallest value found on data inside the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the starting time of each sampling interval
Minimum2	Informs the smallest value found on data inside the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the starting time of each sampling interval. It considers the Simple Bounding Values
MinimumActualTime	Informs the smallest value found on data inside the sampling interval defined in the @sampleInterval parameter, except that the timestamp corresponds to the moment the smallest value found occurred
MinimumActualTime2	Informs the smallest value found on data inside the sampling interval defined in the @sampleInterval parameter, except that the timestamp corresponds to the moment the smallest value found occurred. It considers the Simple Bounding Values
NumberOfTransitions	Informs how many transitions occurred inside the sampling interval between values 0 (zero) and 1 (one)
PercentBad	Returns a percentage of time, relative to the time of the sampling interval, in which data had bad quality
PercentGood	Returns a percentage of time, relative to the time of the sampling interval, in which data had good quality
PercentInStateNonZero	Returns a percentage of time, relative to the time of the sampling interval, in which the value remained different from 0 (zero)
PercentInStateZero	Returns a percentage of time, relative to the time of the sampling interval, in which the value remained in 0 (zero)
Range	Returns the difference between the smallest and the greatest value found inside the sampling interval informed in the @sampleInterval parameter. Ignores bad quality values
Range2	Returns the difference between the smallest and the greatest value found inside the sampling interval informed in the @sampleInterval parameter. Ignores bad quality values and considers the Simple Bounding Values
StandardDeviationPopulation	Returns the population standard deviation (n) in the requested interval. It includes the Simple Bounding Values
StandardDeviationSample	Returns the sampling standard deviation (n - 1) in the requested interval
Start	Equivalent to the Interpolative function, but uses the original timestamp from the first value of the sampling interval
StartBounds	Equivalent to the Interpolative function, but uses the original timestamp from the first value of the sampling interval. It considers the Simple Bounding Values
TimeAverage	Calculates the weighted average by time using the Interpolated Bounding Values method inside the sampling interval defined in the @sampleInterval parameter
TimeAverage2	Calculates the weighted average by time using the Simple Bounding Values method inside the sampling interval defined in the @sampleInterval parameter
Total	Returns the sum of data from the sampling interval defined in the @sampleInterval parameter
Total2	Returns the sum of data with good quality from the sampling interval defined in the @sampleInterval parameter
Trend	Interpolates queried data for a better visualization on charts
VariancePopulation	Returns the variance for the StandardDeviationPopulation function
VarianceSample	Returns the variance for the StandardDeviationSample function
WorstQuality	Returns the worst quality found in the interval specified in the @sampleInterval parameter
WorstQuality2	Returns the worst quality found in the interval specified in the @sampleInterval parameter. It considers the Simple Bounding Values

Resulting Fields

•Name: Name of the variable or variables queried

•Timestamp: Timestamp of data

•Quality: Quality of data

•Value: Value of data

Usage Example

To return data from variable RandomTags_Random1 in the period between 10:23 and 11:23 on 12/07/2020, with a timezone of -3 (minus three) hours and interpolated at every 5 (five) minutes, use the next declaration.

SELECT Name, Timestamp, Quality, Value FROM EpmQueryAggregateFunction(-3, '2020-12-07 10:23:00', '2020-12-07 11:23:00', 300000, 'Interpolative', 'RandomTags1')

Was this page useful?

EpmQueryAggregateFunction

EpmQueryAggregateFunction

Syntax

Parameters

Resulting Fields

Usage Example

Feedback