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

AVG

Dialog