Skip to content

Latest commit

 

History

History
151 lines (114 loc) · 5.67 KB

bin-function.md

File metadata and controls

151 lines (114 loc) · 5.67 KB
title description ms.reviewer ms.topic ms.date adobe-target
bin()
Learn how to use the bin() function to round values down to an integer multiple of a given bin size.
alexans
reference
08/11/2024
true

bin()

[!INCLUDE applies] [!INCLUDE fabric] [!INCLUDE azure-data-explorer] [!INCLUDE monitor] [!INCLUDE 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)

[!INCLUDE syntax-conventions-note]

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

The following example demonstrates the bin() function with numeric data. :::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

print bin(4.5, 1)

Output

print_0
4

The following example demonstrates the bin() function with timespan data.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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

Output

print_0
14:00:00:00

The following example demonstrates the bin() function with datetime data.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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

Output

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

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.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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