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 inStateColumn
).
返回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-nextdcount
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 ofIdColumn
in time window that transitionedprev
-->Sequence
-->next
. 示例:与行的序列对应的 ID 的数组(来自IdColumn
)(最多返回 128 个 ID)。samples: an array of IDs (fromIdColumn
) corresponding to the row's sequence (a maximum of 128 IDs are returned).表 #2 - prev-sequence
dcount
TimelineColumn:已分析的时间范围 上一个:上一个州(如果有任何用户只存在所搜索序列的事件,而没有该序列之前的任何事件,则此项可能为空)。Table #2 - prev-sequencedcount
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 ofIdColumn
in time window that transitionedprev
-->Sequence
-->next
. 示例:与行的序列对应的 ID 的数组(来自IdColumn
)(最多返回 128 个 ID)。samples: an array of IDs (fromIdColumn
) corresponding to the row's sequence (a maximum of 128 IDs are returned).表 #3 - sequence-next
dcount
TimelineColumn:已分析的时间范围 下一个:下一个州(如果有任何用户只存在所搜索序列的事件,而没有该序列之后的任何事件,则此项可能为空)。Table #3 - sequence-nextdcount
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 ofIdColumn
in time window that transitionedprev
-->Sequence
-->next
. 示例:与行的序列对应的 ID 的数组(来自IdColumn
)(最多返回 128 个 ID)。samples: an array of IDs (fromIdColumn
) 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
->Hail
For 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 ofHail
that happened just beforeTornado
.
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 ofHail
that happened afterTornado
.
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 #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.
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 |