NULL函數(shù)

NULL 是一種數(shù)據(jù)類型，表示 SQL 中沒有數(shù)據(jù)。它們經(jīng)常在聚合函數(shù)中被忽略了，在下個部分學(xué)習(xí)使用 COUNT 時你將首次接觸到這一現(xiàn)象叨襟。

注意，NULL 與零不同幔荒，它們表示不存在數(shù)據(jù)的單元格糊闽。

在以下兩種常見情況下，你可能會遇到 NULL：

在執(zhí)行 LEFT JOIN 或 RIGHT JOIN 時爹梁，NULL 經(jīng)常會發(fā)生右犹。你在上節(jié)課見到了，左側(cè)表格中的某些行在做連接時與右側(cè)表格中的行如果不匹配姚垃，這些行在結(jié)果集中就會包含一些 NULL 值念链。

NULL 也可能是因為數(shù)據(jù)庫中缺失數(shù)據(jù)。

NULL空白

提示

在 WHERE 條件中表示 NULL 時积糯，我們寫成 IS NULL 或 IS NOT NULL掂墓。我們不使用 =，因為 NULL 在 SQL 中不屬于值看成。但是它是數(shù)據(jù)的一個屬性君编。

第一個聚合函數(shù)

COUNT 不會考慮具有 NULL 值的行。 因此可用來快速判斷哪些行缺數(shù)據(jù)川慌。后面還會學(xué) GROUP BY啦粹，可以搭配使用

計算表格中的行數(shù)

試著手動數(shù)數(shù)每個表格的行數(shù)偿荷。以下是計算 accounts 表格中的行數(shù)示例：

SELECT COUNT(*)
FROM accounts;

我們也可以輕松地選擇一列來放置聚合函數(shù)：

SELECT COUNT(accounts.id)
FROM accounts;

SUM

與 COUNT 不同，你只能針對數(shù)字列使用 SUM唠椭。但是，SUM 將忽略 NULL 值忍饰，把它當(dāng)0來計算贪嫂，其他聚合函數(shù)也是這樣。

使用SUM

聚合函數(shù)

重要注意事項：聚合函數(shù)只能垂直聚合艾蓝，即聚合列的值力崇。如果你想對行進(jìn)行計算，可以使用簡單算術(shù)表達(dá)式赢织。

練習(xí)

答案

SUM 解決方案

算出 orders 表格中的 poster_qty 紙張總訂單量亮靴。

SELECT SUM(poster_qty) AS total_poster_sales
FROM orders;

算出 orders 表格中 standard_qty 紙張的總訂單量。

SELECT SUM(standard_qty) AS total_standard_sales
FROM orders;

根據(jù) orders 表格中的 total_amt_usd 得出總銷售額于置。

SELECT SUM(total_amt_usd) AS total_dollar_sales
FROM orders;

算出 orders 表格中每個訂單在 standard 和 gloss 紙張上消費的數(shù)額茧吊。結(jié)果應(yīng)該是表格中每個訂單的金額。

注意八毯，此解決方案沒有使用聚合函數(shù)搓侄。

SELECT standard_qty + gloss_qty AS total_standard_gloss
FROM orders;

每個訂單的 price/standard_qty 紙張各不相同。我想得出 orders 表格中每個銷售機(jī)會的這一比例话速。

注意讶踪，此解決方案使用了聚合函數(shù)和數(shù)學(xué)運算符

SELECT SUM(standard_amt_usd)/SUM(standard_qty) AS standard_price_per_unit
FROM orders;

MIN MAX

MIN和MAX的語法與SUM和COUNT相似

此處我們同時獲得了每個紙張類型的 MIN 和 MAX 訂單量。但是泊交，你也可以單獨計算每個類型的訂單量乳讥。

注意，MIN 和 MAX 聚合函數(shù)也會忽略 NULL 值廓俭。請參閱以下專家提示云石，了解關(guān)于 MAX 與 MIN 的實用技巧。

提示

從功能上來說白指，MIN 和 MAX 與 COUNT 相似留晚，它們都可以用在非數(shù)字列上。MIN 將返回最小的數(shù)字告嘲、最早的日期或按字母表排序的最之前的非數(shù)字值错维，具體取決于列類型。MAX 則正好相反橄唬，返回的是最大的數(shù)字赋焕、最近的日期，或與“Z”最接近（按字母表順序排列）的非數(shù)字值仰楚。

