1.GROUP BY（聚合）函數(shù)

本章論述了用于一組數(shù)值操作的group (集合)函數(shù)。除非另作說明，group函數(shù)會(huì)忽略NULL值。

假如你在一個(gè)不包含ROUP BY子句的語句中使用一個(gè)group函數(shù) ，它相當(dāng)于對(duì)所有行進(jìn)行分組。

AVG([DISTINCT]expr)

返回expr的平均值。DISTINCT選項(xiàng)可用于返回expr的不同值的平均值。

若找不到匹配的行，則AVG()返回NULL。

mysql>SELECT student_name, AVG(test_score)->FROM student-> GROUP BY student_name;

BIT_AND(expr)

返回expr中所有比特的bitwise AND。計(jì)算執(zhí)行的精確度為64比特(BIGINT)。

若找不到匹配的行，則這個(gè)函數(shù)返回18446744073709551615。(這是無符號(hào)BIGINT值，所有比特被設(shè)置為1）。

BIT_OR(expr)

返回expr中所有比特的bitwise OR。計(jì)算執(zhí)行的精確度為64比特(BIGINT)。

若找不到匹配的行，則函數(shù)返回0。

BIT_XOR(expr)

返回expr中所有比特的bitwise XOR。計(jì)算執(zhí)行的精確度為64比特(BIGINT)。

若找不到匹配的行，則函數(shù)返回0。

COUNT(expr)

返回SELECT語句檢索到的行中非NULL值的數(shù)目。

若找不到匹配的行，則COUNT()返回0。

mysql>SELECT student.student_name,COUNT(*)->FROM student,course->WHERE student.student_id=course.student_id->GROUP BY student_name;

COUNT(*)的稍微不同之處在于，它返回檢索行的數(shù)目， 不論其是否包含NULL值。

SELECT從一個(gè)表中檢索，而不檢索其它的列，并且沒有WHERE子句時(shí)，COUNT(*)被優(yōu)化到最快的返回速度。例如：

mysql>SELECT COUNT(*) FROM student;

這個(gè)優(yōu)化僅適用于MyISAM表,原因是這些表類型會(huì)儲(chǔ)存一個(gè)函數(shù)返回記錄的精確數(shù)量，而且非常容易訪問。對(duì)于事務(wù)型的存儲(chǔ)引擎(InnoDB, BDB),存儲(chǔ)一個(gè)精確行數(shù)的問題比較多，原因是可能會(huì)發(fā)生多重事物處理,而每個(gè)都可能會(huì)對(duì)行數(shù)產(chǎn)生影響。

COUNT(DISTINCTexpr,[expr...])

返回不同的非NULL值數(shù)目。

若找不到匹配的項(xiàng)，則COUNT(DISTINCT)返回0。

mysql>SELECT COUNT(DISTINCT results) FROM student;

在MySQL中,你通過給定一個(gè)表達(dá)式列表而獲取不包含NULL不同表達(dá)式組合的數(shù)目。在標(biāo)準(zhǔn)SQL中,你將必須在COUNT(DISTINCT ...)中連接所有表達(dá)式。

GROUP_CONCAT(expr)

該函數(shù)返回帶有來自一個(gè)組的連接的非NULL值的字符串結(jié)果。其完整的語法如下所示：

GROUP_CONCAT([DISTINCT]expr[,expr...]

[ORDER BY {unsigned_integer|col_name|expr}

[ASC | DESC] [,col_name...]]

[SEPARATORstr_val])

mysql>SELECT student_name,->GROUP_CONCAT(test_score)->FROM student->GROUP BY student_name;

Or:

mysql>SELECT student_name,->GROUP_CONCAT(DISTINCT test_score->ORDER BY test_score DESC SEPARATOR ’ ’)->FROM student->GROUP BY student_name;

在MySQL中,你可以獲取表達(dá)式組合的連接值。你可以使用DISTINCT刪去重復(fù)值。假若你希望多結(jié)果值進(jìn)行排序，則應(yīng)該使用 ORDER BY子句。若要按相反順序排列，將DESC (遞減)關(guān)鍵詞添加到你要用ORDER BY子句進(jìn)行排序的列名稱中。默認(rèn)順序?yàn)樯?；可使用ASC將其明確指定。 SEPARATOR后面跟隨應(yīng)該被插入結(jié)果的值中間的字符串值。默認(rèn)為逗號(hào)(‘,’)。通過指定SEPARATOR ’’，你可以刪除所有分隔符。

