Functions/Lists & arrays/list_reduce
Lists & arrays Scalar

list_reduce()

Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.

Arguments & returns

2 overloads

list_reduce(list, lambda(x,y))

Scalar
Overload link listlambda(x,y) Returns
#01 ANY[]LAMBDA ANY

list_reduce(list, lambda(x,y), initial_value)

Scalar
Overload link listlambda(x,y)initial_value Returns
#02 ANY[]LAMBDAANY ANY

Example

list_reduce example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

Used in larger rewrites 1

These examples use list_reduce as one part of a larger SQL translation.

Snowflakebitmap_construct_agg()

SELECT BITMAP_CONSTRUCT_AGG(v) FROM t
In DuckDB
SELECT (SELECT CASE WHEN l IS NULL OR LENGTH(l) = 0 THEN NULL WHEN LENGTH(l) <> LENGTH(LIST_FILTER(l, __v -> __v BETWEEN 0 AND 32767)) THEN NULL WHEN LENGTH(l) < 5 THEN UNHEX(PRINTF('%04X', LENGTH(l)) || h || REPEAT('00', GREATEST(0, 4 - LENGTH(l)) * 2)) ELSE UNHEX('08000000000000000000' || h) END FROM (SELECT l, COALESCE(LIST_REDUCE(LIST_TRANSFORM(l, __x -> PRINTF('%02X%02X', CAST(__x AS INT) & 255, (CAST(__x AS INT) >> 8) & 255)), (__a, __b) -> __a || __b, ''), '') AS h FROM (SELECT LIST_SORT(LIST_DISTINCT(LIST(v) FILTER(WHERE NOT v IS NULL))) AS l))) FROM t

SQLGlot example ↗