Forum Stats

  • 3,828,007 Users
  • 2,260,847 Discussions
  • 7,897,427 Comments

Discussions

Add support for the SQL standard WINDOW clause

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Apr 23, 2020 1:01PM in Database Ideas - Ideas

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);

Lukas EderFranck PachotAndre SantosSven W.4897368e-a98e-460b-ad74-64a3c6fee44dfac586Naeel MaqsudovLudovicoCaldaraBPeaslandDBAsensoftNiels HeckerBeGindherzhaugaverillulohmannThorsten KettnerMettemusens2Peter HraškoWilliam RobertsonSamuel NitscheErik van RoonSentinelGregV
22
24 votes

Delivered · Last Updated

Comments

  • BEDE
    BEDE Oracle Developer Member Posts: 2,428 Gold Trophy

    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
    Lukas Eder Member Posts: 126 Bronze Badge

    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...

    Sentinel