series_downsample_fl()
The function series_downsample_fl() downsamples a time series by an integer factor. This function takes a table containing multiple time series (dynamic numerical array), and downsamples each series. The output contains both the coarser series and its respective times array. To avoid aliasing, the function applies a simple low pass filter on each series before subsampling.
Note
This function is a UDF (user-defined function). For more information, see usage.
Syntax
T | invoke series_downsample_fl(t_col, y_col, ds_t_col, ds_y_col, sampling_factor)
Arguments
- t_col: The name of the column (of the input table) containing the time axis of the series to downsample.
- y_col: The name of the column (of the input table) containing the series to downsample.
- ds_t_col: The name of the column to store the downsampled time axis of each series.
- ds_y_col: The name of the column to store the down sampled series.
- sampling_factor: an integer specifying the required down sampling.
Usage
series_downsample_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_downsample_fl=(tbl:(*), t_col:string, y_col:string, ds_t_col:string, ds_y_col:string, sampling_factor:int)
{
tbl
| extend _t_ = column_ifexists(t_col, dynamic(0)), _y_ = column_ifexists(y_col, dynamic(0))
| extend _y_ = series_fir(_y_, repeat(1, sampling_factor), true, true) // apply a simple low pass filter before sub-sampling
| mv-apply _t_ to typeof(DateTime), _y_ to typeof(double) on
(extend rid=row_number()-1
| where rid % sampling_factor == ceiling(sampling_factor/2.0)-1 // sub-sampling
| summarize _t_ = make_list(_t_), _y_ = make_list(_y_))
| extend cols = pack(ds_t_col, _t_, ds_y_col, _y_)
| project-away _t_, _y_
| evaluate bag_unpack(cols)
}
;
demo_make_series1
| make-series num=count() on TimeStamp step 1h by OsVer
| invoke series_downsample_fl('TimeStamp', 'num', 'coarse_TimeStamp', 'coarse_num', 4)
| render timechart with(xcolumn=coarse_TimeStamp, ycolumns=coarse_num)
The time series downsampled by 4:
For reference, here is the original time series (before downsampling):
demo_make_series1
| make-series num=count() on TimeStamp step 1h by OsVer
| render timechart with(xcolumn=TimeStamp, ycolumns=num)