group by分组求和,统计,最大值,最小值等. group by可以完成的,over (partition by ...)一定可以做到,但over (partition by ...)可以做到的,group by 不一定做最到. over(partition by ...)最前还可以有更多的函数,如最常用的有Row_Number(),group by 就不可以做了
做一个实验,你看看:create table ttx(id int, v int)insert into ttx select 1,5 union all select 1,2 union all select 2,3 union all select 2,8 union all select 1,3 union all select 2,6 --group by的用法 select id, sum(v) as sum_v from ttx group by id /* id sum_v 1 10 2 17 */--over(partition by )的用法 select id, v, sum(v) over(partition by id) as sum_v from ttx /* id v sum_v 1 5 10 1 2 10 1 3 10 2 6 17 2 3 17 2 8 17 */
group by 用于分组汇总,即统计时用.over (partition by ...)一般用于row_number(),dense_rank()和rank()函数中,用于产生序号.
1、区别就是不同的写法,一般group by就是用来分组的,而over(partition by)用于窗口函数中进行分组,比如:row_number(),
dense_rank(),
rank(),
ntile()
等函数中,还有就是聚合函数:
sum(),
avg(),
count(),
max(),
min()2、两者之间的联系就是,都是进行分组的意思
group by可以完成的,over (partition by ...)一定可以做到,但over (partition by ...)可以做到的,group by 不一定做最到. over(partition by ...)最前还可以有更多的函数,如最常用的有Row_Number(),group by 就不可以做了
做一个实验,你看看:create table ttx(id int, v int)insert into ttx
select 1,5
union all select 1,2
union all select 2,3
union all select 2,8
union all select 1,3
union all select 2,6
--group by的用法
select id,
sum(v) as sum_v
from ttx
group by id
/*
id sum_v
1 10
2 17
*/--over(partition by )的用法
select id,
v,
sum(v) over(partition by id) as sum_v
from ttx
/*
id v sum_v
1 5 10
1 2 10
1 3 10
2 6 17
2 3 17
2 8 17
*/