Window Functions

Window functions compute a value for each row based on a "window" of related rows — without collapsing the result into fewer rows like GROUP BY does. They are the right tool for running totals, rankings, moving averages, and any calculation that needs to see neighboring rows.

FeatureQL window functions use the same OVER() clause as SQL. The key difference: in FeatureQL, the window function and its OVER() specification are part of a feature definition, so you can compose them with other features.

Syntax

<feature> := <aggregate>(<expression>) OVER (
    [PARTITION BY <key>, ...]
    [ORDER BY <expression> [ASC|DESC], ...]
)

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing). Omit it to treat all rows as one partition.
ORDER BY defines the row ordering within each partition. For cumulative functions like SUM(), this turns them into running totals.

SUM() OVER — running totals and partition sums

The following example demonstrates several SUM() OVER(...) variants on the same dataset: a global sum, partition sums, running totals ordered by ID or value, and combinations of partitioning with ordering. Each column shows a different window specification so you can compare them side by side.

FeatureQL

SELECT
    id as C_ID,
    category as C_CATEGORY,
    value as C_VALUE,
    -- Basic SUM variants
    SUM(value) OVER ( )::BIGINT AS sum_all,
    SUM(value) OVER (PARTITION BY category)::BIGINT AS sum_by_category,
    SUM(value) OVER (ORDER BY id)::BIGINT AS sum_running_by_id,
    SUM(value) OVER (ORDER BY value, id)::BIGINT AS sum_running_by_value,
    SUM(value) OVER (PARTITION BY category ORDER BY id)::BIGINT AS sum_running_cat_by_id,
    SUM(value) OVER (PARTITION BY category ORDER BY value, id)::BIGINT AS sum_running_cat_by_value,
    -- SUM with different columns
    SUM(score) OVER (PARTITION BY category) AS sum_score_by_category,
    SUM(id) OVER (ORDER BY value DESC, id)::BIGINT AS sum_id_desc_value,
    -- SUM with descending order
    SUM(value) OVER (ORDER BY value DESC, id)::BIGINT AS sum_running_desc,
    SUM(value) OVER (PARTITION BY category ORDER BY value DESC, id)::BIGINT AS sum_running_cat_desc,
    -- SUM with multiple order by columns
    SUM(value) OVER (ORDER BY category, value, id)::BIGINT AS sum_running_cat_val,
    SUM(value) OVER (PARTITION BY flag ORDER BY value, id)::BIGINT AS sum_by_flag_ordered
FROM FM.TUTORIALS.WF
FOR
    ID := BIND_VALUES(SEQUENCE(1,10))
ORDER BY id;

Result

C_ID BIGINT	C_CATEGORY VARCHAR	C_VALUE BIGINT	SUM_ALL BIGINT	SUM_BY_CATEGORY BIGINT	SUM_RUNNING_BY_ID BIGINT	SUM_RUNNING_BY_VALUE BIGINT	SUM_RUNNING_CAT_BY_ID BIGINT	SUM_RUNNING_CAT_BY_VALUE BIGINT	SUM_SCORE_BY_CATEGORY VARCHAR	SUM_ID_DESC_VALUE BIGINT	SUM_RUNNING_DESC BIGINT	SUM_RUNNING_CAT_DESC BIGINT	SUM_RUNNING_CAT_VAL BIGINT	SUM_BY_FLAG_ORDERED BIGINT
1	A	10	200	50	10	15	10	10	5.5	48	195	50	10	10
2	A	20	200	50	30	65	30	30	5.5	32	135	20	30	25
3	A	20	200	50	50	85	50	50	5.5	35	155	40	50	60
4	B	15	200	70	65	30	15	15	12.0	39	170	70	65	25
5	B	25	200	70	90	110	40	40	12.0	21	90	55	90	50
6	B	30	200	70	120	165	70	70	12.0	16	65	30	120	115
7	C	5	200	80	125	5	5	5	11.5	55	200	80	125	5
8	C	15	200	80	140	45	20	20	11.5	47	185	75	140	40
9	C	25	200	80	165	135	45	45	11.5	30	115	60	165	85
10	C	35	200	80	200	200	80	80	11.5	10	35	35	200	85

