Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Switch services using the Version drop-down list. Learn more about navigation.
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
The top-hitters operator uses an approximation algorithm that's optimized for performance when the input data is large.
The approximation is based on the Count-Min-Sketch algorithm.
This operator is non-deterministic. Running it twice over the same data doesn't guarantee the same results.
Syntax
T | top-hitters NumberOfValues of ValueExpression [ by SummingExpression ]
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | tabular expression |
✔️ | The input tabular expression. |
| NumberOfValues | int, long, or real | ✔️ | The number of distinct values of ValueExpression. |
| ValueExpression | scalar |
✔️ | An expression over the input table T whose distinct values are returned. |
| SummingExpression | long or real |
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. |
Note
When you include SummingExpression in the syntax, the query approximates the value of:
T | summarize S = sum(SummingExpression) by ValueExpression | top NumberOfValues by S descWhen you don't include SummingExpression in the syntax, the query approximates the value of:
T | summarize C = count() by ValueExpression | top NumberOfValues by C desc
Examples
The examples in this section show how to use the syntax to help you get started.
The examples in this article use publicly available tables in the help cluster, such as the
StormEventstable in the Samples database.
Get top two events by totals
This example summarizes storm event data by calculating the total number of events for each event type. The query then selects the top two event types with the highest total number of events.
StormEvents
| summarize TotalEventId = sum(EventId) by EventType
| top 2 by TotalEventId desc
Output
| EventType | TotalEventId |
|---|---|
| Thunderstorm Wind | 562,509,013 |
| Hail | 474,690,007 |
Get most frequent items
This example shows how to find the top five types of storms.
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
This example shows how to find the states with the most Thunderstorm Wind events.
StormEvents
| where EventType == "Thunderstorm Wind"
| top-hitters 10 of State
Output
| State | approximate_sum_State |
|---|---|
| TEXAS | 830 |
| GEORGIA | 609 |
| MICHIGAN | 602 |
| IOWA | 585 |
| PENNSYLVANIA | 549 |
| ILLINOIS | 533 |
| NEW YORK | 502 |
| VIRGINIA | 482 |
| KANSAS | 476 |
| OHIO | 455 |
Get top hitters by summed value
This example finds the event types with the highest total property damage, using DamageProperty as the summing expression.
StormEvents
| top-hitters 5 of EventType by DamageProperty
Output
| EventType | approximate_sum_DamageProperty |
|---|---|
| Flood | 1,124,327,850 |
| Flash Flood | 626,659,030 |
| Tornado | 492,562,280 |
| Hail | 479,070,850 |
| Thunderstorm Wind | 221,037,650 |