Muokkaa

Jaa

bin()

  • Artikkeli

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

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.

The bin() and floor() functions are equivalent

Syntax

bin(value,roundTo)

Learn more about syntax conventions.

Parameters

Name Type Required Description
value int, long, real, timespan, or datetime ✔️ The value to round down.
roundTo int, long, real, or timespan ✔️ The "bin size" that divides value.

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

print_0
4

Timespan bin

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

Output

print_0
14:00:00:00

Datetime bin

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

Output

print_0
1970-05-11T00:00:00Z

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

StartTime PropertyDamage
2007-04-08T00:00:00Z 3000
2007-04-11T00:00:00Z 1000
2007-04-12T00:00:00Z 105000

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

StartTime PropertyDamage
2007-04-07T00:00:00Z 0
2007-04-08T00:00:00Z 3000
2007-04-09T00:00:00Z 0
2007-04-10T00:00:00Z 0
2007-04-11T00:00:00Z 1000
2007-04-12T00:00:00Z 105000
2007-04-13T00:00:00Z 0
2007-04-14T00:00:00Z 0