AVG

與其他軟件類似隆判，AVG 返回的是數(shù)據(jù)的平均值犬庇，即列中所有的值之和除以列中值的數(shù)量。該聚合函數(shù)同樣會忽略分子和分母中的 NULL 值侨嘀。

如果你想將 NULL 當(dāng)做零臭挽，則需要使用 SUM 和 COUNT。但是咬腕，如果 NULL 值真的只是代表單元格的未知值欢峰，那么這么做可能不太合適。

MEDIAN - 提示
注意涨共，中值可能是更好的衡量方式纽帖，但是僅使用 SQL 非常棘手，以至于有時候在面試中就會提到關(guān)于中值方面的問題举反。

練習(xí)與答案

解決方案：MIN懊直、MAX 與 AVERAGE
根據(jù)以下 SQL 表格信息回答以下問題。如果你遇到問題或想要對比檢查你的答案火鼻，可以在下一部分的頁面中找到我的答案室囊。

• 最早的訂單下于何時？

SELECT MIN(occurred_at) 
FROM orders;

• 嘗試執(zhí)行和第一個問題一樣的查詢凝危，但是不使用聚合函數(shù)波俄。

SELECT occurred_at 
FROM orders 
ORDER BY occurred_at
LIMIT 1;

• 最近的 web_event 發(fā)生在什么時候？

SELECT MAX(occurred_at)
FROM web_events;

• 嘗試以另一種方式執(zhí)行上個問題的查詢蛾默，不使用聚合函數(shù)懦铺。

SELECT occurred_at
FROM web_events
ORDER BY occurred_at DESC
LIMIT 1;

• 算出每個訂單在每種紙張上消費的平均 (AVERAGE) 金額，以及每個訂單針對每種紙張購買的平均數(shù)量支鸡。最終答案應(yīng)該有 6 個值冬念，每個紙張類型平均銷量對應(yīng)一個值，以及平均數(shù)量對應(yīng)一個值牧挣。

SELECT AVG(standard_qty) mean_standard, AVG(gloss_qty) mean_gloss, 
           AVG(poster_qty) mean_poster, AVG(standard_amt_usd) mean_standard_usd, 
           AVG(gloss_amt_usd) mean_gloss_usd, AVG(poster_amt_usd) mean_poster_usd
FROM orders;

你可能對如何計算中位數(shù)感興趣急前。雖然這已經(jīng)超出了目前我們所學(xué)的范圍，但請嘗試探索這個問題：對于所有訂單（orders）數(shù)據(jù)瀑构，其total_usd字段的中位數(shù)是多少裆针？請注意，構(gòu)建一個此問題的通用解決方案已經(jīng)超出了目前所學(xué)的課程范圍寺晌，但我們可以硬寫出以下這段代碼：

SELECT *
FROM (SELECT total_amt_usd
      FROM orders
      ORDER BY total_amt_usd
      LIMIT 3457) AS Table1
ORDER BY total_amt_usd DESC
LIMIT 2;

因為訂單一共有6912個世吨，因此我們需要第3456和第3457個訂單（按total_amt_usd排序）的total_amt_usd字段的平均值。這樣就能得出中位數(shù)結(jié)果呻征，為2482.855耘婚。這顯然不是一個好辦法。如果我們有了新訂單陆赋，再次計算時就必須修改LIMIT沐祷。SQL實際上并不會為我們計算中位數(shù)嚷闭。以上代碼使用了一個子查詢（SUBQUERY），但你可以使用任何方法找到需要的兩個值赖临，然后再求平均即可得到中位數(shù)胞锰。

GROUP BY

這里出現(xiàn)了錯誤，因為account.id不是折疊的兢榨，而SUM操作是折疊的

使用GROUP BY語句對數(shù)據(jù)進(jìn)行聚合胜蛉，這樣加起來的和就是按照ACCOUNT了進(jìn)行排列的了，但是需要加上WHERE

使用GROUP BY

• GROUP BY 可以用來在數(shù)據(jù)子集中聚合數(shù)據(jù)色乾。例如，不同客戶领突、不同區(qū)域或不同銷售代表分組暖璧。

