Upsolver
Search…
AVG_EACH
Stores the average value for each group

Syntax

AVG_EACH([MAX_VALUE, ]GROUP, VALUE)

Arguments

MAX VALUES: The maximum number of groups. Groups beyond this amount will be discarded. ‌ VALUES: In some cases, depending on the data distribution, discarding groups via MAX may cause some data to be discarded from groups that would otherwise have been returned. MAX VALUES is optional. If it's omitted, there is no limit. GROUP: The field being evaluated. VALUE: The numeric field that is averaged.

Returns

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

Notes

Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL. e.g. time period 1628894760000, in the example below, contains a null. A null group key will also be ignored.

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 serverip:
1
SET
2
DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
3
SELECT
4
AVG_EACH(data.serverIp, data.cpuUsage)
5
AS avg_each_data_serverip__data_cpuusage,
6
data.serverIp AS serverip:STRING
7
FROM
8
"TIME_SERIES_DATA_w_NULL"
9
GROUP BY
10
data.serverIp
Copied!

Results:

1
{
2
"avg_each_data_serveripdata_cpuusage":[
3
{
4
"key":"10.0.0.2",
5
"value":5.875000000000001
6
}
7
],
8
"serverip":"10.0.0.2"
9
}{
10
"avg_each_data_serveripdata_cpuusage":[
11
{
12
"key":"10.0.0.1",
13
"value":53.48333333333333
14
}
15
],
16
"serverip":"10.0.0.1"
17
}
Copied!

Dialog

Last modified 8mo ago