匯總數(shù)據(jù)

聚集函數(shù)(aggregate function)

對某些行運行的函數(shù)，計算并返回一個值。

• 利用標準的算術(shù)操作符，所有聚集函數(shù)都可以用來執(zhí)行多個列上的計算

SELECT SUM(column_name_1 * column_name_2) AS column_sum
FROM table_name;
WHERE column_name = 'str'

AVG()函數(shù)

SELECT AVG(column_name) AS column_name_avg
FROM table_name
WHERE column_name = 'str';

• AVG()通過對表中行數(shù)技數(shù)并計算其列值之和，求得該列的平均值
• AVG()函數(shù)只能用來確定特定列的平均值，且列名稱必須作為函數(shù)參數(shù)給出，若需活動多個列的平均值，需使用多個AVG()函數(shù)
• AVG()函數(shù)忽略列值為NULL的行

COUNT()函數(shù)

-- 對所有行進行計數(shù)
SELECT COUNT(*) AS column_num
FROM table_name;

-- 對特定列中有值的行進行計數(shù)
SELECT COUNT(column_name) AS column_num
FROM table_name;

• COUNT()函數(shù)進行計數(shù)
• 若指定列名，COUNT(column_name)函數(shù)忽略指定列值為NULL的行；若對所有行，COUNT(*)函數(shù)不忽略NULL值

MAX()函數(shù)

SELECT MAX(column_name) AS column_max
FROM table_name;

• MAX()函數(shù)返回指定列的最大值
• MAX()函數(shù)忽略列值為NULL的行
• 用于文本數(shù)據(jù)時，MAX()函數(shù)返回按該列排序后的最后一行

MIN()函數(shù)

SELECT MIN(column_name) AS column_min
FROM table_name;

• MIN()函數(shù)返回指定列的最小值
• MIN()函數(shù)忽略列值為NULL的行
• 用于文本數(shù)據(jù)時，MIN()函數(shù)返回按該列排序后的最前一行

SUM()函數(shù)

SELECT SUM(column_name) AS column_sum
FROM table_name;
WHERE column_name = 'str'

• SUM()函數(shù)返回指定列值的總和
• SUM()函數(shù)也可用于合計計算值
• SUM()函數(shù)忽略列值為NULL的行

聚集不同值

SELECT AVG(DISTINCT column_name) AS column_name_avg
FROM table_name
WHERE column_name = 'str';

• 5個聚集函數(shù)都可以對所有行執(zhí)行計算，指定ALL 參數(shù)或不指定參數(shù)（ALL 為默認）；也都可以通過指定DISTINCT 參數(shù)返回只包含不同的值
• DISTINCT 必須使用列名，不能用于計算或表達式
• DISTINCT 不能用于COUNT(*)
• 將DISTINCT 用于MAX()和MIN()實際上無價值

組合聚集函數(shù)

SELECT COUNT(*) AS column_num，
       AVG(DISTINCT column_name) AS column_name_avg，
       MAX(column_name) AS column_max，
       MIN(column_name) AS column_min
FROM table_name

• SELECT 語句可根據(jù)需要包含多個聚集函數(shù)