oracle上浮下浮分析函数_Oracle分析函数简析

最新推荐文章于 2023-02-24 09:22:50 发布
最新推荐文章于 2023-02-24 09:22:50 发布
阅读量102 收藏
点赞数
文章标签： oracle上浮下浮分析函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35621884/article/details/111886839
版权

oracle的分析函数over(Partition by...)

Sql代码

over(Partition by...) 一个超级牛皮的ORACLE特有函数。

最近工作中才接触到这个功能强大而灵活的函数。

oracle的分析函数over 及开窗函数

一：分析函数over

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

下面通过几个例子来说明其应用。

1 1：统计某商店的营业额。2 date sale3 1 20

4 2 15

5 3 14

6 4 18

7 5 30

8 规则：按天统计：每天都统计前面几天的总额9 得到的结果：10 DATE SALE SUM

11 ----- -------- ------

12 1 20 20 --1天

13 2 15 35 --1天＋2天

14 3 14 49 --1天＋2天＋3天

15 4 18 67.16 5 30 97.17

18 2:统计各班成绩第一名的同学信息19 NAME CLASS S20 ----- ----- ----------------------

21 fda 1 80

22 ffd 1 78

23 dss 1 95

24 cfe 2 74

25 gds 2 92

26 gf 3 99

27 ddd 3 99

28 adf 3 45

29 asdf 3 55

30 3dd 3 78

31

32 通过：33 --

34 select * from

35 (36 select name,class,s,rank()over(partition by class order by s desc) mm fromt237 )38 where mm=1

39 --

40 得到结果：41 NAME CLASS S MM42 ----- ----- ---------------------- ----------------------

43 dss 1 95 1

44 gds 2 92 1

45 gf 3 99 1

46 ddd 3 99 1

47

48 注意：49 1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果50 2.rank()和dense_rank()的区别是：51 --rank()是跳跃排序，有两个第二名时接下来就是第四名

52 --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

53

54

55 3.分类统计 (并显示信息)56 A B C57 ---- ----------------------

58 m a 2

59 n a 3

60 m a 2

61 n b 2

62 n b 1

63 x b 3

64 x b 2

65 x b 4

66 h b 3

67 select a,c,sum(c)over(partition by a) fromt268 得到结果：69 A B C SUM(C)OVER(PARTITIONBYA)70 ---- ------- ------------------------

71 h b 3 3

72 m a 2 4

73 m a 2 4

74 n a 3 6

75 n b 2 6

76 n b 1 6

77 x b 3 9

78 x b 2 9

79 x b 4 9

80

81 如果用sum，group by则只能得到82 A SUM(C)83 ------------------------

84 h 3

85 m 4

86 n 6

87 x 9

88 无法得到B列值89

90 ＝＝＝＝＝91 select * fromtest92

93 数据:94 A B C95 1 1 1

96 1 2 2

97 1 3 3

98 2 2 5

99 3 4 6

100

101

102 ---将B栏位值相同的对应的C 栏位值加总

103 select a,b,c, SUM(C) OVER (PARTITION BYB) C_Sum104 fromtest105

106 A B C C_SUM107 1 1 1 1

108 1 2 2 7

109 2 2 5 7

110 1 3 3 3

111 3 4 6 6

112

113 ---如果不需要已某个栏位的值分割,那就要用 null

114

115 eg: 就是将C的栏位值summary 放在每行后面116

117 select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum118 fromtest119

120 A B C C_SUM121 1 1 1 17

122 1 2 2 17

123 1 3 3 17

124 2 2 5 17

125 3 4 6 17

126

127 求个人工资占部门工资的百分比128

129 SQL> select * fromsalary;130

131 NAME DEPT SAL132 ---------- ---- -----

133 a 10 2000

134 b 10 3000

135 c 10 5000

136 d 20 4000

137

138 SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent fromsalary;139

140 NAME DEPT SAL PERCENT

141 ---------- ---- ----- ----------

142 a 10 2000 20

143 b 10 3000 30

144 c 10 5000 50

145 d 20 4000 100

146

