rolling_percentile() 插件rolling_percentile() plugin

按 BinSize 在 BinsPerWindow 大小的滚动(滑动)窗口中,返回 ValueColumn 填充值的指定百分位数的估计值 。Returns an estimate for the specified percentile of the ValueColumn population in a rolling (sliding) BinsPerWindow size window per BinSize .

T | evaluate rolling_percentile(ValueColumn, Percentile, IndexColumn, BinSize, BinsPerWindow)

语法Syntax

T | evaluate rolling_percentile(ValueColumn, Percentile, IndexColumn, BinSize, BinsPerWindow [, dim1, dim2, ...] )T | evaluate rolling_percentile(ValueColumn, Percentile, IndexColumn, BinSize, BinsPerWindow [, dim1, dim2, ...] )

参数Arguments

  • T :输入表格表达式。T : The input tabular expression.
  • ValueColumn :列的名称,其中包含要计算百分位数的值。ValueColumn : The name of the column with values to calculate the percentile of.
  • Percentile :带有要计算的百分位数的标量。Percentile : Scalar with the percentile to calculate.
  • IndexColumn :要对其运行滚动窗口的列名称。IndexColumn : The name of the column to run the rolling window over.
  • BinSize:标量,带有要在 IndexColumn 上应用的箱大小。BinSize : Scalar with size of the bins to apply over the IndexColumn .
  • BinsPerWindow :标量,具有每个窗口中包含的箱数。BinsPerWindow : Scalar with number of bins included in each window.
  • dim1 , dim2 , ... :(可选)要作为切片依据的维度列的列表。dim1 , dim2 , ... : (optional) list of the dimensions columns to slice by.

返回Returns

返回一个表,其中为每个箱设有一行(如果指定,则为维度的组合),该表含有截至箱处的窗口中的滚动百分位值。Returns a table with a row per each bin (and combination of dimensions if specified) that has the rolling percentile of values in the window ending at the bin (inclusive). 每个时间窗口的非重复计数值、新值的非重复计数、聚合非重复计数。distinct count values, distinct count of new values, aggregated distinct count for each time window.

输出表架构如下:Output table schema is:

IndexColumnIndexColumn dim1dim1 ...... dim_ndim_n rolling_BinsPerWindow_percentile_ValueColumn_Pctrolling_BinsPerWindow_percentile_ValueColumn_Pct

示例Examples

每天滚动 3 天的中值Rolling 3-day median value per day

下一个查询按每日粒度计算 3 天的中值。The next query calculates a 3-day median value in daily granularity. 输出中的每一行都表示过去 3 箱(天)的中值,包括箱本身。Each row in the output represents the median value for the last 3 bins (days), including the bin itself.

let T = 
range idx from 0 to 24*10-1 step 1
| project Timestamp = datetime(2018-01-01) + 1h*idx, val=idx+1
| extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
 T  
 | evaluate rolling_percentile(val, 50, Timestamp, 1d, 3)
TimestampTimestamp rolling_3_percentile_val_50rolling_3_percentile_val_50
2018-01-01 00:00:00.00000002018-01-01 00:00:00.0000000 1212
2018-01-02 00:00:00.00000002018-01-02 00:00:00.0000000 2424
2018-01-03 00:00:00.00000002018-01-03 00:00:00.0000000 3636
2018-01-04 00:00:00.00000002018-01-04 00:00:00.0000000 6060
2018-01-05 00:00:00.00000002018-01-05 00:00:00.0000000 8484
2018-01-06 00:00:00.00000002018-01-06 00:00:00.0000000 108108
2018-01-07 00:00:00.00000002018-01-07 00:00:00.0000000 132132
2018-01-08 00:00:00.00000002018-01-08 00:00:00.0000000 156156
2018-01-09 00:00:00.00000002018-01-09 00:00:00.0000000 180180
2018-01-10 00:00:00.00000002018-01-10 00:00:00.0000000 204204

每天按维度滚动 3 天的中值Rolling 3-day median value per day by dimension

依然采用上面的示例,但现在还要计算针对维度的每个值进行分区的滚动窗口。Same example from above, but now also calculates the rolling window partitioned for each value of the dimension.

let T = 
range idx from 0 to 24*10-1 step 1
| project Timestamp = datetime(2018-01-01) + 1h*idx, val=idx+1
| extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
 T  
 | evaluate rolling_percentile(val, 50, Timestamp, 1d, 3, EvenOrOdd)
TimestampTimestamp EvenOrOddEvenOrOdd rolling_3_percentile_val_50rolling_3_percentile_val_50
2018-01-01 00:00:00.00000002018-01-01 00:00:00.0000000 偶数Even 1212
2018-01-02 00:00:00.00000002018-01-02 00:00:00.0000000 偶数Even 2424
2018-01-03 00:00:00.00000002018-01-03 00:00:00.0000000 偶数Even 3636
2018-01-04 00:00:00.00000002018-01-04 00:00:00.0000000 偶数Even 6060
2018-01-05 00:00:00.00000002018-01-05 00:00:00.0000000 偶数Even 8484
2018-01-06 00:00:00.00000002018-01-06 00:00:00.0000000 偶数Even 108108
2018-01-07 00:00:00.00000002018-01-07 00:00:00.0000000 偶数Even 132132
2018-01-08 00:00:00.00000002018-01-08 00:00:00.0000000 偶数Even 156156
2018-01-09 00:00:00.00000002018-01-09 00:00:00.0000000 偶数Even 180180
2018-01-10 00:00:00.00000002018-01-10 00:00:00.0000000 偶数Even 204204
2018-01-01 00:00:00.00000002018-01-01 00:00:00.0000000 奇数Odd 1111
2018-01-02 00:00:00.00000002018-01-02 00:00:00.0000000 奇数Odd 2323
2018-01-03 00:00:00.00000002018-01-03 00:00:00.0000000 奇数Odd 3535
2018-01-04 00:00:00.00000002018-01-04 00:00:00.0000000 奇数Odd 5959
2018-01-05 00:00:00.00000002018-01-05 00:00:00.0000000 奇数Odd 8383
2018-01-06 00:00:00.00000002018-01-06 00:00:00.0000000 奇数Odd 107107
2018-01-07 00:00:00.00000002018-01-07 00:00:00.0000000 奇数Odd 131131
2018-01-08 00:00:00.00000002018-01-08 00:00:00.0000000 奇数Odd 155155
2018-01-09 00:00:00.00000002018-01-09 00:00:00.0000000 奇数Odd 179179
2018-01-10 00:00:00.00000002018-01-10 00:00:00.0000000 奇数Odd 203203