用户定义的函数User-defined functions

“用户定义的函数”是可重复使用的子查询,可将其定义为查询本身(临时函数)的一部分,或作为数据库元数据(存储函数)的一部分长期保存 。User-defined functions are reusable subqueries that can be defined as part of the query itself (ad-hoc functions), or persisted as part of the database metadata (stored functions). 通过“名称”调用用户定义的函数,为其提供零个或多个“输入参数”(可以是标量或表格),这些函数将根据函数体生成单个值(可以是标量或表格) 。User-defined functions are invoked through a name, are provided with zero or more input arguments (which can be scalar or tabular), and produce a single value (which can be scalar or tabular) based on the function body.

用户定义的函数属于以下两个类别之一:A user-defined function belongs to one of two categories:

  • 标量函数Scalar functions
  • 表格函数,也称为视图Tabular functions, also known as views

函数的输入参数和输出决定它是标量还是表格,进而确定其使用方式。The function's input arguments and output determine whether it is scalar or tabular, which then establishes how it might be used.

标量函数Scalar function

  • 具有零个输入参数,或者其所有输入参数均为标量值Has zero input arguments, or all its input arguments are scalar values
  • 生成单个标量值Produces a single scalar value
  • 可在允许使用标量表达式的任何地方使用Can be used wherever a scalar expression is allowed
  • 只能使用定义它的行上下文May only use the row context in which it is defined
  • 只能引用可访问架构中的表(和视图)Can only refer to tables (and views) that are in the accessible schema

表格函数Tabular function

  • 接受一个或多个表格输入参数、零个或多个标量输入参数和/或:Accepts one or more tabular input arguments, and zero or more scalar input arguments, and/or:
  • 生成单个表格值Produces a single tabular value

函数名称Function names

有效的用户定义的函数名称必须遵循与其他实体相同的标识符命名规则Valid user-defined function names must follow the same identifier naming rules as other entities.

该名称在定义的范围内也必须是唯一的。The name must also be unique in its scope of definition.

备注

如果存储函数和表具有相同的名称,则存储函数会在查询该表/函数名时重写。If a stored function and a table both have the same name, the stored function overrides when querying the table/function name.

输入参数Input arguments

有效的用户定义的函数遵循以下规则:Valid user-defined functions follow these rules:

  • 用户定义的函数具有一个包含零个或多个输入参数的强类型列表。A user-defined function has a strongly-typed list of zero or more input arguments.
  • 输入参数具有名称、类型和(对于标量参数)默认值An input argument has a name, a type, and (for scalar arguments) a default value.
  • 输入参数的名称是一个标识符。The name of an input argument is an identifier.
  • 输入参数的类型为标量数据类型之一或表格架构。The type of an input argument is either one of the scalar data types, or a tabular schema.

语法上,输入参数列表是用逗号分隔的参数定义列表,并用括号括起来。Syntactically, the input arguments list is a comma-separated list of argument definitions, wrapped in parenthesis. 每个参数定义都指定为Each argument definition is specified as

ArgName:ArgType [= ArgDefaultValue]

对于表格参数,ArgType 具有与表定义相同的语法(括号和列名/类型对列表),另外还支持单独的 (*)(指示“任何表格架构)。For tabular arguments, ArgType has the same syntax as the table definition (parenthesis and a list of column name/type pairs), with the additional support of a solitary (*) indicating "any tabular schema".

例如:For example:

语法Syntax 输入参数列表说明Input arguments list description
() 无参数No arguments
(s:string) 名为 s 的单个标量参数,其值类型为 stringSingle scalar argument called s taking a value of type string
(a:long, b:bool=true) 两个标量参数,其中的第二个参数具有默认值Two scalar arguments, the second of which has a default value
(T1:(*), T2(r:real), b:bool) 三个参数(两个表格参数和一个标量参数)Three arguments (two tabular arguments and one scalar argument)

备注

同时使用表格输入参数和标量输入参数时,请将所有表格输入参数置于标量输入参数之前。When using both tabular input arguments and scalar input arguments, put all tabular input arguments before the scalar input arguments.

