Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Add support for the SQL standard WINDOW clause

Lukas EderJul 11 2016 — edited Apr 23 2020

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,
  sum(sal) over (w1) sum_sal,
  min(sal) over (w1) min_sal,
  avg(sal) over (w1) avg_sal,
  sum(sal) over (w2) cum_sal
  from emp
  window w1 as (partition by deptno),
  w2 as (partition by deptno order by sal);

  select ename, deptno, sal,
  sum(sal) over (w1 order by sal) cum_sal1,
  sum(sal) over (w2) cum_sal2
  from emp
  window w1 as (partition by deptno),
  w2 as (w1 order by sal);

  select ename, deptno, sal,
  min(sal) over w1 min_sal_3,
  max(sal) over w1 max_sal_3
  from emp
  window w1 as (partition by deptno order by sal
  rows between 1 preceding and 1 following);

Comments

Post Details

Added on Jul 11 2016
2 comments
1,110 views