oracle分析函数over partition by 和group by的区别

over partition by与group by 的区别

今天看到一个老兄的问题,
大概如下:
查询出部门的最低工资的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
1000 1
500 2
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,   c,   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,   d,SUM(d)   OVER(PARTITION   BY   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 ,salary ,dept from TableA
) a

where nRow=1
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值