【讨论】oracle 分组后如何求平均值？

batora · 发表于 2014-10-28 22:00

我需要统计一个作业计划下每个人的操作量，每个人都有自己的部门。
现在的需求是，我先要分开每个部门，然后把所有的工作量平均分给各个部门的人。
比如：

现在我查询出来的SQL结果如下
A          1             6000
B          1             6000
C          2             6000
D          2             6000
E          2             6000

我想根据上表的结果得到下面这个结果

人员    部门       作业量
A          1             3000
B          1             3000
C          2             2000
D          2             2000
E          2             2000
总作业量是6000，部门1的2个人平分6000，部门2的3个人平分6000。

oracle_cj · 发表于 2014-10-28 22:07

with t as (
SELECT 'A' name,'1'dept,'3000' work from dual union all
SELECT 'B' name,'1'dept,'3000' work from dual union all
SELECT 'C' name,'2'dept,'2000' work from dual union all
SELECT 'D' name,'2'dept,'2000' work from dual union all
SELECT 'E' name,'2'dept,'2000' work from dual union all
SELECT 'C' name,'4'dept,'1500' work from dual union all
SELECT 'D' name,'4'dept,'1000' work from dual union all
SELECT 'E' name,'4'dept,'3500' work from dual
)
SELECT T.*, SUM(WORK) OVER(PARTITION BY DEPT ORDER BY 1) NEW_WORK FROM T;

batora · 发表于 2014-10-28 22:22

oracle_cj 发表于 2014-10-28 22:07
with t as (
SELECT 'A' name,'1'dept,'3000' work from dual union all
SELECT 'B' name,'1 ...

第一次查出来的是全6000，，，
那个区分开的才是我想要的效果= =。我觉得你看反了。。

oracle_cj · 发表于 2014-10-28 22:29

是lz你搞反了，自己将结果编辑反了，说我看反了？那你为什么要重新编辑顶楼呢，此题不会做

oracle_cj · 发表于 2014-10-28 22:33

数据都造出来了，接着搞吧。。。
with t as (
SELECT 'A' name,'1'dept,'6000' work from dual union all
SELECT 'B' name,'1'dept,'6000' work from dual union all
SELECT 'C' name,'2'dept,'6000' work from dual union all
SELECT 'D' name,'2'dept,'6000' work from dual union all
SELECT 'E' name,'2'dept,'6000' work from dual union all
SELECT 'C' name,'4'dept,'6000' work from dual union all
SELECT 'D' name,'4'dept,'6000' work from dual union all
SELECT 'E' name,'4'dept,'6000' work from dual
)
SELECT t.*,WORK/COUNT(*)OVER(PARTITION BY dept ORDER BY 1) FROM t;