materialize()materialize()

允许在查询执行期间缓存子查询结果,以便其他子查询可以引用该部分结果。Allows caching a subquery result during the time of query execution in a way that other subqueries can reference the partial result.

语法Syntax

materialize(expression)materialize(expression)

参数Arguments

  • expression:在执行查询期间要计算和缓存的表格表达式。expression: Tabular expression to be evaluated and cached during query execution.

提示Tips

  • 将 materialize 与 join 或 union 一起使用(当其操作数包含可执行一次的相互的子查询时)。Use materialize with join or union when their operands have mutual subqueries that can be executed once. 请参阅下面的示例。See the examples below.

  • 如果需要联接/联合分支,materialize 也很有用。Useful also in scenarios when we need to join/union fork legs.

  • 如果为缓存结果指定了名称,则只能在 let 语句中使用 Materialize。Materialize can only be used in let statements if you give the cached result a name.

注意Note

  • Materialize 的缓存大小限制为 5 GB。Materialize has a cache size limit of 5 GB. 此限制针对每个群集节点,并且对于所有并发运行的查询都是共同的。This limit is per cluster node and is mutual for all queries running concurrently. 如果查询使用 materialize(),而缓存无法保存更多数据,则查询会中止并显示错误。If a query uses materialize() and the cache can't hold any more data, the query will abort with an error.

示例Examples

下面的示例演示如何使用 materialize() 来提高查询的性能。The following example shows how materialize() can be used to improve performance of the query. 表达式 _detailed_data 是使用 materialize() 函数定义的,因此它只计算一次。The expression _detailed_data is defined using materialize() function and therefore it's calculated only once.

let _detailed_data = materialize(StormEvents | summarize Events=count() by State, EventType);
_detailed_data
| summarize TotalStateEvents=sum(Events) by State
| join (_detailed_data) on State
| extend EventPercentage = Events*100.0 / TotalStateEvents
| project State, EventType, EventPercentage, Events
| top 10 by EventPercentage
状态State EventTypeEventType EventPercentageEventPercentage 事件Events
HAWAII WATERSHAWAII WATERS WaterspoutWaterspout 100100 22
LAKE ONTARIOLAKE ONTARIO Marine Thunderstorm WindMarine Thunderstorm Wind 100100 88
GULF OF ALASKAGULF OF ALASKA WaterspoutWaterspout 100100 44
ATLANTIC NORTHATLANTIC NORTH Marine Thunderstorm WindMarine Thunderstorm Wind 95.212765957446895.2127659574468 179179
LAKE ERIELAKE ERIE Marine Thunderstorm WindMarine Thunderstorm Wind 92.592592592592692.5925925925926 2525
E PACIFICE PACIFIC WaterspoutWaterspout 9090 99
LAKE MICHIGANLAKE MICHIGAN Marine Thunderstorm WindMarine Thunderstorm Wind 85.164835164835285.1648351648352 155155
LAKE HURONLAKE HURON Marine Thunderstorm WindMarine Thunderstorm Wind 79.365079365079479.3650793650794 5050
GULF OF MEXICOGULF OF MEXICO Marine Thunderstorm WindMarine Thunderstorm Wind 71.750433275563371.7504332755633 414414
HAWAIIHAWAII High SurfHigh Surf 70.021881838074470.0218818380744 320320

以下示例将生成一组随机数字并计算:The following example generates a set of random numbers and calculates:

  • 集中有多少不同的值 (Dcount)how many distinct values in the set (Dcount)
  • 集中的前三个值the top three values in the set
  • 集中所有这些值的总和the sum of all these values in the set

此操作可以使用批处理和 materialize 来完成:This operation can be done using batches and materialize:

let randomSet = 
    materialize(
        range x from 1 to 3000000 step 1
        | project value = rand(10000000));
randomSet | summarize Dcount=dcount(value);
randomSet | top 3 by value;
randomSet | summarize Sum=sum(value)

结果集 1:Result set 1:

DcountDcount
25783512578351

结果集 2:Result set 2:

valuevalue
99999989999998
99999989999998
99999979999997

结果集 3:Result set 3:

SumSum
1500296054356315002960543563