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, which is the designed behavior in situations like this. - 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
- 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. N is a positive integer.
- PercentRank(). Implemented for DB2, MySQL, PostgreSQL, Oracle, SQL Server
- 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.