今天就淺顯寫一篇文章專門介紹數(shù)據(jù)處理中常見的sql查詢場景中的復(fù)雜查詢。為什么單獨(dú)說復(fù)雜查詢呢？因?yàn)樵跇I(yè)務(wù)開發(fā)中我們常用orm就可以解決很多查詢問題，但是都是比較簡單的那種sql，所以orm就能滿足。但是一旦涉及到復(fù)雜的查詢，orm就束手無策了。這個(gè)時(shí)候我們一般都是寫sql查詢，但是很多sql還是不太復(fù)雜。真正復(fù)雜的sql一般都是業(yè)務(wù)上線之后，領(lǐng)導(dǎo)找你統(tǒng)計(jì)各個(gè)維度的數(shù)據(jù)，那么這個(gè)時(shí)候sql能力就非常重要了，因?yàn)樗婕昂芏啾砗芏嘧侄魏芏嘧硬樵儭Ｄ菫榱艘惶炀湍苷莆諏憦?fù)雜sql的能力，為此爆肝這篇文章，一起學(xué)習(xí)成長。
一. 明確需求
返回所有國家以及相關(guān)呼叫的數(shù)量及其平均持續(xù)通話時(shí)間（以秒為單位）。在結(jié)果中，僅顯示平均呼叫持續(xù)時(shí)間大于所有呼叫的平均呼叫持續(xù)時(shí)間的國家。
1. 拆解需求
-
需要展示所有國家ID或者國家名稱。 -
相關(guān)呼叫的數(shù)量：展示國家的呼叫統(tǒng)計(jì)數(shù)量。 -
平均持續(xù)通話時(shí)間：展示國家的呼叫平均持續(xù)時(shí)間，單位：秒。
2. 條件
僅顯示平均呼叫持續(xù)時(shí)間大于所有呼叫的平均呼叫持續(xù)時(shí)間的國家：
那就是先計(jì)算所有呼叫的AVG(endTime-startTime)，然后每個(gè)國家的平均呼叫時(shí)間與它比較即可。
3. 涉及到的表：
-
國家：country -
呼叫：call -
城市：city -
用戶：customer
為什么需要city和customer表呢？因?yàn)閏all中有外鍵customer，而customer中有外鍵city，city中有外鍵country。
因?yàn)橐故舅袊遥虼艘樵僣ountry，而想統(tǒng)計(jì)呼叫，就得鏈接call，怎么能找到call呢，就得從country->city->customer->call。
二.開始設(shè)計(jì)查詢
1. 先寫出整體框架SQL
通過明確需求得知，我們需要關(guān)聯(lián)四張表：country，city，customer和call。
拆解需求中提到“展示所有國家的名稱”，那么這是大前提，因此如果我們想正確使用這些表，我們需要使用外鍵LEFT JOIN這些表。甚至我們現(xiàn)在不考慮最終查詢，我們就知道它將包含以下部分：
SELECT
...
FROM country
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON city.id = customer.city_id
LEFT JOIN call ON call.customer_id = customer.id
...;
到這里我們必須做一件事，那就是測試這樣的查詢返回什么：
SELECT
*
FROM country
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON city.id = customer.city_id
LEFT JOIN call ON call.customer_id = customer.id;
這一步驟非常必要，因?yàn)槲覀兛梢钥吹綌?shù)據(jù)是什么樣子的，為后面的查詢做好基礎(chǔ)。
2. 在添磚加瓦
計(jì)算條件中的查詢：
SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call
DATEDIFF函數(shù)計(jì)算開始時(shí)間和結(jié)束時(shí)間之間給定時(shí)間段（單位：秒）的單位差。
計(jì)算相關(guān)呼叫的數(shù)量：
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls
SUM函數(shù)對呼叫統(tǒng)計(jì)數(shù)量。
計(jì)算平均持續(xù)通話時(shí)間：
AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diff
這里大家可能發(fā)現(xiàn)我有對NULL做了判斷，不管是使用ISNULL函數(shù)還是IS NOT NULL判斷。為什么呢？因?yàn)?code>LEFT JOIN的時(shí)候，右邊可能出現(xiàn)NULL的情況，我們怎么知道的呢？這就是我們上面提到的：必須將整體SQL框架先打印看結(jié)果，知道數(shù)據(jù)長啥樣，自然就知道哪里需要用NULL邏輯特殊處理了。
好了，現(xiàn)在可以把這些sql添加到SQL框架中了：
SELECT
country.country_name,
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls,
AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diff
FROM country
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON city.id = customer.city_id
LEFT JOIN call ON call.customer_id = customer.id
GROUP BY
country.id,
country.country_name
最后一步：按照條件：“僅顯示平均呼叫持續(xù)時(shí)間大于所有呼叫的平均呼叫持續(xù)時(shí)間的國家”，我們可以很容易得出，查詢最后的結(jié)果需要用HAVING對聚合結(jié)果做下過濾，因?yàn)?ldquo;所有呼叫的平均呼叫持續(xù)時(shí)間”和“均呼叫持續(xù)時(shí)間”我們已經(jīng)統(tǒng)計(jì)出來了，因此大于長這樣：
HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
所以最后的SQL長這樣：
--返回所有國家以及相關(guān)呼叫的數(shù)量及其平均持續(xù)通話時(shí)間（以秒為單位）。在結(jié)果中，僅顯示平均呼叫持續(xù)時(shí)間大于所有呼叫的平均呼叫持續(xù)時(shí)間的國家。
SELECT
country.country_name,
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls,
AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diff
FROM country
-- 使用LEFT JOIN包括沒有任何呼叫的國家
LEFT JOIN city ON city.country_id = country.id
LEFT JOIN customer ON city.id = customer.city_id
LEFT JOIN call ON call.customer_id = customer.id
GROUP BY
country.id,
country.country_name
-- 過濾掉不符合條件的結(jié)果
HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
三.總結(jié)步驟
-
先明確需求 -
拆解展示字段和條件 -
確定所要用到的表 -
先寫出整體框架SQL并打印結(jié)果看數(shù)據(jù)很重要這一步 -
創(chuàng)建子查詢，并且測試結(jié)果，最后添加到整體框架SQL中 -
測試驗(yàn)證所有數(shù)據(jù) -
添加備注，復(fù)雜SQL不寫備注等于沒寫
