make_set() (aggregation function)
Creates a dynamic
array of the set of distinct values that expr takes in the group.
Null values are ignored and don't factor into the calculation.
Note
This function is used in conjunction with the summarize operator.
Deprecated aliases: makeset()
Syntax
make_set(
expr [,
maxSize])
Learn more about syntax conventions.
Parameters
Name | Type | Required | Description |
---|---|---|---|
expr | string |
✔️ | The expression used for the aggregation calculation. |
maxSize | int |
The maximum number of elements returned. The default and max value is 1048576. |
Note
The deprecated version has a default maxSize limit of 128.
Returns
Returns a dynamic
array of the set of distinct values that expr takes in the group.
The array's sort order is undefined.
Tip
To only count distinct values, use dcount() or count_distinct().
Example
Set from a scalar column
The following example shows the set of states grouped with the same amount of crop damage.
StormEvents
| summarize states=make_set(State) by DamageCrops
The results table shown includes only the first 10 rows.
DamageCrops | states |
---|---|
0 | ["NORTH CAROLINA","WISCONSIN","NEW YORK","ALASKA","DELAWARE","OKLAHOMA","INDIANA","ILLINOIS","MINNESOTA","SOUTH DAKOTA","TEXAS","UTAH","COLORADO","VERMONT","NEW JERSEY","VIRGINIA","CALIFORNIA","PENNSYLVANIA","MONTANA","WASHINGTON","OREGON","HAWAII","IDAHO","PUERTO RICO","MICHIGAN","FLORIDA","WYOMING","GULF OF MEXICO","NEVADA","LOUISIANA","TENNESSEE","KENTUCKY","MISSISSIPPI","ALABAMA","GEORGIA","SOUTH CAROLINA","OHIO","NEW MEXICO","ATLANTIC SOUTH","NEW HAMPSHIRE","ATLANTIC NORTH","NORTH DAKOTA","IOWA","NEBRASKA","WEST VIRGINIA","MARYLAND","KANSAS","MISSOURI","ARKANSAS","ARIZONA","MASSACHUSETTS","MAINE","CONNECTICUT","GUAM","HAWAII WATERS","AMERICAN SAMOA","LAKE HURON","DISTRICT OF COLUMBIA","RHODE ISLAND","LAKE MICHIGAN","LAKE SUPERIOR","LAKE ST CLAIR","LAKE ERIE","LAKE ONTARIO","E PACIFIC","GULF OF ALASKA"] |
30000 | ["TEXAS","NEBRASKA","IOWA","MINNESOTA","WISCONSIN"] |
4000000 | ["CALIFORNIA","KENTUCKY","NORTH DAKOTA","WISCONSIN","VIRGINIA"] |
3000000 | ["CALIFORNIA","ILLINOIS","MISSOURI","SOUTH CAROLINA","NORTH CAROLINA","MISSISSIPPI","NORTH DAKOTA","OHIO"] |
14000000 | ["CALIFORNIA","NORTH DAKOTA"] |
400000 | ["CALIFORNIA","MISSOURI","MISSISSIPPI","NEBRASKA","WISCONSIN","NORTH DAKOTA"] |
50000 | ["CALIFORNIA","GEORGIA","NEBRASKA","TEXAS","WEST VIRGINIA","KANSAS","MISSOURI","MISSISSIPPI","NEW MEXICO","IOWA","NORTH DAKOTA","OHIO","WISCONSIN","ILLINOIS","MINNESOTA","KENTUCKY"] |
18000 | ["WASHINGTON","WISCONSIN"] |
107900000 | ["CALIFORNIA"] |
28900000 | ["CALIFORNIA"] |
Set from array column
The following example shows the set of elements in an array.
datatable (Val: int, Arr1: dynamic)
[
1, dynamic(['A1', 'A2', 'A3']),
5, dynamic(['A2', 'C1']),
7, dynamic(['C2', 'A3']),
5, dynamic(['C2', 'A1'])
]
| summarize Val_set=make_set(Val), Arr1_set=make_set(Arr1)
Val_set | Arr1_set |
---|---|
[1,5,7] | ["A1","A2","A3","C1","C2"] |
Related content
- Use
mv-expand
operator for the opposite function. make_set_if
operator is similar tomake_set
, except it also accepts a predicate.
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om