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
1
[
2
   {
3
      "serverIp":"10.0.0.1",
4
      "time":1628894700000,
5
      "cpuUsage":52.3
6
   },
7
   {
8
      "serverIp":"10.0.0.1",
9
      "time":1628894760000,
10
      "cpuUsage":2.4
11
   },
12
   {
13
      "serverIp":"10.0.0.1",
14
      "time":1628894820000,
15
      "cpuUsage":99.3
16
   },
17
   {
18
      "serverIp":"10.0.0.1",
19
      "time":1628894880000,
20
      "cpuUsage":99.6
21
   },
22
   {
23
      "serverIp":"10.0.0.1",
24
      "time":1628894940000,
25
      "cpuUsage":12.3
26
   },
27
   {
28
      "serverIp":"10.0.0.1",
29
      "time":1628895000000,
30
      "cpuUsage":55
31
   },
32
   {
33
      "serverIp":"10.0.0.2",
34
      "time":1628894700000,
35
      "cpuUsage":2.3
36
   },
37
   {
38
      "serverIp":"10.0.0.2",
39
      "time":1628894760000
40
   },
41
   {
42
      "serverIp":"10.0.0.2",
43
      "time":1628894820000,
44
      "cpuUsage":9.3
45
   },
46
   {
47
      "serverIp":"10.0.0.2",
48
      "time":1628894880000,
49
      "cpuUsage":9.6
50
   },
51
   {
52
      "serverIp":"10.0.0.2",
53
      "time":1628894940000,
54
      "cpuUsage":2.3
55
   }
56
]
Copied!
Query:
1
SET
2
   DATE_TIME_UNIX = UNIX_EPOCH_TO_DATE(data.time);
3
SELECT
4
   AVG_TIME_SERIES(data.time, data.cpuusage) AS avg_time_series_data_time__data_cpuusage,
5
   DATE_TIME_UNIX AS date_time_unix:TIMESTAMP 
6
FROM
7
   "TIME_SERIES_w_NULLs" 
8
GROUP BY
9
   DATE_TIME_UNIX
Copied!
Results:
1
{
2
   "avg_time_series_data_timedata_cpuusage":[
3
      {
4
         "key":1628894820000,
5
         "value":54.3
6
      }
7
   ],
8
   "date_time_unix":1628894820000
9
}{
10
   "avg_time_series_data_timedata_cpuusage":[
11
      {
12
         "key":1628895000000,
13
         "value":55
14
      }
15
   ],
16
   "date_time_unix":1628895000000
17
}{
18
   "avg_time_series_data_timedata_cpuusage":[
19
      {
20
         "key":1628894880000,
21
         "value":54.59999999999999
22
      }
23
   ],
24
   "date_time_unix":1628894880000
25
}{
26
   "avg_time_series_data_timedata_cpuusage":[
27
      {
28
         "key":1628894760000,
29
         "value":2.4
30
      }
31
   ],
32
   "date_time_unix":1628894760000
33
}{
34
   "avg_time_series_data_timedata_cpuusage":[
35
      {
36
         "key":1628894700000,
37
         "value":27.29999999999999
38
      }
39
   ],
40
   "date_time_unix":1628894700000
41
}{
42
   "avg_time_series_data_timedata_cpuusage":[
43
      {
44
         "key":1628894940000,
45
         "value":7.300000000000001
46
      }
47
   ],
48
   "date_time_unix":1628894940000
49
}
Copied!
Related Functions
​MAX_TIME_SERIES
​MIN_TIME_SERIES​
Dialog

AVG_EACH

COLLECT_SET

Last modified 8mo ago

Copy link

Contents