Window functions

Window functions compute a value per row using a “window” of related rows. Unlike aggregates, they do not collapse rows; the input row count is preserved.

All window functions in SQE come from datafusion-functions-window (DataFusion’s built-in window crate). No SQE-specific window functions exist; the SQL surface matches DataFusion exactly.

Syntax

window_function(args) OVER (
    [PARTITION BY col1, col2, ...]
    [ORDER BY col1 [ASC|DESC] [NULLS FIRST|LAST], ...]
    [frame_clause]
)

The frame clause has three forms:

ROWS BETWEEN <start> AND <end>
RANGE BETWEEN <start> AND <end>
GROUPS BETWEEN <start> AND <end>

Bounds:

UNBOUNDED PRECEDING
N PRECEDING
CURRENT ROW
N FOLLOWING
UNBOUNDED FOLLOWING

Default frame:

• With ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Without ORDER BY: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Functions

Ranking

Offset

Aggregates as windows

Every aggregate function from Aggregate functions also works as a window function:

SELECT
    customer_id,
    order_date,
    amount,
    sum(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    avg(amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;

Frame examples

Running total

SELECT
    order_date,
    amount,
    sum(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Trailing 7-day average

SELECT
    order_date,
    amount,
    avg(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) AS trailing_7d_avg
FROM orders;

RANGE with an INTERVAL works on date / timestamp ordering keys and respects time gaps. ROWS would just count rows regardless of time.

Top N per group via row_number

WITH ranked AS (
    SELECT
        category,
        product,
        revenue,
        row_number() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 5;

Rolling difference with LAG

SELECT
    order_date,
    amount,
    amount - lag(amount, 1, 0) OVER (ORDER BY order_date) AS day_over_day
FROM orders;

The , 0 argument fills the first row (where there is no predecessor) with zero instead of NULL.

Frame variants compared

GROUPS is rare but useful when ordering on a low-cardinality key produces many ties and you want “the previous distinct value group” semantics.

What is NOT supported (DataFusion blocked)

• QUALIFY clause (filtering on window-function output without a subquery). DataFusion’s parser does not accept QUALIFY. Workaround: wrap the SELECT and filter in an outer query, as in the “Top N per group” example above.

The audit row lives in features.md. Tracked upstream as a parser enhancement.

Performance notes

• PARTITION BY enables parallelism: each partition runs on its own thread / worker. Without partitioning, the window runs single-threaded against the global ordering.
• ROWS frames are cheaper than RANGE frames when the ordering key has many ties; RANGE may need a binary search per row.
• A unbounded preceding ... unbounded following frame on a sorted input lets DataFusion stream-compute aggregates without materialising the partition. Other frames require partition-buffering.

The EXPLAIN ANALYZE output shows partition counts and frame mode per WindowAgg node; use it when a window query is slower than expected.