activity_counts_metrics 插件activity_counts_metrics plugin

计算每个时间窗口与所有先前的时间窗口进行比较/聚合的实用活动指标。Calculates useful activity metrics for each time window compared/aggregated to all previous time windows. 这些指标包括:总计数值、非重复计数值、新值的非重复计数和聚合非重复计数。Metrics include: total count values, distinct count values, distinct count of new values, and aggregated distinct count. 将此插件与 activity_metrics 插件进行比较;在 activity_metrics 插件中,仅将每个时间窗口与其前一个时间窗口进行比较。Compare this plugin to activity_metrics plugin, in which every time window is compared to its previous time window only.

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

语法Syntax

T | evaluate activity_counts_metrics(IdColumn, TimelineColumn, Start, End, Window [, Cohort ] [, dim1, dim2, ...] [, Lookback ] )T | evaluate activity_counts_metrics(IdColumn, TimelineColumn, Start, End, Window [, Cohort ] [, dim1, dim2, ...] [, Lookback ] )

参数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 that represents the timeline.
  • 开始 :带有分析开始时段值的标量。Start : Scalar with value of the analysis start period.
  • End:带有分析结束时段值的标量。End : Scalar with value of the analysis end period.
  • Window:带有分析窗口时段值的标量。Window : Scalar with value of the analysis window period. 既可以是数字/日期时间/时间戳值,也可以是 week/month/year 中的一个字符串(在这种情况下,所有时段都将是 startofweek/startofmonthstartofyear)。Can be either a numeric/datetime/timestamp value, or a string that is one of week/month/year, in which case all periods will be startofweek/startofmonth or startofyear.
  • dim1, dim2, ... :(可选)维度列的列表,用于切分活动指标计算。dim1 , dim2 , ...: (optional) list of the dimensions columns that slice the activity metrics calculation.

返回Returns

返回一个表,其中包括每个时间窗口的总计数值、非重复计数值、新值的非重复计数和聚合非重复计数。Returns a table that has: total count values, distinct count values, distinct count of new values, and aggregated distinct count for each time window.

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

TimelineColumn dim1 ...... dim_n count dcount new_dcount aggregated_dcount
类型:自 TimelineColumntype: as of TimelineColumn .... .... .... longlong longlong longlong longlong longlong
  • TimelineColumn :时间窗口开始时间。TimelineColumn : The time window start time.
  • count :时间窗口和 dim 中的总记录数count : The total records count in the time window and dim(s)
  • dcount :时间窗口和 dim 中的非重复 ID 值dcount : The distinct ID values count in the time window and dim(s)
  • new_dcount :与之前的所有时间窗口相比,时间窗口和 dim 中的非重复 ID 值。new_dcount : The distinct ID values in the time window and dim(s) compared to all previous time windows.
  • aggregated_dcount :从第一个时间窗口到当前时间窗口(含),dim 的聚合非重复 ID 值总和。aggregated_dcount : The total aggregated distinct ID values of dim(s) from first-time window to current (inclusive).

示例Examples

每日活动计数Daily activity counts

下一个查询为提供的输入表计算每日活动计数The next query calculates daily activity counts for the provided input table

let start=datetime(2017-08-01);
let end=datetime(2017-08-04);
let window=1d;
let T = datatable(UserId:string, Timestamp:datetime)
[
'A', datetime(2017-08-01),
'D', datetime(2017-08-01), 
'J', datetime(2017-08-01),
'B', datetime(2017-08-01),
'C', datetime(2017-08-02),  
'T', datetime(2017-08-02),
'J', datetime(2017-08-02),
'H', datetime(2017-08-03),
'T', datetime(2017-08-03),
'T', datetime(2017-08-03),
'J', datetime(2017-08-03),
'B', datetime(2017-08-03),
'S', datetime(2017-08-03),
'S', datetime(2017-08-04),
];
 T 
 | evaluate activity_counts_metrics(UserId, Timestamp, start, end, window)
Timestamp count dcount new_dcount aggregated_dcount
2017-08-01 00:00:00.00000002017-08-01 00:00:00.0000000 44 44 44 44
2017-08-02 00:00:00.00000002017-08-02 00:00:00.0000000 33 33 22 66
2017-08-03 00:00:00.00000002017-08-03 00:00:00.0000000 66 55 22 88
2017-08-04 00:00:00.00000002017-08-04 00:00:00.0000000 11 11 00 88