示例Examples

标量函数:A scalar function:

let Add7 = (arg0:long = 5) { arg0 + 7 };
range x from 1 to 10 step 1
| extend x_plus_7 = Add7(x), five_plus_seven = Add7()

不带参数的表格函数:A tabular function taking no arguments:

let tenNumbers = () { range x from 1 to 10 step 1};
tenNumbers
| extend x_plus_7 = x + 7

同时采用表格输入和标量输入的表格函数:A tabular function taking both a tabular input and a scalar input:

let MyFilter = (T:(x:long), v:long) {
  T | where x >= v
};
MyFilter((range x from 1 to 10 step 1), 9)
xx
99
1010

使用未指定列的表格输入的表格函数。A tabular function that uses a tabular input with no column specified. 可将任意表传递给函数,并且不能在函数内引用任何表列。Any table can be passed to a function, and no table columns can be referenced inside the function.

let MyDistinct = (T:(*)) {
  T | distinct *
};
MyDistinct((range x from 1 to 3 step 1))
xx
11
22
33

声明用户定义的函数Declaring user-defined functions

用户定义的函数的声明提供:The declaration of a user-defined function provides:

  • 函数名称Function name
  • 函数架构(它接受的参数,如果有)Function schema (parameters it accepts, if any)
  • 函数体Function body

备注

不支持重载函数。Overloading functions isn't supported. 不能创建多个具有相同名称和不同输入架构的函数。You can't create multiple functions with the same name and different input schemas.

提示

Lambda 函数没有名称,该函数使用 let 语句绑定到名称。Lambda functions do not have a name and are bound to a name using a let statement. 因此,可将其视为用户定义的存储函数。Therefore, they can be regarded as user-defined stored functions. 示例:Lambda 函数的声明,该函数接受两个参数(称为 sstring 和称为 ilong)。Example: Declaration for a lambda function that accepts two arguments (a string called s and a long called i). 它将返回第一个(将其转换为数字后)和第二个的乘积。It returns the product of the first (after converting it into a number) and the second. Lambda 绑定到名称 fThe lambda is bound to the name f:

let f=(s:string, i:long) {
    tolong(s) * i
};

函数体包括:The function body includes:

  • 恰好一个表达式,该表达式提供函数的返回值(标量或表格值)。Exactly one expression, which provides the function's return value (scalar or tabular value).
  • 任何数量(零个或多个)的 let 语句,这些语句的范围是函数体的范围。Any number (zero or more) of let statements, whose scope is that of the function body. 如果指定,则 let 语句必须在定义函数返回值的表达式之前。If specified, the let statements must precede the expression defining the function's return value.
  • 任何数量(零个或多个)的查询参数语句,这些语句声明函数使用的查询参数。Any number (zero or more) of query parameters statements, which declare query parameters used by the function. 如果指定,则这些语句必须在定义函数返回值的表达式之前。If specified, they must precede the expression defining the function's return value.

备注

函数体内不支持“顶级”查询所支持的其他类型的查询语句Other kinds of query statements that are supported at the query "top level" aren't supported inside a function body.

用户定义的函数示例Examples of user-defined functions

使用 let 语句的用户定义的函数User-defined function that uses a let statement

下面的示例将名称 Test 绑定到使用三个 let 语句的用户定义的函数 (Lambda)。The following example binds the name Test to a user-defined function (lambda) that makes use of three let statements. 输出为 70The output is 70:

let Test1 = (id: int) {
  let Test2 = 10;
  let Test3 = 10 + Test2 + id;
  let Test4 = (arg: int) {
      let Test5 = 20;
      Test2 + Test3 + Test5 + arg
  };
  Test4(10)
};
range x from 1 to Test1(10) step 1
| count

定义参数默认值的用户定义的函数User-defined function that defines a default value for a parameter

以下示例演示一个接受三个参数的函数。The following example shows a function that accepts three arguments. 后两个参数具有默认值,并且不必存在于调用站点。The latter two have a default value and do not have to be present at the call site.