• SELECT 語句中的任何一列如果不在聚合函數(shù)中，則必須在 GROUP BY 條件中君旦。

• GROUP BY 始終在 WHERE 和 ORDER BY 之間澎办。

• ORDER BY 有點像電子表格軟件中的 SORT。

提示

在深入了解如何使用 GROUP BY 語句聚合函數(shù)之前金砍，需要注意的是局蚀，SQL 在 LIMIT 條件之前評估聚合函數(shù)。如果不按任何列分組恕稠，則結(jié)果是 1 行琅绅，沒有問題。如果按照某列分組鹅巍，該列中存在大量的唯一值千扶，超出了 LIMIT 上限，則系統(tǒng)會照常計算聚合結(jié)果骆捧，但是結(jié)果中會忽略某些行澎羞。

這實際上是比較不錯的方式，因為你知道你將獲得正確的聚合結(jié)果敛苇。如果 SQL 將表格裁剪到 100 行妆绞，然后進(jìn)行聚合，結(jié)果將完全不同枫攀。上述查詢的結(jié)果超過了 100 行括饶，因此是個很好的示例。在下一部分脓豪，使用該 SQL 表格并嘗試刪掉 LIMIT巷帝，然后再次運行查詢，看看有哪些變化扫夜。

練習(xí)

問題：GROUP BY
根據(jù)以下 SQL 表格信息回答以下問題楞泼。

難點是驰徊，何時使用某個聚合函數(shù)或其他 SQL 功能最簡單。請嘗試回答以下問題堕阔，看看你能否找到最簡單的解決方案棍厂。

• 哪個客戶（按照名稱）下的訂單最早？你的答案應(yīng)該包含訂單的客戶名稱和日期超陆。

• 算出每個客戶的總銷售額（單位是美元）牺弹。答案應(yīng)該包括兩列：每個公司的訂單總銷售額（單位是美元）以及公司名稱。

• 最近的 web_event 是通過哪個渠道發(fā)生的时呀，與此 web_event 相關(guān)的客戶是哪個张漂？你的查詢應(yīng)該僅返回三個值：日期、渠道和客戶名稱谨娜。

• 算出 web_events 中每種渠道的次數(shù)航攒。最終表格應(yīng)該有兩列：渠道和渠道的使用次數(shù)。

• 與最早的 web_event 相關(guān)的主要聯(lián)系人是誰趴梢？

• 每個客戶所下的最小訂單是什么（以總金額（美元）為準(zhǔn)）漠畜。答案只需兩列：客戶名稱和總金額（美元）。從最小金額到最大金額排序坞靶。

• 算出每個區(qū)域的銷售代表人數(shù)憔狞。最早表格應(yīng)該包含兩列：區(qū)域和 sales_reps 數(shù)量。從最少到最多的代表人數(shù)排序彰阴。

答案

• 哪個客戶（按照名稱）下的訂單最早瘾敢？你的答案應(yīng)該包含訂單的客戶名稱和日期。

SELECT a.name, o.occurred_at
FROM accounts a
JOIN orders o
ON a.id = o.account_id
ORDER BY occurred_at
LIMIT 1;

• 算出每個客戶的總銷售額（單位是美元）硝枉。答案應(yīng)該包括兩列：每個公司的訂單總銷售額（單位是美元）以及公司名稱廉丽。

SELECT a.name, SUM(total_amt_usd) total_sales
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.name;

• 最近的 web_event 是通過哪個渠道發(fā)生的，與此 web_event 相關(guān)的客戶是哪個妻味？你的查詢應(yīng)該僅返回三個值：日期正压、渠道和客戶名稱。

SELECT w.occurred_at, w.channel, a.name
FROM web_events w
JOIN accounts a
ON w.account_id = a.id 
ORDER BY w.occurred_at DESC
LIMIT 1;

• 算出 web_events 中每種渠道的次數(shù)责球。最終表格應(yīng)該有兩列：渠道和渠道的使用次數(shù)焦履。

SELECT w.channel, COUNT(*)
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
GROUP BY w.channel

• 與最早的 web_event 相關(guān)的主要聯(lián)系人是誰？

SELECT a.primary_poc
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
ORDER BY w.occurred_at
LIMIT 1;

