Home Dashboard Directory Help
Search

Window Functions (OVER Clause) - Reuse of Window Definitions with WINDOW Clause by Itzik Ben-Gan


Status: 

Active


153
2
Sign in
to vote
Type: Suggestion
ID: 600499
Opened: 9/17/2010 11:52:38 AM
Access Restriction: Public
0
Workaround(s)
view

Description

This item is related to: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391, only the former suggestion wasn't standard whereas the current one is. Therefore the current is preferred.

With several window functions that rely on the same window definition (or part of it), there's a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable.
For example, instead of:

SELECT empid, ordermonth, qty,
SUM(qty) OVER ( PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW ) AS run_sum_qty,
AVG(qty) OVER ( PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW ) AS run_avg_qty,
FROM Sales.EmpOrders;

You would write:

SELECT empid, ordermonth, qty,
SUM(qty) OVER W1 AS run_sum_qty,
AVG(qty) OVER W1 AS run_avg_qty,
FROM Sales.EmpOrders
WINDOW W1 AS ( PARTITION BY empid
             ORDER BY ordermonth
             ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );
Details
Sign in to post a comment.
Posted by Microsoft on 3/2/2012 at 1:18 PM
Reuse of Window Definitions with WINDOW Clause
This has been rolled up into our "Window Aggregates Enhancements" DCR for future consideration. Thank you for reporting it. All the information you provided has been captured for future reference.

Thanks,
Marc Friedman
Posted by Bryan St on 3/21/2011 at 8:27 PM
I'm currently migrating an established application from another database platform to SQL Server. There's a big, powerful chunk of the app missing due to the absence of the WINDOW clause functionality.

I wouldn't even want to think of how to implement this type of processing in SQLCLR, if that's even possible.

And if WINDOW is standard SQL, shouldn't it be included in the Microsoft product? Competitors have it.
Posted by Microsoft on 9/27/2010 at 2:23 PM
Hi Itzik,
Thanks for your feedback on WINDOW clause. We will consider it for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.