mv-apply 运算符mv-apply operator

对每个记录应用子查询,并返回所有子查询结果的并集。Applies a subquery to each record, and returns the union of the results of all subqueries.

例如,假设表 T 包含 dynamic 类型的列 Metric,其值为 real 数组成的数组。For example, assume a table T has a column Metric of type dynamic whose values are arrays of real numbers. 下面的查询将在每个 Metric 值中找到两个最大值,并返回对应于这些值的记录。The following query will locate the two biggest values in each Metric value, and return the records corresponding to these values.

T | mv-apply Metric to typeof(real) on 
(
   top 2 by Metric desc
)

mv-apply 运算符包括以下处理步骤:The mv-apply operator has the following processing steps:

  1. 使用 mv-expand 运算符将输入中的每条记录扩展为子表。Uses the mv-expand operator to expand each record in the input into subtables.
  2. 为每个子表应用子查询。Applies the subquery for each of the subtables.
  3. 将零个或更多列添加到生成的子表。Adds zero or more columns to the resulting subtable. 这些列包含未扩展的源列的值,并在需要时重复。These columns contain the values of the source columns that aren't expanded, and are repeated where needed.
  4. 返回结果的并集。Returns the union of the results.

mv-expand 运算符获取以下输入:The mv-expand operator gets the following inputs:

  1. 一个或多个表达式,其计算结果为要扩展的动态数组。One or more expressions that evaluate into dynamic arrays to expand. 每个扩展子表中的记录数是每个动态数组的最大长度。The number of records in each expanded subtable is the maximum length of each of those dynamic arrays. 如果指定了多个表达式,且相应数组的长度不同,则会添加 NULL 值。Null values are added where multiple expressions are specified and the corresponding arrays have different lengths.

  2. (可选)用于在扩展后分配表达式值的名称。Optionally, the names to assign the values of the expressions after expansion. 这些名称将成为子表中的列名。These names become the columns names in the subtables. 如果未指定,则在表达式为列引用时使用列的原始名称。If not specified, the original name of the column is used when the expression is a column reference. 否则,使用随机名称。A random name is used otherwise.

    备注

    建议使用默认列名。It is recommended to use the default column names.

  3. 扩展后,这些动态数组的元素的数据类型。The data types of the elements of those dynamic arrays, after expansion. 它们将成为子表中列的列类型。These become the column types of the columns in the subtables. 如果未指定,则使用 dynamicIf not specified, dynamic is used.

  4. (可选)要添加到子表中的列名,该列指定生成子表记录的数组中元素的从 0 开始的索引。Optionally, the name of a column to add to the subtables that specifies the 0-based index of the element in the array that resulted in the subtable record.

  5. (可选)要扩展的最大数组元素数。Optionally, the maximum number of array elements to expand.

可以将 mv-apply 运算符视为 mv-expand 运算符的通用化(事实上,如果子查询只包含投影,则后者可以通过前者来实现。)The mv-apply operator can be thought of as a generalization of the mv-expand operator (in fact, the latter can be implemented by the former, if the subquery includes only projections.)

语法Syntax

T | mv-apply [ItemIndex] ColumnsToExpand [RowLimit] on ( SubQuery ) T | mv-apply [ItemIndex] ColumnsToExpand [RowLimit] on ( SubQuery )

其中 ItemIndex 的语法如下:Where ItemIndex has the syntax:

with_itemindex = IndexColumnNamewith_itemindex = IndexColumnName

ColumnsToExpand 是以逗号分隔的列表,其中列出了一个或多个以下形式的元素:ColumnsToExpand is a comma-separated list of one or more elements of the form:

[Name =] ArrayExpression [to typeof (Typename)] [Name =] ArrayExpression [to typeof (Typename)]

RowLimit 只是:RowLimit is simply:

limit RowLimitlimit RowLimit

SubQuery 具有与任何查询语句相同的语法。and SubQuery has the same syntax of any query statement.

