Upsolver
Search…
AVG
The average value in the time window.

Syntax

AVG(VALUE)

Arguments

VALUE: A numeric field ‌

Returns

The result type is a "number" which can be either integer or floating point.

Notes

Nulls are ignored by this function. If a row is empty or consists only of nulls, the result is NULL. e.g. time period 1628894760000, in the example contains a null.

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:

Find the average cpuUsage in each DATE_TIME period:
1
SET
2
DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
3
SELECT
4
AVG(data.cpuUsage) AS avg_data_cpuusage:DOUBLE,
5
DATE_TIME AS date_time:TIMESTAMP
6
FROM
7
"TIME_SERIES_DATA_w_NULL"
8
GROUP BY
9
DATE_TIME
Copied!

Results:

1
{
2
"avg_data_cpuusage":54,
3
"date_time":1628894820000
4
}{
5
"avg_data_cpuusage":55,
6
"date_time":1628895000000
7
}{
8
"avg_data_cpuusage":54,
9
"date_time":1628894880000
10
}{
11
"avg_data_cpuusage":2,
12
"date_time":1628894760000
13
}{
14
"avg_data_cpuusage":27,
15
"date_time":1628894700000
16
}{
17
"avg_data_cpuusage":7,
18
"date_time":1628894940000
19
}
Copied!

Dialog