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 10 PRECEDING, 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

SELECT c.cust_id, t.calendar_month_desc,

TO_CHAR (SUM(amount_sold),'9,999,999,999') AS SALES,

TO_CHAR(AVG(SUM(amount_sold)) OVER (ORDER BY c.cust_id, t.calendar_month_desc ROWS 2 PRECEDING), '9,999,999,999') 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;

This query calculates the average sales of last 3 month.

Query 3 – Centered calculation using range and interval.

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;

Query 4 - Varying window size for each row by using a function.

The following statement for a hypothetical stock price database uses a user-defined function in its RANGE clause to set window size:

SELECT t_timekey, AVG(stock_price)

OVER (ORDER BY t_timekey RANGE fn(t_timekey) PRECEDING) av_price

FROM stock, time WHERE st_timekey = t_timekey

ORDER BY t_timekey;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061137/，如需轉載，請註明出處，否則將追究法律責任。