|
<< Click to Display Table of Contents >>
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.
SELECT Name, Timestamp, Quality, Value FROM EpmQueryAggregateFunction(@timeZoneOffset, @startTime, @endTime, @sampleInterval, @aggregateFunction, @node)
•@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 |
Returns the total number of Annotations in the sampling interval defined in the @sampleInterval parameter |
Average |
Calculates the average of stored raw values with a good quality in the sampling interval defined in the @sampleInterval parameter |
Count |
Returns the total number of stored raw values with a good quality in the sampling interval defined in the @sampleInterval parameter |
Delta |
Returns the difference between the first and last raw value in the sampling interval defined in the @sampleInterval parameter with a good quality. Corresponds to the result of calculating @endTime – @startTime |
DeltaBounds |
Returns the difference between the value with a good quality of the beginning and ending of the sampling interval defined in the @sampleInterval parameter. Corresponds to the result of calculating EndBounds – StartBounds |
DurationBad |
Returns the time, in milliseconds, in which data had a bad quality in the sampling interval defined in the @sampleInterval parameter. Quality is defined based on the quality of data from this interval's limits |
DurationGood |
Returns the time, in milliseconds, in which data had a good quality in the sampling interval defined in the @sampleInterval parameter. Quality is defined based on the quality of data from this interval's limits |
DurationInStateNonZero |
Returns the elapsed time, in milliseconds, in which the status of a value with a good quality was different from 0 (zero) in the sampling interval defined in the @sampleInterval parameter |
DurationInStateZero |
Returns the elapsed time, in milliseconds, in which the status of a value with a good quality was 0 (zero) in the sampling interval defined in the @sampleInterval parameter |
End |
Returns the last raw value with a good quality in the sampling interval defined in the @sampleInterval parameter with the timestamp in which it occurred |
EndBounds |
Returns the value with a good quality from the end of the sampling interval defined in the @sampleInterval parameter |
Interpolative |
Interpolates the consecutive points of data with a good quality of the sampling interval in the @sampleInterval parameter. For discrete variables, the interpolation only keeps the previous value up to the next one, while for continuous variables the interpolation between two consecutive points is linear |
Maximum |
Equivalent to the MaximumActualTime value, but uses the timestamp of the beginning of the sampling interval in the @sampleInterval parameter instead of when the value effectively occurred |
Maximum2 |
Equivalent to the MaximumActualTime2 value, but uses the timestamp of the beginning of the sampling interval defined in the @sampleInterval parameter instead of when the value effectively occurred |
MaximumActualTime |
Returns the greatest raw value with a good quality in the sampling interval defined in the @sampleInterval parameter, using the timestamp of when the value itself occurred |
MaximumActualTime2 |
Returns the greatest value according to data linearly interpolated in the sampling interval defined in the @sampleInterval parameter |
Minimum |
Equivalent to the MinimumActualTime value, but uses the timestamp of the beginning of the sampling interval defined in the @sampleInterval parameter instead of when the value effectively occurred |
Minimum2 |
Equivalent to the MinimumActualTime2 value, but uses the timestamp of the beginning of the sampling interval defined in the @sampleInterval parameter instead of when the value effectively occurred |
MinimumActualTime |
Returns the smallest raw value with a good quality in the sampling interval defined in the @sampleInterval parameter, using the timestamp of when the value itself occurred |
MinimumActualTime2 |
Returns the smallest value according to data linearly interpolated in the sampling interval defined in the @sampleInterval parameter |
NumberOfTransitions |
Returns the total number of transitions of a raw value with a good quality from a variable in the sampling interval defined in the @sampleInterval parameter |
PercentBad |
Returns the result of calculating (DurationBad ÷ @sampleInterval) × 100 |
PercentGood |
Returns the result of calculating (DurationGood ÷ @sampleInterval) × 100 |
PercentInStateNonZero |
Returns the result of calculating (DurationInStateNonZero ÷ @sampleInterval) × 100 |
PercentInStateZero |
Returns the result of calculating (DurationInStateZero ÷ @sampleInterval) × 100 |
Range |
Returns the difference between the maximum and minimum raw value with a good quality in the sampling interval defined in the @sampleInterval parameter. If there is only one value with a good quality in that interval, returns the value 0 (zero) |
Range2 |
Returns the difference between the greatest and the smallest value according to the linearly interpolated data in the sampling interval defined in the @sampleInterval parameter |
StandardDeviationPopulation |
Returns the population standard deviation in the sampling interval defined in the @sampleInterval parameter |
StandardDeviationSample |
Returns the sample standard deviation in the sampling interval defined in the @sampleInterval parameter |
Start |
Returns the first raw value with a good quality in the sampling interval defined in the @sampleInterval parameter with the timestamp in which it occurred |
StartBounds |
Returns the value with a good quality from the beginning of the sampling interval defined in the @sampleInterval parameter |
TimeAverage |
Calculates the weighted average by time, using data with a good quality linearly interpolated in the sampling interval defined in the @sampleInterval parameter |
TimeAverage2 |
Calculates the weighted average by time, using interpolated data keeping the previous value up to the next one, in the sampling interval defined in the @sampleInterval parameter |
Total |
Returns the result of calculating TimeAverage × @sampleInterval, in seconds |
Total2 |
Returns the result of calculating TimeAverage2 × @sampleInterval, in seconds |
Trend |
Automatically Interpolates data in the sampling interval defined in the @sampleInterval parameter, prioritizing quality for chart viewing according to a given screen resolution |
VariancePopulation |
Returns the population variance, that is, the square root of the population standard deviation in the sampling interval defined in the @sampleInterval parameter |
VarianceSample |
Returns the sample variance, that is, the square root of the sample standard deviation in the sampling interval defined in the @sampleInterval parameter |
WorstQuality |
Returns the worst quality of raw data in the sampling interval defined in the @sampleInterval parameter. A Bad quality is worst than Uncertain, which by its turn is worst than Good. Timestamp always corresponds to the beginning of this interval |
WorstQuality2 |
Returns the worst quality of raw data in the sampling interval defined in the @sampleInterval parameter. A Bad quality is worst than Uncertain, which by its turn is worst than Good. The beginning of this interval is always included to determine the worst quality. Timestamp always corresponds to the beginning of this interval |
•Name: Name of the variable or variables queried
•Timestamp: Timestamp of data
•Quality: Quality of data
•Value: Value of data
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')