Aggregation functions

Learn how to use and combine different aggregation functions in APL.

Statistical functions

All of these functions are used in the context of Summarize operator

Function Name	Description
avg()	Returns an average value across the group.
avgif()	Calculates the average value of Expr in records for which Predicate evaluates to true.
count()	Returns a count of the group without/with a predicate.
countif()	Returns a count of rows for which Predicate evaluates to true
dcount()	Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.
dcountif()	Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true.
max()	Returns the maximum value across the group.
maxif()	Calculates the maximum value of Expr in records for which Predicate evaluates to true.
min()	Returns the minimum value across the group.
minif()	Returns the minimum of Expr in records for which Predicate evaluates to true.
sum()	Calculates the sum of Expr across the group.
sumif()	Calculates the sum of Expr in records for which Predicate evaluates to true.
histogram()	Returns a timeseries heatmap chart across the group.
topk()	calculates the top values of Expr across the group in a dataset
percentile()	calculates the requested percentiles of the group and produces a timeseries chart.
variance()	Calculates the variance of Expr across the group.
varienceif()	Calculates the variance of Expr in records for which Predicate evaluates to true.
stdev()	Calculates the standard deviation of Expr across the group.
stdevif	Calculates the standard deviation of Expr in records for which Predicate evaluates to true.
make_list()	Creates a dynamic JSON object (array) of all the values of Expr in the group
make_list_if()	Creates a dynamic JSON object (array) of Expr values in the group for which Predicate evaluates to true.
make_set()	Creates a dynamic JSON array of the set of distinct values that Expr takes in the group.
make_set_if()	Creates a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true.
rate()	Calculates the rate of values in a group per second.

Each argument has a required section which is denoted with required or optional

• If it’s denoted by required it means the argument must be passed into that function before it’ll work.
• if it’s denoted by optional it means the function can work without passing the argument value.

expr is short for expression In APL, an expression is a combination of functions, operators, and constants used to compute a value or produce a result.

In the context of data explorer, an expression is used to define the specific data manipulation or extraction operation you want to perform on your dataset.

avg()

Calculates the average (arithmetic mean) of Expr across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation

Returns

The average value of Expr across the group.

Examples

avg (Expr)

['sample-http-logs']
| summarize avg( req_duration_ms)

Run in Playground

avgif()

Calculates the average value of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Predicate that if true, the Expr calculated value will be added to the average.

Returns

Returns the average value of Expr in records for which Predicate evaluates to true.

Examples

avgif (Expr, Predicate)

['github-push-event']
| summarize avgif( size, true) by bin_auto(_time)

Run in Playground

count()

Returns a count of the records per summarization group (or in total, if summarization is done without grouping).

Returns

Returns a count of the records per summarization group.

Examples

count ()

['sample-http-logs']
| summarize count()

Run in Playground

countif()

Returns a count of rows for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Predicate	string	Required	Expression that will be used for aggregation calculation. Predicate can be any scalar expression with return type of bool (evaluating to true/false).

Returns

Returns a count of rows for which Predicate evaluates to true.

Examples

countif (Predicate)

dcount()

Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.

Arguments

Name	Type	Required or Optional	Description
Expression	scalar	Required	A scalar expression whose distinct values are to be counted

Returns

Returns an estimate of the number of distinct values of Expr in the group.

Examples

dcount (Expr)

['sample-http-logs']
| summarize dcount( resp_body_size_bytes )

Run in Playground

dcountif()

Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Expression that will be used to filter rows.

Returns

Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true in the group.

Example

dcountif (Expr, Predicate)

max()

Returns the maximum value across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.

Returns

The maximum value of Expr across the group.

Examples

max (Expr)

['sample-http-logs']
| summarize max( resp_body_size_bytes)

Run in Playground

['sample-http-logs']
| summarize max( req_duration_ms) by bin_auto(_time)

Run in Playground

maxif()

Calculates the maximum value of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Expression that will be used to filter rows.

Returns

Returns the maximum value of Expr in records for which Predicate evaluates to true.

Examples

maxif (Expr,Predicate)

['github-push-event']
| summarize maxif(push_id, true) by bin_auto(_time)

Run in Playground

min()

Returns the minimum value across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.

Returns

The minimum value of Expr across the group.

Example

min (Expr)

['sample-http-logs']
| summarize min( resp_body_size_bytes)

Run in Playground

['sample-http-logs']
| summarize min( req_duration_ms) by bin_auto(_time)

Run in Playground

minif()

Returns the minimum of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Expression that will be used to filter rows.

Returns

The minimum value of Expr in records for which Predicate evaluates to true.

Examples

minif (Expr,Predicate)

['github-push-event']
| summarize minif(push_id, true) by bin_auto(_time)

Run in Playground

sum()

Calculates the sum of Expr across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.

Returns

The sum value of Expr across the group.

Examples

sum (Expr)

['sample-http-logs']
| summarize sum( resp_body_size_bytes)

Run in Playground

['sample-http-logs']
| summarize sum(resp_header_size_bytes) by bin_auto(_time)

Run in Playground

sumif()

Calculates the sum of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Expression that will be used to filter rows.

Returns

Returns the sum of Expr for which Predicate evaluates to true.

Examples

sumif (Expr,Predicate)

['github-push-event']
| summarize sumif(size, true)  by bin_auto(_time)

Run in Playground

histogram()

Returns a timeseries heatmap chart across the group

Arguments

• Expr: Expression that will be used for aggregation calculation.

Returns

Returns a timeseries heatmap chart across the group

Examples

histogram(Expr)

['sample-http-logs']
| summarize histogram(resp_header_size_bytes, 10) by bin_auto(_time)

