AVG

The average value in the time window.

Syntax

AVG(VALUE)

Arguments

VALUE: A numeric field ‌

Returns

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

Notes

Nulls are ignored by this function. If a row is empty or consists only of nulls, the result is NULL. e.g. time period 1628894760000, in the example contains a null.

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 DATE_TIME period:

SET
   DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
SELECT
   AVG(data.cpuUsage) AS avg_data_cpuusage:DOUBLE,
   DATE_TIME AS date_time:TIMESTAMP 
FROM
   "TIME_SERIES_DATA_w_NULL" 
GROUP BY
   DATE_TIME

Results:

{
   "avg_data_cpuusage":54,
   "date_time":1628894820000
}{
   "avg_data_cpuusage":55,
   "date_time":1628895000000
}{
   "avg_data_cpuusage":54,
   "date_time":1628894880000
}{
   "avg_data_cpuusage":2,
   "date_time":1628894760000
}{
   "avg_data_cpuusage":27,
   "date_time":1628894700000
}{
   "avg_data_cpuusage":7,
   "date_time":1628894940000
}