[SQL筆記] 聚合函數

R

• count 求數量
• max 求最大值
• min 求最小值
• sum 求和

• avg 求平均值

• round() 小數第幾位

  select ROUND(25.555,2) --25.560
  

• datediff 求時間差

  select DATEDIFF(year,'1991-1-1','1993-3-3') --2
  DATEDIFF(year,EmployeeBirth,getdate())
  

• 求年齡
  • 當前年-生日年：year(getdate())-year(EmployeeBirth)
  • 使用datadiff：DATEDIFF(year,EmployeeBirth,getdate())
• 自定義欄 select '上海/台北的女性' 描述 from Employee

範例：count,max,min,sum,avg,round

--求員工總人數
select count(*) 人數 from Employee
--求最大值，最高工資
select max(EmployeeSalary) 最高工資 from Employee
--求最小值，最低工資
select min(EmployeeSalary) 最低工資 from Employee
--求和，所有員工的工資總和
select sum(EmployeeSalary)工資總和 from Employee
--求平均值，所有員工的工資平均值
select ROUND(avg(EmployeeSalary),2) 平均工資 from Employee

--數量、最大值、最小值、總和、平均值，在一行顯示
select count(*) 人數 ,max(EmployeeSalary) 最高工資, min(EmployeeSalary) 最低工資, sum(EmployeeSalary)工資總和, ROUND(avg(EmployeeSalary),2) 平均工資 from Employee

--查出台北地區的員工人數數量、最大值、最小值、總和、平均值
select count(*) 人數 ,max(EmployeeSalary) 最高工資, min(EmployeeSalary) 最低工資, sum(EmployeeSalary)工資總和, ROUND(avg(EmployeeSalary),2) 平均工資 from Employee
where EmployeeAddress='台北'

範例1:求出工資 比 平均工資高的員工資料

聚合函數不能直接在where後面

--求出工資 比 平均工資高的員工資料
--select * from Employee where EmployeeSalary > 平均工資
select * from Employee where EmployeeSalary >
(select AVG(EmployeeSalary) from Employee)

範例2:時間差：求數量、年齡最大值、年齡最小值、年齡總和、年齡平均值，在一行顯示

思路：

• 取得當前年份select year(getdate())。getdate當前時間
• 取得年齡=當前年-出生年 year(getdate())-year(EmployeeBirth)
• 最高、最低、總和、平均 max、min、sum、avg

方案一：

--求數量、年齡最大值、年齡最小值、年齡總和、年齡平均值，在一行顯示
select count(*) 數量, 
max(year(getdate())-year(EmployeeBirth)) 年齡最高, 
min(year(getdate())-year(EmployeeBirth)) 年齡最低,
sum(year(getdate())-year(EmployeeBirth)) 年齡總和,
avg(year(getdate())-year(EmployeeBirth)) 年齡平均值
from Employee

方案二：使用時間差DATEDIFF

使用時間差DATEDIFF

• 參數1: 時間差的單位, 可以改成相應的單位-> year, month, day
• 參數2: 開始時間, 參數3: 結束時間，後面參數會減去前面參數

--參數1: 時間差的單位, 可以改成相應的單位-> year, month, day
--參數2: 開始時間, 參數3: 結束時間，後面參數會減去前面參數
select DATEDIFF(year,'1991-1-1','1993-3-3') --2

思路：

• 求年齡 DATEDIFF(year,EmployeeBirth,getdate())
• 求最高、最低、總和、平均，再加上 max、min、sum、avg

--求數量、年齡最大值、年齡最小值、年齡總和、年齡平均值，在一行顯示
--方案一
select count(*) 數量,
max(year(getdate())-year(EmployeeBirth)) 年齡最高, 
min(year(getdate())-year(EmployeeBirth)) 年齡最低,
sum(year(getdate())-year(EmployeeBirth)) 年齡總和,
avg(year(getdate())-year(EmployeeBirth)) 年齡平均值
from Employee

