一. 數(shù)據(jù)庫與SQL語言簡介簡介

1.1 SQL：與數(shù)據(jù)庫交互的語言

SQL的全稱是結(jié)構(gòu)化查詢語言（Structured Query Language）猛遍。它屬于領(lǐng)域特定語言（Domain Specific Language）范疇，用于管理關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。SQL基于關(guān)系代數(shù)理論建立的喳篇，包括數(shù)據(jù)定義語言和數(shù)據(jù)操縱語言敌买。SQL的范圍包括數(shù)據(jù)插入，查詢猜揪，更新和刪除惭墓，數(shù)據(jù)庫模式創(chuàng)建和修改，以及數(shù)據(jù)訪問控制而姐。

一般來說腊凶，我們會將數(shù)據(jù)按照一定的范式進行設(shè)計，然后將其設(shè)計為具有相互聯(lián)系的表格拴念，然后將其存放到數(shù)據(jù)庫中钧萍。能夠被這樣設(shè)計的數(shù)據(jù)，我們稱為“結(jié)構(gòu)化數(shù)據(jù)”政鼠。還有一些數(shù)據(jù)是無法結(jié)構(gòu)化的风瘦，我們稱為非結(jié)構(gòu)化數(shù)據(jù)，比如圖像公般，視頻和音頻等等万搔。

除了后端開發(fā)工程師要跟數(shù)據(jù)打交道，SQL也是數(shù)據(jù)分析師必備的基礎(chǔ)技能之一官帘。SQL語義容易理解瞬雹，分析師可以直接訪問數(shù)據(jù)，不需要拿個U盤拷來拷去刽虹。數(shù)據(jù)存放在數(shù)據(jù)庫中易于審核和復(fù)制酗捌。分析師可以通過復(fù)雜的查詢語句一次查詢和分析多個表，分析更復(fù)雜的問題状婶。企業(yè)使用SQL則可以進行數(shù)據(jù)完整性檢查意敛，可以快速訪問數(shù)據(jù)。而且企業(yè)的多個團隊可以并發(fā)使用膛虫，容易共享有價值的數(shù)據(jù)草姻。有大量的工作要用到SQL技能，Top 10有：

1. 商業(yè)分析師
2. 高級軟件工程師
3. 高級SQL Server數(shù)據(jù)庫管理員
4. 質(zhì)量保障測試
5. 網(wǎng)絡(luò)開發(fā)者
6. 系統(tǒng)管理員
7. 軟件工程師
8. SQL Server開發(fā)者
9. 質(zhì)量保障分析師
10. 高級Oracle數(shù)據(jù)庫管理員

那么DB是如何存儲數(shù)據(jù)的呢稍刀？數(shù)據(jù)被存儲于類似Excel電子表格的表中撩独，同一列中所有數(shù)據(jù)必須是同一數(shù)據(jù)類型敞曹，這樣有助于實現(xiàn)快速訪問數(shù)據(jù)。業(yè)界流行的DB有：

1. MySQL
2. Oracle
3. Microsoft SQL Server
4. Postgres
5. SQLite

1.2 重要概念：實體關(guān)系圖

實體關(guān)系圖（ERD）是查看數(shù)據(jù)庫中數(shù)據(jù)的常用方式综膀。數(shù)據(jù)庫中的表并不是孤立的澳迫，相互之間會通過主鍵-外鍵建立聯(lián)系。這些圖可以幫助你可視化正在分析的數(shù)據(jù)剧劝，它包括三個元素：

1. 表的名稱
2. 每個表中的列名
3. 表配合工作的方式橄登，即主鍵和外鍵之間的對應(yīng)關(guān)系

實體關(guān)系圖示例

在開始查詢數(shù)據(jù)前通過ERD圖理解數(shù)據(jù)庫表之間的關(guān)系，有助于使用SQL語言進行各種復(fù)雜的查詢讥此，解決各種復(fù)雜的問題拢锹。上圖為課程中用到的Parch & Posey數(shù)據(jù)庫。這是一家銷售紙張的虛構(gòu)公司萄喳，銷售普通紙卒稳，海報紙和銅版紙。他們有些客戶他巨，都是通過在線廣告招徠的：

