Function reduce() iterates a designated calculation against each elements in an array in a one-by-one manner. Developers need to define the calculation via an expression and give it an initial value, firstly the calculation is executed based on this initial value together with the first element in the array, then the calculation is re-executed based on the previous calculation result and the 2nd element in the array, ... until all elements in the array are traversed.

The expression of the calculation employed in this function usually involves both the calculation result and the element of array, the iteration pattern is similar to the 'for' in most programming languages, just that the index of each iteration is not exposed to developers.

Syntax：

reduce(<result> = <initial_value>, <element> in <array> | <expression>) 

• <result> denotes the variable name of the calculation result
• <initial_value> denotes the initial value of the calculation result
• <element> denotes the variable name of element in the array
• <array> denotes the alias of the array
• <expression> denotes the calculation

If the multiple columns involved in the calculation expression are non-homologous, Cartesian Product will be applied when the function is called in a WITH clause, or column length will be trimmed when called in a RETURN clause.

Common Usage

Example: Calculate the sum of all numbers in array [1,2,3,4]

with [1,2,3] as array
return reduce(sum = 0, element in array | sum + element) as mySum