Oracle Reporting 5 - Windowing

Winodwing clause

: rows | range between ... and ....

ROWS - specifies the window in physical units (rows). For windows expressed in rows,the ordering expressions should be unique to produce deterministic results.
RANGE - specifies the window as a logical offset. A logical offset can be specified with constants such as RANGE 10PRECEDING, or an expression that evaluates to a constant, or by an interval specification like RANGE INTERVAL N DAY/MONTH/YEAR PRECEDING or an expression that evaluates to an interval.

If you omit the windowing_clause entirely, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The sum() over(partition by xxx order by yyyy) therefore is a cumulative aggregation.

If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING: to indicate that the window starts at the first row of the partition.
UNBOUNDED FOLLOWING: to indicate that the window ends at the last row of the partition.

Query 1 - cumulative aggregation using sum()

select deptno,ename,sal,
sum(sal) over(partition by deptno order by empno) cumulative_sal
from emp
order by deptno;

OR

select deptno,ename,sal,
sum(sal) over(partition by deptno order by empno
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_sal
from emp
order by deptno;

Query 2 - Moving aggregation

计算1999年过去3个月平均销售额

SELECT c.cust_id, t.calendar_month_desc, SUM(amount_sold) AS SALES,
round(AVG(SUM(amount_sold)) OVER (ORDER BY c.cust_id, t.calendar_month_desc ROWS 2 PRECEDING),1) AS MOVING_3_MONTH_AVG
FROM sales s, times t, customers c
WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
AND t.calendar_year=1999 AND c.cust_id IN (6510)
GROUP BY c.cust_id, t.calendar_month_desc
ORDER BY c.cust_id, t.calendar_month_desc;

Query 3 – Centered calculation using range and interval.

1999年圣诞周每天销售额，与三天平均销售额

SELECT t.time_id, TO_CHAR (SUM(amount_sold), '9,999,999,999') AS SALES,
AVG(SUM(amount_sold)) OVER (ORDER BY t.time_id RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) AS CENTERED_3_DAY_AVG
FROM sales s, times t
WHERE s.time_id=t.time_id AND t.calendar_week_number IN (51) AND calendar_year=1999
GROUP BY t.time_id
ORDER BY t.time_id;