partition by和group by对比

骑猪看日落 2021-07-04 21:41 480阅读 0赞

今天大概弄懂了`partition by`和`group by`的区别联系。

1.  `group by`是分组函数，`partition by`是分析函数（然后像`sum()`等是聚合函数）；
2.  在执行顺序上，

以下是常用sql关键字的优先级

from > where > group by > having > order by

而`partition by`应用在以上关键字之后，实际上就是在执行完`select`之后，在所得结果集之上进行`partition`。

1.  `partition by`相比较于`group by`，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似`excel`中的操作），而`group by`则只保留参与分组的字段和聚合函数的结果（类似`excel`中的`pivot`）。

**partition by**  
![在这里插入图片描述][2020040223565612.png]  
**group by**  
![在这里插入图片描述][20200402235731680.png]  
4. 如果在`partition`结果上聚合，千万注意聚合函数是逐条累计运行结果的！而在`group by`后的结果集上使用聚合函数，会作用在分组下的所有记录上。

数据如下，  
![在这里插入图片描述][20200402235817678.png]  
**SQL1**

select a.cc,a.item,sum(a.num)
    from table_temp a
    group by a.cc,a.item

**Result1**  
![在这里插入图片描述][20200402235900131.png]  
11条记录经`group by`后为10条，其中`cc='cn' and item='8.1.1'`对应的两条记录的`num`汇总成值`3`.

**SQL2**

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num asc) as amount
    from table_temp a
    group by a.cc,a.num;
    
    select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num desc) as amount
    from table_temp a
    group by a.cc,a.num;

Result2  
![在这里插入图片描述][20200403000048430.png]  
![在这里插入图片描述][20200403000102136.png]  
两个`sql`的唯一区别在于`a.num`的排序上，但从结果红框中的数据对比可以看到`amount`值并不相同，且第二个结果集`amount`并不都是最小值1。

在这里就是要注意将聚合函数用在`partition`后的结果集上时，聚合函数是逐条累积计算值的！

其实`partition by`常同`row_number() over`一起使用，

select a.*, row_number() over (partition by a.cc,a.item order by a.num desc) as seq
    from table_temp a

![在这里插入图片描述][20200403000414779.png]  
清醒时做事，糊涂时读书，大怒时睡觉，独处时思考; 做一个幸福的人，读书，旅行，努力工作，关心身体和心情，成为最好的自己 – 共勉

--------------------

## `mssql sqlserver over(partition by)`同`group by`之间的区别 ##

摘要:  
下文通过举例的方式分析 `over(partition by)`同`group by` 之间的区别，如下所示:  
实验环境:`sqlserver 2008 R2`

### 一、`over(partition by)`同`group by`功能简介 ###

`over` 函数 配合聚合函数(`max、min、sum、avg、count`等)或`row_number`等函数，可以在不改变原显示数据的情况下，新增一列作为聚合函数的计算值；  
`group by` 子句只能同聚合函数(`max、min、sum、avg、count`),对相关列进行分组，只能展示出分组列和聚合列的数据。  
`over(partition by)`比`group by` 具有更多的用武之地，具有更高级的功能

### 二、`over(partition by)`同`group by`举例说明 ###

create table test(keyId int identity,sort varchar(10),qty int)
    go
    insert into test(qty,sort)values
    (1,'a'),(2,'a'),(3,'b'),(8,'c'),(9,'c')
    go
    select *,sum(qty) over(partition by sort) as [分组小计]
    ,sum(qty) over()  as  [总计] from test 
    order by keyId desc 
    
    select sort ,sum(qty) from test 
    group by sort 
    go
    truncate table test 
    drop     table test

![mssql\_sqlserver\_over同groupby对比][mssql_sqlserver_over_groupby]`mssql_sqlserver_over`同`groupby`对比

[2020040223565612.png]: /images/20210704/2d2b180aa54d4a36a862023f791b2bfc.png
[20200402235731680.png]: /images/20210704/5cdc328947144b0fa26685511ae01b02.png
[20200402235817678.png]: /images/20210704/56869d0b58d7407ea1d09901952e0d46.png
[20200402235900131.png]: /images/20210704/15b700fabfa5412eb6d0c81038bf5e74.png
[20200403000048430.png]: /images/20210704/843b0e05953d4ec9bd91f1c402e9ccb3.png
[20200403000102136.png]: /images/20210704/98c20a3d36de4c759e7a6a0334a2bb63.png
[20200403000414779.png]: /images/20210704/d04bf68a74f44c3babd33ad49f98c060.png
[mssql_sqlserver_over_groupby]: /images/20210704/bea887e6a42b4c3b900ce2e43146ad30.png