常見SQL知識點總結(jié)，建議收藏！
SQL是用于數(shù)據(jù)分析和數(shù)據(jù)處理的最重要的編程語言之一， 因此與數(shù)據(jù)科學(xué)相關(guān)的工作（例如數(shù)據(jù)分析師、數(shù)據(jù)科學(xué)家和數(shù)據(jù)工程師）在面試時總會問到關(guān)于 SQL 的問題。
SQL面試問題旨在評估應(yīng)聘者的技術(shù)和解決問題的能力。因此對于應(yīng)聘者來說，關(guān)鍵在于不僅要根據(jù)樣本數(shù)據(jù)編寫出正確的查詢，而且還要像對待現(xiàn)實數(shù)據(jù)集一樣考慮各種場景和邊緣情況。
在這篇文章中，我將介紹 SQL 面試問題中常見的模式，并提供一些在 SQL 查詢中巧妙處理它們的技巧。
01 問問題
要搞定一場 SQL 面試，最重要的是盡量多問問題，獲取關(guān)于給定任務(wù)和數(shù)據(jù)樣本的所有細節(jié)。充分理解需求后，接下來你就可以節(jié)省很多迭代問題的時間，并且能很好地處理邊緣情況。
我注意到許多候選人經(jīng)常還沒完全理解SQL問題或數(shù)據(jù)集， 就直接開始編寫解決方案了。之后，等我指出他們解決方案中存在的問題后，他們只好反復(fù)修改查詢。最后，他們在迭代中浪費了很多面試時間，甚至可能到最后都沒有找到正確的解決方案。
我建議大家在參加SQL面試時，就當(dāng)成是自己在和業(yè)務(wù)伙伴共事。所以在你提供解決方案之前，應(yīng)該要針對數(shù)據(jù)請求了解清楚所有的需求。
舉例
查找薪水最高的前 3 名員工。
樣本Employee_salary表
這里你應(yīng)該要求面試官說清楚“前三名”具體是什么意思。我應(yīng)該在結(jié)果中包括 3 名員工嗎？你要我怎樣處理關(guān)系？此外，請仔細檢查樣本員工數(shù)據(jù)。Salary 字段的數(shù)據(jù)類型是什么？在計算之前是否需要清除數(shù)據(jù)？
02 選哪一個JOIN
在SQL中，JOIN 通常用來合并來自多個表的信息。
有四種不同類型的 JOIN，但在大多數(shù)情況下，我們只使用INNER、LEFT和FULLJOIN，因為 RIGHTJOIN并不是很直觀，還可以使用 LEFTJOIN 很簡單地重寫。在 SQL 面試中，需要根據(jù)給定問題的特定要求選擇你要使用的正確JOIN。
舉例
查找每個學(xué)生參加的課程總數(shù)。（提供學(xué)生 id、姓名和選課的數(shù)量。）
樣本Student和Class_history表
你可能已經(jīng)注意到了，并非所有出現(xiàn)在 Class_history 表中的學(xué)生都出現(xiàn)在了 Student 表中，這可能是因為這些學(xué)生已經(jīng)畢業(yè)了。（這在事務(wù)數(shù)據(jù)庫中實際上是非常典型的情況，因為不再活躍的記錄往往會被刪除。）
根據(jù)面試官是否希望結(jié)果中包含畢業(yè)生，我們需要使用LEFT JOIN或 INNER JOIN來組合兩個表：
WITH?class_count?AS?(
????SELECT?student_id,?COUNT(*)?AS?num_of_class
????FROM?class_history
????GROUP?BY?student_id
)
SELECT?
????c.student_id,
????s.student_name,
????c.num_of_class
FROM?class_count?c
--?CASE?1:?include?only?active?students
JOIN?student?s?ON?c.student_id?=?s.student_id
--?CASE?2:?include?all?students
--?LEFT?JOIN?student?s?ON?c.student_id?=?s.student_id
03 GROUP BY
GROUP BY是SQL中最重要的功能，因為它廣泛用于數(shù)據(jù)聚合。如果在一個 SQL 問題中看到諸如求和、平均值、最小值或最大值之類的關(guān)鍵字，這就表明你可能應(yīng)該在查詢中使用GROUP BY了。
一個常見的陷阱是在GROUP BY過濾數(shù)據(jù)時混淆 WHERE和HAVING——我見過很多人犯了這個錯誤。
舉例
計算每個學(xué)生在每個學(xué)年的必修課程平均 GPA，并找到每個學(xué)期中符合 Dean’s List（GPA≥3.5）資格的學(xué)生。
樣本Gpa_history表
由于我們在GPA計算中僅考慮必修課程，因此需要使用WHERE is_required=TRUE來排除選修課程。
我們需要每位學(xué)生在每學(xué)年的平均GPA，因此我們將同時GROUP BY student_id和School_year 列，并取Gpa列的平均值。最后，我們只保留學(xué)生平均 GPA高于3.5的行，可以使用HAVING來實現(xiàn)。合起來是下面這樣：
SELECT?
????student_id,
????school_year,
????AVG(gpa)?AS?avg_gpa
FROM?gpa_history
WHERE?is_required?=?TRUE?
GROUP?BY?student_id,?school_year
HAVING?AVG(gpa)?>=?3.5
注意：每當(dāng)在查詢中使用GROUP BY時，都只能選擇Group-by列和聚合列，因為其他列中的行級信息已被舍棄。
04 SQL 查詢執(zhí)行順序
大多數(shù)人會從SELECT開始，從上到下編寫SQL查詢。
但你知道SQL引擎執(zhí)行函數(shù)時要到后面才執(zhí)行SELECT嗎？以下是 SQL 查詢的執(zhí)行順序：
FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT, OFFSET
再次考慮前面的示例：
因為我們想在計算平均GPA之前過濾掉選修課程，所以我使用WHERE is_required=TRUE代替HAVING，因為WHERE會在GROUP BY和HAVING之前執(zhí)行。我不能編寫HAVING avg_gpa >= 3.5的原因是，Avg_gpa被定義為SELECT的一部分，因此無法在SELECT之前執(zhí)行的步驟中引用它。
我建議在編寫查詢時遵循引擎的執(zhí)行順序，這在編寫復(fù)雜查詢時會很有用。
05 Window 函數(shù)
Window函數(shù)也經(jīng)常出現(xiàn)在SQL面試中。共有五種常見的Window函數(shù)：
**RANK/DENSE_RANK/ROW_NUMBER：**它們通過排序特定列來為每行分配一個排名。如果給出了任何分區(qū)列，則行將在其所屬的分區(qū)組中排名。
**LAG/LEAD：**它根據(jù)指定的順序和分區(qū)組從前一行或后一行檢索列值。
在SQL面試中，重要的是要了解排名函數(shù)之間的差異，并知道何時使用LAG/LEAD。
舉例
查找每個部門中薪水最高的前 3 名員工。
另一個示例Employee_salary表
當(dāng)一個SQL問題要求計算“TOP N”時，我們可以使用ORDER BY或排名函數(shù)來回答問題。
但在這個示例中，它要求計算“每個 Y 中的 TOP N X”，這強烈暗示我們應(yīng)該使用排名函數(shù)，因為我們需要對每個分區(qū)組中的行進行排名。
以下查詢恰好能找到 3 名薪水最高的員工，而不論他們的關(guān)系如何，如下：
WITH?T?AS?(
SELECT?
????*,
????ROW_NUMBER()?OVER?(PARTITION?BY?department_id?ORDER?BY?employee_salary?DESC)?AS?rank_in_dep
FROM?employee_salary)
SELECT?*?FROM?T
WHERE?rank_in_dep?<=?3?
--?Note:?When?using?ROW_NUMBER,?each?row?will?have?a?unique?rank?number?and?ranks?for?tied?records?are?assigned?randomly.?For?exmaple,?Rimsha?and?Tiah?may?be?rank?2?or?3?in?different?query?runs.
此外，根據(jù)關(guān)系的處理方式，我們可以選擇其他排名函數(shù)。同樣，細節(jié)是很重要的！
ROW_NUMBER,RANK,DENSE_RANK結(jié)果比較
06 重復(fù)項
SQL面試中的另一個常見陷阱是忽略數(shù)據(jù)重復(fù)。?
盡管樣本數(shù)據(jù)中的某些列似乎具有不同的值，但面試官還是希望候選人考慮所有可能性，就像他們在處理真實數(shù)據(jù)集一樣。
例如
在上一個示例Employee_salary表中，可以讓雇員共享相同的名稱。?
要避免由重復(fù)項導(dǎo)致的潛在問題，一種簡單方法是始終使用 ID 列唯一地標(biāo)識不同的記錄。
舉例
使用 Employee_salary 表查找每個部門所有員工的總薪水。正確的解決方案是 GROUP BY employee_id，然后使用 SUM(employee_salary) 計算總薪水。如果需要雇員姓名，請在末尾與 Employee 表聯(lián)接以檢索雇員姓名信息。錯誤的方法是使用 GROUP BY employee_name。
07 NULL
在SQL中，任何謂詞都可以產(chǎn)生三個值之一True，F(xiàn)alse和NULL，后者是Unknown或Missing數(shù)據(jù)值的保留關(guān)鍵字。處理NULL數(shù)據(jù)集時可能會意外地很棘手。
在SQL面試中，面試官可能會特別注意解決方案是否處理了NULL值。有時，很明顯有一列是不能Nullabl的，但對于其他大多數(shù)列來說，很有可能會有NULL值。
建議：確認示例數(shù)據(jù)中的關(guān)鍵列是否為Nullable，
如果可以，請利用IS(NOT)NULL，IFNULL和COALESCE 之類的函數(shù)來覆蓋這些邊緣情況。
08 交流
最后一點也非常重要：在SQL面試期間要隨時與面試官溝通交流。
我面試過的許多候選人都很沉默寡言，有疑問的時候才會知聲。當(dāng)然如果他們最終給出了完美的解決方案，那也不是什么問題。
但是，在技術(shù)面試期間保持溝通交流往往會是有價值的。
例如：你可以談?wù)搶栴}和數(shù)據(jù)的理解，說明你計劃如何解決問題，為什么使用某些函數(shù)而不是其他選項，以及正在考慮哪些極端情況。
09 總結(jié)
首先要提問，收集所需的細節(jié)
在INNER，LEFT和FULL JOIN之間謹慎選擇
使用GROUP BY聚合數(shù)據(jù)并正確使用WHERE和HAVING
了解三個排名函數(shù)之間的差異
知道何時使用LAG/LEAD窗口函數(shù)
如果在創(chuàng)建復(fù)雜的查詢時遇到困難，請嘗試遵循SQL執(zhí)行順序
考慮潛在的數(shù)據(jù)問題，例如重復(fù)和NULL值
與面試官交流你的思路
我是岳哥，最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實例講解和批注等等，非常通俗易懂，方便大家跟著一起來實操。
有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進群，帶你進入高手如云的交流群。
推薦閱讀
