Sequence Functions (Window Functions)

Sequence functions compute a value for each row over an ordered, optionally partitioned window of rows — ranking, numbering, or looking at neighboring rows — without collapsing the result set the way aggregate functions do.

explanation

row_number(), rank(), and dense_rank()

These take no value argument. They require a sort by clause, which defines the order rows are numbered in:

// Number each user's orders from newest to oldest
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, rn = row_number(by o.userId sort by o.createdAt desc))
");

rank() and dense_rank() both assign the same value to tied rows, but differ in what happens next: rank() skips the ranks a tie consumes, dense_rank() does not.

// Rank orders by total within each user, ties share a rank
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.total, r = rank(by o.userId sort by o.total desc))
");
Sorted valuesrank()dense_rank()
10011
10011
8032
5043

ntile()

Distributes rows within each partition into a fixed number of roughly equal buckets, numbered from 1. When the row count doesn't divide evenly, the earlier buckets absorb the remainder:

// Split each user's orders into 4 buckets by order date
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, bucket = ntile(4 by o.userId sort by o.createdAt))
");

lag() and lead()

lag() returns a value from the previous row in the partition; lead() returns one from the next row. Both use sort by to define what "previous" and "next" mean, and return null when there is no such row (the first row of a partition has no previous row; the last has no next):

// For each order, show the total of the user's previous and next order
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (
        o.userId,
        o.id,
        previousTotal = lag(o.total by o.userId sort by o.createdAt),
        nextTotal = lead(o.total by o.userId sort by o.createdAt)
    )
");

Partitioning with by

The optional by clause splits rows into independent partitions — each sequence function restarts at every partition boundary. Without an explicit by, the partition is inferred from the query's other non-aggregate retrieve columns, excluding the range's primary key and any column the function itself already sorts by:

// Explicit by — partitions by userId regardless of what else is selected
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.title, r = rank(by o.userId sort by o.total desc))
");

// Inferred by — o.userId is the only other displayed column (o.id is
// excluded as the primary key), so partitioning is identical to the query above
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, r = rank(sort by o.total desc))
");
Write an explicit by whenever a displayed column other than the primary key would otherwise be swept into the inferred partition and make it too narrow — e.g. a unique column like o.title would put every row in its own single-row partition if left to inference.

Running Aggregates

The regular aggregate functions (sum(), count(), avg(), min(), max()) also accept an inline sort by. Without it, they collapse rows into one per group; with it, they behave like a sequence function instead — a running total, count, or average computed row by row within the (optionally partitioned) order:

// Running total of each user's spend, ordered by order date
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, runningTotal = sum(o.total by o.userId sort by o.createdAt))
");

Filtering on a Sequence Function's Result

Sequence functions can be referenced directly in where, including through an alias — the classic "top N per group" pattern:

// The 3 most recent orders per user
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, o.createdAt, rn = row_number(by o.userId sort by o.createdAt desc))
    where rn <= 3
");

SQL itself forbids referencing a window function's result in the same query block's where — window functions are evaluated after where runs. ObjectQuel works around this transparently: a query like the one above is staged internally into a derived-table subquery that computes the ranking, joined back on the primary key so the outer query can filter on it. No SQL-visible WITH/CTE syntax is needed, and nothing about how you write the query changes.

Any other where conditions combined with and are pushed into that same ranking computation, so they scope which rows are ranked in the first place rather than being applied only after the fact:

// The single most recent PUBLISHED order per user — unpublished orders
// never occupy a rank slot, rather than merely being filtered out afterward
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    retrieve (o.userId, o.id, rn = row_number(by o.userId sort by o.createdAt desc))
    where rn <= 1 and o.status = 'published'
");
Current restrictions: this rewrite only applies to single-range queries, requires a database engine with window function support, and each where condition may reference at most one sequence-function result. Combining a sequence-function result with or, or two sequence-function results in the same condition (e.g. rn + rn2 <= 3), is rejected — split them into separate and-ed conditions instead.

A sequence-function alias that's both filtered on and displayed is computed twice — once to filter, once to display — which is only observable when combining multiple sequence functions that don't agree on the same order. A single filter like where rn <= 3 always displays consistently, because keeping "the first N in this order" can never change any of those N rows' own position in that same order.