series_rolling_fl()
The function series_rolling_fl() applies rolling aggregation on a series. It takes a table containing multiple series (dynamic numerical array) and applies, for each series, a rolling aggregation function.
Note
series_rolling_fl()is a UDF (user-defined function). For more information, see usage.- This function contains inline Python and requires enabling the python() plugin on the cluster.
Syntax
T | invoke series_rolling_fl(y_series, y_rolling_series, n, aggr, aggr_params, center)
Arguments
- y_series: The name of the column (of the input table) containing the series to fit.
- y_rolling_series: The name of the column to store the rolling aggregation series.
- n: The width of the rolling window.
- aggr: The name of the aggregation function to use. See aggregation functions.
- aggr_params: Optional parameters for the aggregation function.
- center: An optional Boolean value that indicates whether the rolling window is one of the following options:
- applied symmetrically before and after the current point, or
- applied from the current point backwards.
By default, center is false, for calculation on streaming data.
Aggregation functions
This function supports any aggregation function from numpy or scipy.stats that calculates a scalar out of a series. The following list is not exhaustive:
summeanminmaxptp (max-min)percentilemedianstdvargmean(geometric mean)hmean(harmonic mean)mode(most common value)moment(nth moment)tmean(trimmed mean)tmintmaxtstdiqr(inter quantile range)
Usage
series_rolling_fl() is a user-defined tabular function, to be applied using the invoke operator. You can either embed its code in your query, or install it in your database. There are two usage options: ad hoc and persistent usage. See the below tabs for examples.
For ad hoc usage, embed its code using let statement. No permission is required.
let series_rolling_fl = (tbl:(*), y_series:string, y_rolling_series:string, n:int, aggr:string, aggr_params:dynamic=dynamic([null]), center:bool=true)
{
let kwargs = pack('y_series', y_series, 'y_rolling_series', y_rolling_series, 'n', n, 'aggr', aggr, 'aggr_params', aggr_params, 'center', center);
let code =
'\n'
'y_series = kargs["y_series"]\n'
'y_rolling_series = kargs["y_rolling_series"]\n'
'n = kargs["n"]\n'
'aggr = kargs["aggr"]\n'
'aggr_params = kargs["aggr_params"]\n'
'center = kargs["center"]\n'
'result = df\n'
'in_s = df[y_series]\n'
'func = getattr(np, aggr, None)\n'
'if not func:\n'
' import scipy.stats\n'
' func = getattr(scipy.stats, aggr)\n'
'if func:\n'
' result[y_rolling_series] = list(pd.Series(in_s[i]).rolling(n, center=center, min_periods=1).apply(func, args=aggr_params).values for i in range(len(in_s)))\n'
'\n';
tbl
| evaluate python(typeof(*), code, kwargs)
}
;
//
// Calculate rolling median of 9 elements
//
demo_make_series1
| make-series num=count() on TimeStamp step 1h by OsVer
| extend rolling_med = dynamic(null)
| invoke series_rolling_fl('num', 'rolling_med', 9, 'median')
| render timechart
Additional examples
The following examples assume the function is already installed:
Calculate rolling min, max & 75th percentile of 15 elements
// // Calculate rolling min, max & 75th percentile of 15 elements // demo_make_series1 | make-series num=count() on TimeStamp step 1h by OsVer | extend rolling_min = dynamic(null), rolling_max = dynamic(null), rolling_pct = dynamic(null) | invoke series_rolling_fl('num', 'rolling_min', 15, 'min', dynamic([null])) | invoke series_rolling_fl('num', 'rolling_max', 15, 'max', dynamic([null])) | invoke series_rolling_fl('num', 'rolling_pct', 15, 'percentile', dynamic([75])) | render timechart
Calculate rolling trimmed mean
// // Calculate rolling trimmed mean // range x from 1 to 100 step 1 | extend y=iff(x % 13 == 0, 2.0, iff(x % 23 == 0, -2.0, rand())) | summarize x=make_list(x), y=make_list(y) | extend yr = dynamic(null) | invoke series_rolling_fl('y', 'yr', 7, 'tmean', pack_array(pack_array(-2, 2), pack_array(false, false))) // trimmed mean: ignoring values outside [-2,2] inclusive | render linechart