分享
 
 
 

新手上路:Oracle分析函数学习笔记三

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

环境:windows 2000 server + Oracle8.1.7 + sql*plus

目的:以oracle自带的scott模式为测试环境,主要通过试验体会分析函数的用法。

类似 sum(...) over ... 的使用

1.原表信息:

SQL

break on deptno skip 1-- 为效果更明显,把不同部门的数据隔段显示。

SQL select deptno,ename,sal

2from emp

3order by deptno;

DEPTNO ENAME SAL

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

10 CLARK2450

KING 5000

MILLER 1300

20 SMITH 800

ADAMS1100

FORD 3000

SCOTT3000

JONES2975

30 ALLEN1600

BLAKE2850

MARTIN 1250

JAMES 950

TURNER 1500

WARD 1250

已选择14行。

2.先来一个简单的,注重over(...)条件的不同,使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,注重over (orderby ename)假如没有order by 子句,求和就不是“连续”的,放在一起,体会一下不同之处:

SQL

break on '' -- 取消数据分段显示

SQL select deptno,ename,sal,

2sum(sal) over (order by ename) 连续求和,

3sum(sal) over () 总和,-- 此处sum(sal) over () 等同于sum(sal)

4100*round(sal/sum(sal) over (),4) "份额(%)"

5from emp

6/

DEPTNO ENAME SAL 连续求和 总和份额(%)

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

20 ADAMS1100 110029025 3.79

30 ALLEN1600 270029025 5.51

30 BLAKE2850 555029025 9.82

10 CLARK2450 800029025 8.44

20 FORD 3000110002902510.34

30 JAMES 9501195029025 3.27

20 JONES2975149252902510.25

10 KING 5000199252902517.23

30 MARTIN 12502117529025 4.31

10 MILLER 13002247529025 4.48

20 SCOTT3000254752902510.34

20 SMITH 8002627529025 2.76

30 TURNER 15002777529025 5.17

30 WARD 12502902529025 4.31

已选择14行。

3.使用子分区查出各部门薪水连续的总和。注重按部门分区。注重over(...)条件的不同,

sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和

sum(sal) over (partition by deptno) 按部门求总和

sum(sal) over (order by deptno,ename) 不按部门“连续”求总和

sum(sal) over () 不按部门,求所有员工总和,效果等同于sum(sal)。

SQL

break on deptno skip 1-- 为效果更明显,把不同部门的数据隔段显示。

SQL select deptno,ename,sal,

2sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和

3sum(sal) over (partition by deptno) 部门总和,-- 部门统计的总和,同一部门总和不变

4100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",

5sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和

6sum(sal) over () 总和,-- 此处sum(sal) over () 等同于sum(sal),所有员工的薪水总和

7100*round(sal/sum(sal) over (),4) "总份额(%)"

8from emp

9/

DEPTNO ENAMESAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和总份额(%)

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

10 CLARK 2450 2450 875028 245029025 8.44

KING5000 7450 8750 57.14 74502902517.23

MILLER1300 8750 8750 14.86 875029025 4.48

20 ADAMS 1100 110010875 10.11 985029025 3.79

FORD3000 410010875 27.59128502902510.34

JONES 2975 707510875 27.36158252902510.25

SCOTT 30001007510875 27.59188252902510.34

SMITH80010875108757.361962529025 2.76

30 ALLEN 1600 1600 9400 17.022122529025 5.51

BLAKE 2850 4450 9400 30.322407529025 9.82

JAMES950 5400 9400 10.112502529025 3.27

MARTIN1250 6650 940013.32627529025 4.31

TURNER1500 8150 9400 15.962777529025 5.17

WARD1250 9400 940013.32902529025 4.31

已选择14行。

4.来一个综合的例子,求和规则有按部门分区的,有不分区的例子

SQL select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,

2sum(sal) over (order by deptno,sal) sum

3from emp;

DEPTNO ENAME SAL DEPT_SUMSUM

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

10 MILLER 1300 1300 1300

CLARK2450 3750 3750

KING 5000 8750 8750

20 SMITH 800800 9550

ADAMS1100 190010650

JONES2975 487513625

SCOTT30001087519625

FORD 30001087519625

30 JAMES 95095020575

WARD 1250 345023075

MARTIN 1250 345023075

TURNER 1500 495024575

ALLEN1600 655026175

BLAKE2850 940029025

已选择14行。

5.来一个逆序的,即部门从大到小排列,部门里各员工的薪水从高到低排列,累计和的规则不变。

SQL select deptno,ename,sal,

2sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,

3sum(sal) over (order by deptno desc,sal desc) sum

4from emp;

DEPTNO ENAME SAL DEPT_SUMSUM

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

30 BLAKE2850 2850 2850

ALLEN1600 4450 4450

TURNER 1500 5950 5950

WARD 1250 8450 8450

MARTIN 1250 8450 8450

JAMES 950 9400 9400

20 SCOTT3000 600015400

FORD 3000 600015400

JONES2975 897518375

ADAMS11001007519475

SMITH 8001087520275

10 KING 5000 500025275

CLARK2450 745027725

MILLER 1300 8

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有