group by和partition by区别小结
程序员文章站
2022-05-17 23:05:49
...
- group by是分组函数,partition by是分析函数(然后像sum()等是聚合函数);
- 在执行顺序上,对于group by而言
from > where > group by > having > order by
对于partition by而言,在执行完select之后,在所得结果集之上进行partition。 - 在group by后的结果集上使用聚合函数,会作用在分组下的所有记录上。而如果在partition结果上聚合,千万注意聚合函数是逐条累计运行结果的!
- group by将分组记录汇总成一条记录,具有去重效果
partition by显示所有数据,不会去重
实例说明
name,orderdate,cost
jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94
对于group by
select
name,sum(cost)
from test
group by name
对于partition by
select
name,orderdate,cost,sum(cost) over(partition by name)
from test
如果partition 加上order by
select
name,orderdate,cost,sum(cost) over(partition by name order by orderdate)
from test
结果
说明在分区内进行排序,也就是在相同name区间内排序