Upsolver
Search…
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!

Dialog

Last modified 8mo ago