• 每個客戶所下的最小訂單是什么（以總金額（美元）為準(zhǔn)）雏逾。答案只需兩列：客戶名稱和總金額（美元）嘉裤。從最小金額到最大金額排序。

SELECT a.name, MIN(total_amt_usd) smallest_order
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name
ORDER BY smallest_order;

奇怪的是栖博，很多訂單沒有美元金額屑宠。我們可能需要檢查下這些訂單。

算出每個區(qū)域的銷售代表人數(shù)仇让。最終表格應(yīng)該包含兩列：區(qū)域和 sales_reps 數(shù)量典奉。從最少到最多的代表人數(shù)排序躺翻。

SELECT r.name, COUNT(*) num_reps
FROM region r
JOIN sales_reps s
ON r.id = s.region_id
GROUP BY r.name
ORDER BY num_reps;

GROUP BY 實現(xiàn)多個分組

GROUP BY實現(xiàn)同時按照多列分組

可以同時按照多列分組，正如此處所顯示的那樣卫玖。這樣經(jīng)彻悖可以在大量不同的細(xì)分中更好地獲得聚合結(jié)果。

ORDER BY 條件中列出的列順序有區(qū)別假瞬。你是從左到右讓列排序陕靠。

提示

GROUP BY 條件中的列名稱順序并不重要，結(jié)果還是一樣的脱茉。如果運行相同的查詢并顛倒 GROUP BY 條件中列名稱的順序剪芥，可以看到結(jié)果是一樣的。

和 ORDER BY 一樣琴许，你可以在 GROUP BY 條件中用數(shù)字替換列名稱粗俱。僅當(dāng)你對大量的列分組時，或者其他原因?qū)е?GROUP BY 條件中的文字過長時虚吟，才建議這么做。

提醒下签财，任何不在聚合函數(shù)中的列必須顯示 GROUP BY 語句串慰。如果忘記了，可能會遇到錯誤唱蒸。但是邦鲫，即使查詢可行，你也可能不會喜歡最后的結(jié)果神汹！

練習(xí)

• 對于每個客戶庆捺，確定他們在訂單中購買的每種紙張的平均數(shù)額。結(jié)果應(yīng)該有四列：客戶名稱一列屁魏，每種紙張類型的平均數(shù)額一列滔以。

SELECT a.name, AVG(o.standard_qty) avg_stand, AVG(gloss_qty) avg_gloss, AVG(poster_qty) avg_post
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name;

• 對于每個客戶，確定在每個訂單中針對每個紙張類型的平均消費數(shù)額氓拼。結(jié)果應(yīng)該有四列：客戶名稱一列你画，每種紙張類型的平均消費數(shù)額一列。

SELECT a.name, AVG(o.standard_amt_usd) avg_stand, AVG(gloss_amt_usd) avg_gloss, AVG(poster_amt_usd) avg_post
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name;

• 確定在 web_events 表格中每個銷售代表使用特定渠道的次數(shù)桃漾。最終表格應(yīng)該有三列：銷售代表的名稱坏匪、渠道和發(fā)生次數(shù)。按照最高的發(fā)生次數(shù)在最上面對表格排序撬统。

SELECT s.name, w.channel, COUNT(*) num_events
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY num_events DESC;

• 確定在 web_events 表格中針對每個地區(qū)特定渠道的使用次數(shù)适滓。最終表格應(yīng)該有三列：區(qū)域名稱、渠道和發(fā)生次數(shù)恋追。按照最高的發(fā)生次數(shù)在最上面對表格排序凭迹。

SELECT r.name, w.channel, COUNT(*) num_events
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
JOIN region r
ON r.id = s.region_id
GROUP BY r.name, w.channel
ORDER BY num_events DESC;

DISTINCT

你可以將 DISTINCT 看做僅返回特定列的唯一值的函數(shù)罚屋。

使用GROUP

使用DISTINCT會得到一樣的結(jié)果

提示

需要注意的是，在使用 DISTINCT 時蕊苗，尤其是在聚合函數(shù)中使用時沿后，會讓查詢速度有所減慢。

DISTINCT的用法的補(bǔ)充說明

HAVING

