Window functions (OVER())

The LLBLGen Pro Runtime Framework comes with support for the SQL OVER() clause, which is used with window functions. OVER() allows aggregates and specific window functions to define a window in the resultset. This support is two fold: existing aggregate functions can be extended with an OVER() clause in the SQL query and new window functions like RANK() and FIRST_VALUE() are supported and can be specified directly in an LLBLGen Pro query together with an OVER() clause.

The OVER() clause

The OVER() clause consists of three, all optional, parts: OVER(partition, ordering, framebounds). As all parts are optional, the query system defines a way to specify just the elements you need. Not all databases support all three parts, in which case the parts which aren't supported are simply ignored.

Window functions are only supported in the projection of a select query. Specifying a window function elsewhere has no effect.

Supported SQL syntax

The following SQL syntax elements are supported:

For the partition part:

  • PARTITION BY exp1, exp2, ..,expn. The expressions can be fields, expressions using fields etc.

For the ordering part:

  • ORDER BY exp ASC/DESC.

For the framebounds part:

  • ROWS frame start
  • ROWS BETWEEN frame start AND frame end
  • RANGE frame start
  • RANGE frame start AND frame end

where frame start can be

  • UNBOUND PRECEDING
  • positive integer PRECEDING
  • CURRENT ROW

where frameend can be

  • UNBOUND FOLLOWING
  • positive integer FOLLOWING
  • CURRENT ROW

where positive integer is any integer larger than 0 including 0.

For PostgreSQL and SQL Server positive integer PRECEDING/FOLLOWING can't be used with RANGE. Firebird doesn't support PRECEDING/FOLLOWING.

Supported databases

The OVER() clause is supported on the following databases:

  • SQL Server
  • PostgreSQL
  • Firebird 3+. Firebird 3.0+ supports the partition and ordering parts of the OVER() clause. If framebounds are specified, they're still generated into the SQL as the generation is generic across all databases. This will then cause an exception in the database if you're using Firebird 3.x or earlier, which is the designed behavior in situations like this. Firebird 4.x+ supports framebounds and the queries won't generate an error on Firebird 4.x+.
  • IBM DB2 (which calls it OLAP specification)
  • MySQL 8+
  • Oracle

Supported query systems

OVER() is used with aggregates and aggregate like window functions, and this makes it a tough call for Linq which has a somewhat odd system for specifying aggregates. Therefore Window functions support is present in QuerySpec and the low-level API and not in Linq. Below first the low-level API support is described, which introduces the objects also used in the QuerySpec support. Additionally examples are given to get you started quickly with window functions in LLBLGen Pro. It's recommended you use the QuerySpec way of querying your database instead of the low-level API.

Low-level API

For the Low-level API, you'll be working with the OverClauseToApply property of an IEntityFieldCore instance, similar to the AggregateFunctionToApply property for specifying aggregate functions. This property is of type WindowSpecification. If this property is set, the runtime will append an OVER() specification according to the values in the WindowSpecification instance to any set AggregateFunction or Expression on the IEntityFieldCore instance.

The WindowSpecification class consists of three elements:

  • a partition definition, in the form of a WindowPartition instance, or null if no partition has to be emitted
  • an ISortExpression for the ordering, or null if no ordering has to be emitted
  • a framebounds definition in the form of a WindowBounds instance, or null if no frame bounds have to be emitted.

The WindowPartition instance mainly consists of a List<object> which can be empty, in which case no partition information is emitted in the OVER() clause. The ISortExpression can be null / empty and specifies an order by clause to be added to the OVER() clause. The WindowBounds instance consists of two parts: frame start and frame end. Through Enum values you can define the frame start and frame end: FrameStartBound and FrameEndBound. The frame start / end can also be an integer value.

You can instantiate one WindowSpecification and assign it to multiple fields, to re-use a window specification without defining a duplicate.

WindowBounds and literals

For WindowBounds it's possible to define a literal number as preceding/following argument. This number isn't emitted as a parameter but emitted as a literal in the query string. The main reason is that e.g. SQL Server doesn't accept a parameter at that location (e.g. the '1' in ROWS CURRENT ROW AND 1 FOLLOWING) and it's safe: the value can only be an integer so there's no risk for sql injection.