147 二：开窗函数148 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：149 1：150 over(order by salary) 按照salary排序进行累计，orderby是个默认的开窗函数151 over(partition bydeptno)按照部门分区152 2：153 over(order by salary range between 5 preceding and 5following)154 每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5155 例如:对于以下列156 aa157 1

158 2

159 2

160 2

161 3

162 4

163 5

164 6

165 7

166 9

167

168 sum(aa)over(order by aa range between 2 preceding and 2following)169 得出的结果是170 AA SUM

171 ---------------------- -------------------------------------------------------

172 1 10

173 2 14

174 2 14

175 2 14

176 3 18

177 4 18

178 5 22

179 6 18

180 7 22

181 9 9

182

183 就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2的和184 对于aa=2来说 ，sum=1+2+2+2+3+4=14；185 又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9；186

187 3：其它：188 over(order by salary rows between 2 preceding and 4following)189 每行对应的数据窗口是之前2行，之后4行190 4：下面三条语句等效：191 over(order by salary rows between unbounded preceding andunbounded following)192 每行对应的数据窗口是从第一行到最后一行，等效：193 over(order by salary range between unbounded preceding andunbounded following)194 等效195 over(partition by null)196

197 常用的分析函数如下所列:198

199 row_number() over(partition by ... order by...)200 rank() over(partition by ... order by...)201 dense_rank() over(partition by ... order by...)202 count() over(partition by ... order by...)203 max() over(partition by ... order by...)204 min() over(partition by ... order by...)205 sum() over(partition by ... order by...)206 avg() over(partition by ... order by...)207 first_value() over(partition by ... order by...)208 last_value() over(partition by ... order by...)209 lag() over(partition by ... order by...)210 lead() over(partition by ... order by...)211

212 示例213 SQL> select type,qty fromtest;214

215 TYPE QTY216 ---------- ----------

217 1 6

218 2 9

219

220 SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 fromtest;221

222 TYPE QTY CNT2223 ---------- ---------- ------------

224 3 1/2

225 1 6 2/2

226 2 5 1/3

227 7 2/3

228 2 9 3/3

229

230 SQL> select * fromtest;231 ---------- -------------------------------------------------

232 1 11111

233 2 22222

234 3 33333

235 4 44444

236