HAVING 是過濾被聚合的查詢的 the “整潔”方式朽砰，但是通常采用子查詢的方式來實現(xiàn)尖滚。本質(zhì)上，只要你想對通過聚合創(chuàng)建的查詢中的元素執(zhí)行 WHERE 條件瞧柔，就需要使用 HAVING漆弄。

單純的WHERE 語句 不允許過濾聚合列

聚合過濾的正確姿勢

HAVING總是要與一個聚合函數(shù)一起使用的，這個只有在一個或者多個列分組的時候才有用造锅。如果你聚合的是整個數(shù)據(jù)集撼唾，那么輸出只有一行，所以就沒有必要進(jìn)行過濾了哥蔚。

練習(xí)

辨析

• 有多少位銷售代表需要管理超過 5 個客戶倒谷？

SELECT s.id, s.name, COUNT(*) num_accounts
FROM accounts a
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.id, s.name
HAVING COUNT(*) > 5
ORDER BY num_accounts;

• 實際上，我們可以使用 SUBQUERY 獲得這一結(jié)果糙箍，如下所示渤愁。其他查詢也可以使用這一邏輯，下面就不顯示了深夯。

SELECT COUNT(*) num_reps_above5
FROM(SELECT s.id, s.name, COUNT(*) num_accounts
     FROM accounts a
     JOIN sales_reps s
     ON s.id = a.sales_rep_id
     GROUP BY s.id, s.name
     HAVING COUNT(*) > 5
     ORDER BY num_accounts) AS Table1;

• 有多少個客戶具有超過 20 個訂單抖格？

SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING COUNT(*) > 20
ORDER BY num_orders;

• 哪個客戶的訂單最多？

SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY num_orders DESC
LIMIT 1;

• 有多少個客戶在所有訂單上消費的總額超過了 30,000 美元咕晋？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING SUM(o.total_amt_usd) > 30000
ORDER BY total_spent;

• 有多少個客戶在所有訂單上消費的總額不到 1,000 美元雹拄？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING SUM(o.total_amt_usd) < 1000
ORDER BY total_spent;

• 哪個客戶消費的最多？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_spent DESC
LIMIT 1;

• 哪個客戶消費的最少掌呜？

SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_spent
LIMIT 1;

• 哪個客戶使用 facebook 作為與消費者溝通的渠道超過 6 次滓玖？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name, w.channel
HAVING COUNT(*) > 6 AND w.channel = 'facebook'
ORDER BY use_of_channel;

• 哪個客戶使用 facebook 作為溝通渠道的次數(shù)最多？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE w.channel = 'facebook'
GROUP BY a.id, a.name, w.channel
ORDER BY use_of_channel DESC
LIMIT 1;

• 哪個渠道是客戶最常用的渠道质蕉？

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name, w.channel
ORDER BY use_of_channel DESC
LIMIT 10;

上面的所有 10 列都是 direct呢撞。

DATE

在 SQL 中，按照日期列分組通常不太實用饰剥，因為這些列可能包含小到一秒的交易數(shù)據(jù)殊霞。按照如此詳細(xì)的級別保存信息即有好處，又存在不足之處汰蓉，因為提供了非常準(zhǔn)確的信息（好處）绷蹲，但是也讓信息分組變得很難（不足之處），幾乎所有訂單的時間戳都是唯一的。

日期的聚合問題

幸運的是祝钢，有很多 SQL 內(nèi)置函數(shù)可以幫助我們改善日期處理體驗比规。

這里，我們看到日期存儲為年拦英、月蜒什、日、小時疤估、分鐘灾常、秒，可以幫助我們截取信息铃拇。在下個部分钞瀑，你將看到在 SQL 中我們可以使用大量函數(shù)來利用這一功能。

日期函數(shù) DATE_TRUNC

DATE_TRUNC 使你能夠?qū)⑷掌诮厝〉饺掌跁r間列的特定部分慷荔。常見的截取依據(jù)包括日期雕什、月份 和 年份。這是一篇 MODE 發(fā)表的精彩博文显晶，介紹了關(guān)于此函數(shù)的強(qiáng)大功能贷岸。

DATE_PART 可以用來獲取日期的特定部分，但是注意獲取 month 或 dow （day of week磷雇，會返回0~6的值漾岳，0代表周日棚放、6代表周六）意味著無法讓年份按順序排列隙姿。而是按照特定的部分分組荣茫，無論它們屬于哪個年份落剪。

