学习笔记[@more@]

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

DEPTNO	ENAME	SAL	DEPT_SAL	ALL_TOTAL	DEPT_AVG	ALL_AVG	MIN_SAL	MAX_SAL
10	I_RGJOB	210	210	210	210	210	210	210
10	I_LOBFRAG$_FRAGOBJ$	260	470	470	235	235	210	260
10	I_METHOD1	310	780	780	260	260	210	310
10	I_DIMLEVEL$_1	410	1190	1190	297.5	297.5	210	410
10	ALL_REGISTRY_BANNERS	660	1850	1850	370	370	210	660
20	ICOL$	20	20	1870	20	311.67	20	20
20	ARGUMENT$	70	90	1940	45	277.14	20	70
20	HIER$	420	510	2360	170	295	20	420
20	I_EXTERNAL_LOCATION1$	470	980	2830	245	314.44	20	470
20	I_APPLY_SOURCE_SCHEMA1	570	1550	3400	310	340	20	570

可以看到,对分组后的每一行, 都是跟当前行及组内的以前行去计算,包括求和,求平均数,求最大数和最小数. ALL_TOTAL由于没有分组.所有是所有的累计.

2. ratio_to_report函数

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

ENAME	SUM_SAL	RATIO
10	660	0.356756757
10	410	0.221621622
10	260	0.140540541
10	310	0.167567568
10	210	0.113513514
20	70	0.04516129
20	420	0.270967742
20	20	0.012903226
20	570	0.367741935
20	470	0.303225806

分组后,看每行在组内的占比

其它举例:

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

ITPub博客

Oracle分析函数-4

最新文章