sqlserver之group by 与over函数

╰+哭是因爲堅強的太久メ 2021-06-26 16:06 777阅读 0赞

`group by` 函数主要用来对数据进行分组，`over()`函数则是一个“开窗函数”，它更多的是与聚合函数如：`sum()、max()、min()、avg()、count()`等函数以及排名函数如：`row_number()、rank()、dense_rank()、ntile()`函数结合使用。

## 1.`group by` 函数 ##

原始数据如下，数据表名为`hr.employeee`  
![在这里插入图片描述][2021041622314775.png]  
对以上数据按照`city`字段进行分组，并计算了每组中存在的行数：

select city,count(city)as n
     from hr.employeee
     group by city;

分组结果：  
![在这里插入图片描述][2021041713453063.png]  
根据以上结果，`London`这个值在原始数据中出现了4次，该组的行数为4。

## 2.`over()`函数 ##

一般来说，当使用了`group by` 进行分组查询，select查询阶段出现的`columnlists`如果没有出现在`group by` 后作为分组依据，就必须被包含在聚合函数中。但是往往在书写的时候就会忘记这个限制。`over()`函数则很好的解决了这个问题，该函数能够实现分组的效果。

原始数据如下（总共有830行）：  
![在这里插入图片描述][20210416223310462.png]  
进行以下查询：

select distinct val,row_number()over(order by val)as rownum
    from sales.ordervalues

![在这里插入图片描述][2021041713460839.png]  
运行结果仍然是830行，但实际该数据是存在5行的重复数据，所以说，明明在select阶段使用`distinct`取不同值，为何会没作用呢？？？

T-SQL语言基础这本书是这样解释的：`row_number`函数是在`distinct`子句之前处理的，当其为数据分配了唯一的行号后，再处理`distinct`子句，所以这时不会有任何重复的行。 （还未理解透，`distinct`是对`val`做处理，只要`val`存在重复值就剔除呀，难道不是这样的吗？–因为`distinct`是对其后的两列数据进行去重的！）

**这也说明，在同一`select`子句中不能同时使用`distinct`和`row_number()`函数，因为`distinct`会失效！！！**

要想得到不含重复值的数据，可以进行以下查询：

select  val,row_number()over(order by val)as rownum
    from sales.ordervalues
    group by val;

![在这里插入图片描述][20210417134725651.png]

这个时候就筛除了5行重复数据。

以下情形值得注意：

--代码1--分组之后计算每组的行数
    select val,count(val) as n from sales.ordervalues group by val;--注意查看分组后val值，这时已经达到去重的效果了
    
    --代码2--利用over函数达到分组效果，partition by对某列字段分区
    select val,count(val) over(partition by val ) as num from sales.ordervalues
    
    --代码3--代码2和3进行对比，注意区别
    select val,count(val) over(partition by val ) as num from sales.ordervalues group by val;

代码1：利用`group by` 子句进行分组查询，并计算了每组的行数，观察结果发现，`group by`起到了去重的功效。  
![在这里插入图片描述][20210417135506253.png]  
代码2：利用`over`函数达到分组效果，`partition by`对某列字段分区，并计算分区后每组的行数，这种情况下并没有去重的效果。  
![在这里插入图片描述][20210417135527440.png]

代码3：由于`group by` 处理顺序优于`select`，前面说到`group by`具有去重功效，每组数据只有唯一值！因此再进行`over`函数计算每组行数只有一个结果。  
![在这里插入图片描述][20210417135611575.png]  
再来一组查询对比，当在`over`函数中同时指定`partition by` 和`order by` 的字段为同一个时，排序失效：

--按照val降序排列失效！！！
    select val,count(val) over(partition by val order by val desc ) as num from sales.ordervalues;
    
    select val,count(val) over(partition by val ) as num from sales.ordervalues order by val desc ;

![在这里插入图片描述][2021041714012180.png]

以上按照`val`降序排列失效的原因在于，`partition by` 分区后的数据即按照了一定的顺序（升序）排列了，再使用`order by` 排序就会失效（个人理解）：

select val from sales.ordervalues 
    
    select val from sales.ordervalues group by val;

![在这里插入图片描述][20210417140223542.png]  
![在这里插入图片描述][20210417140259967.png]  
以上对比查询看到，`group by`不仅具有去重功效，还有按照升序排列数据的功能(单列数据查询）。

[2021041622314775.png]: /images/20210626/0367a0566c60405e9b704dd5d874408e.png
[2021041713453063.png]: /images/20210626/8fca372c06c1463ea3de27c2a62288f7.png
[20210416223310462.png]: /images/20210626/cb03a0ff114e4437bf16dbdf993007a0.png
[2021041713460839.png]: /images/20210626/76eacc525f224edea359e12a151ab120.png
[20210417134725651.png]: /images/20210626/df0fcbbee7ae4a37ac8ff4c35eb367fd.png
[20210417135506253.png]: /images/20210626/08e344ba97194f6bbc1b06621af99ab7.png
[20210417135527440.png]: /images/20210626/9fcbdfc8a150408bba14acc7131686f9.png
[20210417135611575.png]: /images/20210626/62ec5a2381224611a062aa8ac024d4f4.png
[2021041714012180.png]: /images/20210626/3c334bde6136490d838586e9e0730652.png
[20210417140223542.png]: /images/20210626/18e922bbddc34a6b8b1803e791f08f42.png
[20210417140259967.png]: /images/20210626/17cf1b57216f4ecbb6b7eb6f649abcc9.png