要了解其他日期函數(shù)睁本，請參閱這篇文檔，但是上面介紹的函數(shù)絕對夠你入門了忠怖！

可用day來分組呢堰，一些數(shù)據(jù)庫采用year分組，但是結(jié)果返回有時候會錯誤

比如 day, week, month, quarter, year

提取感興趣的日期的某一小部分

看看周幾銷量最高

練習(xí)

處理日期

• Parch & Posey 在哪一年的總銷售額最高凡泣？數(shù)據(jù)集中的所有年份保持均勻分布嗎枉疼？

SELECT DATE_PART('year', occurred_at) ord_year,  SUM(total_amt_usd) total_spent
FROM orders
GROUP BY 1
ORDER BY 2 DESC;

對于 2013 年和 2017 年來說，每一年只有一個月的銷量（2013 年為 12鞋拟，2017 年為 1）骂维。 因此，二者都不是均勻分布贺纲。銷量一年比一年高航闺，2016 年是到目前為止最高的一年。按照這個速度，我們預(yù)計 2017 年可能是最高銷量的一年潦刃。

• Parch & Posey 在哪一個月的總銷售額最高侮措？數(shù)據(jù)集中的所有月份保持均勻分布嗎？

為了保持公平乖杠，我們應(yīng)該刪掉 2013 年和 2017 年的銷量分扎。原因如上。

SELECT DATE_PART('month', occurred_at) ord_month, SUM(total_amt_usd) total_spent
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC;

12 月的銷量最高胧洒。

Parch & Posey 在哪一年的總訂單量最多畏吓？數(shù)據(jù)集中的所有年份保持均勻分布嗎？

SELECT DATE_PART('year', occurred_at) ord_year,  COUNT(*) total_sales
FROM orders
GROUP BY 1
ORDER BY 2 DESC;

同樣略荡，到目前為止庵佣，2016 年的訂單量最多，但是與數(shù)據(jù)集中的其他年份相比汛兜，2013 年和 2017 年的分布不均勻巴粪。

• Parch & Posey 在哪一個月的總訂單量最多？數(shù)據(jù)集中的所有年份保持均勻分布嗎粥谬？

SELECT DATE_PART('month', occurred_at) ord_month, COUNT(*) total_sales
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC;

12 月依然是銷量最多的月份肛根，但是有趣的是，11 月是銷量第二多的月份漏策。為了保持公平派哲，刪掉了 2017 年和 2013 年的數(shù)據(jù)。

• Walmart 在哪一年的哪一個月在銅版紙上的消費最多掺喻？

SELECT DATE_TRUNC('month', o.occurred_at) ord_date, SUM(o.gloss_amt_usd) tot_spent
FROM orders o 
JOIN accounts a
ON a.id = o.account_id
WHERE a.name = 'Walmart'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

在 2016 年 5 月芭届，Walmart 在銅版紙上的消費做多。

CASE

無ELSE

加入ELSE

多渠道還可以加OR

WHEN其實就相當(dāng)于你想在WHERE語句中加入邏輯條件感耙。

多種情況

但是各個WHEN中最好還是構(gòu)建不重疊的

提示

• CASE 語句始終位于 SELECT 條件中褂乍。

• CASE 必須包含以下幾個部分：WHEN、THEN 和 END即硼。ELSE 是可選組成部分逃片，用來包含不符合上述任一 CASE 條件的情況。

• 你可以在 WHEN 和 THEN 之間使用任何條件運算符編寫任何條件語句（例如 WHERE）只酥，包括使用 AND 和 OR 連接多個條件語句褥实。

• 你可以再次包含多個 WHEN 語句以及 ELSE 語句，以便處理任何未處理的條件裂允。

示例

在第一節(jié)課的練習(xí)中损离，你看到了以下問題：

1. 創(chuàng)建一列用于將 standard_amt_usd 除以 standard_qty，以便計算每個訂單的標(biāo)準(zhǔn)紙張的單價绝编，將結(jié)果限制到前 10 個訂單草冈，并包含 id 和 account_id 字段。注意 - 如果你的答案正確，系統(tǒng)將顯示一個錯誤怎棱，這是因為你除以了 0哩俭。當(dāng)你在下個部分學(xué)習(xí) CASE 語句時，你將了解如何讓此查詢不會報錯拳恋。

