Edit

OVER (Azure Stream Analytics)

  • Article

For each row, the OVER clause determines the grouping of rows before an associated aggregate or analytic function is applied.

You can use the OVER clause anywhere a scalar function is allowed. For example, you can use the OVER clause in the SELECT, WHERE, JOIN or GROUP BY clauses.

The OVER clause groups rows directly from the query input. It is not affected by predicates in the WHERE clause, join conditions in the JOIN clause, or grouping conditions in the GROUP BY clause.

Currently, the following aggregate functions are supported with the OVER clause:

  • SUM
  • AVG
  • MIN
  • MAX

Syntax

OVER ([<PARTITION BY clause>] <LIMIT DURATION clause> [<WHEN clause>])

<PARTITION BY clause> ::== PARTITION BY partition_key, ... [n]

<LIMIT DURATION clause> ::== LIMIT DURATION (unit, length)

<WHEN clause> ::== WHEN boolean_expression

Arguments

<PARTITION BY clause>

Specifies that only the rows with the same <partition_key> will be considered for the aggregate or analytic function.

<LIMIT DURATION clause>

Specifies how much history from the current row is included in the group. See DATEDIFF for a detailed description of supported units and their abbreviations.

<WHEN CLAUSE>

Specifies the boolean condition for the rows to be included in the group.

General Remarks

Applying an aggregate or analytic function with OVER on the result set of a windowing function may produce unexpected results. Windowing functions alter the timestamp of events, as every window operation outputs event at the end of the window. The current timestamp of an event can be accessed with system.timestamp(), after a window operation it will differ from the original event time attribute.

Examples

Compute the average temperature over the last 5 minutes, per sensor:

SELECT AVG(temperature) OVER (PARTITION BY id LIMIT DURATION (minute, 5))
FROM input

Throw an alert if resource has been unhealthy over the last hour:

SELECT 1 AS alert
FROM input
WHERE MAX(healthy) OVER (PARTITION BY id LIMIT DURATION (hour, 1)) = 0

See Also

Analytic Functions
Aggregate Functions