let f = (a:long, b:string = "b.default", c:long = 0) {
  strcat(a, "-", b, "-", c)
};
print f(12, c=7) // Returns "12-b.default-7"

调用用户定义的函数Invoking a user-defined function

不带参数的用户定义的函数可以通过函数名称或名称以及括号中的空参数列表进行调用。A user-defined function that takes no arguments can be invoked either by its name or by its name and an empty argument list in parentheses.

示例:Examples:

// Bind the identifier a to a user-defined function (lambda) that takes
// no arguments and returns a constant of type long:
let a=(){123};
// Invoke the function in two equivalent ways:
range x from 1 to 10 step 1
| extend y = x * a, z = x * a() 
// Bind the identifier T to a user-defined function (lambda) that takes
// no arguments and returns a random two-by-two table:
let T=(){
  range x from 1 to 2 step 1
  | project x1 = rand(), x2 = rand()
};
// Invoke the function in two equivalent ways:
// (Note that the second invocation must be itself wrapped in
// an additional set of parentheses, as the union operator
// differentiates between "plain" names and expressions)
union T, (T())

采用一个或多个标量参数的用户定义的函数可以使用表名称和括号中的具体参数列表进行调用:A user-defined function that takes one or more scalar arguments can be invoked by using the table name and a concrete argument list in parentheses:

let f=(a:string, b:string) {
  strcat(a, " (la la la)", b)
};
print f("hello", "world")

采用一个或多个表参数(和任意数量的标量参数)的用户定义的函数可以使用表名称和括号中的具体参数列表及进行调用:A user-defined function that takes one or more table arguments (and any number of scalar arguments) can be invoked using the table name and a concrete argument list in parentheses:

let MyFilter = (T:(x:long), v:long) {
  T | where x >= v 
};
MyFilter((range x from 1 to 10 step 1), 9)

还可以使用运算符 invoke 来调用采用一个或多个表参数并返回一个表的用户定义的函数。You can also use the operator invoke to invoke a user-defined function that takes one or more table arguments and returns a table. 如果函数的第一个具体表参数是 invoke 运算符的源,此函数很有用:This function is useful when the first concrete table argument to the function is the source of the invoke operator:

let append_to_column_a=(T:(a:string), what:string) {
    T | extend a=strcat(a, " ", what)
};
datatable (a:string) ["sad", "really", "sad"]
| invoke append_to_column_a(":-)")

默认值Default values

在以下条件下,函数可以为其某些参数提供默认值:Functions may provide default values to some of their parameters under the following conditions:

  • 只能为标量参数提供默认值。Default values may be provided for scalar parameters only.
  • 默认值始终为文本(常量)。Default values are always literals (constants). 它们不能是任意计算。They can't be arbitrary calculations.
  • 没有默认值的参数始终位于具有默认值的参数之前。Parameters with no default value always precede parameters that do have a default value.
  • 调用方必须提供所有参数的值,并且没有以与函数声明相同的顺序进行排列的默认值。Callers must provide the value of all parameters with no default values arranged in the same order as the function declaration.
  • 调用方不需要为具有默认值的参数提供值,但可以这样做。Callers don't need to provide the value for parameters with default values, but may do so.
  • 调用方可以按与参数顺序不匹配的顺序提供参数。Callers may provide arguments in an order that doesn't match the order of the parameters. 如果是这样,则他们必须为其参数命名。If so, they must name their arguments.

以下示例返回一个包含两个相同记录的表。The following example returns a table with two identical records. f 的第一次调用中,参数被完全“打乱”,因此每个参数都被明确赋予了一个名称:In the first invocation of f, the arguments are completely "scrambled", so each one is explicitly given a name:

let f = (a:long, b:string = "b.default", c:long = 0) {
  strcat(a, "-", b, "-", c)
};
union
  (print x=f(c=7, a=12)), // "12-b.default-7"
  (print x=f(12, c=7))    // "12-b.default-7"