我們來看看如何使用 CASE 語句來避免這一錯誤凡资。

SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price
FROM orders
LIMIT 10;

現(xiàn)在我們使用一個 CASE 語句，這樣的話谬运，一旦 standard_qty 為 0隙赁，我們將返回 0，否則返回 unit_price梆暖。

SELECT account_id, CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0
                        ELSE standard_amt_usd/standard_qty END AS unit_price
FROM orders
LIMIT 10;

該語句的第一部分將捕獲任何分母為 0 并導(dǎo)致錯誤的情況伞访，其他部分將按照常規(guī)步驟相除。你將發(fā)現(xiàn)對于標(biāo)準(zhǔn)紙張轰驳，所有客戶的單價是 4.99 美元厚掷。這樣比較合理，不會波動级解，并且比在上節(jié)課中向分母上加 1 來暫時解決錯誤這一方法更準(zhǔn)確冒黑。

你可以使用下面的數(shù)據(jù)自己嘗試下。

CASE與聚合

練習(xí)

CASE

• 我們想要根據(jù)相關(guān)的購買量了解三組不同的客戶勤哗。最高的一組是終身價值（所有訂單的總銷售額）大于 200,000 美元的客戶抡爹。第二組是在 200,000 到 100,000 美元之間的客戶。最低的一組是低于 under 100,000 美元的客戶芒划。請?zhí)峁┮粋€表格冬竟，其中包含與每個客戶相關(guān)的級別。你應(yīng)該提供客戶的名稱民逼、所有訂單的總銷售額和級別泵殴。消費最高的客戶列在最上面。

SELECT a.name, SUM(total_amt_usd) total_spent, 
     CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
     WHEN  SUM(total_amt_usd) > 100000 THEN 'middle'
     ELSE 'low' END AS customer_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
GROUP BY a.name
ORDER BY 2 DESC;

• 現(xiàn)在我們想要執(zhí)行和第一個問題相似的計算過程缴挖，但是我們想要獲取在 2016 年和 2017 年客戶的總消費數(shù)額。級別和上一個問題保持一樣焚辅。消費最高的客戶列在最上面映屋。

SELECT a.name, SUM(total_amt_usd) total_spent, 
     CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
     WHEN  SUM(total_amt_usd) > 100000 THEN 'middle'
     ELSE 'low' END AS customer_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE occurred_at > '2015-12-31' 
GROUP BY 1
ORDER BY 2 DESC;

• 我們想要找出績效最高的銷售代表，也就是有超過 200 個訂單的銷售代表同蜻。創(chuàng)建一個包含以下列的表格：銷售代表名稱棚点、訂單總量和標(biāo)為 top 或 not 的列（取決于是否擁有超過 200 個訂單）。銷售量最高的銷售代表列在最上面湾蔓。

SELECT s.name, COUNT(*) num_ords,
     CASE WHEN COUNT(*) > 200 THEN 'top'
     ELSE 'not' END AS sales_rep_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name
ORDER BY 2 DESC;

值得注意的是瘫析，上述語句假定每個名稱是唯一的，好幾次都是這么假定的。否則需要根據(jù)名稱和 ID 拆分表格贬循。

• 之前的問題沒有考慮中間水平的銷售代表或銷售額咸包。管理層決定也要看看這些數(shù)據(jù)。我們想要找出績效很高的銷售代表杖虾，也就是有超過 200 個訂單或總銷售額超過 750000 美元的銷售代表烂瘫。中間級別是指有超過 150 個訂單或銷售額超過 500000 美元的銷售代表。創(chuàng)建一個包含以下列的表格：銷售代表名稱奇适、總訂單量坟比、所有訂單的總銷售額，以及標(biāo)為 top嚷往、middle 或 low 的列（取決于上述條件）葛账。在最終表格中將銷售額最高的銷售代表列在最上面。

SELECT s.name, COUNT(*), SUM(o.total_amt_usd) total_spent, 
     CASE WHEN COUNT(*) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top'
     WHEN COUNT(*) > 150 OR SUM(o.total_amt_usd) > 500000 THEN 'middle'
     ELSE 'low' END AS sales_rep_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name
ORDER BY 3 DESC;