funnel_sequence_completion 插件funnel_sequence_completion plugin

计算已完成的序列步骤(在起对比作用的不同时间段内)的漏斗。Calculates funnel of completed sequence steps within comparing different time periods.

T | evaluate funnel_sequence_completion(id, datetime_column, startofday(ago(30d)), startofday(now()), 1d, state_column, dynamic(['S1', 'S2', 'S3']), dynamic([10m, 30min, 1h]))

语法Syntax

T | evaluate funnel_sequence_completion(IdColumn, TimelineColumn, Start, End, Step, StateColumn, Sequence, MaxSequenceStepWindows)T | evaluate funnel_sequence_completion(IdColumn, TimelineColumn, Start, End, Step, StateColumn, Sequence, MaxSequenceStepWindows)

参数Arguments

  • T :输入表格表达式。T : The input tabular expression.
  • IdColum :列引用,必须出现在源表达式中。IdColum : column reference, must be present in the source expression.
  • TimelineColumn :表示时间线的列引用,必须出现在源表达式中。TimelineColumn : column reference representing timeline, must be present in the source expression.
  • Start :分析开始时间段的标量常数值。Start : scalar constant value of the analysis start period.
  • End :分析结束时间段的标量常数值。End : scalar constant value of the analysis end period.
  • Step :分析步骤时间段(箱)的标量常数值。Step : scalar constant value of the analysis step period (bin).
  • StateColumn :表示状态的列引用,必须出现在源表达式中。StateColumn : column reference representing the state, must be present in the source expression.
  • Sequence :常数动态数组,包含序列值(在 StateColumn 中查找值)。Sequence : a constant dynamic array with the sequence values (values are looked up in StateColumn).
  • MaxSequenceStepWindows :标量常数动态数组,包含序列中第一个和最后一个顺序步骤之间所允许的时间跨度最大值。MaxSequenceStepWindows : scalar constant dynamic array with the values of the max allowed timespan between the first and last sequential steps in the sequence. 该数组中每个窗口(时间段)都会生成漏斗分析结果。Each Window (period) in the array generates a funnel analysis result.

返回Returns

返回一个表,用于为所分析的序列构造漏斗图:Returns a single table useful for constructing a funnel diagram for the analyzed sequence:

  • TimelineColumn:分析的时间窗口TimelineColumn: the analyzed time window
  • StateColumn:序列的状态。StateColumn: the state of the sequence.
  • Period:可用于完成漏斗序列中步骤(从该序列中第一步度量)的最大时间段(窗口)。Period: the maximal period (window) allowed for completing steps in the funnel sequence measured from the first step in the sequence. MaxSequenceStepWindows 中的每个值都会生成与某个单独时间段相关的漏斗分析。Each value in MaxSequenceStepWindows generates a funnel analysis with a separate period.
  • dcount:从第一个序列状态转换到 StateColumn 值的时间窗口中 IdColumn 的非重复计数。dcount: distinct count of IdColumn in time window that transitioned from first sequence state to the value of StateColumn.

示例Examples

暴风雨活动探究Exploring Storm Events

以下查询检查序列(“总体”时间分别为 1 小时、4 小时和 1 天的 Hail -> Tornado -> Thunderstorm Wind)的完成漏斗。The following query checks the completion funnel of the sequence: Hail -> Tornado -> Thunderstorm Wind in "overall" time of 1hour, 4hours, 1day.

let _start = datetime(2007-01-01);
let _end =  datetime(2008-01-01);
let _windowSize = 365d;
let _sequence = dynamic(['Hail', 'Tornado', 'Thunderstorm Wind']);
let _periods = dynamic([1h, 4h, 1d]);
StormEvents
| evaluate funnel_sequence_completion(EpisodeId, StartTime, _start, _end, _windowSize, EventType, _sequence, _periods) 
StartTime EventType Period dcount
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 01:00:0001:00:00 28772877
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 龙卷风Tornado 01:00:0001:00:00 208208
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 01:00:0001:00:00 8787
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 04:00:0004:00:00 28772877
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 龙卷风Tornado 04:00:0004:00:00 231231
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 04:00:0004:00:00 141141
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 1.00:00:001.00:00:00 28772877
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 龙卷风Tornado 1.00:00:001.00:00:00 244244
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 1.00:00:001.00:00:00 155155

理解结果:Understanding the results:
结果为三个漏斗(所针对的时间段:1 小时、4 小时和 1 天)。The outcome is three funnels (for periods: One hour, 4 hours, and one day). 对于每个漏斗步骤,会显示多个非重复计数。For each funnel step, a number of distinct counts of are shown. 可以看出,给予完成整个 Hail -> Tornado -> Thunderstorm Wind 序列的时间越长,获得的 dcount 值就越高。You can see that the more time is given to complete the whole sequence of Hail -> Tornado -> Thunderstorm Wind, the higher dcount value is obtained. 换句话说,到达漏斗步骤的序列发生的次数也就越多。In other words, there were more occurrences of the sequence reaching the funnel step.