1. web_events：在線廣告渠道數(shù)據(jù)
2. accounts：客戶數(shù)據(jù)
3. orders：訂單數(shù)據(jù)
4. sales_reps：銷售代表數(shù)據(jù)
5. region：銷售區(qū)域數(shù)據(jù)

ERD使用Crow's Foot Notation來表示表之間的關(guān)系充坑。PK的意思是Primary Key，即主鍵染突，F(xiàn)K表示Foreign Key捻爷，即外鍵。某個表的主鍵是另一個表的外鍵份企，比如accounts表中的主鍵id就是orders表中的外鍵account_id役衡，意味著我可以通過這個關(guān)聯(lián)查找某個客戶的訂單數(shù)據(jù)薪棒。復(fù)雜的數(shù)據(jù)查詢就是這么建立起來的，下面我們就以這個數(shù)據(jù)庫為例，分三個階段，由淺入深入門SQL語言。

二. SQL語言入門

2.1 基本的SQL查詢

SELECT語句是SQL中的查詢語句刘莹。其基本語法格式為：

SELECT 逗號分隔的列名列表 FROM 表名;

例如矿咕，從訂單表（orders）中查詢id和發(fā)生時間（ocurred_at），則可以這么寫：

SELECT id, occurred_at FROM orders;

如果想要查詢所有的列剥悟，也可以不用顯式地列出字段集的名字毁枯，而是用*號代替赂韵。FROM子句用于指明數(shù)據(jù)的來源质涛，即表名。

關(guān)于SQL語句的格式纠炮，這里還要特別說明一下。對于SQL命令（SELECT，F(xiàn)ROM），我們選擇大寫，而其他內(nèi)容比如列名選擇小寫。SQL本身并不區(qū)分大小寫拓颓，然而適當?shù)挠么笮憗韰^(qū)分命令和其他內(nèi)容场航，能夠增加SQL語句的可讀性嫡意。另外表和變量名的命名不要有空格蔬螟，用下劃線來代替。每個SQL語句寫完之后要寫一個分號汽畴。關(guān)鍵字的順序也很重要旧巾，有時候?qū)戝e順序耸序，SQL在執(zhí)行的時候就會報錯。

這里推薦一種以SQL命令開頭的寫法鲁猩。這種寫法以多行完成一個SQL語句磺樱。能夠進一步提高可讀性：

SELECT account_id
FROM orders;

對于做后端開發(fā)的同學(xué)而言皂甘，還有一種最佳實踐淋袖，是給字段加上反引號婴削，這么做是因為SQL是不區(qū)分大小寫的，萬一不小心列名與SQL的某個關(guān)鍵字重復(fù)了隙券，使用反引號能指明這是列名男应，而不是關(guān)鍵字，能夠避免SQL執(zhí)行錯誤：

SELECT `id`, `account_id`
FROM orders;

查詢語句中還有其他一些比較常見的子句娱仔，這些子句是可選的沐飘，但這些可選的子句很多時候能夠make your life much better。下面我們就來一一介紹一下牲迫。

2.1.1 LIMIT

LIMIT子句用于查詢前幾行耐朴。有時候我們查詢某個表格只是為了觀察下它有哪些列，并不需要全部查詢出來恩溅，特別是表格數(shù)據(jù)量很大的時候，如果像上面那樣編寫會輸出全部的數(shù)據(jù)谓娃，滾屏輸出要花很長時間脚乡，使用LIMIT子句，我們可以只查看前5行：

SELECT id, account_id 
FROM orders
LIMIT 5;

2.1.2 ORDER BY

ORDER BY子句可使我們按任意順序?qū)Σ樵兘Y(jié)果進行排序滨达。默認的排序是升序奶稠，如果需要降序排列，則可以在之后添加DESC捡遍，比如查看最新的10個訂單數(shù)據(jù)锌订，可以這么寫：

SELECT *
FROM orders
ORDER BY occurred_at DESC
LIMIT 10;

ORDER BY子句可以實現(xiàn)多列排序。比如想要將查詢結(jié)果先按照賬戶進行排序画株，然后對于每個賬戶的訂單數(shù)據(jù)從大到小排序辆飘。這樣就能迅速看出每個賬戶最大的一筆訂單了：

SELECT account_id, total_amt_usd
FROM orders
ORDER BY account_id, total_amt_usd DESC;