Run in Playground

['sample-http-logs']
| summarize histogram(resp_header_size_bytes, 10) by bin_auto(_time), ['geo.country']

Run in Playground

topk()

calculates the top values of Expr across the group in a dataset

Arguments

• Expr: Expression that will be used for aggregation calculation.

Returns

• A separate result for each group plotted on a timeseries chart.

Examples

topk(Expr)

['sample-http-logs']
| summarize topk(method, 4) by bin_auto(_time)

Run in Playground

['sample-http-logs']
| summarize topk(method, 10) by bin_auto(_time), ['geo.city'], is_tls

Run in Playground

percentile(), percentiles_array()

Calculates the requested percentile of the group and produces a timeseries chart.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Percentile	double	Required	A constant that specifies the percentile.

Returns

A separate result for each group plotted on a horizontal bar chart, allowing for visual comparison across the groups.

Examples

percentile(Expr, percentile)

percentiles_array (Expr, Percentile1 [,Percentile2])

['sample-http-logs']
| summarize percentile(resp_header_size_bytes, 10) by bin_auto(_time)

Run in Playground

variance()

Calculates the variance of Expr across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.

Returns

The variance value of Expr across the group.

Examples

variance (Expr)

['sample-http-logs']
| summarize variance(resp_header_size_bytes) by bin_auto(_time)

Run in Playground

varienceif()

Calculates the variance of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Predicate that if true, the Expr calculated value will be added to the variance.

Returns

Returns the variance value of Expr in records for which Predicate evaluates to true.

Examples

varianceif (Expr, Predicate)

['github-push-event']
| summarize varianceif(size, true)  by bin_auto(_time)

Run in Playground

stdev()

Calculates the standard deviation of Expr across the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.

Returns

The standard deviation value of Expr across the group.

Examples

stdev (Expr)

['sample-http-logs']
| summarize stdev(resp_header_size_bytes) by bin_auto(_time)

Run in Playground

['sample-http-logs']
| summarize stdev( req_duration_ms) by bin_auto(_time), content_type

Run in Playground

stdevif()

Calculates the standard deviation of Expr in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculation.
Predicate	string	Required	Predicate that has to evaluate to true, in order for Expr to be added to the result.

Returns

Returns the standard deviation value of Expr in records for which Predicate evaluates to true.

Examples

stdevif (Expr,Predicate)

['github-push-event']
| summarize stdevif(size, true)  by bin_auto(_time)

Run in Playground

make_list()

Creates a dynamic JSON object (array) of all the values of Expression in the group.

Arguments

Name	Type	Required or Optional	Description
Expression	dynamic	Required	Expression that will be used for aggregation calculations.
MaxSize	integer	Optional	The limit on the maximum number of elements returned.

Returns

Returns a dynamic JSON array of all the values of Expr in the group.

Examples

make_list (Expr [, MaxSize])

['sample-http-logs'] 
| summarize dynamic_list =  make_list(status)

Run in Playground

make_list_if()

Creates a dynamic JSON object (array) of Expr values in the group for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculations.
Predicate	string	Required	Predicate that has to evaluate to true, in order for Expr to be added to the result.
MaxSize	integer	Optional	The limit on the maximum number of elements returned.

Returns

Returns a dynamic JSON object (array) of Expr vlaues in the group for which Predicate evaluates to true.

Examples

make_list_if (Expr, Predicate [, MaxSize])

['sample-http-logs']
| summarize dynamic_dated_list = make_list_if(resp_body_size_bytes, true)

Run in Playground

make_set()

Creates a dynamic JSON array of the set of distinct values that Expression takes in the group.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculations.
MaxSize	integer	Optional	The limit on the maximum number of elements returned.

Returns

Returns a dynamic JSON array of the set of distinct values that Expr takes in the group. The array’s sort order is undefined.

Example

make_set (Expr [, MaxSize])

['sample-http-logs']
| summarize make_set(resp_body_size_bytes)

Run in Playground

make_set_if()

Creates a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true.

Arguments

Name	Type	Required or Optional	Description
Expression	string	Required	Expression that will be used for aggregation calculations.
Predicate	string	Required	Predicate that has to evaluate to true, in order for Expr to be added to the result.
MaxSize	integer	Optional	The limit on the maximum number of elements returned.

Returns

Returns a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true.

Example

make_set_if (Expr, Predicate [, MaxSize])

['sample-http-logs']
| summarize data_metrics = make_set_if(resp_body_size_bytes, true)

Run in Playground

rate()

Calculates the rate of values in a group per second.

Arguments

Name	Type	Required or Optional	Description
Expression	The column can contain one of the following data types: integer, float, or timespan	Required	Expression that will be used for aggregation calculation.

Returns

Returns the rate of X per second, as a float.

X = Column with summable data (i.e. request duration, file size, $ spent, etc.)

Tip: If you want to see the rate of events, use Rate(1).

Examples

rate (Expr)

['http-logs']
| summarize rate(resp_body_size_bytes) by bin_auto(_time)

Run in Playground

Real-Time HTTP Request Rate per Second

['sample-http-logs'] 
| where _time >= ago(10s)
| summarize Rate=rate(1) by bin(_time, 1s)

Run in Playground

HTTP Request Duration Rate per Second Analysis

['sample-http-logs'] 
| where _time >= ago(10s)
| summarize Rate=rate(req_duration_ms) by bin(_time, 1s)

Run in Playground

Automatically Binned Analysis of Request Duration Rates Over Time

['sample-http-logs'] 
| where _time >= ago(10s)
| summarize Rate=rate(req_duration_ms) by bin_auto(_time)

Run in Playground