今天就淺顯寫一篇文章專門介紹數據處理中常見的sql查詢場景中的復雜查詢。為什么單獨說復雜查詢呢？因為在業務開發中我們常用orm就可以解決很多查詢問題，但是都是比較簡單的那種sql，所以orm就能滿足。但是一旦涉及到復雜的查詢，orm就束手無策了。這個時候我們一般都是寫sql查詢，但是很多sql還是不太復雜。真正復雜的sql一般都是業務上線之后，領導找你統計各個維度的數據，那么這個時候sql能力就非常重要了，因為它往往涉及很多表很多字段很多子查詢。那為了一天就能掌握寫復雜sql的能力，為此爆肝這篇文章，一起學習成長。
一. 明確需求
返回所有國家以及相關呼叫的數量及其平均持續通話時間（以秒為單位）。在結果中，僅顯示平均呼叫持續時間大于所有呼叫的平均呼叫持續時間的國家。
1. 拆解需求
-
需要展示所有國家ID或者國家名稱。 -
相關呼叫的數量：展示國家的呼叫統計數量。 -
平均持續通話時間：展示國家的呼叫平均持續時間，單位：秒。
2. 條件
僅顯示平均呼叫持續時間大于所有呼叫的平均呼叫持續時間的國家：
那就是先計算所有呼叫的AVG(endTime-startTime)，然后每個國家的平均呼叫時間與它比較即可。
3. 涉及到的表：
-
國家：country -
呼叫：call -
城市：city -
用戶：customer
為什么需要city和customer表呢？因為call中有外鍵customer，而customer中有外鍵city，city中有外鍵country。
因為要展示所有國家，因此要查詢country，而想統計呼叫，就得鏈接call，怎么能找到call呢，就得從country->city->customer->call。
二.開始設計查詢
1. 先寫出整體框架SQL
通過明確需求得知，我們需要關聯四張表：country，city，customer和call。
拆解需求中提到“展示所有國家的名稱”，那么這是大前提，因此如果我們想正確使用這些表，我們需要使用外鍵LEFT JOIN這些表。甚至我們現在不考慮最終查詢，我們就知道它將包含以下部分：
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;
這一步驟非常必要，因為我們可以看到數據是什么樣子的，為后面的查詢做好基礎。
2. 在添磚加瓦
計算條件中的查詢：
SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call
DATEDIFF函數計算開始時間和結束時間之間給定時間段（單位：秒）的單位差。
計算相關呼叫的數量：
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls
SUM函數對呼叫統計數量。
計算平均持續通話時間：
AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diff
這里大家可能發現我有對NULL做了判斷，不管是使用ISNULL函數還是IS NOT NULL判斷。為什么呢？因為LEFT JOIN的時候，右邊可能出現NULL的情況，我們怎么知道的呢？這就是我們上面提到的：必須將整體SQL框架先打印看結果，知道數據長啥樣，自然就知道哪里需要用NULL邏輯特殊處理了。
好了，現在可以把這些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
最后一步：按照條件：“僅顯示平均呼叫持續時間大于所有呼叫的平均呼叫持續時間的國家”，我們可以很容易得出，查詢最后的結果需要用HAVING對聚合結果做下過濾，因為“所有呼叫的平均呼叫持續時間”和“均呼叫持續時間”我們已經統計出來了，因此大于長這樣：
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長這樣：
--返回所有國家以及相關呼叫的數量及其平均持續通話時間（以秒為單位）。在結果中，僅顯示平均呼叫持續時間大于所有呼叫的平均呼叫持續時間的國家。
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
-- 過濾掉不符合條件的結果
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并打印結果看數據很重要這一步 -
創建子查詢，并且測試結果，最后添加到整體框架SQL中 -
測試驗證所有數據 -
添加備注，復雜SQL不寫備注等于沒寫
