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
[
{
"serverIp":"10.0.0.1",
"time":1628894700000,
"cpuUsage":52.3
},
{
"serverIp":"10.0.0.1",
"time":1628894760000,
"cpuUsage":2.4
},
{
"serverIp":"10.0.0.1",
"time":1628894820000,
"cpuUsage":99.3
},
{
"serverIp":"10.0.0.1",
"time":1628894880000,
"cpuUsage":99.6
},
{
"serverIp":"10.0.0.1",
"time":1628894940000,
"cpuUsage":12.3
},
{
"serverIp":"10.0.0.1",
"time":1628895000000,
"cpuUsage":55
},
{
"serverIp":"10.0.0.2",
"time":1628894700000,
"cpuUsage":2.3
},
{
"serverIp":"10.0.0.2",
"time":1628894760000
},
{
"serverIp":"10.0.0.2",
"time":1628894820000,
"cpuUsage":9.3
},
{
"serverIp":"10.0.0.2",
"time":1628894880000,
"cpuUsage":9.6
},
{
"serverIp":"10.0.0.2",
"time":1628894940000,
"cpuUsage":2.3
}
]
Query
Find the weighted average cpu usage, giving weight to the time the data was received:
SET
DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
SET
string_date = TO_STRING(data.time);
SELECT
data.serverIp AS serverip:STRING,
WEIGHTED_AVERAGE(data.cpuUsage, data.time)
AS weighted_average_data_cpuusage__headers_head_index:NUMBER,
AVG(data.cpuUsage) AS avg_data_cpuusage:DOUBLE
FROM
"TIME_SERIES_DATA_w_NULL"
GROUP BY
data.serverIp
Results
{
"avg_data_cpuusage":5.875000000000001,
"weighted_average_data_cpuusageheaders_head_index":5.875000034302398,
"serverip":"10.0.0.2"
}{
"avg_data_cpuusage":53.48333333333333,
"weighted_average_data_cpuusageheaders_head_index":53.48333346685945,
"serverip":"10.0.0.1"
}
Related Functions
Dialog

Last updated
Was this helpful?