Add support for the SQL standard WINDOW clause

Lukas Eder

When using a lot of window functions, repeating the window specifications may become tedious. This is why the SQL standard defines the WINDOW clause (currently implemented by PostgreSQL and Sybase SQL Anywhere), to specify windows for reuse across a SELECT statement. For example:

SELECT a, b, COUNT(*) OVER w, SUM(x) OVER w1

FROM t

WINDOW

  w1 AS (PARTITION BY e ORDER BY f DESC),

  w2 AS (PARTITION BY f)

This is really a very trivial feature and probably just exposes some already existing internals through the SQL language.

UPDATE: This has been implemented in Oracle 20c.

select ename, deptno, sal,<br/>  sum(sal) over (w1) sum_sal,<br/>  min(sal) over (w1) min_sal,<br/>  avg(sal) over (w1) avg_sal,<br/>  sum(sal) over (w2) cum_sal<br/>  from emp<br/>  window w1 as (partition by deptno),<br/>  w2 as (partition by deptno order by sal);<br/><br/>  select ename, deptno, sal,<br/>  sum(sal) over (w1 order by sal) cum_sal1,<br/>  sum(sal) over (w2) cum_sal2<br/>  from emp<br/>  window w1 as (partition by deptno),<br/>  w2 as (w1 order by sal);<br/><br/>  select ename, deptno, sal,<br/>  min(sal) over w1 min_sal_3,<br/>  max(sal) over w1 max_sal_3<br/>  from emp<br/>  window w1 as (partition by deptno order by sal <br/>  rows between 1 preceding and 1 following);

BEDE

This goes both ways... In a way it would be simple not to repeat the same (partition by... order by...), which is good. But, imagine you would have a really large SQL statement, with lots of columns and several joins and filter conditions in where clause. So: to see what w1 means or what w2 means, you may have to look some 100 lines of code below... Would this be nice? If you do have different windows, wouldn't this be more error prone than just copy&paste the same (partition by... order by...)? That is why I thing it may not be worth the effort of developing such a thing.

Lukas Eder

This goes both ways... In a way it would be simple not to repeat the same (partition by... order by...), which is good. But, imagine you would have a really large SQL statement, with lots of columns and several joins and filter conditions in where clause. So: to see what w1 means or what w2 means, you may have to look some 100 lines of code below... Would this be nice? If you do have different windows, wouldn't this be more error prone than just copy&paste the same (partition by... order by...)? That is why I thing it may not be worth the effort of developing such a thing.

Sure. Your argument is valid for every single time we use something called a "variable" or even a "function". Why not just inline everything and stop factoring things out . In a similar way, you could probably argue against the WITH clause, or views...