237 SQL> select t.id,mc,to_char(b.rn)||'/'||t.id)e238 2 fromtest t,239 (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L240 4 where b.rn<=to_number(t.id)241 order byid242

243 ID MC TO_CHAR(B.RN)||'/'||T.ID244 --------- -------------------------------------------------- ---------------------------------------------------

245 1 11111 1/1

246 2 22222 1/2

247 2 22222 2/2

248 3 33333 1/3

249 3 33333 2/3

250 3 33333 3/3

251 44444 1/4 44444 2/4

252 4 44444 3/4CNOUG4 44444 4/4

253

254 10rows selected255

256 *******************************************************************

2，rank over 说明

排序：

---rank()over(order by 列名 排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4

select scoreid, studentid,COURSENAME,totalexamscore ,

rank()over(order by TOTALEXAMSCORE desc)orderbyNum

from SCORECOURSE a ,COURSESCORE b

where a.SCORECOURSEID = b.SCORECOURSEID

---dense_rank()over(order by 列名 排序)的结果是连续的，如果有4个人，其中有3个是并列第1名， 那么最后的排序结果如：1 1 1 2

select scoreid, studentid,COURSENAME,totalexamscore ,

dense_rank()over(order by TOTALEXAMSCORE desc)orderbyNum

from SCORECOURSE a ,COURSESCORE b

where a.SCORECOURSEID = b.SCORECOURSEID

----rank () OVER (PARTITION BY 列名 ORDER BY 列名 排序)使用分区方式获取每门课程的最高分

SELECT *

FROM (SELECT scoreid, studentid, coursename, TOTALEXAMSCORE,

rank () OVER (PARTITION BY coursename ORDER BY TOTALEXAMSCORE DESC)orderbynum

FROM scorecourse a, coursescore b

WHERE a.scorecourseid = b.scorecourseid and studentID = 'xxxxx')

WHERE orderbynum < 2

-----使用over实现成绩求和

-----SUM (totalexamscore) OVER (ORDER BY studentid) sum1  实现的是连续求和，如第一个学生的总评成绩是30，则sum1就展示为30，到第二个学生成绩出现的时候，则会依次累加

-----SUM (totalexamscore) OVER () sum2 就相当于是单纯的求和，和直接使用sum是一致的

SELECT scoreid, studentid, totalexamscore,

SUM (totalexamscore) OVER (ORDER BY studentid) sum1,

SUM (totalexamscore) OVER () sum2

FROM coursescore

注：这个案例想了很久也没想到很直观的描述，还请大家亲自去测试一下吧！！

语法：

rank() over (order by 排序字段 顺序)

rank() over (partition by 分组字段 order by 排序字段 顺序)

1.顺序：asc|desc  名次与业务相关：

示例：求优秀学员，成绩：降序  迟到次数：升序

2.分区字段：根据什么字段进行分区。

问题：分区与分组有什么区别?

•分区只是将原始数据进行名次排列(记录数不变),

•分组是对原始数据进行聚合统计(记录数变少,每组返回一条)。

注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的

(如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。

所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名 排序 nulls last)

这样只要排序字段为null,就会放在最后，而不会影响排序结果).

灰灰大狼
关注
oracle 百分位数,Oracle分析函数PERCENTILE_CONT，percentile函数
weixin_29970399的博客
04-04 2946
Oracle分析函数PERCENTILE_CONT，percentile函数查询各部门中薪水分布处于25%、50%、75%位置的人的薪水，percent_rank()是确定排行中的相对位置。create table EMP(EMPNO NUMBER(4) not null,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4...
ORACLE实现MySQL中inet_aton函数、inet_ntoa函数
weixin_44158741的博客
12-02 1362
目录MySQLinet_aton函数inet_ntoa函数ORACLE实现inet_aton函数inet_ntoa函数 MySQL inet_aton函数 inet_aton(expr) mysql> SELECT INET_ATON('192.168.1.88') address_number; +----------------+ | address_number | +----------------+ | 3232235864 | +----------------+ 1 row in
python函数返回列表、输出所有正数之和_函数对正数列表进行排序以首先返回奇数，然后返回偶数las...
weixin_39779004的博客
12-17 1180
我想写一个函数，它接受一个数字列表(正整数)并返回一个排序的数字列表，这样奇数第一，偶数最后例如：我的分类([1，2，3，4，5，6，7，8，9，10])=&gt；[1，3，5，7，9，2，4，6，8，10]我的分类([1，2])=&gt；[1，2]我的分类([2，1])=&gt；[1，2]我的分类([3，3，4])=&gt；[3，3，4]我的分类([90，45，6...
python函数返回列表、输出所有正数之和_python函数在数组中查找正数之和
weixin_39610188的博客
12-17 1518
i need to write a function that takes an array of numbers and finds the maximum sum of all the numbers. In other words, I need to find the sum of just the positive numbers. I wrote this, I'm getting "...
python函数返回列表、输出所有正数之和,python函数在数组中查找正数之和
weixin_42317626的博客
12-17 1223
i need to write a function that takes an array of numbers and finds the maximum sum of all the numbers. In other words, I need to find the sum of just the positive numbers. I wrote this, I'm getting "...
编写一个函数mysum，以整数列表为参数，返回所以元素之和。
weixin_58123489的博客
04-01 2748
def mysum(List): sum=0 for item in List: sum+=item return sum List=[1,2,3] res=mysum(List) print(res)
python函数返回列表、输出所有正数之和_用python产生（-19,19）的随机数，print出最大的两负数和最小的两个正数。Sampleoutputs$pythonquiz_1.py0Thege...
weixin_39766867的博客
12-17 452
用python产生(-19,19)的随机数，print出最大的两负数和最小的两个正数。Sample outputs$ python quiz_1.py 0The generated list is: [5, 7, -17, -3, 13, 12, 6, 0, 11, 3]The largest and second largest strictly negative integers are -3...
Oracle分析函数使用总结
02-01
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
Oracle过程与函数的区别分析
01-19
Oracle过程和函数相信大家都比较了解，下面就为您详细介绍Oracle过程和函数二者之间的区别，希望可以让您对Oracle过程和函数有更深的认识。 Oracle过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也...
Oracle 12c 之分析函数— FIRST_VALUE
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
10-12 1645
Oracle 12c 之分析函数— FIRST_VALUE FIRST_VALUE是一个分析函数，它返回一个有序的值集合中的第一个值。如果集合中的第一个值为NULL，则函数将返回NULL，除非指定忽略NULL。 下面我们来看看如何使用这个分析函数： 以Oracle 12c Sample中的HR用户为例，查找部门编号为90部门的员工的最低工资： SELECT employee_id, las...
python基础-函数
weixin_30823683的博客
03-21 325
函数 查看自带的函数 查看命令：cmd下输入python,输入dir(__builtins__),可输出自带的函数 再用help查看函数如何使用。 函数的规则 没有return 值时会返回None 练习：找出一个序列中第二大的数 方法一： 先排序，输出第二个 #conding=utf-8 def secondBiggest(a): a.sort() ...
oracle上浮下浮分析函数_【分析函数】Oracle分析函数之LAG和LEAD
weixin_35671421的博客
12-23 130
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。lag的语法如下：lead的语法如下：lead 和lag 的语法类似以下以lag为例进行讲解！lag(exp_str,offset,defval) over()exp_str 是要做对比的字段offset 是exp_str字段的偏移量 比如说 o...
初学Python利用列表求和函数（可接收无数个参数求和）
hongxheng的博客
09-16 5981
1.注意：append与直接使用下标赋值的区别 numbeer = []#定义空列表用[]而不是{} def summ(numbeer): c = 0 for x in range(0, numbeer.__len__()):#range中需要跟上具体数字 a = numbeer[x] c += a x += 1 retu...
Python每日一练(20230224) 列表奇偶拆分、随机生成车牌号、接雨水
最新发布
汉阳Hann's Home
02-24 2184
1. 列表奇偶拆分★ 　 2. 随机生成车牌号★ 　 3. 接雨水 ★★★
day6-列表作业
qq_67036231的博客
02-22 434
1. 基础题 已知一个数字列表，打印列表中所有的奇数 list1=[22,33,45,67,78] for x in list1: if x%2 !=0: print(x) 已知一个数字列表，打印列表中所有能被能被3整除但是不能被2整除的数 list2=[2,3,6,8,9,10] for x in list2: if x%3==0 and x%2 !=0: print(x) 已知一个数字列表，计算所有偶数的和 sum=0 list3=[2,
7、python入门-函数
xululu123的博客
06-02 455
python入门-函数调用函数 调用函数 1）要调用一个函数，需要知道函数的名称和参数 2）查看内置函数定义 python官网：http://docs.python.org/2/library/functions.html#abs 交互式命令行查看：例如通过 help(abs) 查看abs函数的帮助信息 ...
LeetCode-Part 2
Ashely的博客
07-28 595
大学三年都没写过除了hello world之外的代码 突然觉得算法so interesting #1295 统计位数为偶数的数字 给你一个整数数组nums，请你返回其中位数为偶数的数字的个数。 思路： 1.将数组中的每一个数字转为字符串 2.获取该字符串的长度 3.如果除以二的余数是0，则sum+1 代码： class Solution: def findNumbers(self, nums: List[int]) -> int: sum = 0 ...
python 实验八 函数 （下）
热门推荐
m0_51346837专栏
05-25 6万+
15.重复元素判定。编写一个函数，接受一个列表最为参数，如果一个元素在列表中出现了不止一次，则返回True，但不要改变源列表的值。最后在主程序中测试该函数。 def fun(ls): for n in ls: if ls.count(n) > 1: return True else: return False def fun2(ls): return len(set(ls)) < len(ls) ls = e
python（4）
yjhklxx的博客
04-11 7843
1、编写函数求三个整数的最大值，函数原型为 def max3(a, b, c) def max3(a, b, c): if a > b: if a > c: return a else: return c else: if b > c: return b else: return c a, b, c = in
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值