reduce operator

Groups a set of strings together based on value similarity.

For each such group, the operator returns a pattern, count, and representative. The pattern best describes the group, in which the * character represents a wildcard. The count is the number of values in the group, and the representative is one of the original values in the group.

Syntax

T | reduce [kind = ReduceKind] by Expr [with [threshold = Threshold] [, characters = Characters]]

Parameters

Returns

A table with as many rows as there are groups and columns titled pattern, count, and representative. The pattern best describes the group, in which the * character represents a wildcard, or placeholder for an arbitrary insertion string. The count is the number of values in the group, and the representative is one of the original values in the group.

For example, the result of reduce by city might include:

Examples

The example in this section shows how to use the syntax to help you get started.

Small threshold value

This query generates a range of numbers, creates a new column with concatenated strings and random integers, and then groups the rows by the new column with specific reduction parameters.

range x from 1 to 1000 step 1
| project MyText = strcat("MachineLearningX", tostring(toint(rand(10))))
| reduce by MyText  with threshold=0.001 , characters = "X" 

Output

Large threshold value

This query generates a range of numbers, creates a new column with concatenated strings and random integers, and then groups the rows by the new column with specific reduction parameters.

range x from 1 to 1000 step 1
| project MyText = strcat("MachineLearningX", tostring(toint(rand(10))))
| reduce by MyText  with threshold=0.9 , characters = "X" 

Output

The result includes only those groups where the MyText value appears in at least 90% of the rows.

Behavior of Characters parameter

If the Characters parameter is unspecified, then every non-ascii numeric character becomes a term separator.

range x from 1 to 10 step 1 | project str = strcat("foo", "Z", tostring(x)) | reduce by str

Output

However, if you specify that “Z” is a separator, then it’s as if each value in str is two terms: foo and tostring(x):

range x from 1 to 10 step 1 | project str = strcat("foo", "Z", tostring(x)) | reduce by str with characters="Z"

Output

Apply reduce to sanitized input

The following example shows how one might apply the reduce operator to a “sanitized” input, in which GUIDs in the column being reduced are replaced before reducing:

Start with a few records from the Trace table. Then reduce the Text column which includes random GUIDs. As random GUIDs interfere with the reduce operation, replace them all by the string “GUID”. Now perform the reduce operation. In case there are other “quasi-random” identifiers with embedded ‘-’ or ‘_’ characters in them, treat characters as non-term-breakers.

Trace
| take 10000
| extend Text = replace(@"[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}", "GUID", Text)
| reduce by Text with characters="-_"

Related content

• autocluster