Window functions

A window function computes across a set of rows and keeps every row, where GROUP BY would collapse them. The results come back through Project.

A running position

type Ranked struct {
	Name     string
	Position int64
}

var R = ant.Fields[Ranked]()

byAge := ant.OrderedBy(ant.Col(&U.Age).Asc(), ant.Col(&U.Name).Asc())

rows, err := ant.Project[Ranked](
	db.Users.OrderBy(&U.Name),
	ant.Into(&R.Name, &U.Name),
	ant.IntoExpr(&R.Position, ant.RowNumber().Over(byAge)),
).All(ctx)

Defining a window

ant.OrderedByfunc OrderedBy(terms ...Sort) WindowOrderedBy starts a window over all rows in the given order. orders the rows, and ant.PartitionByfunc PartitionBy[V any](field *V) WindowPartitionBy starts a window divided by a column. restarts for each group.

perAge := ant.PartitionBy(&U.Age).OrderBy(ant.Col(&U.Name).Asc())

position := ant.RowNumber().Over(perAge)

The functions

Function	Gives
ant.RowNumber()func RowNumber() Expr[int64]RowNumber numbers rows within a window, from 1.	1, 2, 3, 4
ant.Rank()func Rank() Expr[int64]Rank ranks rows within a window, leaving gaps after ties.	1, 2, 2, 4: gaps after ties
ant.DenseRank()func DenseRank() Expr[int64]DenseRank ranks rows within a window, with no gaps after ties.	1, 2, 2, 3: no gaps
ant.Lag(&U.Age)func Lag[V any]( field *V, ) Expr[sql.Null[V]]Lag returns field from the previous row of a window.	the previous row's value, NULL for the first
ant.Lead(&U.Age)func Lead[V any]( field *V, ) Expr[sql.Null[V]]Lead returns field from the next row of a window.	the next row's value, NULL for the last

Any aggregate works as a window too, which is how you get a running total.

type Running struct {
	Total    int
	Previous sql.Null[int]
}

var T = ant.Fields[Running]()

rows, err := ant.Project[Running](
	db.Users.OrderBy(&U.Name),
	ant.IntoExpr(&T.Total, ant.Sum(&U.Age).Over(byAge)),
	ant.IntoExpr(&T.Previous, ant.Lag(&U.Age).Over(byAge)),
).All(ctx)

Overfunc (e Expr[V]) Over(w Window) Expr[V]Over makes an aggregate or ranking function a window function. needs a function: ant.Col(&U.Age).Over(w)func Col[V any](field *V) Expr[V]Col returns the expression for a column. is an error.

See it tested: tests/features/querying/window_test.go