注意這里排列的順序很重要，這里的DESC只對total_amt_usd起作用谓传。

2.1.3 WHERE

用于在查詢時指定結(jié)果集必須滿足的條件蜈项。相當于使用篩選功能。WHERE 語句中使用的常用符號包括：

1. >（大于）
2. <（小于）
3. >=（大于或等于）
4. <=（小于或等于）
5. =（等于）
6. !=（不等于）

舉例续挟，查詢某個客戶（ID=4251）最新的10個訂單數(shù)據(jù)：

SELECT *
FROM orders
WHERE account_id = 4251
ORDER BY occurred_at
LIMIT 10;

比較運算符可以與非數(shù)值數(shù)據(jù)字段一起使用紧卒。我們可以使用=和!=，分別表示“等于”和“不等于”诗祸，文本數(shù)據(jù)注意使用單引號跑芳。舉例轴总，查詢名稱為United Technologies的客戶資料信息：

SELECT *
FROM accounts
WHERE name = 'United Technologies';

2.1.4 算術(shù)運算符

還可以通過算數(shù)運算符，將現(xiàn)有的列進行組合博个，得到派生列怀樟。常見運算包括：

1. *（乘法）
2. +（加法）
3. -（減法）
4. /（除法）

與在做數(shù)據(jù)分析時根據(jù)已有特征創(chuàng)建新特征是一個道理。使用派生列的時候需要對它進行命名坡倔，可以使用AS命令漂佩。舉例，從訂單數(shù)據(jù)中查詢賬戶名罪塔，時間投蝉，標準紙購買數(shù)量，銅版紙購買數(shù)量征堪，海報紙購買數(shù)量和非標準類紙購買數(shù)量（即銅版紙和海報紙數(shù)量之和）：

SELECT account_id, 
  occurred_at, 
  standard_qty, 
  gloss_qty, 
  poster_qty, 
  gloss_qty + poster_qty AS nonstandard_qty
FROM orders;

2.1.5 邏輯運算符

邏輯運算符包括：

1. LIKE：模糊查找和模糊匹配
2. IN ：用于多個條件的情況瘩缆，相當于從某個集合中選取
3. NOT：這與 IN 和 LIKE 一起使用，用于選擇 NOT LIKE 或 NOT IN 某個條件的所有行
4. AND & BETWEEN：可用于組合所有組合條件必須為真的運算
5. OR：可用于組合至少一個組合條件必須為真的運算佃蚜。

LIKE運算符用于處理文本庸娱，我們經(jīng)常在WHERE子句中使用。LIKE運算符經(jīng)常與%一起使用谐算。后者相當于通配符熟尉，表示任意數(shù)量的任意字符。舉例：查詢引用頁URL中包含“google”的數(shù)據(jù)：

SELECT *
FROM web_events
WHERE referrer_url LIKE '%google%';

IN運算符用于從若干可能的值集合中篩選數(shù)據(jù)洲脂。比如如果我們只想看沃爾瑪和蘋果公司的賬戶信息斤儿，可以這么查詢：

SELECT *
FROM accounts
WHERE name IN ('Walmart', 'Apple');

NOT 可以與之前介紹的兩個運算符 IN 和 LIKE 一起運算】纸酰可以查找到所有不符合特定條件的行往果。舉例，查找除321500和321570以外的其他銷售代表：

SELECT sales_rep_id, name
FROM accounts
WHERE sales_rep_id NOT IN (321500, 321570)
ORDER BY sales_rep_id;

或者查找引用頁URL中不包含“google”的數(shù)據(jù)：

SELECT * 
FROM web_events
WHERE referer_url NOT LIKE '%google%';

AND運算符表達邏輯與運算陕贮，用于 WHERE 語句中潘飘，可一次性同時滿足多個邏輯子句。使用 AND 語句進行連接時要指定感興趣的列卜录。比如查詢2016年4月1日后，2016年10月1日前的訂單數(shù)據(jù)：

SELECT * 
FROM orders
WHERE occurred_at >= '2016-04-01' AND occurred_at <= '2016-10-01'
ORDER BY occurred_at DESC;

可以用BETWEEN運算符改寫峦甩，請參考：

