Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add support for standard nested window functions in T-SQL

The ISO/IEC SQL:2016 standard defines a concept called nested window functions that allows you to nest two kinds of window functions as an argument of a window aggregate function. The idea is to allow you to interact with row markers representing strategic points in windowing elements like the beginning or end of the partition, beginning or end of the frame, the current outer row, and the current inner frame row.
The two standard nested window functions are the nested row number function and the nested value_of expression at row function.
The former allows you to refer in an argument of a window aggregate function to a row number at a specified row marker. Here’s the syntax of the nested row number function:

<agg_func>(< argument with ROW_NUMBER(<row marker>) >) OVER(<specification>)

You can use the following row markers:
• BEGIN_PARTITION
• END_PARTITION
• BEGIN_FRAME
• END_FRAME
• CURRENT_ROW (current outer row)
• FRAME_ROW (current inner frame row)

As an example for using a nested row number function, consider the following query against the Sales.OrderValues view (ER diagram can be found here: http://tsql.solidq.com/SampleDatabases/Diagrams/TSQLV5.jpg ) in the TSQLV5 sample database (source code can be found here: http://tsql.solidq.com/SampleDatabases/TSQLV5.zip):

SELECT orderid, custid, orderdate, val,
val -
AVG( CASE WHEN ROW_NUMBER(FRAME_ROW) NOT IN ( ROW_NUMBER(BEGIN_PARTITION), ROW_NUMBER(END_PARTITION) ) THEN val END )
OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS diff
FROM Sales.OrderValues;

This query computes the difference between the current order value, and the customer average, but excluding the customer’s first and last orders from the average.
As for the nested value_of expression at row function, this function allows you to interact in an argument of a window aggregate function with the value of an expression at any of the aforementioned row markers. Here’s the syntax of the function:

<agg_func>(< argument with
VALUE OF <expression> AT <row marker> [<delta>] [, <default>]
>) OVER(<specification>)

As you can see, you can specify a certain negative or positive delta with respect to the row marker, and optionally provide a default value in case a row doesn’t exist at the specified position.
As an example, suppose you wanted to compute the difference between the current order value, and the customer’s average, but excluding orders placed on the current order’s order date. What you need is something similar to a correlation within the window function. By referring to the row marker CURRENT_ROW, you interact with the current outer row, and this way you can emulate a correlation. Here’s the query that you would use to achieve this task:

SELECT orderid, custid, orderdate, val,
val - AVG( CASE WHEN orderdate <> VALUE OF orderdate AT CURRENT_ROW THEN val END )
OVER( PARTITION BY custid ) AS diff
FROM Sales.OrderValues;

The other useful thing about nested window functions is that you can use them to emulate full support for the RANGE window frame unit, which is missing in T-SQL. For example, according to the standard, you can use the following query to return the moving average order value of the customer’s orders in the last 14 days:

SELECT orderid, custid, orderdate, val,
AVG(val) OVER( PARTITION BY custid ORDER BY orderdate
RANGE BETWEEN INTERVAL '13' DAY PRECEDING AND CURRENT ROW ) AS movingavg14days
FROM Sales.OrderValues;

But this syntax isn’t supported in T-SQL. If we had support for nested window functions, you would have been able to achieve the task like so:

SELECT orderid, custid, orderdate, val,
AVG( CASE WHEN DATEDIFF(day, orderdate, VALUE OF orderdate AT CURRENT_ROW) BETWEEN 0 AND 13 THEN val END )
OVER( PARTITION BY custid ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS movingavg14days
FROM Sales.OrderValues;

These functions seem to be very powerful, and it would be good to have support for them in T-SQL.
Thanks for your consideration!

89 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Itzik Ben-Gan shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

5 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base