Oracle分析函式-4

1. sum,avg,max,min移動計算

select deptno,
ename,
sal,
sum(sal) over(partition by deptno order by sal) dept_sal,
sum(sal) over(order by deptno, sal) all_Total,
avg(sal) over(partition by deptno order by sal) dept_avg,
avg(sal) over(order by deptno, sal) all_avg,

min(sal) over(partition by deptno ) min_sal,
max(sal) over(partition by deptno order by sal ) max_sal
from hyf_t

可以看到,對分組後的每一行, 都是跟當前行及組內的以前行去計算,包括求和,求平均數,求最大數和最小數. ALL_TOTAL由於沒有分組.所有是所有的累計.

2. ratio_to_report函式

select deptno ename,
sum(sal),
ratio_to_report(sum(sal)) over(partition by deptno)
from hyf_T

分組後,看每行在組內的佔比

其它舉例:

Quote:

1 select bill_month,area_code,sum(local_fare) local_fare,

2 ratio_to_report(sum(local_fare)) over

3 ( partition by bill_month ) area_pct

4 from t

5* group by bill_month,area_code

SQL> break on bill_month skip 1

SQL> compute sum of local_fare on bill_month

SQL> compute sum of area_pct on bill_month

SQL> /

BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT

---------- --------- ---------------- ----------

200405 5761 13060.433 .171149279

5762 12643.791 .165689431

5763 13060.433 .171149279

5764 12487.791 .163645143

5765 25057.736 .328366866

********** ---------------- ----------

sum 76310.184 1

200406 5761 13318.930 .169050772

5762 12795.060 .162401542

5763 13318.930 .169050772

5764 13295.187 .168749414

5765 26058.460 .330747499

********** ---------------- ----------

sum 78786.567 1