SELECT * 
FROM orders
WHERE occurred_at BETWEEN  '2016-04-01' AND  '2016-10-01'
ORDER BY occurred_at DESC;

與 AND 運算符類似，OR 運算符可以組合多個語句。但是與AND不同凯傲，OR組合的多個條件中只至少有一個滿足即可犬辰。比如從訂單數(shù)據(jù)中查詢不是三種紙張都采購的訂單：

SELECT account_id, 
  occurred_at, 
  standard_qty, 
  gloss_qty, 
  poster_qty
FROM orders
WHERE standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0;

SQL的基本用法就介紹到這里。但SQL的強大遠不止如此冰单。對于比較復(fù)雜的分析問題而言幌缝，我們需要進行更復(fù)雜的連表查詢才能回答。因此下面我們來重點研究下如何使用SQL進行表連接復(fù)雜查詢诫欠。

2.2 SQL的JOIN運算

SQL的強大之處在于它可以同時處理多張表格涵卵。關(guān)系型數(shù)據(jù)庫中的表都是相互聯(lián)系的，我們需要通過表連接運算從多個表格中整合信息荒叼。

為什么關(guān)系型數(shù)據(jù)庫要分表轿偎，有多種原因。其中一個重要原因是我們可以把表中每一行的數(shù)據(jù)看作一個對象被廓，而對象是有不同類型的坏晦，比如訂單和賬戶就是不同類型的數(shù)據(jù)（對象），分開的話更容易組織嫁乘。另外昆婿，多表格結(jié)構(gòu)可以保證更快的查詢。創(chuàng)建數(shù)據(jù)庫并進行表格設(shè)計時蜓斧，一定要思考如何存儲數(shù)據(jù)仓蛆。設(shè)計新的數(shù)據(jù)庫要理解數(shù)據(jù)庫的規(guī)范化：

1. 表格存儲了邏輯分組的數(shù)據(jù)嗎？
2. 我能在一個位置進行更改挎春，而不是在多個表格中對同一信息作出更改嗎看疙？
3. 我能快速高效地訪問和操縱數(shù)據(jù)嗎？

這篇文章詳細介紹了與數(shù)據(jù)庫規(guī)范化設(shè)計有關(guān)的知識搂蜓。

2.2.1 INNER JOIN

我們先看看被稱為INNER JOIN的內(nèi)連接運算狼荞。后面我們還會接觸其他類型的連接運算辽装。無論什么類型的連接運算帮碰，它們的目標都是一次性從多個表格中獲取數(shù)據(jù)，所以你可以把JOIN看作是第二條FROM子句拾积。比如我們要連表查詢，匹配的條件是accounts表中的id字段和orders表中的account_id字段：

SELECT orders.*, accounts.*
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

那么這個查詢的目的是什么呢斯碌？它的目的是同時從orders和accounts表中查詢數(shù)據(jù)，既然是兩個表的數(shù)據(jù)共同構(gòu)成的結(jié)果集，那每條結(jié)果總要以一定的條件拼接在一起伪煤，條件就是：orders表中的account_id與accounts表中的id相等。

如果重新翻看一下前面提到的實體關(guān)系圖扁誓，我們就能發(fā)現(xiàn)捷泞，accounts表中的id是主鍵肚邢，它對應(yīng)orders表中的account_id列骡湖，是外鍵响蕴。主鍵是特定表格的唯一列浦夷，外鍵是另一個表格中的主鍵。如果想要連接更多的表格呐馆，同樣可以采取相同的邏輯：

FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

連表查詢時一個突出的問題是經(jīng)常要打出多個表格的全名续膳，寫出來的語句往往又很復(fù)雜坟岔，這樣會很累社付。我們完全可以在寫連表查詢語句時使用表的別名縮寫纪隙，讓我們的工作輕松些：

SELECT o.*, a.*
FROM orders o
JOIN accounts a
ON o.account_id = a.id;

2.2.2 其他類型的JOIN

除了INNER JOIN绵咱，我們還有OUTER JOIN運算悲伶。OUTER JOIN又分為左連接LEFT JOIN和右連接RIGHT JOIN麸锉。要理解它們的區(qū)別花沉，需要從集合論的角度去理解，別忘了娩脾，我們的關(guān)系型數(shù)據(jù)庫是基于關(guān)系代數(shù)理論構(gòu)建的柿赊。INNER JOIN運算實際上只會返回兩個表格中都有的行碰声，也就是求交集運算胰挑，如下圖：

