bin()

Rounds values down to an integer multiple of a given bin size.

Used frequently in combination with summarize by .... If you have a scattered set of values, they’ll be grouped into a smaller set of specific values.

Syntax

bin(value,roundTo)

Parameters

Returns

The nearest multiple of roundTo below value. Null values, a null bin size, or a negative bin size will result in null.

Examples

Numeric bin

print bin(4.5, 1)

Output

Timespan bin

print bin(time(16d), 7d)

Output

Datetime bin

print bin(datetime(1970-05-11 13:45:07), 1d)

Output

Pad a table with null bins

When there are rows for bins with no corresponding row in the table, we recommend to pad the table with those bins. The following query looks at strong wind storm events in California for a week in April. However, there are no events on some of the days.

let Start = datetime('2007-04-07');
let End = Start + 7d;
StormEvents
| where StartTime between (Start .. End)
| where State == "CALIFORNIA" and EventType == "Strong Wind"
| summarize PropertyDamage=sum(DamageProperty) by bin(StartTime, 1d)

Output

In order to represent the full week, the following query pads the result table with null values for the missing days. Here’s a step-by-step explanation of the process:

1. Use the union operator to add more rows to the table.
2. The range operator produces a table that has a single row and column.
3. The mv-expand operator over the range function creates as many rows as there are bins between StartTime and EndTime.
4. Use a PropertyDamage of 0.
5. The summarize operator groups together bins from the original table to the table produced by the union expression. This process ensures that the output has one row per bin whose value is either zero or the original count.

let Start = datetime('2007-04-07');
let End = Start + 7d;
StormEvents
| where StartTime between (Start .. End)
| where State == "CALIFORNIA" and EventType == "Strong Wind"
| union (
    range x from 1 to 1 step 1
    | mv-expand StartTime=range(Start, End, 1d) to typeof(datetime)
    | extend PropertyDamage=0
    )
| summarize PropertyDamage=sum(DamageProperty) by bin(StartTime, 1d)

Output