使用group_concat_max_len系統(tǒng)變量，你可以設(shè)置允許的最大長度。程序中進(jìn)行這項(xiàng)操作的語法如下，其中val是一個(gè)無符號(hào)整數(shù)：

SET [SESSION | GLOBAL] group_concat_max_len = val;

若已經(jīng)設(shè)置了最大長度， 則結(jié)果被截至這個(gè)最大長度。

MIN([DISTINCT]expr), MAX([DISTINCT]expr)

返回expr的最小值和最大值。MIN()和MAX()的取值可以是一個(gè)字符串參數(shù)；在這些情況下， 它們返回最小或最大字符串值。DISTINCT關(guān)鍵詞可以被用來查找expr的不同值的最小或最大值，然而，這產(chǎn)生的結(jié)果與省略DISTINCT的結(jié)果相同。

若找不到匹配的行，MIN()和MAX()返回NULL。

mysql>SELECT student_name, MIN(test_score), MAX(test_score)-> FROM student-> GROUP BY student_name;

對(duì)于MIN()、MAX()和其它集合函數(shù)，MySQL當(dāng)前按照它們的字符串值而非字符串在集合中的相關(guān)位置比較ENUM和SET列。這同ORDER BY比較二者的方式有所不同。這一點(diǎn)應(yīng)該在MySQL的未來版本中得到改善。

STD(expr) STDDEV(expr)

返回expr的總體標(biāo)準(zhǔn)偏差。這是標(biāo)準(zhǔn)SQL的延伸。這個(gè)函數(shù)的STDDEV()形式用來提供和Oracle的兼容性。可使用標(biāo)準(zhǔn)SQL函數(shù)STDDEV_POP()進(jìn)行代替。

若找不到匹配的行，則這些函數(shù)返回NULL。

STDDEV_POP(expr)

返回expr的總體標(biāo)準(zhǔn)偏差(VAR_POP()的平方根)。你也可以使用 STD()或STDDEV(),它們具有相同的意義，然而不是標(biāo)準(zhǔn)的SQL。

若找不到匹配的行，則STDDEV_POP()返回NULL。

STDDEV_SAMP(expr)

返回expr的樣本標(biāo)準(zhǔn)差( VAR_SAMP()的平方根)。

若找不到匹配的行，則STDDEV_SAMP()返回NULL。

SUM([DISTINCT]expr)

返回expr的總數(shù)。 若返回集合中無任何行，則SUM()返回NULL。DISTINCT關(guān)鍵詞可用于MySQL 5.1中，求得expr不同值的總和。

若找不到匹配的行，則SUM()返回NULL。

VAR_POP(expr)

返回expr總體標(biāo)準(zhǔn)方差。它將行視為總體，而不是一個(gè)樣本， 所以它將行數(shù)作為分母。你也可以使用VARIANCE(),它具有相同的意義然而不是 標(biāo)準(zhǔn)的SQL。

若找不到匹配的項(xiàng)，則VAR_POP()返回NULL。

VAR_SAMP(expr)

返回expr的樣本方差。更確切的說，分母的數(shù)字是行數(shù)減去1。

若找不到匹配的行，則VAR_SAMP()返回NULL。

VARIANCE(expr)

返回expr的總體標(biāo)準(zhǔn)方差。這是標(biāo)準(zhǔn)SQL的延伸。可使用標(biāo)準(zhǔn)SQL函數(shù)VAR_POP()進(jìn)行代替。

若找不到匹配的項(xiàng)，則VARIANCE()返回NULL。

2.GROUP BY修改程序

GROUP BY子句允許一個(gè)將額外行添加到簡略輸出端WITH ROLLUP修飾符。這些行代表高層(或高聚集)簡略操作。ROLLUP因而允許你在多層分析的角度回答有關(guān)問詢的問題。例如，它可以用來向OLAP (聯(lián)機(jī)分析處理)操作提供支持。

設(shè)想一個(gè)名為sales的表具有年份、國家、產(chǎn)品及記錄銷售利潤的利潤列：

CREATE TABLE sales( year INT NOT NULL, country VARCHAR(20) NOT NULL, product VARCHAR(32) NOT NULL, profit INT);

可以使用這樣的簡單GROUP BY，每年對(duì)表的內(nèi)容做一次總結(jié)：

