Window functions

What they do#

An aggregate collapses rows: count(*) over ten rows returns one. A window function computes across rows and keeps every one of them — each row gets its own answer, calculated from the rows around it.

rn := orm.RowNumber().Over(orm.Window().
    PartitionBy(orm.Of(Posts.AuthorID)).
    OrderBy(orm.Of(Posts.CreatedAt).Desc()))

sql

row_number() OVER (PARTITION BY author_id ORDER BY created_at DESC)

Read the window as: restart the numbering for each author, and within an author, order by newest first.

The two halves#

A window function is always a function plus a window:

orm.RowNumber().Over(orm.Window()...)
//  └ the function        └ the window it looks through

orm.Window() builds the window:

Method	Adds
`PartitionBy(...)`	`PARTITION BY` — restart for each group
`OrderBy(...)`	`ORDER BY` — the order within a partition
`Rows(start, end)`	`ROWS` frame
`Range(start, end)`	`RANGE` frame
`Groups(start, end)`	`GROUPS` frame

Any of them may be omitted. Over(orm.Window()) with nothing set is one window over the whole result.

Using one#

A window function is an expression, so it goes in a projection like any other:

type Ranked struct {
    Title string
    N     int64
}

rn := orm.RowNumber().Over(orm.Window().
    PartitionBy(orm.Of(Posts.AuthorID)).
    OrderBy(orm.Of(Posts.CreatedAt).Desc()))

shape := orm.Project2(
    orm.Of(Posts.Title), rn,
    func(title string, n int64) Ranked { return Ranked{title, n} },
)

rows, err := orm.Compose(pool, shape).From(Posts.Source()).All(ctx)

The functions#

Ranking#

orm.RowNumber()    // 1, 2, 3, 4        -> int64
orm.Rank()         // 1, 2, 2, 4        -> int64  (ties share, then skip)
orm.DenseRank()    // 1, 2, 2, 3        -> int64  (ties share, no gap)
orm.PercentRank()  // 0.0 … 1.0         -> float64
orm.CumeDist()     // cumulative        -> float64
orm.Ntile(4)       // quartile bucket   -> int32

Rank and DenseRank differ only in what happens after a tie, which is the thing people get wrong: Rank leaves a hole, DenseRank does not.

Reaching other rows#

orm.Lag(Posts.Score)         // the previous row's value
orm.LagN(Posts.Score, 3)     // three rows back
orm.Lead(Posts.Score)        // the next row's value
orm.LeadN(Posts.Score, 3)
orm.FirstValue(Posts.Score)  // first in the frame
orm.LastValue(Posts.Score)   // last in the frame
orm.NthValue(Posts.Score, 2) // the second

All of these return the nullable form of the column's type. There is no previous row for the first row, and no next row for the last — so Lag over a NOT NULL column is still *T, and the type says so rather than letting a NULL arrive at a destination that cannot hold it.

Aggregates as windows#

Any aggregate becomes a window function with Over:

running := orm.SumInt64[Order, int64](Orders.Total).Over(orm.Window().
    OrderBy(orm.Of(Orders.Placed).Asc()).
    Rows(orm.UnboundedPreceding(), orm.CurrentRow()))

sql

sum(total) OVER (ORDER BY placed ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

That is a running total: every row sees itself and everything before it.

Frames#

A frame narrows which rows of the partition the function sees. The bounds:

orm.UnboundedPreceding()  // the start of the partition
orm.Preceding(3)          // three rows back
orm.CurrentRow()
orm.Following(3)
orm.UnboundedFollowing()  // the end of the partition

// a trailing 7-row average
orm.AvgInt64[Order, float64](Orders.Total).Over(orm.Window().
    OrderBy(orm.Of(Orders.Placed).Asc()).
    Rows(orm.Preceding(6), orm.CurrentRow()))

Rows counts rows. Range counts by value, so peers with equal ORDER BY values are included together. Groups counts peer groups. They are different answers on tied data, which is why all three exist rather than one.

Where a window function cannot go#

Not in WHERE, and not in HAVING. PostgreSQL evaluates windows after those clauses, so the value does not exist yet.

To filter on one, compute it in a derived table and filter outside — which is the Top-N recipe:

rank := orm.Named("rn", orm.RowNumber().Over(orm.Window().
    PartitionBy(orm.Of(Orders.UserID)).
    OrderBy(orm.Of(Orders.Placed).Desc())))

ranked := orm.Sub("ranked", orm.Rows(
    orm.Named("id", orm.Of(Orders.ID)),
    rank,
).From(Orders.Source()))

rows, err := orm.Compose(pool, shape).
    From(ranked).
    Where(orm.Ref(ranked, rank).Lte(3)).   // the three most recent per user
    All(ctx)

See Hard queries for the whole of that one.

Worked examples#

A leaderboard with ties handled#

w := orm.Window().OrderBy(orm.Of(Scores.Points).Desc())

var board = orm.Project3(
    orm.Of(Scores.Player),
    orm.Rank().Over(w),        // 1, 2, 2, 4 — a tie leaves a hole
    orm.DenseRank().Over(w),   // 1, 2, 2, 3 — a tie does not
    func(p string, r, d int64) Row { return Row{p, r, d} },
)

orm.Compose(pool, board).From(Scores.Source()).All(ctx)

Which one is right depends on whether "third place" should exist when two people tie for second. That is a product decision, and the two functions let you make it.

Change since the previous reading#

w := orm.Window().
    PartitionBy(orm.Of(Meters.MeterID)).
    OrderBy(orm.Of(Meters.ReadAt).Asc())

previous := orm.Lag(Meters.Value).Over(w)

var deltas = orm.Project3(
    orm.Of(Meters.MeterID), orm.Of(Meters.Value), previous,
    func(id int64, now int32, before *int32) Delta { return Delta{id, now, before} },
)

before is a pointer because the first reading of each meter has nothing behind it. Partitioning restarts that at every meter.

A running balance#

running := orm.SumInt32[orm.Composed](orm.Of(Entries.AmountCents)).
    Over(orm.Window().
        PartitionBy(orm.Of(Entries.AccountID)).
        OrderBy(orm.Of(Entries.PostedAt).Asc()).
        Rows(orm.UnboundedPreceding(), orm.CurrentRow()))

A seven-day moving average#

avg := orm.AvgInt64[orm.Composed, float64](orm.Of(Daily.Total)).
    Over(orm.Window().
        OrderBy(orm.Of(Daily.Day).Asc()).
        Rows(orm.Preceding(6), orm.CurrentRow()))

Rows(6 preceding, current) is seven rows including this one. Using Range instead would group days with equal values together, which is not what a moving average means.