Compare the columns: OVER () gives the same grand total everywhere, PARTITION BY category gives per-category totals, and adding ORDER BY turns the sum into a running total that grows row by row. Combining PARTITION BY with ORDER BY gives a running total that resets at each category boundary.

ARRAY_AGG() OVER — accumulating values

ARRAY_AGG() as a window function collects values into an array as it moves through the window. This is useful for seeing which values have been encountered so far, or for building up context for each row.

FeatureQL

SELECT
    id as C_ID,
    category as C_CATEGORY,
    value as C_VALUE,
    -- Basic ARRAY_AGG variants
    ARRAY_AGG(name) OVER ( ) AS array_all,
    ARRAY_AGG(name) OVER (PARTITION BY category) AS array_by_category,
    ARRAY_AGG(name) OVER (ORDER BY id) AS array_running_by_id,
    ARRAY_AGG(name) OVER (ORDER BY value, id) AS array_running_by_value,
    ARRAY_AGG(name) OVER (PARTITION BY category ORDER BY id) AS array_running_cat_by_id,
    ARRAY_AGG(name) OVER (PARTITION BY category ORDER BY value, id) AS array_running_cat_by_value,
    -- ARRAY_AGG with different columns
    ARRAY_AGG(value) OVER (PARTITION BY category) AS array_values_by_category,
    ARRAY_AGG(id) OVER (ORDER BY value DESC, id) AS array_ids_desc_value,
    -- ARRAY_AGG with descending order
    ARRAY_AGG(name) OVER (ORDER BY value DESC, id) AS array_running_desc,
    ARRAY_AGG(name) OVER (PARTITION BY category ORDER BY value DESC, id) AS array_running_cat_desc,
    -- ARRAY_AGG with multiple order by columns
    ARRAY_AGG(name) OVER (ORDER BY category, value, id) AS array_running_cat_val,
    ARRAY_AGG(name) OVER (PARTITION BY flag ORDER BY value, id) AS array_by_flag_ordered
FROM FM.TUTORIALS.WF
FOR
    ID := BIND_VALUES(SEQUENCE(1,10))
ORDER BY id;

Result

