Window Functions

Relevant source files

The following files were used as context for generating this wiki page:

• src/query/window.rs
• src/backend/query_builder.rs
• src/expr.rs
• src/query/select.rs
• changelog/1.0.0-rc.33.md

Window functions in SeaQuery allow users to perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not cause rows to become grouped into a single output row; the rows retain their separate identities while the calculation is performed.

This feature is implemented through the WindowStatement and integrated into the SelectStatement builder, enabling complex analytical queries across supported backends like PostgreSQL, MySQL, and SQLite. Sources: src/query/window.rs, changelog/1.0.0-rc.33.md:68-87

Architecture and Components

The implementation of window functions revolves around defining a window frame, partitioning logic, and ordering. These definitions can be applied directly to a selection expression or defined as a named window within a query.

Window Statement Structure

The WindowStatement is the primary data structure used to define the window's behavior. It consists of three main components:

• Partition By: Dividies the result set into partitions to which the window function is applied.
• Order By: Defines the logical order of rows within each partition.
• Frame: Specifies a subset of rows within the partition (the "window frame") relative to the current row.

Application in Queries

SeaQuery provides two primary ways to utilize window functions within a SelectStatement:

1. Inline Over Clauses

Users can attach a window definition directly to an expression using the .over() method provided by SelectExprTrait. This generates an OVER (...) clause in the resulting SQL.

Query::select()
    .from(Char::Table)
    .expr(
        Expr::col(Char::Character)
            .max()
            .over(WindowStatement::partition_by(Char::FontSize))
            .alias("max_char"),
    );

Sources: changelog/1.0.0-rc.33.md:76-83, src/backend/query_builder.rs:475-492

2. Named Windows

For reusability, a window can be defined once using the .window() method on a SelectStatement and then referenced by name in multiple expressions.

Query::select()
    .from(Char::Table)
    .expr(Expr::col(Char::Character).max().over("w"))
    .window("w", WindowStatement::partition_by(Char::FontSize));

Sources: changelog/1.0.0-rc.33.md:85-88, src/backend/query_builder.rs:166-172

Data Flow: From AST to SQL

The QueryBuilder trait defines the logic for translating the window AST into string representations. The prepare_window_statement function iterates through partitions, orders, and frame definitions to construct the SQL clause.

Frame Bounds and Types

The window frame specifies the extent of the window. SeaQuery supports both ROWS and RANGE types.

Frame Bound	SQL Translation	Description
UnboundedPreceding	UNBOUNDED PRECEDING	Starts at the first row of the partition.
Preceding(v)	VALUE PRECEDING	Starts/ends N rows/units before current row.
CurrentRow	CURRENT ROW	Bound is the current row.
Following(v)	VALUE FOLLOWING	Starts/ends N rows/units after current row.
UnboundedFollowing	UNBOUNDED FOLLOWING	Ends at the last row of the partition.

Sources: src/backend/query_builder.rs:823-840, src/query/window.rs:85-103

Integration with Backends

The prepare_select_expr method in the QueryBuilder ensures that the window components are correctly rendered for the specific SQL dialect.

• PostgreSQL/MySQL: Fully support PARTITION BY, ORDER BY, and detailed FRAME specifications including BETWEEN.
• Named Windows: Handled via the WINDOW clause at the end of the SELECT statement before limits or locks.

Sources: src/backend/query_builder.rs:166-172, src/backend/query_builder.rs:475-492

Summary

Window Functions in SeaQuery provide a robust API for complex analytical operations. By leveraging the WindowStatement and SelectExprTrait, developers can construct queries that perform row-relative calculations while maintaining the original row structure. The system supports both inline definitions for simplicity and named windows for complex, multi-aggregation queries. Sources: src/query/window.rs, src/backend/query_builder.rs:842-887