Skip to content

Windows

group makes one group of the rows with one key, and a step after it reduces each group to one value. Some questions keep every row and add a value that depends on the row's neighbours: the position of an order among its customer's orders, the balance after each ledger entry, the change from the previous revision. In SQL those are window functions, written OVER (PARTITION BY ... ORDER BY ...). In HQL they are the step over.

One value per row

over(by, order, into) partitions a sequence by the key by gives each row, orders each partition by order, and calls into once per row, with the row and its Window: the row's place in its ordered partition. The result is a List holding what into gave for each row, at the row's input position. No row is merged or dropped.

row_number is the row's position in its partition, from 1. Ranking the replay's rows within each id, latest first, and keeping the first of each is the deduplication of the previous page in its SQL form, row_number() over (partition by id order by at desc) = 1:

type Event {
    id       : Int
    kind     : String
    customer : String
    amount   : Decimal
    at       : String
}
rows : Seq<Event> = source("silver_replayed")
rows
| over(by = r => r.id, order = (a, b) => compare(b.at, a.at),
       into = (r, w) => {row: r, n: w | row_number})
| filter(x => x.n == 1)
| map(x => {id: x.row.id, amount: x.row.amount})
[{amount: 120.50, id: 1}, {amount: 42.00, id: 2}, {amount: 42.00, id: 3}, {amount: 7.99, id: 4}, {amount: 120.50, id: 5}, {amount: 18.00, id: 6}]

order = (a, b) => compare(b.at, a.at) is a comparison of two rows, here reversed for latest first; order may also be a key, r => r.at, for ascending order. The kept rows are in input order, each at its own row's position. Without by, every row is in one partition.

group with max_by gives the same six rows, and is the shorter program when one row per key is all a step wants. over is the step when a row's position is itself the value, or when every row stays.

Ranks

Rows equal under order keep their input order, so row_number never gives two rows one position. rank gives rows equal under the order one rank and skips the positions they share; dense_rank does not skip:

type Score {
    name  : String
    score : Int
}

scores = [
    Score(name = "a", score = 9),
    Score(name = "b", score = 7),
    Score(name = "c", score = 9),
    Score(name = "d", score = 5),
]
scores
| over(order = (x, y) => compare(y.score, x.score),
       into = (s, w) => [w | row_number, w | rank, w | dense_rank])
[[1, 1, 1], [3, 3, 2], [2, 1, 1], [4, 4, 3]]

a and c tie at 9. a is first in the input, so its row_number is 1 and c's is 2; both have rank 1. b has rank 3 and dense rank 2.

A total order

A row kept by its position among equals is kept by where it happened to arrive. Two revisions of one order written in the same second are equal by at, and max_by, like row_number, keeps the earlier in the input:

type Revision {
    id     : Int
    at     : String
    seq    : Int
    amount : Int
}
rows = [
    Revision(id = 6, at = "2026-11-02T11:50:00Z", seq = 1, amount = 15),
    Revision(id = 6, at = "2026-11-02T11:50:00Z", seq = 2, amount = 18),
]
rows
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| map(r => r.amount)
{15}

With the rows in the other order the result is 18. then composes two comparisons, the second deciding where the first is Equal; a comparison that ends in a key no two rows share, here the change sequence seq, is a total order, and the row kept no longer depends on input order:

rows = [
    Revision(id = 6, at = "2026-11-02T11:50:00Z", seq = 1, amount = 15),
    Revision(id = 6, at = "2026-11-02T11:50:00Z", seq = 2, amount = 18),
]
rows
| group(by = r => r.id)
| map(g => g.items | max_by((a, b) => compare(a.at, b.at) | then(compare(a.seq, b.seq))))
| somes
| map(r => r.amount)
{18}

Neighbours

lag is the row before in the partition and lead the row after, each an Option, none past either end. lag(n) and lead(n) reach n rows:

[1, 4, 9]
| over(order = x => x,
       into = (x, w) => [w | lag | unwrap_or(0), w | lead | unwrap_or(0)])
[[0, 4], [1, 9], [4, 0]]

Frames

A frame is a list of rows of the partition, in order. running is the rows from the first up to and including the row, rows(before, after) the rows from before positions before to after after, clipped at the ends, and whole the partition. An aggregate over a frame is an ordinary step over a list. The balance after each ledger entry, per account:

type Posting {
    account : String
    seq     : Int
    amount  : Int
}

ledger = [
    Posting(account = "a", seq = 1, amount = 100),
    Posting(account = "b", seq = 1, amount = 50),
    Posting(account = "a", seq = 2, amount = -30),
    Posting(account = "b", seq = 2, amount = 20),
    Posting(account = "a", seq = 3, amount = 10),
]
ledger
| over(by = t => t.account, order = t => t.seq,
       into = (t, w) => w | running | map(x => x.amount) | sum)
[100, 50, 70, 70, 80]

running counts rows, as SQL's ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW does. SQL's default frame with an ORDER BY also takes in the rows equal to the current one; running does not. A moving sum, and the partition's first, last and size:

[1, 2, 3, 4]
| over(order = x => x,
       into = (x, w) => [w | rows(1, 1) | sum, w | first, w | last, w | size, w | whole | sum])
[[3, 1, 4, 4, 10], [6, 1, 4, 4, 10], [9, 1, 4, 4, 10], [7, 1, 4, 4, 10]]

The first n of each key

Keeping the two latest orders of each customer is row_number and a filter. A record literal is Data, whose fields are Option<Data> and not ordered, so into builds a declared record to compare n with <=:

type Order {
    id       : Int
    customer : String
    at       : Int
}

type Ranked {
    id : Int
    n  : Int
}

orders = [
    Order(id = 1, customer = "ada", at = 1),
    Order(id = 2, customer = "ada", at = 3),
    Order(id = 3, customer = "bob", at = 2),
    Order(id = 4, customer = "ada", at = 2),
    Order(id = 5, customer = "bob", at = 1),
    Order(id = 6, customer = "ada", at = 4),
]
orders
| over(by = o => o.customer, order = (a, b) => compare(b.at, a.at),
       into = (o, w) => Ranked(id = o.id, n = w | row_number))
| filter(x => x.n <= 2)
| map(x => x.id)
[2, 3, 5, 6]

What over refuses

A window is a view of its partition while into runs. into may not give one back, in any type: the checker refuses into = (x, w) => [w]. And over takes a Seq: a partition of a stream is never complete, since a stream may not end, so over of a Stream is refused like group of one. Inside collect(n), over ranks each batch, and a row in one batch is not compared with a row in another.