SQL Server聚合函数与聚合开窗函数

周翔

　　以下面这个表的数据作为示例。

　　什么是聚合函数？
　　聚合函数：聚合函数就是对一组值进行计算后返回单个值（即分组）。聚合函数在计算时都会忽略空值(null)。
　　所有的聚合函数均为确定性函数。即任何时候使用一组相同的输入值调用聚合函数执行后的返回值都是相同的，无二义性。
　　COUNT（统计函数）：COUNT函数可以用来返回所有选中行的总行数，它有一个参数，可以是 '*'（即所有列）、'1'（效果同前者类似）和具体的列名。
　　列名前面还可以加上 DISTINCT 关键字（即去掉重复项）。返回值类型为： int
　　

     select count(1) from Student　　

　　
     select count(S_Sex) from Student   
　　

　　
     select count(distinct S_Sex) from Student   
　　

　　COUNT_BIG（统计函数）：COUNT_BIG函数的使用方法和COUNT函数相同，只不过返回值为 bigint 数据类型。
　　注意：count(字段名)，如果字段名为NULL，则count函数不会统计。
　　SUM（求和函数）：SUM函数用于求和，计算指定列中所有不为空（null）的总和。返回值类型为： int
　　

　　
     select SUM(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select SUM(distinct convert(int,S_Height)) from Student
　　

　　AVG（求平均值函数）：AVG函数用于求平均值，计算指定列中所有不为空（null）的平均值。返回值类型为： int
　　

　　
     select AVG(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select AVG(distinct convert(int,S_Height)) from Student
　　

　　
     
　　
     select AVG(convert(int,S_Height)) from Student where S_Sex='男'   
　　

　　MAX（最大值函数）：MAX函数用于计算最大值，可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。返回值类型为： int
　　

　　
     select MAX(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select MAX(convert(int,S_Height)) from Student where S_Sex='女'   
　　

　　MIN（最小值函数）：MIN函数用于计算最小值，可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。返回值类型为： int
　　

　　
     select MIN(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select MIN(convert(int,S_Height)) from Student where S_Sex='女'   
　　

　　STDEV（标准偏差值函数）：STDEV函数用于计算标准偏差值，即每一个数值与平均值的标准差。返回值类型为：float
　　

　　
     select STDEV(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select STDEV(convert(int,S_Height)) from Student where S_Sex='女'   
　　

　　VAR（方差值函数）：VAR函数用于计算方差值。返回值类型为：float
　　

　　
     select VAR(convert(int,S_Height)) from Student   
　　

　　
     
　　
     select VAR(convert(int,S_Height)) from Student where S_Sex='女'   
　　

　　HAVING：HAVING子句仅用于带有 GROUP BY 子句的查询语句中，WHERE子句用于每一行数据的筛选（在变成一个组的某一部分之前），而HAVING子句用于分组的聚合值的筛选。
　　

　　
     select S_Height,count(S_Height)
　　
     from Student
　　
     group by S_Height
　　
     having count(1)>1
　　

　　聚合开窗函数：
　　聚合函数加上 over() 开窗函数就是聚合开窗函数。
　　下面看两个示例：
　　

　　select S_StuNo,S_Name,S_Height,　　

     sum(convert(int,S_Height)) over(order by S_Height)　　

     from Student　　

　　以上是未使用 partition by 指定分区的，可以看出，聚合列的值，第一行的值，为当前行，第二行的值为前面的所有行和当前行的值，以此类推。
　　

　　select S_StuNo,S_Name,S_Height,　　

     sum(convert(int,S_Height)) over(partition by S_Height order by S_Height)　　

     from Student　　

　　以上是使用 partition by 指定分区的，所以会先分区，再聚合，因为 S_Height 这一列只有两条数据的值是相同的，所以这两条数据为一个分区，其他全部一条数据一个分区，所以只有相同值的两条数据的值为聚合值。
　　

     select S_StuNo,S_Name,S_Height,　　

     count(S_Height) over(order by S_Height)　　

     from Student　　

　　可以看到上面的数据是没有 7 的，而是直接是 6 跳到 8，我猜可能是因为字段的值相同的存在2条。
　　

     select S_StuNo,S_Name,S_Height,　　

     count(S_Height) over(partition by S_Height order by S_Height)　　

     from Student　　

　　因为学号为 012 和 013 的 S_Height 值相同，存在一个分区，所以是2条，其他都是一个分区一条数据。
　　

　　select S_StuNo,S_Name,S_Height,　　

     AVG(convert(int,S_Height)) over(order by S_Height)　　

     from Student　　

　　以上未使用 partition by 子句指定分区，可以看出第一条数据为当前行的平均值，第二条数据为前面所有行和当前行的平均值，以此类推。
　　

　　select S_StuNo,S_Name,S_Height,　　

     AVG(convert(int,S_Height)) over(partition by S_Height order by S_Height)　　

     from Student　　

　　以上使用 partition by 子句指定了分区，所以是在每个分区进行计算平均值。
　　其它聚合开窗函数使用方法类似，就不记了。