最近在給某網(wǎng)站的后臺(tái)添加一系列的統(tǒng)計(jì)功能，遇到很多需要按條件計(jì)數(shù)的情況。嘗試了幾種方法，下面簡(jiǎn)要記錄，供大家參考。

問(wèn)題描述

為使討論簡(jiǎn)單易懂，我將問(wèn)題稍作簡(jiǎn)化，去掉諸多的背景。

從前有一個(gè)皇帝，他有50個(gè)妃子，這些妃子很沒(méi)有天理的給他生了100,000個(gè)兒子，于是，皇帝很苦惱，海量的兒子很難管理，而且，他想知道每個(gè)妃子給他生了多少個(gè)兒子，從而論功行賞，這很難辦。于是，皇帝請(qǐng)了一個(gè)程序員幫他編了一個(gè)程序，用數(shù)據(jù)庫(kù)來(lái)存儲(chǔ)所有的兒子的信息，這樣就可以用程序來(lái)統(tǒng)計(jì)和管理啦。

數(shù)據(jù)庫(kù)的結(jié)構(gòu)如下：

皇帝把妃子分成了兩個(gè)等級(jí)，天宮娘娘(編號(hào)小于25)和地宮娘娘(編號(hào)大于等于25)，他想知道天宮娘娘們和地宮娘娘們的生育能力孰強(qiáng)孰弱。于是，程序員開(kāi)始寫(xiě)SQL Query了。

方法1：使用GROUP BY

SQL Query

SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

執(zhí)行結(jié)果

count(*)
50029
49971

在100,000行數(shù)據(jù)上的運(yùn)行時(shí)間：0.0335 秒

分析

這種GROUP BY方法的最大問(wèn)題在于：無(wú)法區(qū)分所得到的結(jié)果。這兩個(gè)數(shù)字哪一個(gè)是天宮娘娘們所生的皇子數(shù)，哪一個(gè)是地宮娘娘們所生的皇子數(shù)呢？不知道。所以，盡管它統(tǒng)計(jì)出了總數(shù)，但是沒(méi)有什么意義。

因此，為了區(qū)分統(tǒng)計(jì)結(jié)果，必須要把條件 mother > 24 也作為一個(gè)字段在結(jié)果集中作為一個(gè)字段體現(xiàn)出來(lái)，修改后的sql如下：

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

執(zhí)行結(jié)果

number type
50029 0
49971 1

條件表達(dá)式作為字段時(shí)，該字段的值就是該條件表達(dá)式的值，因此，對(duì)應(yīng)我們的例子，type = 1 也就是表示 mother > 24 的值為1，因此，第二行中的數(shù)字代表地宮娘娘們所生的皇子數(shù)。

經(jīng)過(guò)修改后，我們看出，天宮娘娘們略勝一籌。

優(yōu)缺點(diǎn)

缺點(diǎn)是顯而易見(jiàn)的，由于使用了條件表達(dá)式作為分組依據(jù)，它只能做二元的劃分，對(duì)于要分成多類(lèi)進(jìn)行統(tǒng)計(jì)的情況不能夠勝任。比如要分別統(tǒng)計(jì)1~10號(hào)、11~24號(hào)，25號(hào)~50號(hào)妃子的產(chǎn)子數(shù)，就無(wú)法實(shí)現(xiàn)了。

另外，由于使用了GROUP BY，因此涉及到排序，執(zhí)行時(shí)間上要更長(zhǎng)。

我暫時(shí)沒(méi)有發(fā)現(xiàn)這種方法的優(yōu)點(diǎn)。

方法2：使用嵌套的SELECT

使用嵌套的SELECT也可以達(dá)到目的，在每個(gè)SELECT子句中統(tǒng)計(jì)一個(gè)條件下的數(shù)據(jù)，然后用一個(gè)主SELECT把這些統(tǒng)計(jì)數(shù)據(jù)整合起來(lái)。

SQL Query

SELECT 
  ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`, 
  ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

執(zhí)行結(jié)果

digong tiangong
49971 50029

在100,000行數(shù)據(jù)上的運(yùn)行時(shí)間：0.0216 秒

分析

這種嵌套SELECT的方法非常直觀，就是分別統(tǒng)計(jì)各個(gè)條件下的數(shù)值，最后進(jìn)行匯總，通俗易懂，跟自然語(yǔ)言沒(méi)啥區(qū)別了。

優(yōu)缺點(diǎn)

優(yōu)點(diǎn)就是直觀，而且速度也比GROUP BY要快。雖然是3條SELECT語(yǔ)句，看起來(lái)比GROUP BY的方案多了2條語(yǔ)句，但是它不涉及到排序，這就節(jié)省了很多時(shí)間。

缺點(diǎn)可能就是語(yǔ)句稍多，對(duì)語(yǔ)句數(shù)量有潔癖的同學(xué)可能會(huì)比較不舒服。

方法3：使用CASE WHEN

CASE WHEN語(yǔ)句的功能很強(qiáng)大，可以定義靈活的查詢條件，很適合進(jìn)行分類(lèi)統(tǒng)計(jì)。

SQL Query

SELECT 
  COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`, 
  COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`
FROM prince

執(zhí)行結(jié)果

digong tiangong
49971 50029

在100,000行數(shù)據(jù)上的運(yùn)行時(shí)間：0.02365825 秒

分析

此方法的關(guān)鍵在于

COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END )

這里的COUNT和CASE WHEN聯(lián)合使用，做到了分類(lèi)計(jì)數(shù)。先使用CASE WHEN，當(dāng)滿足條件時(shí)，將字段值設(shè)置為 1， 不滿足條件時(shí)，將字段值設(shè)置為NULL，接著COUNT函數(shù)僅對(duì)非NULL字段進(jìn)行計(jì)數(shù)，于是，問(wèn)題解決。

優(yōu)缺點(diǎn)

優(yōu)點(diǎn)嘛，此方法也不涉及到排序，因此運(yùn)行時(shí)間上與方法2相當(dāng)，SELECT語(yǔ)句減少到了 1 條。

缺點(diǎn)就是語(yǔ)句比較長(zhǎng)，對(duì)語(yǔ)句長(zhǎng)度有潔癖的同學(xué)可能會(huì)比較不舒服。

總結(jié)

對(duì)于確定分類(lèi)的按條件計(jì)數(shù)，可以盡量不用GROUP BY，從而避免排序動(dòng)作，加速Q(mào)uery的執(zhí)行。

如果需要根據(jù)某個(gè)字段的值進(jìn)行分類(lèi)，而該字段的值是可變的，比如皇帝要統(tǒng)計(jì)每一個(gè)妃子的產(chǎn)子數(shù)，而他可能不停的再娶很多妃子，這種情況下，使用方法2和方法3就不太靈光了，還是使用一個(gè)GROUP BY來(lái)得簡(jiǎn)單便捷。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論