Window functions

Compute ranking, neighbour values and running/windowed aggregates over a partition with the SQL OVER clause.

Prerequisites: Querying and projections · Grouping and aggregates · Sorting and paging

Overview

Window functions live on SqlFunctions.Sql. Each call returns a WindowFunction<T> marker that must be completed with .Over(...); inside the query expression it reads as the value type of the function (int for row_number/rank/dense_rank/ntile, double for percent_rank/cume_dist, T? for the value/aggregate functions). The marker types and their methods are only ever interpreted by the expression visitor - they are never executed.

Over with no arguments renders an empty specification (over ()). Because C# expression trees reject named arguments that skip a preceding defaulted parameter, an order-only specification must use the WindowOrder overload - Over(SqlFunctions.Sql.asc(() => e.Id)) - rather than Over(orderBy: ...). SqlFunctions.Sql.asc(expression) and SqlFunctions.Sql.desc(expression) return a WindowOrder (an order key plus an OrderDirection).

Calling a window function without Over is an error: the visitor throws NotSupportedException whose message mentions Over.

Functions

Function	Sql call	Emitted SQL
Row number	row_number()	row_number()
Rank (with gaps)	rank()	rank()
Dense rank	dense_rank()	dense_rank()
Relative rank	percent_rank()	percent_rank()
Cumulative distribution	cume_dist()	cume_dist()
Interpolated percentile	percentile_cont(fraction, property)	percentile_cont(f) within group (order by expr) over (...)
Discrete percentile	percentile_disc(fraction, property)	percentile_disc(f) within group (order by expr) over (...)
Median (analytic)	SqlFunctions.SqlServer.median(property) / SqlFunctions.MySql.median(property)	percentile_cont(0.5) within group (order by expr) over (...)
Buckets	ntile(buckets)	ntile(n)
Previous value	lag(property[, offset[, defaultValue]])	lag(expr, offset[, default])
Next value	lead(property[, offset[, defaultValue]])	lead(expr, offset[, default])
Previous value in frame (ClickHouse)	SqlFunctions.ClickHouse.lag_in_frame(property[, offset[, defaultValue]])	lagInFrame(expr, offset[, default])
Next value in frame (ClickHouse)	SqlFunctions.ClickHouse.lead_in_frame(property[, offset[, defaultValue]])	leadInFrame(expr, offset[, default])
First in frame	first_value(property)	first_value(expr)
Last in frame	last_value(property)	last_value(expr)
N-th in frame	nth_value(property, n)	nth_value(expr, n)
Windowed sum	sum_over(property)	sum(expr)
Windowed average	avg_over(property)	avg(expr)
Windowed minimum	min_over(property)	min(expr)
Windowed maximum	max_over(property)	max(expr)
Windowed count	count_over() / count_over(property)	count(*) / count(expr)

The aggregate variants carry an _over suffix so they do not clash with the scalar aggregates sum, avg, min, max and count that are used with GroupBy.

Row number over a partition

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        rn = SqlFunctions.Sql.row_number().Over(partitionBy: () => e.Int, orderBy: () => e.Id)
    })
    .OrderBy(1, OrderDirection.Asc)
    .ToList();

select id, row_number() over (partition by nullableint order by id) as 'rn'
 from complex_entity

The Output: tables below show the rows returned by each example against the integration-test seed data (tests/nextorm.integration.tests/Providers/TestProviders/SqliteTestProvider.cs).

Output:

Id	rn
1	1
2	1
3	2

The seeded complex_entity has a singleton nullableint partition (id 1) and a two-row partition (id 2, 3), so rn is 1, 1, 2.

Rank, dense rank, and asc/desc

rank() leaves a gap after a tie, dense_rank() does not. Both use the WindowOrder overload here:

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        r = SqlFunctions.Sql.rank().Over(SqlFunctions.Sql.asc(() => e.Id)),
        dr = SqlFunctions.Sql.dense_rank().Over(SqlFunctions.Sql.asc(() => e.Id))
    })
    .ToList();

