ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 753|回复: 2

[笔记] over partition by与group by 的区别

[复制链接]
认证徽章
论坛徽章:
0
跳转到指定楼层
1#
发表于 2017-11-21 14:52 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
今天看到一个老兄的问题,
大概如下:
查询出部门的最低工资的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
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
2#
发表于 2017-11-21 22:38 | 只看该作者
这就是最浅显的分析函数,那个高人根本就是在胡闹。
假设每个部门只有一个人是最低工资,可以这么写:
SELECT dept,MIN(userid) KEEP(DENSE_RANK FIRST ORDER BY salary) FROM ss

如果不止一人,
SELECT * FROM (SELECT ss.*,RANK() OVER(PARTITION BY dept ORDER BY salary) rnk FROM ss) WHERE rnk=1;

当然也可以用你自己的分组后连接的写法。

使用道具 举报

回复
论坛徽章:
2
2015年新春福章
日期:2015-03-04 14:55:132015年新春福章
日期:2015-03-06 11:59:47
3#
发表于 2017-11-23 23:39 | 只看该作者
就是来支持的呢。。。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表