CODEWITHSUNDEEP
key-valuepercentageazure-data-explorerkql
rolledback
rolledback

Reputation: 175

Kusto query which calculates percentages of values by keys

I want to calculate percentages of values by keys. An example would be, given a table like:

datatable (key: string, value: string) 
[ 
    "a","1",
    "a","2",
    "b","x",
    "b","x",
    "b","x",
    "b","y",
]

I want to get results like:

[
    "a","1",.5,
    "a","2",.5,
    "b","x",.75,
    "b","y",.25,

]

I understand how to use as and toscalar to get percentages of values across all keys, but I can't figure out how to make that work by keys.

Upvotes: 1

Views: 357

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

We need to use join between aggregations in two levels

datatable (key: string, value: string) 
[ 
    "a","1",
    "a","2",
    "b","x",
    "b","x",
    "b","x",
    "b","y",
]
| summarize count() by key, value
| as summarize_by_key_value
| summarize sum(count_) by key
| join kind=inner summarize_by_key_value on key
| project key, value, percentage = 1.0 * count_ / sum_count_
key value percentage
a 1 0.5
a 2 0.5
b x 0.75
b y 0.25

Fiddle

Upvotes: 1

Related Questions