Window Functions

Window Functions

Window functions compute a value for each row based on a set of rows related to it, called a window. Unlike aggregate functions in a GROUP BY, window functions do not collapse rows so you still get one output row per input row. Support is built into every SQLite-provider package.

Platform compatibility. Basic window functions were added in SQLite 3.25.0, but full support including the GROUPS frame type and expression-based PRECEDING/FOLLOWING boundaries requires SQLite 3.28.0 (2019-04-16). Android API level 30 (Android 11) ships with SQLite 3.28.0. iOS 13 ships with SQLite 3.28.0 as well. Older Android and iOS versions do not include window function support.

If you need to support Android API 29 or earlier, or iOS 12 or earlier, use SQLite.Framework.Bundled or SQLite.Framework.Cipher instead of the default SQLite.Framework package. Both ship their own SQLite binary and work on any supported OS version.

When you use SQLite.Framework (the OS-bundled flavor), SQLiteWindowFunctions and FrameBoundary carry [SupportedOSPlatform("android30.0")] and [SupportedOSPlatform("ios13.0")] so the .NET platform compatibility analyzer (CA1416) warns when you target a lower minimum. In a MAUI or multi-targeted csproj, raise the minimum once your supported floor is high enough:

<PropertyGroup>
    <SupportedOSPlatformVersion Condition="'$(TargetPlatformIdentifier)' == 'android'">30.0</SupportedOSPlatformVersion>
    <SupportedOSPlatformVersion Condition="'$(TargetPlatformIdentifier)' == 'ios'">13.0</SupportedOSPlatformVersion>
</PropertyGroup>

The SQLite.Framework.Bundled and SQLite.Framework.Cipher packages do not carry the attributes and never trigger the warning.

Building a window

Every window expression starts with a function call followed by .Over(). The .Over() call produces an empty window that covers the entire result set. You then chain further methods to narrow it down.

When you project into a typed DTO, the value unwraps to T automatically through an implicit conversion:

db.Table<Order>().Select(o => new OrderWithRowNum
{
    Id = o.Id,
    RowNum = SQLiteWindowFunctions.RowNumber()
        .Over()
        .OrderBy(o.Id),
})

When you project into an anonymous type or a var, call .AsValue() at the end of the chain so the field type is T and not SQLiteWindow<T>:

db.Table<Order>().Select(o => new
{
    o.Id,
    RowNum = SQLiteWindowFunctions.RowNumber()
        .Over()
        .OrderBy(o.Id)
        .AsValue(),
})

The methods are translated to SQL at query time. They throw InvalidOperationException if called outside a LINQ expression.

Partition

PartitionBy splits the rows into independent groups. The window function resets at the start of each partition.

SQLiteWindowFunctions.RowNumber()
    .Over()
    .PartitionBy(o.CustomerId)
    .OrderBy(o.Date)

Use ThenPartitionBy to add more partition columns:

SQLiteWindowFunctions.RowNumber()
    .Over()
    .PartitionBy(o.Year)
    .ThenPartitionBy(o.CustomerId)
    .OrderBy(o.Date)

Order

OrderBy and OrderByDescending control the order of rows within the window. Use ThenOrderBy and ThenOrderByDescending for secondary sort keys.

SQLiteWindowFunctions.Rank()
    .Over()
    .PartitionBy(o.CustomerId)
    .OrderByDescending(o.Amount)
    .ThenOrderBy(o.Id)

Frame

By default SQLite uses a range from the start of the partition to the current row when an ORDER BY is present. You can set an explicit frame with Rows, Range, or Groups, using the FrameBoundary helpers to specify each end.

SQLiteWindowFunctions.Sum(o.Amount)
    .Over()
    .OrderBy(o.Date)
    .Rows(FrameBoundary.UnboundedPreceding(), FrameBoundary.CurrentRow())

Boundary	SQL produced
FrameBoundary.UnboundedPreceding()	UNBOUNDED PRECEDING
FrameBoundary.CurrentRow()	CURRENT ROW
FrameBoundary.UnboundedFollowing()	UNBOUNDED FOLLOWING
FrameBoundary.Preceding(n)	n PRECEDING
FrameBoundary.Following(n)	n FOLLOWING

Aggregate functions

These compute a value over the rows in the window.

Method	SQL produced
Sum<T>(value)	SUM(value)
Avg<T>(value)	AVG(value)
Min<T>(value)	MIN(value)
Max<T>(value)	MAX(value)
Count()	COUNT(*)
Count<T>(value)	COUNT(value)

Running total

var results = await db.Table<Order>()
    .Select(o => new
    {
        o.Id,
        o.Amount,
        RunningTotal = SQLiteWindowFunctions.Sum(o.Amount)
            .Over()
            .PartitionBy(o.CustomerId)
            .OrderBy(o.Date)
            .AsValue(),
    })
    .ToListAsync();

Ranking functions

These assign a numeric rank or position to each row within the window.

Method	SQL produced
RowNumber()	ROW_NUMBER()
Rank()	RANK()
DenseRank()	DENSE_RANK()
PercentRank()	PERCENT_RANK()
CumeDist()	CUME_DIST()
NTile(buckets)	NTILE(buckets)

Rank and DenseRank both rank rows by the ORDER BY columns, but Rank leaves gaps after a tie while DenseRank does not.

Rank within a group

var results = await db.Table<Order>()
    .Select(o => new
    {
        o.Id,
        o.CustomerId,
        Rank = SQLiteWindowFunctions.Rank()
            .Over()
            .PartitionBy(o.CustomerId)
            .OrderByDescending(o.Amount)
            .AsValue(),
    })
    .ToListAsync();

Navigation functions

These look up values from other rows within the window relative to the current row.

Method	SQL produced
Lag<T>(value)	LAG(value)
Lag<T>(value, offset)	LAG(value, offset)
Lag<T>(value, offset, default)	LAG(value, offset, default)
Lead<T>(value)	LEAD(value)
Lead<T>(value, offset)	LEAD(value, offset)
Lead<T>(value, offset, default)	LEAD(value, offset, default)
FirstValue<T>(value)	FIRST_VALUE(value)
LastValue<T>(value)	LAST_VALUE(value)
NthValue<T>(value, n)	NTH_VALUE(value, n)

Lag looks back at a previous row and Lead looks forward. Both return NULL for rows where no such row exists unless you provide a default value.

Compare to previous row

var results = await db.Table<Order>()
    .Select(o => new
    {
        o.Id,
        o.Amount,
        PreviousAmount = SQLiteWindowFunctions.Lag(o.Amount, 1L, 0.0)
            .Over()
            .OrderBy(o.Date)
            .AsValue(),
    })
    .ToListAsync();

Native AOT

The framework keeps all public methods on SQLiteWindowFunctions and FrameBoundary rooted for the trimmer, so those marker methods are never removed from the output. No extra setup is needed.