Querying helpers

IEntityField and IEntityField2 now have a SetOverClauseToApply() method so command chaining can occur in low-level API scenarios.

Example

Example which re-uses a window specification with multiple aggregated fields to create two different values from the same window:

var fields = new ResultsetFields(4);
var datePartCall = new DbFunctionCall("DATEPART(yy, {0})", new object[] {SalesPersonFields.ModifiedDate});
var window = new WindowSpecification(WindowPartition.By(SalesPersonFields.TerritoryId), 
                                     new SortExpression(new EntityField2("E", datePartCall).Ascending()));
fields.DefineField(SalesPersonFields.TerritoryId, 0);
fields.DefineField(SalesPersonFields.ModifiedDate, 1, "SalesYear");
fields.DefineField(SalesPersonFields.SalesYtd, 2, "MovingAvg", AggregateFunction.Avg);
fields.DefineField(SalesPersonFields.SalesYtd, 3, "CumulativeTotal", AggregateFunction.Sum);
fields[1].ExpressionToApply = datePartCall;
fields[2].OverClauseToApply = window;
fields[3].OverClauseToApply = window;
var filter = new RelationPredicateBucket(new PredicateExpression(SalesPersonFields.TerritoryId.IsNull().Or(SalesPersonFields.TerritoryId.LesserThan(5))));
var sorter = new SortExpression(SalesPersonFields.TerritoryId.Ascending());
sorter.Add(fields[1].Ascending());
sorter[1].EmitAliasForExpressionAggregateField = true;
var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter, 0, sorter, false);
}

var fields = new ResultsetFields(4);
var datePartCall = new DbFunctionCall("DATEPART(yy, {0})", new object[] {SalesPersonFields.ModifiedDate});
fields.DefineField(SalesPersonFields.EmployeeId, 0);
fields.DefineField(SalesPersonFields.TerritoryId, 1);
fields.DefineField(SalesPersonFields.ModifiedDate, 2, "SalesYear");
fields.DefineField(SalesPersonFields.SalesYtd, 3, "CumulativeTotal", AggregateFunction.Sum);
fields[2].ExpressionToApply = datePartCall;
fields[3].OverClauseToApply = new WindowSpecification(WindowPartition.By(SalesPersonFields.TerritoryId), 
                                                      new SortExpression(new EntityField2("E", datePartCall).Ascending()), 
                                                      WindowBounds.Rows(FrameStartBound.UnboundedPreceding));
var filter = new PredicateExpression(SalesPersonFields.TerritoryId.IsNull().Or(SalesPersonFields.TerritoryId.LesserThan(5)));
var sorter = new SortExpression(SalesPersonFields.TerritoryId.Ascending());
sorter.Add(fields[1].Ascending());
sorter[1].EmitAliasForExpressionAggregateField = true;
var dao = new TypedListDAO();
var results = new DataTable();
dao.GetMultiAsDataTable(fields, results, 0, sorter, filter, null, false, null, null, 0, 0);

SELECT DISTINCT 
    [LPA_L2].[TerritoryID] AS [TerritoryId],
    DATEPART(yy, [LPA_L1].[ModifiedDate]) AS [SalesYear],
    AVG([LPA_L2].[SalesYTD]) OVER(PARTITION BY [LPA_L2].[TerritoryID] ORDER BY DATEPART(yy, [LPA_L1].[ModifiedDate]) ASC) AS [MovingAvg],
    SUM([LPA_L2].[SalesYTD]) OVER(PARTITION BY [LPA_L2].[TerritoryID] ORDER BY DATEPART(yy, [LPA_L1].[ModifiedDate]) ASC) AS [CumulativeTotal]
FROM ([AdventureWorksUnitTests].[HumanResources].[Employee] [LPA_L1]
        LEFT JOIN [AdventureWorksUnitTests].[Sales].[SalesPerson] [LPA_L2]
            ON [LPA_L1].[EmployeeID] = [LPA_L2].[SalesPersonID])
