原文:小蚊子資料分析
SQL是用於資料分析和資料處理的最重要的程式語言之一,因此與資料科學相關的工作(例如資料分析師、資料科學家和資料工程師)在面試時總會問到關於 SQL 的問題。
SQL面試問題旨在評估應聘者的技術和解決問題的能力。因此對於應聘者來說,關鍵在於不僅要根據樣本資料編寫出正確的查詢,而且還要像對待現實資料集一樣考慮各種場景和邊緣情況。
在這篇文章中,我將介紹 SQL 面試問題中常見的模式,並提供一些在 SQL 查詢中巧妙處理它們的技巧。
NO.1 問問題
要搞定一場 SQL 面試,最重要的是儘量多問問題,獲取關於給定任務和資料樣本的所有細節。充分理解需求後,接下來你就可以節省很多迭代問題的時間,並且能很好地處理邊緣情況。
我注意到許多候選人經常還沒完全理解SQL問題或資料集,就直接開始編寫解決方案了。之後,等我指出他們解決方案中存在的問題後,他們只好反覆修改查詢。最後,他們在迭代中浪費了很多面試時間,甚至可能到最後都沒有找到正確的解決方案。
我建議大家在參加SQL面試時,就當成是自己在和業務夥伴共事。所以在你提供解決方案之前,應該要針對資料請求瞭解清楚所有的需求。
舉例:
查詢薪水最高的前 3 名員工。
這裡你應該要求面試官說清楚“前三名”具體是什麼意思。我應該在結果中包括 3 名員工嗎?你要我怎樣處理關係?此外,請仔細檢查樣本員工資料。salary 欄位的資料型別是什麼?在計算之前是否需要清除資料?
NO.2選哪一個JOIN
在SQL中,JOIN 通常用來合併來自多個表的資訊。
有四種不同型別的 JOIN,但在大多數情況下,我們只使用INNER、LEFT和FULLJOIN,因為 RIGHTJOIN並不是很直觀,還可以使用 LEFTJOIN 很簡單地重寫。在 SQL 面試中,需要根據給定問題的特定要求選擇你要使用的正確JOIN。
舉例:
查詢每個學生參加的課程總數。(提供學生 id、姓名和選課的數量。)
你可能已經注意到了,並非所有出現在 class_history 表中的學生都出現在了 student 表中,這可能是因為這些學生已經畢業了。(這在事務資料庫中實際上是非常典型的情況,因為不再活躍的記錄往往會被刪除。)
根據面試官是否希望結果中包含畢業生,我們需要使用LEFT JOIN或 INNER JOIN來組合兩個表:
NO.3 Group by
GROUP BY是SQL中最重要的功能,因為它廣泛用於資料聚合。如果在一個 SQL 問題中看到諸如求和、平均值、最小值或最大值之類的關鍵字,這就表明你可能應該在查詢中使用GROUP BY了。
一個常見的陷阱是在GROUP BY過濾資料時混淆 WHERE和HAVING — — 我見過很多人犯了這個錯誤。
舉例:
計算每個學生在每個學年的必修課程平均 GPA,並找到每個學期中符合 Dean’s List(GPA≥3.5)資格的學生。
由於我們在GPA計算中僅考慮必修課程,因此需要使用WHERE is_required=TRUE來排除選修課程。
我們需要每位學生在每學年的平均GPA,因此我們將同時GROUP BY student_id和school_year 列,並取gpa列的平均值。最後,我們只保留學生平均 GPA高於3.5的行,可以使用HAVING來實現。合起來是下面這樣:
注意:每當在查詢中使用GROUP BY時,都只能選擇group-by列和聚合列,因為其他列中的行級資訊已被捨棄
NO.4 SQL查詢執行順序
大多數人會從SELECT開始,從上到下編寫SQL查詢。
但你知道SQL引擎執行函式時要到後面才執行SELECT嗎?以下是 SQL 查詢的執行順序:
- FROM, JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMIT, OFFSET
再次考慮前面的示例:
因為我們想在計算平均GPA之前過濾掉選修課程,所以我使用WHERE is_required=TRUE代替HAVING,因為WHERE會在GROUP BY和HAVING之前執行。我不能編寫HAVING avg_gpa >= 3.5的原因是,avg_gpa被定義為SELECT的一部分,因此無法在SELECT之前執行的步驟中引用它。
我建議在編寫查詢時遵循引擎的執行順序,這在編寫複雜查詢時會很有用。
NO.5 Windows函數
Window函式也經常出現在SQL面試中。共有五種常見的Window函式:
- RANK/DENSE_RANK/ROW_NUMBER:它們透過排序特定列來為每行分配一個排名。如果給出了任何分割槽列,則行將在其所屬的分割槽組中排名。
- LAG/LEAD:它根據指定的順序和分割槽組從前一行或後一行檢索列值。
在SQL面試中,重要的是要了解排名函式之間的差異,並知道何時使用LAG/LEAD。
舉例:
查詢每個部門中薪水最高的前 3 名員工。
當一個SQL問題要求計算“TOP N”時,我們可以使用ORDER BY或排名函式來回答問題。
但在這個示例中,它要求計算“每個 Y 中的 TOP N X”,這強烈暗示我們應該使用排名函式,因為我們需要對每個分割槽組中的行進行排名。
以下查詢恰好能找到 3 名薪水最高的員工,而不論他們的關係如何,如下:
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.
此外,根據關係的處理方式,我們可以選擇其他排名函式。同樣,細節是很重要的!
NO.6 重複項
SQL面試中的另一個常見陷阱是忽略資料重複。
儘管樣本資料中的某些列似乎具有不同的值,但面試官還是希望候選人考慮所有可能性,就像他們在處理真實資料集一樣。
例如:
在上一個示例employee_salary表中,可以讓僱員共享相同的名稱。
要避免由重複項導致的潛在問題,一種簡單方法是始終使用 ID 列唯一地標識不同的記錄。
舉例:
使用 employee_salary 表查詢每個部門所有員工的總薪水。
正確的解決方案是 GROUP BY employee_id,然後使用 SUM(employee_salary) 計算總薪水。如果需要僱員姓名,請在末尾與 employee 表聯接以檢索僱員姓名資訊。
錯誤的方法是使用 GROUP BY employee_name。
NO.7 NULL
在SQL中,任何謂詞都可以產生三個值之一true,false和NULL,後者是unknown或missing資料值的保留關鍵字。處理NULL資料集時可能會意外地很棘手。
在SQL面試中,面試官可能會特別注意解決方案是否處理了NULL值。有時,很明顯有一列是不能nullabl的,但對於其他大多數列來說,很有可能會有NULL值。
建議:確認示例資料中的關鍵列是否為nullable
如果可以,請利用IS(NOT)NULL,IFNULL和COALESCE 之類的函式來覆蓋這些邊緣情況。
NO.8交流
最後一點也非常重要:在SQL面試期間要隨時與面試官溝通交流。
我面試過的許多候選人都很沉默寡言,有疑問的時候才會知聲。當然如果他們最終給出了完美的解決方案,那也不是什麼問題。
但是,在技術面試期間保持溝通交流往往會是有價值的。
例如:你可以談論對問題和資料的理解,說明你計劃如何解決問題,為什麼使用某些函式而不是其他選項,以及正在考慮哪些極端情況。
NO.9 總結
- 首先要提問,收集所需的細節
- 在INNER,LEFT和FULL JOIN之間謹慎選擇
- 使用GROUP BY聚合資料並正確使用WHERE和HAVING
- 瞭解三個排名函式之間的差異
- 知道何時使用LAG/LEAD視窗函式
- 如果在建立複雜的查詢時遇到困難,請嘗試遵循SQL執行順序
- 考慮潛在的資料問題,例如重複和NULL值
- 與面試官交流你的思路
按讚我的臉書 IT Value 研討社,發現更多精彩內容 😃
偷偷告訴你,私訊臉書小編,關鍵詞【數位化轉型】,即可獲取一份14個行業,240個轉型案例的最全資料包😉