oracle 分组分析型函数,oracle分析函数over partition by 和group by的区别-CSDN博客

今天看到一个老兄的问题，

大概如下：

查询出部门的最低工资的userid 号

表结构：

D号工资部门

userid salary dept

1 2000 1

2 1000 1

3 500 2

4 1000 2

有一个高人给出了一种答案：

SELECT MIN (salary) OVER (PARTITION BY dept ) salary,dept

FROM ss

运行后得到：

1000 1

500 2

楼主那位老兄一看觉得很高深。大叹真是高人阿~

我也觉得这位老兄实在是高啊。

但我仔细研究一下发现那位老兄对PARTITION BY的用法理解并不深刻。并没有解决楼主的问题。

大家请看我修改后的语句

SELECT userid,salary,dept,MIN (salary) OVER (PARTITION BY dept ) salary

FROM ss

运行后的结果：

userid salary dept MIN (salary) OVER (PARTITION BY dept )

1 2000 1 1000

2 1000 1 1000

3 500 2 500

4 1000 2 500

大家看出端倪了吧。

高深的未必适合。

一下是我给出的答案：

SELECT * FROM SS

INNER JOIN (SELECT MIN(SALARY) AS SALARY,DEPT FROM SS GROUP BY DEPT) SS2

USING(SALARY,DEPT)

运行后的结果：

salary dept userid

1000 1 2

500 2 3

由此我想到总结一下group by和partition by的用法

group by是对检索结果的保留行进行单纯分组，一般总爱和聚合函数一块用例如AVG()，COUNT()，max()，main()等一块用。

partition by虽然也具有分组功能，但同时也具有其他的功能。

它属于oracle的分析用函数。

借用一个勤快人的数据说明一下：

sum() over (PARTITION BY ...) 是一个分析函数。他执行的效果跟普通的sum ...group by ...不一样，它计算组中表达式的累积和，而不是简单的和。

表a，内容如下：

B C D

02 02 1

02 03 2

02 04 3

02 05 4

02 01 5

02 06 6

02 07 7

02 03 5

02 02 12

02 01 2

02 01 23

select b,c,sum(d) e from a group by b,c

得到：

B C E

02 01 30

02 02 13

02 03 7

02 04 3

02 05 4

02 06 6

02 07 7

而使用分析函数得到的结果是：

SELECT b,d,SUM(d) OVER(PARTITION BY b,c ORDER BY d) e FROM a

B C E

02 01 2

02 01 7

02 01 30

02 02 1

02 02 13

02 03 2

02 03 7

02 04 3

02 05 4

02 06 6

02 07 7

结果不一样，这样看还不是很清楚，我们把d的内容也显示出来就更清楚了：

SELECT b,c ORDER BY d) e FROM a

B C D E

02 01 2 2 d=2,sum(d)=2

02 01 5 7 d=5,sum(d)=7

02 01 23 30 d=23,sum(d)=30

02 02 1 1 c值不同，重新累计

02 02 12 13

02 03 2 2

02 03 5 7

02 04 3 3

02 05 4 4

02 06 6 6

02 07 7 7

------------------------------------最佳答案--------------------------- select a.* from ( 　　select row_number() over(partition by dept order by a.salary asc) nRow,userid,dept from TableA ) a where nRow=1

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。