WHERE (([LPA_L2].[TerritoryID] IS NULL
         OR [LPA_L2].[TerritoryID] < @p1)
    AND (([LPA_L2].[SalesPersonID] IS NOT NULL)))
ORDER  BY [LPA_L2].[TerritoryID] ASC,
          [SalesYear] ASC

QuerySpec

For QuerySpec, an extension method, Over(), is defined for field and function mapping expression to specify the WindowSpecification. Its overloads have the same signatures as the WIndowSpecification constructor.

Window Functions specification.

To specify window functions like RANK or FIRST_VALUE, you use the class WindowFunctions, similar to the Functions class to specify normal SQL functions like Count. The methods provided by WindowFunctions produce a function mapping expression to which an Over() call can be appended. This mechanism relies on function mappings defined by the Dynamic Query Engine.

Examples

The examples are given for Adapter however the query definition for SelfServicing is the same.

Example which re-uses a window specification with multiple aggregated fields to create two different values from the same window:

// query definition. Is equal for SelfServicing or Adapter
var qf = new QueryFactory();
var window = new WindowSpecification()
             .PartitionBy(SalesPersonFields.TerritoryId)
             .OrderBy(SalesPersonFields.ModifiedDate.Year().Ascending())
             .FrameByRows(FrameStartBound.CurrentRow, 1);
var q = qf.SalesPerson
          .Where(SalesPersonFields.TerritoryId.IsNull().Or(SalesPersonFields.TerritoryId.LesserThan(5)))
          .OrderBy(SalesPersonFields.TerritoryId.Ascending(), qf.Field("SalesYear").Ascending())
          .Select(() => new
                        {
                            EmployeeId = SalesPersonFields.EmployeeId.ToValue<int>(),
                            TerritoryId = SalesPersonFields.TerritoryId.ToValue<int>(),
                            SalesYear = SalesPersonFields.ModifiedDate.Year().As("SalesYear").ToValue<int>(),
                            CumulativeTotal = SalesPersonFields.SalesYtd.Sum().Over(window).As("CumulativeTotal").ToValue<decimal>(),
                            MovingAvg = SalesPersonFields.SalesYtd.Avg().Over(window).As("MovingAvg").ToValue<decimal>()
                        })
          .Distinct();
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
    
    // consume results here... 
}

