series_rolling_fl()series_rolling_fl()

函数 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.

备注

语法Syntax

T | invoke series_rolling_fl(y_series, y_rolling_series, n, aggr, aggr_params, center)T | invoke series_rolling_fl(y_series, y_rolling_series, n, aggr, aggr_params, center)

参数Arguments

  • y_series:要拟合的序列所在的输入表列的名称。y_series : The name of the column (of the input table) containing the series to fit.
  • y_rolling_series:存储滚动聚合序列的列的名称。y_rolling_series : The name of the column to store the rolling aggregation series.
  • n:滚动窗口的宽度。n : The width of the rolling window.
  • aggr:要使用的聚合函数的名称。aggr : The name of the aggregation function to use. 请参阅聚合函数See aggregation functions.
  • aggr_params:聚合函数的可选参数。aggr_params : Optional parameters for the aggregation function.
  • center:一个可选布尔值,指示滚动窗口是否为以下选项之一: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.
      默认情况下,对于流数据的计算,center 为 false。By default, center is false, for calculation on streaming data.

聚合函数Aggregation functions

此函数支持从序列计算标量的 numpyscipy.stats 中的任何聚合函数。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:

使用情况Usage

series_rolling_fl() 是用户定义的表格函数,需使用 invoke 运算符进行应用。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.

如果是临时使用,请使用 let 语句嵌入该函数的代码。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

描述了 9 个元素的滚动中值的图形

其他示例Additional examples

以下示例假定已安装该函数:The following examples assume the function is already installed:

  1. 计算 15 个元素的滚动最小值、最大值和第 75 个百分位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
    

    描述了 15 个元素的滚动最小值、最大值和第 75 个百分位的图形

  2. 计算滚动剪裁平均值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
    

    描述了滚动剪裁平均值的图形