--方案二：使用DATEDIFF
select count(*) 數量,
max(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最高, 
min(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最低,
sum(DATEDIFF(year,EmployeeBirth,getdate())) 年齡總和,
avg(DATEDIFF(year,EmployeeBirth,getdate())) 年齡平均值
from Employee

範例3:計算出月薪在50000以上的男性員工的最大年齡、最小年齡、平均年齡

承上，再加上條件限制

--計算出月薪在50000以上的男性員工的最大年齡、最小年齡、平均年齡

--方案一:
select '月薪5萬以上的男性' 描述,
count(*) 數量,
max(year(getdate())-year(EmployeeBirth)) 年齡最高, 
min(year(getdate())-year(EmployeeBirth)) 年齡最低,
sum(year(getdate())-year(EmployeeBirth)) 年齡總和,
avg(year(getdate())-year(EmployeeBirth)) 年齡平均值
from Employee where EmployeeSalary >= 50000 and EmployeeSex='男'

--方案二：使用DATEDIFF
select '月薪5萬以上的男性' 描述,
count(*) 數量,
max(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最高, 
min(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最低,
sum(DATEDIFF(year,EmployeeBirth,getdate())) 年齡總和,
avg(DATEDIFF(year,EmployeeBirth,getdate())) 年齡平均值
from Employee where EmployeeSalary >= 50000 and EmployeeSex='男'

範例4:計算出地區在上海、台北的女性員工的數量、最大年齡、最小年齡、平均年齡

承上，修改條件限制

--計算出地區在上海、台北的女性員工的數量、最大年齡、最小年齡、平均年齡
--方案一:
select '上海/台北的女性' 描述,
count(*) 數量,
max(year(getdate())-year(EmployeeBirth)) 年齡最高, 
min(year(getdate())-year(EmployeeBirth)) 年齡最低,
sum(year(getdate())-year(EmployeeBirth)) 年齡總和,
avg(year(getdate())-year(EmployeeBirth)) 年齡平均值
from Employee where EmployeeSex='女' and EmployeeAddress in('上海','台北')
--from Employee where EmployeeSex='女' and (EmployeeAddress='上海' or EmployeeAddress='台北')

--方案二：使用DATEDIFF
select '上海/台北的女性' 描述,
count(*) 數量,
max(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最高, 
min(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最低,
sum(DATEDIFF(year,EmployeeBirth,getdate())) 年齡總和,
avg(DATEDIFF(year,EmployeeBirth,getdate())) 年齡平均值
from Employee where EmployeeSex='女' and (EmployeeAddress='上海' or EmployeeAddress='台北')

-- where使用in
select count(*) 數量,
max(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最高, 
min(DATEDIFF(year,EmployeeBirth,getdate())) 年齡最低,
sum(DATEDIFF(year,EmployeeBirth,getdate())) 年齡總和,
avg(DATEDIFF(year,EmployeeBirth,getdate())) 年齡平均值
from Employee where EmployeeSex='女' and EmployeeAddress in('上海','台北')

範例5:求出年齡比平均年齡高的員工資料

思路

• 計算年齡：
  • 當前年份-生日年份 (year(getdate())-year(EmployeeBirth))
  • 或是使用DATEDIFF DATEDIFF(year,EmployeeBirth,getdate()
• 計算平均年齡，使用子查詢

select avg((year(getdate())-year(EmployeeBirth))) 平均年齡 from Employee

--求出年齡比平均齡高的員工資料
--select * from Employee where 年齡 > 平均齡高
--select * from Employee where (year(getdate())-year(EmployeeBirth)) > 平均齡高
select * from Employee
where (year(getdate())-year(EmployeeBirth)) > 
(select avg((year(getdate())-year(EmployeeBirth))) from Employee) --計算平均年齡，使用子查詢

count(*)、count(1)

• count(*) 回傳所有列數(行數)，包含欄位值為null。
• count(1) 回傳所有列數(行數)，包含欄位值為null，在有主鍵的情況下快
• count(欄位別) 回傳列數，不包含欄位值為NULL。

不要使用 count(欄位) 來統計記錄個數，因為它的效率是最差的。

count(*)、count(1)、count(欄位)區別

執行效果：

1. count(1)and count(*)

當表的數據量大些時，對表作分析之後，使用count(1)還要比使用count(*)用時多了！ 從執行計劃來看，count(1)和count(*)的效果是一樣的。但是在表做過分析之後，count(1)會比count(*)的用時少些（1w以內數據量），不過差不了多少。

如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 因為count(*),自動會優化指定到那一個字段。所以沒必要去count(1)，用count()，sql會幫你完成優化的 因此： count(1)和count()基本沒有差別！

1. count(1) and count(欄位) 兩者的主要區別是： （1） count(1) 會統計表中的所有的記錄數， 包含字段為null 的記錄。
   （2） count(字段) 會統計該字段在表中出現的次數，忽略字段為null 的情況。即 不統計字段為null 的記錄。 http://www.cnblogs.com/Dhouse/p/6734837.html

執行效果上 ：

• count(*)包括了所有的列，相當於行數，在統計結果的時候， 不會忽略列值為NULL
• count(1)包括了忽略所有列，用1代表代碼行，在統計結果的時候， 不會忽略列值為NULL
• count(欄位)只包括列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是只空字符串或者0，而是表示null）的計數， 即某個字段值為NULL時，不統計。

執行效率上：

• 欄位為主鍵，count(欄位)會比count(1)快
• 欄位不為主鍵，count(1)會比count(欄位)快
• 如果表多個列並且沒有主鍵，則 count（1） 的執行效率優於 count（*）
• 如果有主鍵，則 select count（主鍵）的執行效率是最優的
• 如果表只有一個欄位，則 select count（*）最優。 http://eeeewwwqq.iteye.com/blog/1972576

https://www.bilibili.com/video/BV1XV411C7TP?p=10