Window Function Extensions

New Support for defining window functions in extensions was introduced in DuckDB 2.0. The API is part of the C++ extension API and may still change.

Every aggregate function can be used as a window function, but not every windowed operation is an aggregate. For example, fill interpolates missing values, a reduce(init, lambda) function could apply running updates without a frame, and a rank(value) function could compute the “hypothetical rank” of a value. Since DuckDB 2.0, window functions are catalog entries, so extensions can define new ones using the same API that the built-in window functions use.

Registering Window Functions

To define a window function, derive from the WindowFunction class and register it in the extension's load function using the ExtensionLoader:

DuckWeedFunction duckweed;
loader.RegisterFunction(duckweed);

Registered window functions are listed by duckdb_functions() with the function type window and can be looked up with the CLI's .manual dot command.

Binding

The WindowFunction constructor takes the function's name, argument types, return type, and callbacks. The optional bind callback receives the arguments along with the types of the window's ORDER BY and the argument ORDER BY clauses, so it can resolve the return type and add bind data. The following methods declare which modifiers the function supports:

DuckDB 2.1 adds the SetIsRanking method (default: false), which marks the function as a ranking function such as rank or row_number. The planner uses it to exclude ranking functions from optimizations for degenerate frames, i.e., frames that only contain the current row. This property is currently only available on the main branch of the duckdb/duckdb repository and is not part of the DuckDB 2.0.0 release.

Blocking Interface

Window functions are typically evaluated in a blocking fashion: the window operator first partitions and sorts the input into hash groups, then evaluates the function on each hash group. A blocking window function implements the following callbacks:

Streaming Interface

Some window functions can also be streamed, i.e., evaluated one chunk at a time without materializing the input. DuckDB only considers streaming if the window has no PARTITION BY, no ORDER BY, and no EXCLUDE clause. For aggregates, this covers “running totals” (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). A window function opts in by implementing the following callbacks:

Example

The loadable extension demo defines duckweed, a “fill down” window function that replaces NULL values with the last non-NULL value and implements both the blocking and the streaming interface. With the demo extension loaded, the following query uses the streaming interface:

SELECT i, m, duckweed(m) OVER () AS d
FROM (
    SELECT i, if(i % 5, i, NULL) AS m
    FROM range(8) t(i)
);

Adding PARTITION BY or ORDER BY to the OVER clause switches to the blocking interface.

For more details, see Richard Wesley's talk “Window Extensions: Defining New Window Functions”.