SQL Server聚合函數與聚合開窗函數

以下麵這個表的數據作為示例。

什麼是聚合函數？

聚合函數：聚合函數就是對一組值進行計算後返回單個值（即分組）。聚合函數在計算時都會忽略空值(null)。

所有的聚合函數均為確定性函數。即任何時候使用一組相同的輸入值調用聚合函數執行後的返回值都是相同的，無二義性。

COUNT（統計函數）：COUNT函數可以用來返回所有選中行的總行數，它有一個參數，可以是 '*'（即所有列）、'1'（效果同前者類似）和具體的列名。

　　　　　列名前面還可以加上 DISTINCT 關鍵字（即去掉重覆項）。返回值類型為： int

1     select count(1) from Student    -- 返回 9
2 
3     select count(S_Sex) from Student    -- 返回 9
4 
5     select count(distinct S_Sex) from Student    -- 返回 2，即去重之後性別隻有兩種

COUNT_BIG（統計函數）：COUNT_BIG函數的使用方法和COUNT函數相同，只不過返回值為 bigint 數據類型。

註意：count(欄位名)，如果欄位名為NULL，則count函數不會統計。

SUM（求和函數）：SUM函數用於求和，計算指定列中所有不為空（null）的總和。返回值類型為： int

1 --求所有學員的身高總和,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select SUM(convert(int,S_Height)) from Student    -- 返回 1588
3 
4     --求不同身高的學員的身高總和（去掉重覆項）
5     select SUM(distinct convert(int,S_Height)) from Student -- 返回 1403

AVG（求平均值函數）：AVG函數用於求平均值，計算指定列中所有不為空（null）的平均值。返回值類型為： int

1     --求所有學員的平均身高,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select AVG(convert(int,S_Height)) from Student    -- 返回 176
3 
4     --求不同身高的學員的平均身高（去掉重覆項）
5     select AVG(distinct convert(int,S_Height)) from Student -- 返回 175
6 
7     --求男同學的平均身高
8     select AVG(convert(int,S_Height)) from Student where S_Sex='男'    -- 返回 177

MAX（最大值函數）：MAX函數用於計算最大值，可以使用於numeric、char、varchar、money、smallmoney、或datetime列，但不能用於bit列。返回值類型為： int

1     --求所有學員的最大身高,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select MAX(convert(int,S_Height)) from Student    -- 返回 190
3 
4     --求女同學的最大身高
5     select MAX(convert(int,S_Height)) from Student where S_Sex='女'    -- 返回 183

MIN（最小值函數）：MIN函數用於計算最小值，可以使用於numeric、char、varchar、money、smallmoney、或datetime列，但不能用於bit列。返回值類型為： int

1     --求所有學員的最小身高,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select MIN(convert(int,S_Height)) from Student    -- 返回 155
3 
4     --求女同學的最小身高
5     select MIN(convert(int,S_Height)) from Student where S_Sex='女'    -- 返回 165

STDEV（標準偏差值函數）：STDEV函數用於計算標準偏差值，即每一個數值與平均值的標準差。返回值類型為：float

1     --求所有學員的身高標準差,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select STDEV(convert(int,S_Height)) from Student    -- 返回 11.2928197443234
3 
4     --求女同學的身高標準差
5     select STDEV(convert(int,S_Height)) from Student where S_Sex='女'    -- 返回 7.88986691902975

VAR（方差值函數）：VAR函數用於計算方差值。返回值類型為：float

1     --求所有學員的身高方差,因為欄位類型是 varchar 類型，所以需要轉換一下
2     select VAR(convert(int,S_Height)) from Student    -- 返回 127.527777777781
3 
4     --求女同學的身高方差
5     select VAR(convert(int,S_Height)) from Student where S_Sex='女'    -- 返回 62.25

HAVING：HAVING子句僅用於帶有 GROUP BY 子句的查詢語句中，WHERE子句用於每一行數據的篩選（在變成一個組的某一部分之前），而HAVING子句用於分組的聚合值的篩選。

1 --查詢身高至少有一個相同的值
2     select S_Height,count(S_Height) 
3     from Student
4     group by S_Height
5     having count(1)>1

聚合開窗函數：

聚合函數加上 over() 開窗函數就是聚合開窗函數。

下麵看兩個示例：

1 　　select S_StuNo,S_Name,S_Height,
2     sum(convert(int,S_Height)) over(order by S_Height) 
3     from Student

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

1 　　select S_StuNo,S_Name,S_Height,
2     sum(convert(int,S_Height)) over(partition by S_Height order by S_Height) 
3     from Student

以上是使用 partition by 指定分區的，所以會先分區，再聚合，因為 S_Height 這一列只有兩條數據的值是相同的，所以這兩條數據為一個分區，其他全部一條數據一個分區，所以只有相同值的兩條數據的值為聚合值。

1     select S_StuNo,S_Name,S_Height,
2     count(S_Height) over(order by S_Height) 
3     from Student

可以看到上面的數據是沒有 7 的，而是直接是 6 跳到 8，我猜可能是因為欄位的值相同的存在2條。

1     select S_StuNo,S_Name,S_Height,
2     count(S_Height) over(partition by S_Height order by S_Height) 
3     from Student

因為學號為 012 和 013 的 S_Height 值相同，存在一個分區，所以是2條，其他都是一個分區一條數據。

1 　　select S_StuNo,S_Name,S_Height,
2     AVG(convert(int,S_Height)) over(order by S_Height) 
3     from Student

以上未使用 partition by 子句指定分區，可以看出第一條數據為當前行的平均值，第二條數據為前面所有行和當前行的平均值，以此類推。

1 　　select S_StuNo,S_Name,S_Height,
2     AVG(convert(int,S_Height)) over(partition by S_Height order by S_Height) 
3     from Student

以上使用 partition by 子句指定了分區，所以是在每個分區進行計算平均值。

其它聚合開窗函數使用方法類似，就不記了。