INNER JOIN

如果我們要查詢的數(shù)據(jù)并未同時存在與多張表格中脚粟，就要用到外連接運算核无。要理解其他類型的連接運算，首先要學(xué)會區(qū)分左表和右表的概念：

SELECT
FROM left table
LEFT JOIN right table

直接看韋恩圖就能明白左連接和右連接的區(qū)別了炼彪，其實就是包含的元素比內(nèi)連接多了一些：

LEFT JOIN

RIGHT JOIN

其實還有一種被稱為FULL OUTER JOIN的：

FULL OUTER JOIN

數(shù)據(jù)分析中常用的連接運算就這些局义。如果你想更深入理解SQL提供的豐富的連接運算萄唇，那么首先你可以學(xué)習(xí)一下SQL的各種JOIN另萤，然后你可能要理解為什么不能有多對多的關(guān)系四敞，以及完全外連接和完全外連接罕見用法忿危，以及一些比較特殊的連接運算，比如CrossJOIN和SelfJOIN幻梯。另外，SQL JOIN可視化也是不錯的資源伐蒂。

2.3 SQL的聚合運算

學(xué)會了連表查詢逸邦，下面我們可以真正開始用SQL語句來進行基于聚合運算的數(shù)據(jù)分析了雷客。熟練掌握了這些技能桥狡，我們以后就能做知識遷移。當你在使用Python的pandas庫進行數(shù)據(jù)分析娜汁，或者使用Spark進行分布式數(shù)據(jù)分析時掐禁，你會遇到一些和這里介紹的知識似曾相識的概念穆桂。你會觸類旁通很快掌握。

2.3.1 計數(shù)運算

COUNT運算可以計算某列一共有多少行般又。但我們要注意SQL中的一種數(shù)據(jù)類型：NULL巍佑，它表示沒有數(shù)據(jù)堕义，它們經(jīng)常在聚合函數(shù)中被忽略脆栋，這個一定要注意怕膛。比如COUNT運算就只計數(shù)不為NULL的項褐捻。舉例柠逞，計算accounts表格中有多少行數(shù)據(jù)：

SELECT COUNT(*)
FROM accounts;

2.3.2 求和運算

與 COUNT 不同板壮，我們只能針對數(shù)值列使用 SUM个束，因為求和本身只對數(shù)值才有意義。注意SUM會將NULL值當作0處理阱表。舉例贡珊，統(tǒng)計每種紙張類型的總銷量：

SELECT SUM(standard_qty) AS standard,
  SUM(gloss_qty) AS gloss,
  SUM(poster_qty) AS poster
FROM orders;

2.3.3 最值運算

最值運算有兩種门岔，MIN表示求最小值寒随，MAX表示求最大值互艾。比如我們要統(tǒng)計訂單中每種紙張類型的最大購買量和最小購買量：

SELECT MIN(standard_qty) AS standard_min,
  MIN(gloss_qty) AS gloss_min,
  MIN(poster_qty) AS poster_min,
  MAX(standard_qty) AS standard_max,
  MAX(gloss_qty) AS gloss_max,
  MAX(poster_qty) AS poster_max
FROM orders;

MIN和MAX與COUNT相似纫普，它們都可以用在非數(shù)字列上。MIN將返回最小的數(shù)字悦昵、最早的日期或按字母表排序的最之前的非數(shù)字值但指，具體取決于列類型棋凳。MAX則正好相反剩岳，返回的是最大的數(shù)字晓铆、最近的日期骄噪，或與“Z”最接近（按字母表順序排列）的非數(shù)字值链蕊。

2.3.4 均值運算

AVG運算返回的是數(shù)據(jù)的平均值，即某數(shù)值列之和除以數(shù)量陪蜻。該聚合函數(shù)會忽略分子和分母中的NULL值囱皿。舉例，統(tǒng)計各種類型紙張的平均銷量：

SELECT AVG(standard_qty) AS standard_avg,
  AVG(gloss_qty) AS gloss_avg,
  AVG(poster_qty) AS poster_avg
FROM orders;

