funnel_sequence 插件funnel_sequence plugin

• 08/06/2020
• • 
  • 

计算已获取州序列的用户的非重复计数，以及序列产生前/后的上一个/下一个州的分布。Calculates distinct count of users who have taken a sequence of states, and the distribution of previous/next states that have led to/were followed by the sequence.

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

语法Syntax

T | evaluate funnel_sequence(IdColumn, TimelineColumn, Start, End, MaxSequenceStepWindow, Step, StateColumn, Sequence)T | evaluate funnel_sequence(IdColumn, TimelineColumn, Start, End, MaxSequenceStepWindow, Step, StateColumn, Sequence)

参数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.
• MaxSequenceStepWindow：序列中两个顺序步骤之间允许的最大时间跨度的标量常数值。MaxSequenceStepWindow: scalar constant value of the max allowed timespan between two sequential steps in the sequence.
• 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).

返回Returns

返回三个输出表，用于构造已分析序列的桑基图：Returns three output tables, which are useful for constructing a sankey diagram for the analyzed sequence:

• 表 #1 - prev-sequence-next dcount TimelineColumn：已分析的时间范围 上一个：上一个州（如果有任何用户只存在所搜索序列的事件，而没有该序列之前的任何事件，则此项可能为空）。Table #1 - prev-sequence-next dcount TimelineColumn: the analyzed time window prev: the prev state (may be empty if there were any users that only had events for the searched sequence, but not any events prior to it). 下一个：下一个州（如果有任何用户只存在所搜索序列的事件，而没有该序列之后的任何事件，则此项可能为空）。next: the next state (may be empty if there were any users that only had events for the searched sequence, but not any events that followed it). dcount：时间范围内进行了 prev --> Sequence --> next 转换的 IdColumn 的非重复计数。dcount: distinct count of IdColumn in time window that transitioned prev --> Sequence --> next. 示例：与行的序列对应的 ID 的数组（来自 IdColumn）（最多返回 128 个 ID）。samples: an array of IDs (from IdColumn) corresponding to the row's sequence (a maximum of 128 IDs are returned).

• 表 #2 - prev-sequence dcount TimelineColumn：已分析的时间范围 上一个：上一个州（如果有任何用户只存在所搜索序列的事件，而没有该序列之前的任何事件，则此项可能为空）。Table #2 - prev-sequence dcount TimelineColumn: the analyzed time window prev: the prev state (may be empty if there were any users that only had events for the searched sequence, but not any events prior to it). dcount：时间范围内进行了 prev --> Sequence --> next 转换的 IdColumn 的非重复计数。dcount: distinct count of IdColumn in time window that transitioned prev --> Sequence --> next. 示例：与行的序列对应的 ID 的数组（来自 IdColumn）（最多返回 128 个 ID）。samples: an array of IDs (from IdColumn) corresponding to the row's sequence (a maximum of 128 IDs are returned).

• 表 #3 - sequence-next dcount TimelineColumn：已分析的时间范围 下一个：下一个州（如果有任何用户只存在所搜索序列的事件，而没有该序列之后的任何事件，则此项可能为空）。Table #3 - sequence-next dcount TimelineColumn: the analyzed time window next: the next state (may be empty if there were any users that only had events for the searched sequence, but not any events that followed it). dcount：时间范围内进行了 prev --> Sequence --> next 转换的 IdColumn 的非重复计数。dcount: distinct count of IdColumn in time window that transitioned prev --> Sequence --> next. 示例：与行的序列对应的 ID 的数组（来自 IdColumn）（最多返回 128 个 ID）。samples: an array of IDs (from IdColumn) corresponding to the row's sequence (a maximum of 128 IDs are returned).

示例Examples

探索 StormEventsExploring Storm Events

以下查询查看表 StormEvents（2007 年的天气统计信息），并显示在 2007 年中发生所有龙卷风事件之前/之后发生的事件。The following query looks at the table StormEvents (weather statistics for 2007) and shows which events happened before/after all Tornado events occurred in 2007.

// Looking on StormEvents statistics: 
// Q1: What happens before Tornado event?
// Q2: What happens after Tornado event?
StormEvents
| evaluate funnel_sequence(EpisodeId, StartTime, datetime(2007-01-01), datetime(2008-01-01), 1d,365d, EventType, dynamic(['Tornado']))

结果包括三个表：Result includes three tables:

• 表 #1：序列之前和之后发生的情况的所有可能的变体。Table #1: All possible variants of what happened before and after the sequence. 例如，第二行表示存在 87 个不同的事件，这些事件具有以下序列：Hail -> Tornado -> HailFor example, the second line means that there were 87 different events that had following sequence: Hail -> Tornado -> Hail

• 表 #2：显示按上一个事件分组的所有不同事件。Table #2: shows all distinct events grouped by the previous event. 例如，第二行表明在 Tornado 之前总共发生了 150 个 Hail 事件。For example, the second line shows that there were a total of 150 events of Hail that happened just before Tornado.

• 表 #3：显示按下一个事件分组的所有不同事件。Table #3: shows all distinct events grouped by next event. 例如，第二行表明在 Tornado 之后总共发生了 143 个 Hail 事件。For example, the second line shows that there were a total of 143 events of Hail that happened after Tornado.

现在，让我们尝试查明下面的序列如何继续进行：Now, let's try to find out how the following sequence continues:
Hail -> Tornado -> Thunderstorm Wind

StormEvents
| evaluate funnel_sequence(EpisodeId, StartTime, datetime(2007-01-01), datetime(2008-01-01), 1d,365d, EventType, 
dynamic(['Hail', 'Tornado', 'Thunderstorm Wind']))

跳过 Table #1 和 Table #2，查看 Table #3，我们可以断定在以序列 Hail -> Tornado -> Thunderstorm Wind 结束的 92 个事件中，有 41 个事件继续发生了 Hail，有 14 个事件回头发生了 Tornado。Skipping Table #1 and Table #2, and looking at Table #3, we can conclude that sequence Hail -> Tornado -> Thunderstorm Wind in 92 events ended with this sequence, continued as Hail in 41 events, and turned back to Tornado in 14.