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

[
   {
      "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 average cpuUsage in each serverip:

SET
   DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
SELECT
   AVG_EACH(data.serverIp, data.cpuUsage) 
       AS avg_each_data_serverip__data_cpuusage,
   data.serverIp AS serverip:STRING 
FROM
   "TIME_SERIES_DATA_w_NULL" 
GROUP BY
   data.serverIp

Results:

{
   "avg_each_data_serveripdata_cpuusage":[
      {
         "key":"10.0.0.2",
         "value":5.875000000000001
      }
   ],
   "serverip":"10.0.0.2"
}{
   "avg_each_data_serveripdata_cpuusage":[
      {
         "key":"10.0.0.1",
         "value":53.48333333333333
      }
   ],
   "serverip":"10.0.0.1"
}

Related Functions

AVG AVG_TIME_SERIES

Dialog