top-hitters operator

Article
09/30/2024

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

Returns an approximation for the most popular distinct values, or the values with the largest sum, in the input.

Note

top-hitters uses an approximation algorithm optimized for performance when the input data is large. The approximation is based on the Count-Min-Sketch algorithm.

Syntax

T | top-hitters NumberOfValues of ValueExpression [ by SummingExpression ]

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
T	`string`	✔️	The input tabular expression.
NumberOfValues	int, long, or real	✔️	The number of distinct values of ValueExpression.
ValueExpression	`string`	✔️	An expression over the input table T whose distinct values are returned.
SummingExpression	`string`		If specified, a numeric expression over the input table T whose sum per distinct value of ValueExpression establishes which values to emit. If not specified, the count of each distinct value of ValueExpression is used instead.

Remarks

The first syntax (no SummingExpression) is conceptually equivalent to:

T | summarize C``=``count() by ValueExpression | top NumberOfValues by C desc

The second syntax (with SummingExpression) is conceptually equivalent to:

T | summarize S``=``sum(*SummingExpression*) by ValueExpression | top NumberOfValues by S desc

Examples

Get most frequent items

The next example shows how to find top-5 types of storms.

Run the query

StormEvents
| top-hitters 5 of EventType

Output

EventType	approximate_count_EventType
Thunderstorm Wind	13015
Hail	12711
Flash Flood	3688
Drought	3616
Winter Weather	3349

Get top hitters based on column value

The next example shows how to find the States with the most "Thunderstorm Wind" events.