SELECT  [LPA_L2].[SalesPersonID] AS [EmployeeId], 
        [LPA_L2].[TerritoryID] AS [TerritoryId], 
        YEAR([LPA_L1].[ModifiedDate]) AS [SalesYear], 
        SUM([LPA_L2].[SalesYTD]) OVER(PARTITION BY [LPA_L2].[TerritoryID] ORDER BY YEAR([LPA_L1].[ModifiedDate]) ASC 
            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS [CumulativeTotal], 
        AVG([LPA_L2].[SalesYTD]) OVER(PARTITION BY [LPA_L2].[TerritoryID] ORDER BY YEAR([LPA_L1].[ModifiedDate]) ASC 
            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS [MovingAvg] 
FROM ([AdventureWorksUnitTests].[HumanResources].[Employee] [LPA_L1] 
        LEFT JOIN [AdventureWorksUnitTests].[Sales].[SalesPerson] [LPA_L2] ON [LPA_L1].[EmployeeID]=[LPA_L2].[SalesPersonID]) 
WHERE ((([LPA_L2].[TerritoryID] IS NULL OR [LPA_L2].[TerritoryID] < @p1)) 
        AND ( ( [LPA_L2].[SalesPersonID] IS NOT NULL))) 
ORDER BY [LPA_L2].[TerritoryID] ASC, [SalesYear] ASC

Example which uses the window function RANK, demonstrating the WindowFunctions class to specify a window function with an over clause, which is defined inline:

// query definition. Is equal for SelfServicing or Adapter
var qf = new QueryFactory();
var q = qf.ProductInventory
          .From(QueryTarget.InnerJoin(qf.Product)
                           .On(ProductInventoryFields.ProductId.Equal(ProductFields.ProductId)))
          .Select(() => new
                        {
                            ProductId = ProductFields.ProductId.ToValue<int>(),
                            Name = ProductFields.Name.ToValue<string>(),
                            LocationID = ProductInventoryFields.LocationId.ToValue<int>(),
                            Quantity = ProductInventoryFields.Qty.ToValue<short>(),
                            Rank = WindowFunctions.Rank()
                                 .Over(WindowPartition.By(ProductInventoryFields.LocationId), 
                                       ProductInventoryFields.Qty.Descending()).As("Rank").ToValue<int>()
                        })
          .Where(ProductInventoryFields.LocationId.Between(3, 4))
          .OrderBy(ProductInventoryFields.LocationId.Ascending(), qf.Field("Rank").Ascending());
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
    
    // consume the results here... 
}

SELECT [AdventureWorksUnitTests].[Production].[Product].[ProductID] AS [ProductId],
       [AdventureWorksUnitTests].[Production].[Product].[Name], 
       [AdventureWorksUnitTests].[Production].[ProductInventory].[LocationID]  AS [LocationId],
       [AdventureWorksUnitTests].[Production].[ProductInventory].[Quantity] AS [Qty],
       RANK() OVER(PARTITION BY [AdventureWorksUnitTests].[Production].[ProductInventory].[LocationID] 
                ORDER BY [AdventureWorksUnitTests].[Production].[ProductInventory].[Quantity] DESC) AS [Rank]
FROM   ([AdventureWorksUnitTests].[Production].[ProductInventory]
        INNER JOIN [AdventureWorksUnitTests].[Production].[Product]
            ON [AdventureWorksUnitTests].[Production].[ProductInventory].[ProductID] = 
                    [AdventureWorksUnitTests].[Production].[Product].[ProductID])
WHERE  (([AdventureWorksUnitTests].[Production].[ProductInventory].[LocationID] BETWEEN @p1 AND @p2))
ORDER  BY [AdventureWorksUnitTests].[Production].[ProductInventory].[LocationID] ASC,
          [Rank] ASC

Supported Window functions

Besides aggregate functions, there are other functions which can be used with a window specification: Analytic functions. All the analytic/window functions require a window specification to function.

LLBLGen Pro comes with support for most window (analytical) functions available in PostgreSQL, Oracle and SQL Server, which should be enough for the vast majority of cases. Not all functions are available on all supported databases, the list of databases supporting a function is specified with each function. Window functions are implemented through function call mappings and are meant to be used mainly in QuerySpec.

There's no low-level implementation for these functions, for the the low-level API you have to fall back to DbFunctionCall instances. The functions are available through the class WindowFunctions, which is in the QuerySpec namespace and works the same way as the Functions class for regular SQL functions: the methods of the class create a FunctionMappingExpression to the proper function and returns that, including the arguments (if applicable).

Implemented functions

Values between [] are optional.

  • CumeDist(). Implemented for DB2, MySQL, PostgreSQL, Oracle, SQL Server, Firebird 4.0+
  • DenseRank(). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server
  • FirstValue(scalar_expr). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server. scalar_expr is a valid expression or subquery.
  • Lag(scalar_expr, [offset], [default]). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server. scalar_expr is a valid scalar expression, offset is the number of rows back from the current row, default is the scalar value to return when scalar_expr at offset is NULL.
  • LastValue(scalar_expr). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server. scalar_expr is a valid expression or subquery.
  • Lead(scalar_expr, [offset], [default]). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server. See Lag() for details regarding the arguments
  • NthValue(expr, N). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle. expr is a valid expression / field, N is a positive integer.
  • Ntile(N). Implemented for DB2, MySQL, PostgreSQL, Oracle, SQL Server, Firebird 4.0+. N is a positive integer.
  • PercentRank(). Implemented for DB2, MySQL, PostgreSQL, Oracle, SQL Server, Firebird 4.0+
  • PercentileCont(N). Implemented for DB2, Oracle, SQL Server. N is a double, ranging from 0.0 to 1.0.
  • PercentileDisc(N). Implemented for DB2, Oracle, SQL Server. N is a double, ranging from 0.0 to 1.0
  • Rank().Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server
  • RowNumber(). Implemented for DB2, Firebird, MySQL, PostgreSQL, Oracle, SQL Server

Please consult the documentation of your database with regarding the functionality of the function itself.