pivot 插件pivot plugin

通过将输入表的某一列中的唯一值转换为输出表中的多个列来旋转表,并在必要时对最终输出中所需的任何其余列值执行聚合。Rotates a table by turning the unique values from one column in the input table into multiple columns in the output table, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

T | evaluate pivot(PivotColumn)

备注

pivot 插件的输出架构基于数据,因此,查询可能会为任意两个运行生成不同的架构。The output schema of the pivot plugin is based on the data and therefore query may produce different schema for any two runs. 这也意味着,引用未包装列的查询随时可能会“中断”。This also means that query that is referencing unpacked columns may become 'broken' at any time. 由于此原因,不建议将此插件用于自动化作业。Due to this reason - it is not advised to use this plugin for automation jobs.

语法Syntax

T | evaluate pivot(pivotColumn[, aggregationFunction] [,column1 [,column2 ... ]])T | evaluate pivot(pivotColumn[, aggregationFunction] [,column1 [,column2 ... ]])

参数Arguments

  • pivotColumn:要旋转的列。pivotColumn : The column to rotate. 此列中的每个唯一值将是输出表中的一个列。each unique value from this column will be a column in the output table.
  • 聚合函数:(可选)将输入表中的多个行聚合到输出表中的单个行。aggregation function : (optional) aggregates multiple rows in the input table to a single row in the output table. 当前支持的函数:min()max()any()sum()dcount()avg()stdev()variance()make_list()make_bag()make_set()count()(默认值为 count())。Currently supported functions: min(), max(), any(), sum(), dcount(), avg(), stdev(), variance(), make_list(), make_bag(), make_set(), count() (default is count()).
  • column1、column2……:(可选)列名称。column1 , column2 , ...: (optional) column names. 输出表将为每个指定列包含一个额外的列。The output table will contain an additional column per each specified column. 默认值:除已透视列和聚合列以外的所有列。default: all columns other than the pivoted column and the aggregation column.

返回Returns

Pivot 返回包含指定列( column1column2 ……)及透视列的所有唯一值的旋转表。Pivot returns the rotated table with specified columns ( column1 , column2 , ...) plus all unique values of the pivot columns. 已透视列的每个单元格都将包含聚合函数计算。Each cell for the pivoted columns will contain the aggregate function computation.

示例Examples

按列透视Pivot by a column

对于以“AL”开头的每个 EventType 和 State,计算此州内该类型的事件数。For each EventType and States starting with 'AL', count the number of events of this type in this state.

StormEvents
| project State, EventType 
| where State startswith "AL" 
| where EventType has "Wind" 
| evaluate pivot(State)
EventTypeEventType ALABAMAALABAMA ALASKAALASKA
雷雨大风Thunderstorm Wind 352352 11
High WindHigh Wind 00 9595
Extreme Cold/Wind ChillExtreme Cold/Wind Chill 00 1010
Strong WindStrong Wind 2222 00

使用聚合函数按列进行透视Pivot by a column with aggregation function

对于每个以“AR”开头的 EventType 和 State,显示直接死亡人数的总数。For each EventType and States starting with 'AR', display the total number of direct deaths.

StormEvents 
| where State startswith "AR" 
| project State, EventType, DeathsDirect 
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect))
EventTypeEventType ARKANSASARKANSAS ARIZONAARIZONA
暴雨Heavy Rain 11 00
雷雨大风Thunderstorm Wind 11 00
LightningLightning 00 11
山洪Flash Flood 00 66
Strong WindStrong Wind 11 00
Heat 33 00

按包含聚合函数的列和单个附加列进行透视Pivot by a column with aggregation function and a single additional column

结果与上一示例相同。Result is identical to previous example.

StormEvents 
| where State startswith "AR" 
| project State, EventType, DeathsDirect 
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType)
EventTypeEventType ARKANSASARKANSAS ARIZONAARIZONA
暴雨Heavy Rain 11 00
雷雨大风Thunderstorm Wind 11 00
LightningLightning 00 11
山洪Flash Flood 00 66
Strong WindStrong Wind 11 00
Heat 33 00

指定已透视列、聚合函数和多个附加列Specify the pivoted column, aggregation function and multiple additional columns

对于每个事件类型、源和州,计算直接死亡人数之和。For each event type, source and state, sum the number of direct deaths.

StormEvents 
| where State startswith "AR" 
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType, Source)
EventTypeEventType SourceSource ARKANSASARKANSAS ARIZONAARIZONA
暴雨Heavy Rain 灾害管理Emergency Manager 11 00
雷雨大风Thunderstorm Wind 灾害管理Emergency Manager 11 00
LightningLightning 报纸Newspaper 00 11
山洪Flash Flood 专业观测员Trained Spotter 00 22
山洪Flash Flood Broadcast MediaBroadcast Media 00 33
山洪Flash Flood 报纸Newspaper 00 11
Strong WindStrong Wind 执法机构Law Enforcement 11 00
Heat 报纸Newspaper 33 00