xx
12-b.default-712-b.default-7
12-b.default-712-b.default-7

视图函数View functions

如果用户定义的函数不采用任何参数并返回一个表格表达式,则可以将其标记为“视图”。A user-defined function that takes no arguments and returns a tabular expression can be marked as a view. 将用户定义的函数标记为视图意味着每当通配符表名称解析完成时,该函数的行为就类似于表。Marking a user-defined function as a view means that the function behaves like a table whenever wildcard table name resolution is done. 以下示例演示两个用户定义的函数(T_viewT_notview),并展示 union 中的通配符引用如何仅解析第一个函数:The following example shows two user-defined functions, T_view and T_notview, and shows how only the first one is resolved by the wildcard reference in the union:

let T_view = view () { print x=1 };
let T_notview = () { print x=2 };
union T*

限制Restrictions

存在以下限制:The following restrictions apply:

  • 用户定义的函数无法传入 toscalar() 调用信息中,该信息取决于调用该函数的行上下文。User-defined functions can't pass into toscalar() invocation information that depends on the row-context in which the function is called.
  • 不能使用随行上下文变化的参数来调用返回表格表达式的用户定义函数。User-defined functions that return a tabular expression can't be invoked with an argument that varies with the row context.
  • 无法在远程群集上调用至少具有一个表格输入的函数。A function taking at least one tabular input can't be invoked on a remote cluster.
  • 无法在远程群集上调用标量函数。A scalar function can't be invoked on a remote cluster.

只有当用户定义的函数仅由标量函数构成,且不使用 toscalar() 时,才可以使用随行上下文而变化的参数来调用用户定义的函数。The only place a user-defined function may be invoked with an argument that varies with the row context is when the user-defined function is composed of scalar functions only and doesn't use toscalar().

限制示例 1Example of Restriction 1

// Supported:
// f is a scalar function that doesn't reference any tabular expression
let Table1 = datatable(xdate:datetime)[datetime(1970-01-01)];
let Table2 = datatable(Column:long)[1235];
let f = (hours:long) { now() + hours*1h };
Table2 | where Column != 123 | project d = f(10)

// Supported:
// f is a scalar function that references the tabular expression Table1,
// but is invoked with no reference to the current row context f(10):
let Table1 = datatable(xdate:datetime)[datetime(1970-01-01)];
let Table2 = datatable(Column:long)[1235];
let f = (hours:long) { toscalar(Table1 | summarize min(xdate) - hours*1h) };
Table2 | where Column != 123 | project d = f(10)

// Not supported:
// f is a scalar function that references the tabular expression Table1,
// and is invoked with a reference to the current row context f(Column):
let Table1 = datatable(xdate:datetime)[datetime(1970-01-01)];
let Table2 = datatable(Column:long)[1235];
let f = (hours:long) { toscalar(Table1 | summarize min(xdate) - hours*1h) };
Table2 | where Column != 123 | project d = f(Column)

限制示例 2Example of Restriction 2

// Not supported:
// f is a tabular function that is invoked in a context
// that expects a scalar value.
let Table1 = datatable(xdate:datetime)[datetime(1970-01-01)];
let Table2 = datatable(Column:long)[1235];
let f = (hours:long) { range x from 1 to hours step 1 | summarize make_list(x) };
Table2 | where Column != 123 | project d = f(Column)

用户定义的函数当前不支持的功能Features that are currently unsupported by user-defined functions

为了确保完整性,下面列出了用户定义的函数当前不支持的一些常见请求功能:For completeness, here are some commonly-requested features for user-defined functions that are currently not supported:

  1. 函数重载:目前没有办法重载函数(即创建多个具有相同名称和不同输入架构的函数)。Function overloading: There is currently no way to overload a function (i.e., create multiple functions with the same name and different input schema).

  2. 默认值:函数的标量参数默认值必须是标量文本(常量)。Default values: The default value for a scalar parameter to a function must be a scalar literal (constant). 此外,存储函数的默认值不能为 dynamic 类型。Furthermore, stored functions cannot have a default value of type dynamic.