参数Arguments

  • ItemIndex:如果使用,则指示 long 类型的列的名称,该列在数组扩展阶段追加到输入,并指示扩展值的从 0 开始的数组索引。ItemIndex: If used, indicates the name of a column of type long that is appended to the input as part of the array-expansion phase and indicates the 0-based array index of the expanded value.

  • 名称:如果使用,则为用于分配每个数组扩展表达式的数组扩展值的名称。Name: If used, the name to assign the array-expanded values of each array-expanded expression. 如果未指定,则使用列名(如果可用)。If not specified, the name of the column will be used if available. 如果 ArrayExpression 不是简单的列名,则生成随机名称。A random name is generated if ArrayExpression is not a simple column name.

  • ArrayExpression:dynamic 类型的表达式,其值将经过数组扩展。ArrayExpression: An expression of type dynamic whose values will be array-expanded. 如果表达式为输入中的列名,则会从输入中删除输入列,输出中将显示具有相同名称(如已指定,则为 ColumnName)的新列。If the expression is the name of a column in the input, the input column is removed from the input and a new column of the same name (or ColumnName if specified) appears in the output.

  • Typename:如果使用,则为 dynamic 数组 ArrayExpression 的各个元素采用的类型名称。Typename: If used, the name of the type that the individual elements of the dynamic array ArrayExpression take. 不符合此类型的元素将替换为 NULL 值。Elements that do not conform to this type will be replaced by a null value. (如果未指定,则默认情况下使用 dynamic。)(If unspecified, dynamic is used by default.)

  • RowLimit:如果使用,则为基于每个输入记录生成的记录数限制。RowLimit: If used, a limit on the number of records to generate from each record of the input. (如果未指定,则使用 2147483647。)(If unspecified, 2147483647 is used.)

  • SubQuery:具有隐式表格源的表格查询表达式,应用于每个数组扩展子表。SubQuery: A tabular query expression with an implicit tabular source that gets applied to each array-expanded subtable.

备注Notes

  • mv-expand 运算符不同,mv-apply 运算符仅支持数组扩展。Unlike the mv-expand operator, the mv-apply operator supports array expansion only. 不支持扩展属性包。There's no support for expanding property bags.

示例Examples

获取数组中最大的元素Getting the largest element from the array

let _data =
range x from 1 to 8 step 1
| summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply element=l to typeof(long) on 
(
   top 1 by element
)
xMod2 ll elementelement
11 [1, 3, 5, 7][1, 3, 5, 7] 77
00 [2, 4, 6, 8][2, 4, 6, 8] 88

计算数组中最大的两个元素的总和Calculating the sum of the largest two elements in an array

let _data =
range x from 1 to 8 step 1
| summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply l to typeof(long) on
(
   top 2 by l
   | summarize SumOfTop2=sum(l)
)
xMod2 ll SumOfTop2SumOfTop2
11 [1,3,5,7][1,3,5,7] 1212
00 [2,4,6,8][2,4,6,8] 1414

使用 with_itemindex 来处理数组的子集Using with_itemindex for working with a subset of the array

let _data =
range x from 1 to 10 step 1
| summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply with_itemindex=index element=l to typeof(long) on 
(
   // here you have 'index' column
   where index >= 3
)
| project index, element
indexindex elementelement
33 77
44 99
33 88
44 1010

使用 mv-apply 运算符按某个键对 makelist 聚合的输出进行排序Using the mv-apply operator to sort the output of makelist aggregate by some key

datatable(command:string, command_time:datetime, user_id:string)
[
    'chmod',        datetime(2019-07-15),   "user1",
    'ls',           datetime(2019-07-02),   "user1",
    'dir',          datetime(2019-07-22),   "user1",
    'mkdir',        datetime(2019-07-14),   "user1",
    'rm',           datetime(2019-07-27),   "user1",
    'pwd',          datetime(2019-07-25),   "user1",
    'rm',           datetime(2019-07-23),   "user2",
    'pwd',          datetime(2019-07-25),   "user2",
]
| summarize commands_details = make_list(pack('command', command, 'command_time', command_time)) by user_id
| mv-apply command_details = commands_details on
(
    order by todatetime(command_details['command_time']) asc
    | summarize make_list(tostring(command_details['command']))
)
| project-away commands_details
user_id list_command_details_command
user1user1 [[
"ls","ls",
"mkdir","mkdir",
"chmod","chmod",
"dir","dir",
"pwd","pwd",
"rm""rm"
]]
user2user2 [[
"rm","rm",
"pwd""pwd"
]]

另请参阅See also