select id, rank() over (order by id) as 'r', dense_rank() over (order by id) as 'dr'
 from complex_entity

A descending key is written with SqlFunctions.Sql.desc. When several keys (or a mix of directions) are needed, use the WindowOrder[] overload; partitions are supplied through the array overload:

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        r = SqlFunctions.Sql.row_number().Over(
            partitionBy: new Expression<Func<object?>>[] { () => e.Int },
            orderBy: new[] { SqlFunctions.Sql.desc(() => e.Id) })
    })
    .ToList();

select id, row_number() over (partition by nullableint order by id desc) as 'r'
 from complex_entity

For a single partition expression and a single ordering key the arrays are unnecessary: pass the partition expression and the WindowOrder directly as the first two arguments, with an optional frame as the third. The generated SQL is identical to the one-element array form above:

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        r = SqlFunctions.Sql.row_number().Over(
            () => e.Int,
            SqlFunctions.Sql.desc(() => e.Id)),
        running = SqlFunctions.Sql.sum_over(e.Id).Over(
            () => e.Int,
            SqlFunctions.Sql.asc(() => e.Id),
            frame: WindowFrame.RowsUnboundedPrecedingToCurrentRow)
    })
    .ToList();

select id, row_number() over (partition by nullableint order by id desc) as 'r', sum(id) over (partition by nullableint order by id rows between unbounded preceding and current row) as 'running'
 from complex_entity

lag and lead with offset and default

lag/lead accept an optional offset and default. The default only fills a missing row at the boundary; a null value in an existing row is returned unchanged:

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        prev = SqlFunctions.Sql.lag(e.Id, 1, 0L).Over(SqlFunctions.Sql.asc(() => e.Id)),
        next = SqlFunctions.Sql.lead(e.Int, 2, 0).Over(SqlFunctions.Sql.asc(() => e.Id))
    })
    .ToList();

select id, lag(id, 1, 0) over (order by id) as 'prev', lead(nullableint, 2, 0) over (order by id) as 'next'
 from complex_entity

lagInFrame / leadInFrame (ClickHouse)

ClickHouse's lagInFrame/leadInFrame are the frame-respecting counterparts of lag/lead, exposed as SqlFunctions.ClickHouse.lag_in_frame/lead_in_frame (SupportsInFrameWindowFunctions). The standard lag/lead look at the whole partition and, on ClickHouse, reject an explicit frame with BAD_ARGUMENTS; the in-frame variants are evaluated within the ordered frame, so a partial frame can yield the default where the standard function would return a partition row:

var startingFrame = WindowFrame.Rows(WindowFrameBound.CurrentRow, WindowFrameBound.Following(1));

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        prev = SqlFunctions.Sql.lag(e.Id, 1, 0L).Over(SqlFunctions.Sql.asc(() => e.Id)),
        prevInFrame = SqlFunctions.ClickHouse.lag_in_frame(e.Id, 1, 0L)
            .Over(SqlFunctions.Sql.asc(() => e.Id), startingFrame)
    })
    .ToList();

-- ClickHouse: the frame [current row, 1 following] has no preceding row, so prevInFrame is always 0
select id,
       lag(id, 1, 0) over (order by id) as `prev`,
       lagInFrame(id, 1, 0) over (order by id rows between current row and 1 following) as `prevInFrame`
 from complex_entity

Windowed aggregates

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        total = SqlFunctions.Sql.sum_over(e.Id).Over(partitionBy: () => e.Int),
        n = SqlFunctions.Sql.count_over().Over(partitionBy: () => e.Int)
    })
    .ToList();

select id, sum(id) over (partition by nullableint) as 'total', count(*) over (partition by nullableint) as 'n'
 from complex_entity

avg_over inherits the grouped avg contract: an integral property yields double? and SQL Server widens the argument inside the aggregate (avg(cast(id as float)) over (...)), while the other dialects keep the native argument — exactly as described in AVG result types and argument policy. A running average therefore never loses the fraction:

var rows = dataContext.From<ISimpleEntity>()
    .Select(e => new
    {
        e.Id,
        runningAvg = SqlFunctions.Sql.avg_over(e.Id).Over(
            SqlFunctions.Sql.asc(() => e.Id),
            WindowFrame.RowsUnboundedPrecedingToCurrentRow)
    })
    .ToList();

select id, avg(id) over (order by id rows between unbounded preceding and current row) as 'runningAvg'
 from simple_entity

Output for the rows with id 1, 2, 3 ([1, 2, 3]):

Id	runningAvg
1	1
2	1.5
3	2

Frames

A frame restricts the rows an aggregate sees. WindowFrame has factory methods for both SQL frame units and WindowFrameBound has the boundaries:

Factory	Renders
Rows	rows between <start> and <end>
Range	range between <start> and <end>
Groups	groups between <start> and <end> (peer groups)
Rows	rows between <preceding> preceding and <following> following
RowsUnboundedPrecedingToCurrentRow	rows between unbounded preceding and current row
RangeUnboundedPrecedingToCurrentRow	range between unbounded preceding and current row

Boundary	Renders
UnboundedPreceding	unbounded preceding
Preceding	<n> preceding
CurrentRow	current row
Following	<n> following
UnboundedFollowing	unbounded following

A framed running aggregate and a sliding window:

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        e.Id,
        running = SqlFunctions.Sql.sum_over(e.Id).Over(
            SqlFunctions.Sql.asc(() => e.Id),
            WindowFrame.RowsUnboundedPrecedingToCurrentRow),
        sliding = SqlFunctions.Sql.sum_over(e.Id).Over(
            SqlFunctions.Sql.asc(() => e.Id),
            WindowFrame.Rows(1, 1))
    })
    .ToList();

select id, sum(id) over (order by id rows between unbounded preceding and current row) as 'running', sum(id) over (order by id rows between 1 preceding and 1 following) as 'sliding'
 from complex_entity

A frame may also exclude rows around the current one with WindowFrame.WithExclusion and a WindowFrameExclusion:

Exclusion	Renders
NoOthers	exclude no others (the default)
CurrentRow	exclude current row
Group	exclude group (current row and its peers)
Ties	exclude ties (peers only)

Named windows

A window specification can be declared once on the query and reused by several window functions, rendering a single SQL WINDOW clause. Declare it with EntityBuilder<TEntity>.Window(name, partitionBy, orderBy, frame) and reference it with Over("name"); build the ORDER BY keys with the builder's Asc/Desc helpers.

var e = dataContext.From<IComplexEntity>();

var rows = e
    .Window("w", partitionBy: [x => x.Int], orderBy: [e.Asc(x => x.Id)])
    .Select(x => new
    {
        x.Id,
        rn = SqlFunctions.Sql.row_number().Over("w"),
        total = SqlFunctions.Sql.sum_over(x.Id).Over("w")
    })
    .ToList();

select id, row_number() over w as 'rn', sum(id) over w as 'total'
 from complex_entity window w as (partition by nullableint order by id)

The name must be a plain SQL identifier. A repeated name on the same query, an invalid name, or Window before a later Join is rejected. Named windows are supported by PostgreSQL, MySQL, MariaDB, ClickHouse and SQLite (SupportsNamedWindows); SQL Server has no WINDOW clause and rejects the query with NotSupportedException.

Provider differences

Window functions are ANSI and every SQL provider nextorm targets supports the ROWS and RANGE frame units, so most SQL is the same apart from identifier quoting (single quotes, brackets or double quotes for the alias; see Provider overview). The percent_rank()/cume_dist() pair is supported by every provider (SupportsPercentRankCumeDist). The only non-portable value function is nth_value: SQL Server has no NTH_VALUE, so SupportsNthValue makes it reject the call with NotSupportedException, while PostgreSQL, MySQL/MariaDB, SQLite and ClickHouse render nth_value(expr, n).

The newer pieces are gated individually:

• Named windows (SupportsNamedWindows) - PostgreSQL, MySQL, MariaDB, ClickHouse and SQLite; SQL Server has no WINDOW clause.
• GROUPS frame unit (SupportsWindowFrameGroups) - PostgreSQL (11+), ClickHouse and SQLite (3.28+); SQL Server, MySQL and MariaDB reject it.
• Frame EXCLUDE (SupportsWindowFrameExclusion) - PostgreSQL and SQLite; SQL Server, MySQL, MariaDB and ClickHouse reject it.

The window percentiles are another exception: percentile_cont/percentile_disc are not portable as window functions. SQL Server and MariaDB render them as percentile_cont(f) within group (order by x) over (...) under SupportsPercentileWindow; PostgreSQL expresses percentiles as an ordered-set aggregate instead (SqlFunctions.Postgres.percentile_cont), and MySQL, SQLite and ClickHouse reject the window form with NotSupportedException.

The median has no spelling of its own on SQL Server or MariaDB: SqlFunctions.SqlServer.median(value) and SqlFunctions.MySql.median(value) are the same analytic composition with the fraction fixed at 0.5 (percentile_cont(0.5) within group (order by value) over (...)), so they must be completed with Over just like the window percentiles. These forms are analytic, not aggregates: they keep one row per input row, whereas an aggregate collapses each group to a single row. Only a partition-only window is supported (.Over(partitionBy: ...), or the empty over ()); a window ORDER BY or a frame is refused, because the ordering comes from the value argument. The fraction of percentile_cont/percentile_disc is a double in [0, 1], not a percentage, and must be finite and row-independent (a literal or a captured variable); a row-dependent fraction is refused at SQL generation. PostgreSQL's ordered-set aggregates never take OVER, and the generic percentile(fraction, value) member is refused by every provider — it never aliases the continuous form.

Provider	Behaviour
SQLite	Full OVER support, named windows, GROUPS and EXCLUDE; column aliases single-quoted (as 'rn').
SQL Server	Full OVER support; no named windows, no GROUPS, no EXCLUDE, no nth_value; aliases bracket-quoted (as [rn]).
PostgreSQL	Full OVER support, named windows, GROUPS (11+) and EXCLUDE; aliases double-quoted (as "rn").
MySQL	OVER support and named windows; no GROUPS, no EXCLUDE; column aliases backtick-quoted (as `rn`).
MariaDB	OVER support and named windows; no GROUPS, no EXCLUDE; aliases backtick-quoted.
ClickHouse	OVER support, named windows and GROUPS; no EXCLUDE; aliases backtick-quoted.
In-memory	Not applicable: window functions are rendered by the SQL dialects and are not part of the in-memory provider.

See also

• Grouping and aggregates - the scalar aggregates that _over variants shadow.
• Sorting and paging - ordering the outer query that selects window columns.
• Provider overview - alias quoting and provider capability flags.

---

Source: src/nextorm.core/Query/Windows/WindowFunctions.cs (WindowFunction<T>, WindowOrder, frame enums, WindowFrameBound, WindowFrame); src/nextorm.core/Query/Windows/WindowDefinition.cs (WindowDefinition, NamedWindowOrderKey); src/nextorm.core/Query/SqlFunctions/SqlFunctions.cs (asc/desc, functions); src/nextorm.core/Visitors/Windows/WindowFunctionTranslator.cs, src/nextorm.core/Visitors/Windows/WindowSql.cs; tests/nextorm.integration.tests/CommonTestSuite/Queries/Rows/CommonTestSuite.Window.cs, tests/nextorm.integration.tests/Postgres/PostgresSpecificTests.cs (named windows/GROUPS/EXCLUDE); tests/nextorm.core.tests/SqlRendering/WindowFunctionMarkerTests.cs; generated SQL: tests/nextorm.postgres.tests/SqlGeneration/SqlGenerationTests.cs, tests/nextorm.sqlite.tests/SqlGeneration/SqlGenerationTests.cs.