Timeseries regression - trend line

[J01am]

 Hi all,

I needed to display the trend of a time series using a mathematical regression. Since DQL doesn’t provide an out-of-the-box function for this today, I implemented it with the following DQL query.

timeseries y=avg(dt.host.cpu.usage)

// N = nombre de points non-null
| fieldsAdd n = arraySize(arrayRemoveNulls(y))

// Sommes pour la régression (on ignore les slots où y[] est null)
| fieldsAdd sumY = arraySum(y),
sumX = arraySum(iCollectArray(if(isNotNull(y[]), iIndex()))),
sumXX = arraySum(iCollectArray(if(isNotNull(y[]), toDouble(iIndex()) * toDouble(iIndex())))),
sumXY = arraySum(iCollectArray(if(isNotNull(y[]), toDouble(iIndex()) * toDouble(y[]))))

// Coefficients slope/intercept
| fieldsAdd denom = (n * sumXX - sumX * sumX)
| fieldsAdd slope = if(denom != 0, (n * sumXY - sumX * sumY) / denom)
| fieldsAdd intercept = if(n != 0, (sumY - slope * sumX) / n)

// Série trend (même longueur que y, nulls conservés aux mêmes positions)
| fieldsAdd trend = iCollectArray(if(isNotNull(y[]), intercept + slope * toDouble(iIndex())))

// Output: 2 courbes (y + trend)
| fields timeframe, interval, y, trend

Hopefully this is useful to others—and maybe it could even become an out-of-the-box DQL function in the future.

Thanks