mysql>SELECT year, SUM(profit) FROM sales GROUP BY year;+------+-------------+| year | SUM(profit) |+------+-------------+| 2000 | 4525 || 2001 | 3010 |+------+-------------+

這個(gè)輸出結(jié)果顯示了每年的總利潤， 但如果你也想確定所有年份的總利潤，你必須自己累加每年的單個(gè)值或運(yùn)行一個(gè)加法詢問。

或者你可以使用ROLLUP,它能用一個(gè)問詢提供雙層分析。將一個(gè)WITH ROLLUP修飾符添加到GROUP BY語句，使詢問產(chǎn)生另一行結(jié)果，該行顯示了所有年份的總價(jià)值：

mysql>SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;+------+-------------+| year | SUM(profit) |+------+-------------+| 2000 | 4525 || 2001 | 3010 || NULL | 7535 |+------+-------------+

總計(jì)高聚集行被年份列中的NULL值標(biāo)出。

當(dāng)有多重GROUP BY列時(shí)，ROLLUP產(chǎn)生的效果更加復(fù)雜。這時(shí)，每次在除了最后一個(gè)分類列之外的任何列出現(xiàn)一個(gè) “break”(值的改變)，則問訊會(huì)產(chǎn)生一個(gè)高聚集累計(jì)行。

例如，在沒有ROLLUP的情況下，一個(gè)以年、國家和產(chǎn)品為基礎(chǔ)的關(guān)于sales表的一覽表可能如下所示：

mysql>SELECT year, country, product, SUM(profit)->FROM sales->GROUP BY year, country, product;+------+---------+------------+-------------+| year | country | product | SUM(profit) |+------+---------+------------+-------------+| 2000 | Finland | Computer | 1500 || 2000 | Finland | Phone | 100 || 2000 | India | Calculator | 150 || 2000 | India | Computer | 1200 || 2000 | USA | Calculator | 75 || 2000 | USA | Computer | 1500 || 2001 | Finland | Phone | 10 || 2001 | USA | Calculator | 50 || 2001 | USA | Computer | 2700 || 2001 | USA | TV | 250 |+------+---------+------------+-------------+

表示總值的輸出結(jié)果僅位于年/國家/產(chǎn)品的分析級(jí)別。當(dāng)添加了ROLLUP后， 問詢會(huì)產(chǎn)生一些額外的行：

mysql>SELECT year, country, product, SUM(profit) ->FROM sales ->GROUP BY year, country, product WITH ROLLUP;+------+---------+------------+-------------+| year | country | product | SUM(profit) |+------+---------+------------+-------------+| 2000 | Finland | Computer | 1500 || 2000 | Finland | Phone | 100 || 2000 | Finland | NULL | 1600 || 2000 | India | Calculator | 150 || 2000 | India | Computer | 1200 || 2000 | India | NULL | 1350 || 2000 | USA | Calculator | 75 || 2000 | USA | Computer | 1500 || 2000 | USA | NULL | 1575 || 2000 | NULL | NULL | 4525 || 2001 | Finland | Phone | 10 || 2001 | Finland | NULL | 10 || 2001 | USA | Calculator | 50 || 2001 | USA | Computer | 2700 || 2001 | USA | TV | 250 || 2001 | USA | NULL | 3000 || 2001 | NULL | NULL | 3010 || NULL | NULL | NULL | 7535 |+------+---------+------------+-------------+

對(duì)于這個(gè)問詢， 添加ROLLUP子句使村輸出結(jié)果包含了四層分析的簡略信息，而不只是一個(gè)下面是怎樣解釋 ROLLUP輸出：

一組給定的年份和國家的每組產(chǎn)品行后面,會(huì)產(chǎn)生一個(gè)額外的總計(jì)行， 顯示所有產(chǎn)品的總值。這些行將產(chǎn)品列設(shè)置為NULL。一組給定年份的行后面，會(huì)產(chǎn)生一個(gè)額外的總計(jì)行，顯示所有國家和產(chǎn)品的總值。這些行將國家和產(chǎn)品列設(shè)置為 NULL。最后,在所有其它行后面，會(huì)產(chǎn)生一個(gè)額外的總計(jì)列，顯示所有年份、國家及產(chǎn)品的總值。 這一行將年份、國家和產(chǎn)品列設(shè)置為NULL。

使用ROLLUP時(shí)的其它注意事項(xiàng)

以下各項(xiàng)列出了一些MySQL執(zhí)行ROLLUP的特殊狀態(tài)：

