AVG_TIME_SERIES

Get the average value per time interval. The size of the time intervals is configurable and dynamic. The time interval used will change depending on how many points you wish to hold per aggregation key. ‌You can change the time interval, and/or the number of points per interval, to suit your graph.

Syntax

AVG_TIME_SERIES([MAX POINTS, INTERVALS, ]TIME, VALUE)

Arguments

MAX POINTS:The maximum amount of points to hold before reducing the resolution to the next interval size. Default: 300 INTERVALS: Array of integers representing the time interval buckets for which to return data. If the aggregation has more than MAX POINTS values in the given window it will use the next interval from this array to reduce the amount of points to comply with MAX POINTS. Default: [60000, 300000, 600000, 1800000, 3600000, 10800000, 21600000, 43200000, 86400000] TIME: An expression returning the time value that the VALUE is associated with. VALUE: An expression returning the value to aggregate in the provided TIME bucket.

Returns

An array of key value pairs, the key is the time (in epoch ms) and the value is of type VALUE and contains the average of the values within that time frame.

Notes

The default set of intervals assumes that the time is using epoch ms. In general, INTERVALS should use the same resolution as TIME.

Example

Data

[
   {
      "serverIp":"10.0.0.1",
      "time":1628894700000,
      "cpuUsage":52.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894760000,
      "cpuUsage":2.4
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894820000,
      "cpuUsage":99.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894880000,
      "cpuUsage":99.6
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894940000,
      "cpuUsage":12.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628895000000,
      "cpuUsage":55
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894700000,
      "cpuUsage":2.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894760000
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894820000,
      "cpuUsage":9.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894880000,
      "cpuUsage":9.6
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894940000,
      "cpuUsage":2.3
   }
]

Query:

SET
   DATE_TIME_UNIX = UNIX_EPOCH_TO_DATE(data.time);
SELECT
   AVG_TIME_SERIES(data.time, data.cpuusage) AS avg_time_series_data_time__data_cpuusage,
   DATE_TIME_UNIX AS date_time_unix:TIMESTAMP 
FROM
   "TIME_SERIES_w_NULLs" 
GROUP BY
   DATE_TIME_UNIX

Results:

{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628894820000,
         "value":54.3
      }
   ],
   "date_time_unix":1628894820000
}{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628895000000,
         "value":55
      }
   ],
   "date_time_unix":1628895000000
}{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628894880000,
         "value":54.59999999999999
      }
   ],
   "date_time_unix":1628894880000
}{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628894760000,
         "value":2.4
      }
   ],
   "date_time_unix":1628894760000
}{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628894700000,
         "value":27.29999999999999
      }
   ],
   "date_time_unix":1628894700000
}{
   "avg_time_series_data_timedata_cpuusage":[
      {
         "key":1628894940000,
         "value":7.300000000000001
      }
   ],
   "date_time_unix":1628894940000
}

Related Functions

MAX_TIME_SERIES MIN_TIME_SERIES

Dialog