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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/638844/viewspace-1061137/