當(dāng)你使用ROLLUP時(shí),你不能同時(shí)使用ORDER BY子句進(jìn)行結(jié)果排序。換言之，ROLLUP和ORDER BY是互相排斥的。然而，你仍可以對(duì)排序進(jìn)行一些控制。在MySQL中，GROUP BY可以對(duì)結(jié)果進(jìn)行排序，而且你可以在GROUP BY列表指定的列中使用明確的ASC和DESC關(guān)鍵詞，從而對(duì)個(gè)別列進(jìn)行排序。(不論如何排序被ROLLUP添加的較高級(jí)別的總計(jì)行仍出現(xiàn)在它們被計(jì)算出的行后面）。

LIMIT可用來限制返回客戶端的行數(shù)。LIMIT用在ROLLUP后面,因此這個(gè)限制 會(huì)取消被ROLLUP添加的行。例如:

mysql>SELECT year, country, product, SUM(profit) ->FROM sales ->GROUP BY year, country, product WITH ROLLUP ->LIMIT 5;+------+---------+------------+-------------+| year | country | product | SUM(profit) |+------+---------+------------+-------------+| 2000 | Finland | Computer | 1500 || 2000 | Finland | Phone | 100 || 2000 | Finland | NULL | 1600 || 2000 | India | Calculator | 150 || 2000 | India | Computer | 1200 |+------+---------+------------+-------------+

將ROLLUP同LIMIT一起使用可能會(huì)產(chǎn)生更加難以解釋的結(jié)果，原因是對(duì)于理解高聚集行，你所掌握的上下文較少。

在每個(gè)高聚集行中的NULL指示符會(huì)在該行被送至客戶端時(shí)產(chǎn)生。服務(wù)器會(huì)查看最左邊的改變值后面的GROUP BY子句指定的列。對(duì)于任何結(jié)果集合中的，有一個(gè)詞匹配這些名字的列,其值被設(shè)為NULL。(若你使用列數(shù)字指定了分組列，則服務(wù)器會(huì)通過數(shù)字確定將哪個(gè)列設(shè)置為NULL）。

由于在高聚集行中的NULL值在問詢處理階段被放入結(jié)果集合中，你無法將它們在問詢本身中作為NULL值檢驗(yàn)。例如，你無法將HAVING product IS NULL添加到問詢中，從而在輸出結(jié)果中刪去除了高聚集行以外的部分。

另一方面, NULL值在客戶端不以NULL的形式出現(xiàn)， 因而可以使用任何MySQL客戶端編程接口進(jìn)行檢驗(yàn)。

3.具有隱含字段的GROUP BY

MySQL擴(kuò)展了GROUP BY的用途，因此你可以使用SELECT列表中不出現(xiàn)在GROUP BY語句中的列或運(yùn)算。這代表 “對(duì)該組的任何可能值 ”。你可以通過避免排序和對(duì)不必要項(xiàng)分組的辦法得到它更好的性能。例如，在下列問詢中，你無須對(duì)customer.name進(jìn)行分組：

mysql>SELECT order.custid, customer.name, MAX(payments) ->FROM order,customer ->WHERE order.custid = customer.custid -> GROUP BY order.custid;

在標(biāo)準(zhǔn)SQL中,你必須將customer.name添加到GROUP BY子句中。在MySQL中,假如你不在ANSI模式中運(yùn)行，則這個(gè)名字就是多余的。

假如你從GROUP BY部分省略的列在該組中不是唯一的，那么不要使用這個(gè)功能!你會(huì)得到非預(yù)測性結(jié)果。

在有些情況下,你可以使用MIN()和MAX()獲取一個(gè)特殊的列值，即使他不是唯一的。下面給出了來自包含排序列中最小值的列中的值：

SUBSTR(MIN(CONCAT(RPAD(sort,6,’ ’),column)),7)

注意，假如你正在嘗試遵循標(biāo)準(zhǔn)SQL,你不能使用GROUP BY或ORDER BY子句中的表達(dá)式。你可以通過使用表達(dá)式的別名繞過這一限制：

mysql>SELECT id,FLOOR(value/100) AS val ->FROMtbl_name ->GROUP BY id, val ORDER BY val;

然而, MySQL允許你使用GROUP BY及ORDER BY子句中的表達(dá)式。例如：

mysql>SELECT id, FLOOR(value/100) FROMtbl_nameORDER BY RAND();