Skip to main content
Last updated on

MEASURES clause for MATCH_RECOGNIZE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19.0 and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Defines the output columns produced for each pattern match in MATCH_RECOGNIZE. Each measure is a named scalar expression computed from the rows that participated in that match.

Syntax​

MEASURES { expr AS measure } [, ...]

Parameters​

  • expr

    A scalar expression that does not include subqueries. You can also use the aggregate functions count(), sum(), avg(), min(), and max().

    Column references within expr can be wrapped by the following functions specific to the MEASURES clause:

    In ALL ROWS PER MATCH mode, navigation functions resolve relative to each output row within the match. Aggregate functions compute over the rows that participate in the match.

    A column reference can be qualified by a row pattern variable name (rowPatternVar.colName). This selects values from rows mapped to that variable in the current match. For example, LAST(down.tstamp) is the timestamp of the last row classified as down. Unqualified column references refer to the current output row in the match without selecting a specific pattern variable.

    If a row pattern variable is not yet matched, the qualified column reference evaluates to NULL.

    Window functions are not supported in MEASURES. If you use one, Databricks raises MATCH_RECOGNIZE_UNSUPPORTED_WINDOW_FUNCTION.

    DISTINCT is not supported in aggregate functions within MEASURES. If you use it, Databricks raises MATCH_RECOGNIZE_AGGREGATE_DISTINCT.

  • measure

    A name that identifies the measure in the result. The type matches the type of expr. Each MEASURES expression must use the AS keyword. If you omit an alias, Databricks raises MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED.

Common error conditions​

On this page