sliding_window_counts 插件sliding_window_counts plugin

使用此处所述的技术,计算回溯期间滑动窗口中的值的计数和非重复计数。Calculates counts and distinct count of values in a sliding window over a lookback period, using the technique described here.

例如,对于每一天,计算上周的用户计数和非重复计数 。For instance, for each day , calculate count and distinct count of users in previous week .

T | evaluate sliding_window_counts(id, datetime_column, startofday(ago(30d)), startofday(now()), 7d, 1d, dim1, dim2, dim3)

语法Syntax

T | evaluate sliding_window_counts(IdColumn, TimelineColumn, Start, End, LookbackWindow, Bin , [ dim1, dim2, ...])T | evaluate sliding_window_counts(IdColumn, TimelineColumn, Start, End, LookbackWindow, Bin , [ dim1, dim2, ...])

参数Arguments

  • T :输入表格表达式。T : The input tabular expression.
  • IdColumn :列的名称,其 ID 值表示用户活动。IdColumn : The name of the column with ID values that represent user activity.
  • TimelineColumn:表示时间线的列的名称。TimelineColumn : The name of the column representing the timeline.
  • 开始 :带有分析开始时段值的标量。Start : Scalar with value of the analysis start period.
  • End:带有分析结束时段值的标量。End : Scalar with value of the analysis end period.
  • LookbackWindow :回溯期间的标量常数值(例如,对于过去 7 天的 dcount 用户:LookbackWindow = 7d)LookbackWindow : Scalar constant value of the lookback period (for example, for dcount users in past 7d: LookbackWindow = 7d)
  • Bin :分析步骤时间段的标量常数值。Bin : Scalar constant value of the analysis step period. 此值可以是数字/日期时间/时间戳值。This value can be a numeric/datetime/timestamp value. 如果该值是格式为 week/month/year 的字符串,则所有期间都将为 startofweek/startofmonth/startofyearIf the value is a string with the format week/month/year, all periods will be startofweek/startofmonth/startofyear.
  • dim1, dim2, ... :(可选)维度列的列表,用于切分活动指标计算。dim1 , dim2 , ...: (optional) list of the dimensions columns that slice the activity metrics calculation.

返回Returns

返回一个表,该表包含回溯期间每个时间线时段(根据 bin)和每个现有维度组合的 ID 的计数和非重复计数值。Returns a table that has the count and distinct count values of Ids in the lookback period, for each timeline period (by bin) and for each existing dimensions combination.

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

TimelineColumnTimelineColumn dim1 .... dim_n count dcount
类型:自 TimelineColumn 起type: as of TimelineColumn .... .... .... longlong longlong

示例Examples

计算过去一周内每天分析时段的用户计数和 dcountsCalculate counts and dcounts for users in past week, for each day in the analysis period.

let start = datetime(2017 - 08 - 01);
let end = datetime(2017 - 08 - 07); 
let lookbackWindow = 3d;  
let bin = 1d;
let T = datatable(UserId:string, Timestamp:datetime)
[
'Bob',      datetime(2017 - 08 - 01), 
'David',    datetime(2017 - 08 - 01), 
'David',    datetime(2017 - 08 - 01), 
'John',     datetime(2017 - 08 - 01), 
'Bob',      datetime(2017 - 08 - 01), 
'Ananda',   datetime(2017 - 08 - 02),  
'Atul',     datetime(2017 - 08 - 02), 
'John',     datetime(2017 - 08 - 02), 
'Ananda',   datetime(2017 - 08 - 03), 
'Atul',     datetime(2017 - 08 - 03), 
'Atul',     datetime(2017 - 08 - 03), 
'John',     datetime(2017 - 08 - 03), 
'Bob',      datetime(2017 - 08 - 03), 
'Betsy',    datetime(2017 - 08 - 04), 
'Bob',      datetime(2017 - 08 - 05), 
];
T | evaluate sliding_window_counts(UserId, Timestamp, start, end, lookbackWindow, bin)


TimestampTimestamp 计数Count dcount
2017-08-01 00:00:00.00000002017-08-01 00:00:00.0000000 55 33
2017-08-02 00:00:00.00000002017-08-02 00:00:00.0000000 88 55
2017-08-03 00:00:00.00000002017-08-03 00:00:00.0000000 1313 55
2017-08-04 00:00:00.00000002017-08-04 00:00:00.0000000 99 55
2017-08-05 00:00:00.00000002017-08-05 00:00:00.0000000 77 55
2017-08-06 00:00:00.00000002017-08-06 00:00:00.0000000 22 22
2017-08-07 00:00:00.00000002017-08-07 00:00:00.0000000 11 11