SQL Server Analytic functions

[popcatalin81]

Add support for SQL Server Analytic Functions

Currently the only supported function is ROW_NUMBER but even this function is not supported directly, it's only used internally by the Query pipeline in certain scenarios.

For example PERCENTILE_CONT or PERCENTILE_DISC are analytic functions which can be used to calculated the median of a dateset. An operation which is overly complicated using other query methods.

Example 1 of Median calculation with Pure SQL:

SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median

Example 2 of Median calculation with PERCENTILE_CONT

SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Score) OVER (PARTITION BY null) As Median FROM Posts 

The first query is very hard to translate into Linq (As SELECT TOP PERCENT is not supported). It requires to query the count first, then query the max value from top half and the min value from bottom half and average them)

Desired solution:

Accessing the SQL Server Analytic functions could be done using the EF.Functions extensions:

Example:

db.Posts.Select(x => EF.Functions.PercentileCont(0.5, x.Score, null)).First();

The above Linq expression would translate to Example 2 SQL.

Dependencies:

• Full support for custom provider aggregate functions #22957
• Support database window functions #12747

[roji]

Translating to PERCENTILE_CONT should be partially possible once we have support for custom provider aggregate functions (#22957). The partition notably wouldn't be covered (this is probably related to window functions, #12747).