Creates a new window function expression.

Parameters:

• function: The window or aggregate function to apply
• orderBy: How to sort rows within each partition (required, must not be empty)
• partitionBy: Optional expressions to partition rows by
• boundary: Optional specification of which rows to include in calculations

Throws ArgumentError if orderBy is empty.

Note that Window Function is only available from sqlite 3.25.0, released on 2018-09-15. Most devices will use an older sqlite version.

EXCLUDE clause, GROUPS frame types, window chaining, and support for <expr> PRECEDING and <expr> FOLLOWING boundaries in RANGE frames are only available from sqlite 3.28.0, released on 2019-04-16.

More information at Window Functions documentation.

Basic Usage

// Simple running total example
final runningTotal = WindowFunctionExpression<double>(
  items.price.sum(), // aggregate function
  orderBy: [OrderingTerm.asc(items.date)],
);

// Use in a query
final query = select(items).addColumns([runningTotal]);

Partitioning

Partition data into groups before applying the window function:

// Running total per category
final runningTotalByCategory = WindowFunctionExpression<int>(
  items.price.sum(),
  orderBy: [OrderingTerm.asc(items.date)],
  partitionBy: [items.categoryId],
);

Frame Boundaries

Rows Frame

Operates on physical rows:

// Moving average of previous 3 rows and current row
final movingAverage = WindowFunctionExpression<double>(
  items.price.avg(),
  orderBy: [OrderingTerm.asc(items.date)],
  boundary: FrameBoundary.rows(
    start: -3, // 3 rows preceding
    end: 0,    // current row
  ),
);

Groups Frame

Operates on groups of rows with same ORDER BY values:

// Include current group and one group before
final groupFrame = WindowFunctionExpression<int>(
  items.quantity.sum(),
  orderBy: [OrderingTerm.asc(items.category)],
  boundary: FrameBoundary.groups(
    start: null, // Unbounded preceding groups
    end: 0,    // current group
  ),
);

Range Frame

Operates on value ranges (default):

// Sum of items within price range of ±10 from current row
final rangeFrame = WindowFunctionExpression<int>(
  items.quantity.sum(),
  orderBy: [OrderingTerm.asc(items.price)],
  boundary: FrameBoundary.range(
    start: -10, // 10 units less than current value
    end: 10,    // 10 units more than current value
  ),
);