2.3.5 分組聚合

上述聚合函數(shù)真正厲害的地方在于它們可以和分組運算結(jié)合起來忱嘹。把數(shù)據(jù)按照某些條件進行分組然后進行組內(nèi)聚合運算嘱腥。GROUP BY運算可以創(chuàng)建在聚合運算時相互獨立的分組。要注意拘悦，SELECT語句中沒有被聚合的字段都應(yīng)該顯式出現(xiàn)在GROUP BY中齿兔，比如我們要按照account_id來分組統(tǒng)計不同類型紙張的購買量：

SELECT account_id,
  SUM(standard_qty) AS standard_sum,
  SUM(gloss_qty) AS gloss_sum,
  SUM(poster_qty) AS poster_sum,
FROM orders
GROUP BY account_id
ORDER BY account_id;

我們還可以用多列分組的方式，把數(shù)據(jù)切割稱更細粒度的數(shù)據(jù)塊础米。舉例：統(tǒng)計每個賬戶ID的每個渠道事件：

SELECT account_id,
  channel,
  COUNT(id) AS events
FROM web_events
GROUP BY account_id, channel
ORDER BY account_id, channel;

2.3.6 唯一值

DISTINCT可用于僅返回特定列的唯一值的函數(shù)分苇。比如，檢查每個客戶是不是對應(yīng)了多個渠道：

SELECT DISTINCT account_id,
  channel
FROM web_events
ORDER BY account_id;

2.3.7 聚合列的條件判斷函數(shù)

HAVING之于GROUP BY医寿，就好比WHERE之于FROM竖瘾。對于聚合創(chuàng)建的查詢中的元素執(zhí)行WHERE條件時庸论，就要用到HAVING。HAVING總是緊隨GROUP BY語句之后蒲凶，總是與聚合函數(shù)一起使用灵巧，是聚合字段條件篩選敏弃。舉個例子瓶颠，查詢所有銷售額超過25萬的賬戶：

SELECT account_id, SUM(total_amt_usd) AS total_amt_usd
FROM orders
GROUP BY 1
HAVING SUM(total_amt_usd) > 250000
ORDER BY 2 DESC;

2.3.8 日期運算

在 SQL 中欢搜，直接按照日期列分組聚合通常不實用疮装，因為很多情況下這些列可能包含小到一秒的交易數(shù)據(jù)。按照如此細粒度的級別保存信息专缠，直接分組聚合就沒有意義了墩弯。有很多 SQL 內(nèi)置函數(shù)可以幫助我們改善日期處理體驗梁丘。在數(shù)據(jù)庫中，日期一般是按照YYYY-MM-DD HH:MM:SS的格式存儲的晚吞。

DATE_TRUNC函數(shù)能夠?qū)⑷掌诮厝〉饺掌跁r間列的特定部分集畅。常見的截取依據(jù)包括日期、月份 和 年份媒怯。這篇文章 詳細介紹了此函數(shù)的強大功能。

DATE_PART 可以用來獲取日期的特定部分山宾，但是注意獲取 month 或 dow 意味著無法讓年份按順序排列鞭盟。而是按照特定的部分分組俊扳，無論它們屬于哪個年份。要了解其他日期函數(shù)籽慢，請參閱這篇介紹文檔。

舉例击碗，按天匯總標準紙張的數(shù)量：

SELECT DATE_TRUNC('day', occurred_at) AS day,
  SUM(standard_qty) AS standard_qty_sum
FROM orders
GROUP BY DATE_TRUNC('day', occurred_at)
ORDER BY DATE_TRUNC('day', occurred_at);

2.3.9 條件運算

SQL中的CASE語句就相當于其他語言中的IF語句坷虑，按照WHEN-THEN-ELSE-END的格式書寫定躏。舉例氏捞，根據(jù)訂單大小給訂單打分組標簽：

SELECT account_id, 
  occurred_at, 
  total,
  CASE WHEN total > 500 THEN 'Over 500'
             WHEN total > 300 THEN '301 - 500'
             WHEN total > 100 THEN '101 - 300'
             ELSE '100 or under' END AS total_group
FROM orders;

在CASE的基礎(chǔ)上進行聚合舉例滞造，將訂單分為總銷售額超過500和500及以下的分組：

