Let statement

Artikkeli
08/12/2024

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

A let statement is used to set a variable name equal to an expression or a function, or to create views.

let statements are useful for:

Breaking up a complex expression into multiple parts, each represented by a variable.
Defining constants outside of the query body for readability.
Defining a variable once and using it multiple times within a query.

If the variable previously represented another value, for example in nested statements, the innermost let statement applies.

To optimize multiple uses of the let statement within a single query, see Optimize queries that use named expressions.

Note

The let statement binds a name to a calculation, not to the evaluated value of that calculation. This behavior means that multiple references to the same name can return different values due to the calculation being evaluated multiple times. If this is not the desired behavior, use toscalar() or materialize().

Syntax: Scalar or tabular expressions

let Name = Expression

Important

Let statements must be followed by a semicolon. There can be no blank lines between let statements or between let statements and other query statements.

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
Name	`string`	✔️	The variable name. You can escape the name with brackets. For example, `["Name with spaces"]`.
Expression	`string`	✔️	An expression with a scalar or tabular result. For example, an expression with a scalar result would be `let one=1;`, and an expression with a tabular result would be `let RecentLog = Logs \| where Timestamp > ago(1h)`.

Syntax: View or function

let Name = [view] ([ Parameters ]) { FunctionBody }

Important

Let statements must be followed by a semicolon. There can be no blank lines between let statements or between let statements and other query statements.

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
FunctionBody	`string`	✔️	An expression that yields a user defined function.
`view`	`string`		Only relevant for a parameter-less `let` statement. When used, the `let` statement is included in queries with a `union` operator with wildcard selection of the tables/views. For an example, see Create a view or virtual table.
Parameters	`string`		Zero or more comma-separated tabular or scalar function parameters. For each parameter of tabular type, the parameter should be in the format TableName`:`TableSchema, in which TableSchema is either a comma-separated list of columns in the format ColumnName`:`ColumnType or a wildcard (``). If columns are specified, then the input tabular argument must contain these columns. If a wildcard is specified, then the input tabular argument can have any schema. To reference columns in the function body, they must be specified. For examples, see Tabular argument with schema and Tabular argument with wildcard. For each parameter of scalar type, provide the parameter name and parameter type in the format Name`:`Type. The name can appear in the FunctionBody* and is bound to a particular value when the user defined function is invoked. The only supported types are `bool`, `string`, `long`, `datetime`, `timespan`, `real`, `dynamic`, and the aliases to these types.

Note

Tabular parameters must appear before scalar parameters.
Any two statements must be separated by a semicolon.

Examples

Define scalar values

The following example uses a scalar expression statement.

let n = 10;  // number
let place = "Dallas";  // string
let cutoff = ago(62d); // datetime 
Events 
| where timestamp > cutoff 
    and city == place 
| take n

The following example binds the name some number using the ['name'] notation, and then uses it in a tabular expression statement.

Run the query

let ['some number'] = 20;
range y from 0 to ['some number'] step 5

Create a user defined function with scalar calculation

This example uses the let statement with arguments for scalar calculation. The query defines function MultiplyByN for multiplying two numbers.

Run the query

let MultiplyByN = (val:long, n:long) { val * n };
range x from 1 to 5 step 1 
| extend result = MultiplyByN(x, 5)

Output

x	result
1	5
2	10
3	15
4	20
5	25

Create a user defined function that trims input

The following example removes leading and trailing ones from the input.

Run the query

let TrimOnes = (s:string) { trim("1", s) };
range x from 10 to 15 step 1 
| extend result = TrimOnes(tostring(x))

Output

x	result
10	0
11
12	2
13	3
14	4
15	5

Use multiple let statements

This example defines two let statements where one statement (foo2) uses another (foo1).

Run the query

let foo1 = (_start:long, _end:long, _step:long) { range x from _start to _end step _step};
let foo2 = (_step:long) { foo1(1, 100, _step)};
foo2(2) | count

Output

result
50

Create a view or virtual table

This example shows you how to use a let statement to create a view or virtual table.

Run the query

let Range10 = view () { range MyColumn from 1 to 10 step 1 };
let Range20 = view () { range MyColumn from 1 to 20 step 1 };
search MyColumn == 5

Output

$table	MyColumn
Range10	5
Range20	5

Use a materialize function

The materialize() function lets you cache subquery results during the time of query execution. When you use the materialize() function, the data is cached, and any subsequent invocation of the result uses cached data.

let totalPagesPerDay = PageViews
| summarize by Page, Day = startofday(Timestamp)
| summarize count() by Day;
let materializedScope = PageViews
| summarize by Page, Day = startofday(Timestamp);
let cachedResult = materialize(materializedScope);
cachedResult
| project Page, Day1 = Day
| join kind = inner
(
    cachedResult
    | project Page, Day2 = Day
)
on Page
| where Day2 > Day1
| summarize count() by Day1, Day2
| join kind = inner
    totalPagesPerDay
on $left.Day1 == $right.Day
| project Day1, Day2, Percentage = count_*100.0/count_1

Output

Day1	Day2	Percentage
2016-05-01 00:00:00.0000000	2016-05-02 00:00:00.0000000	34.0645725975255
2016-05-01 00:00:00.0000000	2016-05-03 00:00:00.0000000	16.618368960101
2016-05-02 00:00:00.0000000	2016-05-03 00:00:00.0000000	14.6291376489636

Using nested let statements

Nested let statements are permitted, including within a user defined function expression. Let statements and arguments apply in both the current and inner scope of the function body.

let start_time = ago(5h); 
let end_time = start_time + 2h; 
T | where Time > start_time and Time < end_time | ...

Tabular argument with schema

The following example specifies that the table parameter T must have a column State of type string. The table T may include other columns as well, but they can't be referenced in the function StateState because the aren't declared.

Run the query

let StateState=(T: (State: string)) { T | extend s_s=strcat(State, State) };
StormEvents
| invoke StateState()
| project State, s_s

Output

State	s_s
ATLANTIC SOUTH	ATLANTIC SOUTHATLANTIC SOUTH
FLORIDA	FLORIDAFLORIDA
FLORIDA	FLORIDAFLORIDA
GEORGIA	GEORGIAGEORGIA
MISSISSIPPI	MISSISSIPPIMISSISSIPPI
...	...

Tabular argument with wildcard

The table parameter T can have any schema, and the function CountRecordsInTable will work.

Run the query

let CountRecordsInTable=(T: (*)) { T | count };
StormEvents | invoke CountRecordsInTable()

Output

Count
59,066

Jaa

Let statement

Syntax: Scalar or tabular expressions

Parameters

Syntax: View or function

Parameters

Examples

Define scalar values

Create a user defined function with scalar calculation

Create a user defined function that trims input

Use multiple let statements

Create a view or virtual table

Use a materialize function

Using nested let statements

Tabular argument with schema

Tabular argument with wildcard

Palaute

Lisäresursseja