Links

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
}

Dialog