funnel_sequence 插件funnel_sequence plugin

计算已获取州序列的用户的非重复计数,以及序列产生前/后的上一个/下一个州的分布。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
StartTime prev next dcount
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 293293
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 冰雹Hail 8787
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 雷雨大风Thunderstorm Wind 7777
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 雷雨大风Thunderstorm Wind 2828
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 2828
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 2727
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 2525
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 冰雹Hail 2424
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 2424
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 山洪Flash Flood 1212
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 山洪Flash Flood 88
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 88
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 雷雨大风Thunderstorm Wind 66
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 66
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 66
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 漏斗云Funnel Cloud 66
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 山洪Flash Flood 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 雷雨大风Thunderstorm Wind 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 漏斗云Funnel Cloud 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 冰雹Hail 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 漏斗云Funnel Cloud 33
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 雷雨大风Thunderstorm Wind 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 漏斗云Funnel Cloud 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 冰雹Hail 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 强风Strong Wind 雷雨大风Thunderstorm Wind 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 山洪Flash Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 冰雹Hail 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 洪水Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 冰雹Hail 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 闪电Lightning 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 暴雨Heavy Rain 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 洪水Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 洪水Flood 山洪Flash Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 闪电Lightning 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 雷雨大风Thunderstorm Wind 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 洪水Flood 雷雨大风Thunderstorm Wind 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 闪电Lightning 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 热带风暴Tropical Storm 飓风(台风)Hurricane (Typhoon) 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 沿海洪水Coastal Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 裂流Rip Current 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 大雪Heavy Snow 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 强风Strong Wind 11
  • 表 #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.
StartTime prev dcount
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 331331
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 150150
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 135135
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 2828
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 2222
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 55
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 洪水Flood 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 强风Strong Wind 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 大雪Heavy Snow 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 裂流Rip Current 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 沿海洪水Coastal Flood 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 热带风暴Tropical Storm 11
  • 表 #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.
StartTime next dcount
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 332332
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 145145
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 雷雨大风Thunderstorm Wind 143143
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 3232
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 漏斗云Funnel Cloud 2121
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 44
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 洪水Flood 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 飓风(台风)Hurricane (Typhoon) 11

现在,让我们尝试查明下面的序列如何继续进行: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 #1Table #2,查看 Table #3,我们可以断定在以序列 Hail -> Tornado -> Thunderstorm Wind 结束的 92 个事件中,有 41 个事件继续发生了 Hail,有 14 个事件回头发生了 TornadoSkipping 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.

StartTime next dcount
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 9292
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 冰雹Hail 4141
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 龙卷风Tornado 1414
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 山洪Flash Flood 1111
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 闪电Lightning 22
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 暴雨Heavy Rain 11
2007-01-01 00:00:00.00000002007-01-01 00:00:00.0000000 洪水Flood 11