C_ID BIGINT	C_CATEGORY VARCHAR	C_VALUE BIGINT	ARRAY_ALL ARRAY	ARRAY_BY_CATEGORY ARRAY	ARRAY_RUNNING_BY_ID ARRAY	ARRAY_RUNNING_BY_VALUE ARRAY	ARRAY_RUNNING_CAT_BY_ID ARRAY	ARRAY_RUNNING_CAT_BY_VALUE ARRAY	ARRAY_VALUES_BY_CATEGORY ARRAY	ARRAY_IDS_DESC_VALUE ARRAY	ARRAY_RUNNING_DESC ARRAY	ARRAY_RUNNING_CAT_DESC ARRAY	ARRAY_RUNNING_CAT_VAL ARRAY	ARRAY_BY_FLAG_ORDERED ARRAY
1	A	10	[g, c, b, f, y, z, a, e, x, d]	[x, y, z]	[x]	[d, x]	[x]	[x]	[10, 20, 20]	[10, 6, 5, 9, 2, 3, 4, 8, 1]	[g, c, b, f, y, z, a, e, x]	[y, z, x]	[x]	[x]
2	A	20	[g, c, b, f, y, z, a, e, x, d]	[x, y, z]	[x, y]	[d, x, a, e, y]	[x, y]	[x, y]	[10, 20, 20]	[10, 6, 5, 9, 2]	[g, c, b, f, y]	[y]	[x, y]	[d, y]
3	A	20	[g, c, b, f, y, z, a, e, x, d]	[x, y, z]	[x, y, z]	[d, x, a, e, y, z]	[x, y, z]	[x, y, z]	[10, 20, 20]	[10, 6, 5, 9, 2, 3]	[g, c, b, f, y, z]	[y, z]	[x, y, z]	[x, a, e, z]
4	B	15	[g, c, b, f, y, z, a, e, x, d]	[a, b, c]	[x, y, z, a]	[d, x, a]	[a]	[a]	[15, 25, 30]	[10, 6, 5, 9, 2, 3, 4]	[g, c, b, f, y, z, a]	[c, b, a]	[x, y, z, a]	[x, a]
5	B	25	[g, c, b, f, y, z, a, e, x, d]	[a, b, c]	[x, y, z, a, b]	[d, x, a, e, y, z, b]	[a, b]	[a, b]	[15, 25, 30]	[10, 6, 5]	[g, c, b]	[c, b]	[x, y, z, a, b]	[d, y, b]
6	B	30	[g, c, b, f, y, z, a, e, x, d]	[a, b, c]	[x, y, z, a, b, c]	[d, x, a, e, y, z, b, f, c]	[a, b, c]	[a, b, c]	[15, 25, 30]	[10, 6]	[g, c]	[c]	[x, y, z, a, b, c]	[x, a, e, z, f, c]
7	C	5	[g, c, b, f, y, z, a, e, x, d]	[d, e, f, g]	[x, y, z, a, b, c, d]	[d]	[d]	[d]	[5, 15, 25, 35]	[10, 6, 5, 9, 2, 3, 4, 8, 1, 7]	[g, c, b, f, y, z, a, e, x, d]	[g, f, e, d]	[x, y, z, a, b, c, d]	[d]
8	C	15	[g, c, b, f, y, z, a, e, x, d]	[d, e, f, g]	[x, y, z, a, b, c, d, e]	[d, x, a, e]	[d, e]	[d, e]	[5, 15, 25, 35]	[10, 6, 5, 9, 2, 3, 4, 8]	[g, c, b, f, y, z, a, e]	[g, f, e]	[x, y, z, a, b, c, d, e]	[x, a, e]
9	C	25	[g, c, b, f, y, z, a, e, x, d]	[d, e, f, g]	[x, y, z, a, b, c, d, e, f]	[d, x, a, e, y, z, b, f]	[d, e, f]	[d, e, f]	[5, 15, 25, 35]	[10, 6, 5, 9]	[g, c, b, f]	[g, f]	[x, y, z, a, b, c, d, e, f]	[x, a, e, z, f]
10	C	35	[g, c, b, f, y, z, a, e, x, d]	[d, e, f, g]	[x, y, z, a, b, c, d, e, f, g]	[d, x, a, e, y, z, b, f, c, g]	[d, e, f, g]	[d, e, f, g]	[5, 15, 25, 35]	[10]	[g]	[g]	[x, y, z, a, b, c, d, e, f, g]	[d, y, b, g]

The same partitioning and ordering patterns apply: ARRAY_AGG(name) OVER (ORDER BY id) builds a growing array as rows are processed, while PARTITION BY category resets the accumulation at each category boundary.

Available window functions

All standard aggregate functions (SUM(), AVG(), MIN(), MAX(), COUNT(), ARRAY_AGG(), etc.) work as window functions when combined with OVER(). FeatureQL also supports dedicated window functions:

Function	What it does
`ROW_NUMBER()`	Sequential number within the partition
`RANK()`	Rank with gaps for ties
`DENSE_RANK()`	Rank without gaps for ties
`NTILE(n)`	Distributes rows into n roughly equal buckets
`LEAD(expr, offset)`	Value from a row offset positions ahead
`LAG(expr, offset)`	Value from a row offset positions behind
`FIRST_VALUE(expr)`	First value in the window frame
`LAST_VALUE(expr)`	Last value in the window frame

For full signatures and parameters, see the Window function reference .

Suggest changes to this page

Last update at: 2026/03/03 16:47:38

On this page

Syntax SUM() OVER — running totals and partition sums ARRAY_AGG() OVER — accumulating values Available window functions