REDUCE

​

Syntax

​

Scalar

reduce(array, initial, merge [, finish])

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.
• S is the initial running value type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.
• U is the result type of the lambda or finish expression: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.

​

Returns

• Type: S without finish; U with finish
• Possible values: The final running value or finish result.
• NULL behavior: NULL array returns NULL. NULL elements and a NULL initial state are passed to lambdas; the lambda determines resulting NULL behavior.
• Empty input: For an empty array, returns initial or finish(initial).
• Shape: The initial value’s type or finish return type determines result shape.
• Ordering: Merge processes elements from first to last.

​

Properties

​

Usage notes

• An empty array yields the initial state, or finish(initial) when finish is supplied.
• The merge expression must return exactly the same type as the initial value. A different type raises an error.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.

​

Examples

​

Typical behavior

SELECT reduce(ARRAY[1, 2, 3], 0, (acc, x) -> acc + x) AS "value";

​

Apply a final transformation

SELECT reduce(ARRAY[1, 2, 3], 0, (acc, x) -> acc + x, acc -> acc * 10) AS result;