SQL Windowing

Reuse of Window Definitions

Suppose that you need to invoke multiple window functions in the same query and part of the window specification (or all of it) is common to multiple functions. If you indicate the complete window specifications in all functions, the code can quickly get lengthy. Here’s an example illustrating the problem:

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,
  MIN(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_min_qty,
  MAX(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_max_qty
FROM Sales.EmpOrders;

Standard SQL has an answer to this problem in the form of a clause called WINDOW that allows naming a window specification or part of it; then you can refer to that name in other window definitions—ones used by window functions or even by a definition of another window name. This clause is conceptually evaluated after the HAVING clause and before the SELECT clause.

SQL Server doesn’t yet support the WINDOW clause. But according to standard SQL, you can abbreviate the preceding query using the WINDOW clause like so:

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

That’s quite a difference, as you can see. In this case, the window clause assigns the name W1 to a complete window specification with partitioning, ordering, and framing options. Then all four functions refer to W1 as their window specification. The WINDOW clause is actually quite sophisticated. As mentioned, it doesn’t have to name a complete window specification; rather, it can even name only part of it. Then a window definition can include a mix of named parts plus explicit parts. As an aside, the coverage of standard SQL for the WINDOW clause is a striking length of 10 pages! And trying to decipher the details is no picnic.

It would be great to see SQL Server add such support in the future, especially now that it has extensive support for window functions and people are likely to end up with lengthy window specifications.