SELECT CASE WHEN total > 500 THEN 'Over 500'
                          ELSE '500 or under' END AS total_group,
               COUNT(*) AS order_count
FROM orders
GROUP BY 1;

2.4 SQL子查詢和臨時表

SQL還有一種更高級的應(yīng)用达址，是從已得到的查詢結(jié)果中再進行查詢，被稱為子查詢炎辨。子查詢的使用比較靈活捕透，可以用在各種子句中：表名，列名碴萧，單個值激率，條件邏輯等等。

編寫子查詢時勿决，要學(xué)會把復(fù)雜的步驟拆分成幾個不同的部分乒躺。比如要計算出每個渠道每天的平均事件數(shù)，我們要把這個任務(wù)拆分成兩個部分：首先查詢得到第一個表格低缩，提供每個渠道每天的事件次數(shù)嘉冒；然后再在這個表格上進行第二次查詢，對這些值求平均咆繁，先寫出第一個查詢

SELECT DATE_TRUNC('day', occurred_at) AS day,
               channel,
               COUNT(*) AS event_count
FROM web_events
GROUP BY 1, 2

可以直接運行一下上述查詢讳推，看下得到的中間結(jié)果是不是自己想要的。然后在這個的基礎(chǔ)上寫出第二個查詢：

SELECT channel, 
               AVG(event_count) AS avg_event_count
FROM
(SELECT DATE_TRUNC('day', occurred_at) AS day,
               channel,
               COUNT(*) AS event_count
FROM web_events
GROUP BY 1, 2
) sub
GROUP BY 1
ORDER BY 2 DESC;

子查詢實際上可以在很多地方用到玩般。它可以用在任何地方银觅，甚至條件邏輯。比如坏为，想要查詢與首個訂單相同月份的訂單究驴，我們來一步一步的計算，首先編寫一個帶MIN函數(shù)的子查詢得到首個訂單：

SELECT MIN(occurred_at) AS min
FROM orders;

運行一下沒問題匀伏，用DATE_TRUNC函數(shù)得到月份：

SELECT DATE_TRUNC('month', MIN(occurred_at)) AS min_month
FROM orders;

OK洒忧，接下來查詢與首個訂單月份相同的訂單數(shù)據(jù)：

SELECT * 
FROM orders
WHERE DATE_TRUNC('month', occurred_at) = 
(SELECT DATE_TRUNC('month', MIN(occurred_at)) AS min_month
FROM orders)
ORDER BY occurred_at;

2.4.1 公用表表達式

子查詢的一個問題是查詢語句看起來復(fù)雜又冗長，難以理解够颠。公用表表達式可以將查詢分解成單獨的各個部分熙侍，使查詢邏輯易于閱讀。比如上面的這個例子：

SELECT channel, 
               AVG(event_count) AS avg_event_count
FROM
(SELECT DATE_TRUNC('day', occurred_at) AS day,
               channel,
               COUNT(*) AS event_count
FROM web_events
GROUP BY 1, 2
) sub
GROUP BY 1
ORDER BY 2 DESC;

可以用WITH語句改寫為：

WITH events AS (SELECT DATE_TRUNC('day', occurred_at) AS day,
               channel,
               COUNT(*) AS event_count
FROM web_events
GROUP BY 1, 2)

SELECT channel, 
               AVG(event_count) AS avg_event_count
FROM events
GROUP BY 1
ORDER BY 2 DESC;

相比之下要清晰很多履磨。如果我們要創(chuàng)建第二個表格來從中獲取數(shù)據(jù)蛉抓。我們可以按照以下方式來創(chuàng)建額外的表格并從中獲取數(shù)據(jù)：

WITH table1 AS (
SELECT *
FROM web_events),

table2 AS (
SELECT *
FROM accounts)


SELECT *
FROM table1
JOIN table2
ON table1.account_id = table2.id;

然后，你可以按照相同的方式使用WITH語句添加越來越多的表格剃诅。WITH語句為運行時間長的查詢創(chuàng)建臨時表巷送，然后在臨時表的基礎(chǔ)上做其他查詢。這么做的優(yōu)勢在于對于計算代價高的查詢综苔，只做一次惩系，并且使冗長難以閱讀的子查詢變得容易理解位岔。