ARRAY_AGG() OVER ...
All functions > WINDOW FUNCTION > ARRAY_AGG() OVER ...
Returns an array of all values in the window frame.
Syntax
ARRAY_AGG([DISTINCT] expr) [FILTER (WHERE condition) | WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])
Notes
- Collects all values from the window frame into an array
- Optional DISTINCT collects unique values per frame position
- ORDER BY determines the order of elements in the array
- NULL values are included in the array
- Returns ARRAY type with element type matching input
See also
Examples
FeatureQL
SELECT
-- Growing list of values in the frame (v permuted so inner arrays are not sorted)
f1 := ZIP(ARRAY(1, 2, 3, 4) AS id, ARRAY(30, 10, 20, 5) AS v).TRANSFORM(
SELECT ARRAY_AGG(v) OVER (ORDER BY id ASC)
).UNWRAP(),
-- Growing list counting only rows where v > 15 (10 and 5 excluded from the frame)
f2 := ZIP(ARRAY(1, 2, 3, 4) AS id, ARRAY(30, 10, 20, 5) AS v).TRANSFORM(
SELECT ARRAY_AGG(v) FILTER (WHERE v > 15) OVER (ORDER BY id ASC)
).UNWRAP(),
-- Growing distinct values in the frame (pairs with COUNT(DISTINCT … OVER …))
f3 := ZIP(ARRAY(1, 2, 3, 4) AS id, ARRAY(30, 10, 20, 5) AS v).TRANSFORM(
SELECT ARRAY_AGG(DISTINCT v) OVER (ORDER BY id ASC)
).UNWRAP(),
-- Growing distinct values counting only rows where v > 15 (10 and 5 excluded from the frame)
f4 := ZIP(ARRAY(1, 2, 3, 4) AS id, ARRAY(30, 10, 20, 5) AS v).TRANSFORM(
SELECT ARRAY_AGG(DISTINCT v) FILTER (WHERE v > 15) OVER (ORDER BY id ASC)
).UNWRAP()
;Result
| f1 ARRAY | f2 ARRAY | f3 ARRAY | f4 ARRAY |
|---|---|---|---|
| [[30], [30, 10], [30, 10, 20], [30, 10, 20, 5]] | [[30], [30], [30, 20], [30, 20]] | [[30], [30, 10], [30, 10, 20], [30, 10, 20, 5]] | [[30], [30], [30, 20], [30, 20]] |