Upsolver
Search…
WEIGHTED_AVERAGE
The weighted average of a value in the time window. The more current the time the higher the weight.

Syntax

WEIGHTED_AVERAGE(VALUE, WEIGHT)

Arguments

VALUE: The field to average. WEIGHT: The field giving weight. ‌

Returns

The result matches the type of value.

Notes

In the example below, cpuUsage is multiplied by time to give a Weighted Total. The Weighted Total is then divided by the sum of the times to give a Weighted Average. In Athena output, WEIGHTED_AVERAGE data can be BIGINT or NUMBER type. In Upsolver output, WEIGHTED_AVERAGE data can be NUMBER type only.

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 weighted average cpu usage, giving weight to the time the data was received:
1
SET
2
DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
3
SET
4
string_date = TO_STRING(data.time);
5
SELECT
6
data.serverIp AS serverip:STRING,
7
WEIGHTED_AVERAGE(data.cpuUsage, data.time)
8
AS weighted_average_data_cpuusage__headers_head_index:NUMBER,
9
AVG(data.cpuUsage) AS avg_data_cpuusage:DOUBLE
10
FROM
11
"TIME_SERIES_DATA_w_NULL"
12
GROUP BY
13
data.serverIp
Copied!

Results

1
{
2
"avg_data_cpuusage":5.875000000000001,
3
"weighted_average_data_cpuusageheaders_head_index":5.875000034302398,
4
"serverip":"10.0.0.2"
5
}{
6
"avg_data_cpuusage":53.48333333333333,
7
"weighted_average_data_cpuusageheaders_head_index":53.48333346685945,
8
"serverip":"10.0.0.1"
9
}
Copied!
AVG

Dialog