Twitch數據科學家手把手教你拿下面試必考的SQL

數據科學方面的面試比較難找到準確的應對方法。它涉及多個學科領域的內容，意味著需要覆蓋和練習眾多方面才能準備妥當，不然面試的時候就會不知所措。不管你是第一次面試數據科學的職位，還是即便已經有過一些經驗了，這篇由Twitch（視頻遊戲實時流媒體視頻平臺，2014年被亞馬遜收購）數據科學家親撰的《拿下數據科學中的SQL面試》文章都值得一看。其中不僅包括了SQL基礎知識介紹、常考內容示例，還有關於面試數據科學SQL測試的一些注意事項。我參加過的每一場數據科學面試，都會以某種方式涉及到SQL。我幾乎可以保證，在你找工作的過程中，一定會被要求寫一些SQL語句給面試官，考察你的基本數據分析能力和數據處理邏輯。儘管一些數據科學家認為他們現在在處理大數據的時候要用Hadoop這樣的分布式系統，但事實是，大部分現代數據存儲仍然依賴（或允許）用SQL語法來檢索和處理數據。SQL的基本語句都很直截了當，但是在數據科學面試中遇到的SQL問題可能非常複雜。我猜讀這篇文章的大多數人都有一些關於這門語言的經驗，本文的其餘部分假設你對以下內容已有所了解（若非如此，推薦你報名MarTechApe的《SQL基礎班特訓課程》，3小時掌握企業內最常用sql語句，可劃到文末了解）：

SELECT
FROM
JOIN
WHERE
(AND)
GROUP BY
ORDER BY
LIMIT

現舉個最基本的例子，一個SELECT…FROM語句將從指定的表中返回一組行和列，這些行和列的詳細信息由放在關鍵字之後的內容決定。例如，要根據用戶名的字母順序來計算今天的網頁瀏覽量，你可以這樣寫：

SELECT username, count(1) as number_of_views
FROM pageviews
WHERE day = '2017-09-08'
GROUP BY username
ORDER BY username;

子查詢(Subqueries)和公共表表達式(Common Table Expressions)現在你知道如何檢索一組行和列，這可能會讓你通過面試官的第一個問題。但是更高級的問題將涉及子查詢或公共表表達式(CTEs)。讓我們討論一下這些是什麼以及如何使用它們。CTEs和子查詢允許獲取數據的子集並使用名稱存儲數據的子集，然後從中選擇並執行更多操作。這兩個方法的功能幾乎相同。子查詢是將一個查詢塊嵌套在另一個查詢塊的WHERE或HAVING字句的條件中查詢塊。比如想得到營收額高於或等於在同一地區所有商店平均營收額的店鋪ID。這張店鋪表的別名為「s「：

SELECT store_id, zipcode, revenue

FROM stores as s
WHERE revenue >=

(SELECT Avg(revenue)

FROM stores

WHERE s.zipcode = stores.zipcode）

ORDER BY zipcode;

上面是一個較為簡單的例子，而更複雜的子查詢可以層層嵌套。這裡我主要關注CTEs，因為我認為它的語法更易於閱讀。此外，CTEs會預存子查詢的結果，所以運行速度通常比子查詢要快。尤其是當某個子查詢的表被重複使用的時候，效率會顯著提升。
假設你被問到如何計算某個用戶下達訂單之間的平均時間。你有一個名為transactions的表，該表包含用戶名和交易時間。要解決這個問題，你需要將每個交易時間與該用戶的下一個交易時間放在一行中。一個查詢並不能解決所有問題：你需要先提取一個用戶的交易，將其存儲在CTEs中，然後將它與此用戶之後的交易連結，以便計算平均時間。這可以是這樣的:

-- 首先，找出2017年9月8日所有user_a的交易記錄

with user_a_trans as (
     SELECT username, time
     FROM transactions
     WHERE day = '2017-09-08'
     AND username = 'user_a'),

-- 將每一條交易與在它之後發生的所有交易合併在一張表格裡

joined_trans as (
     SELECT username, time, future_times
     FROM user_a_trans a
     INNER JOIN user_a_trans b
     ON b.time > a.time),

-- 使用MIN()函數找出最近的下一次交易

next_trans as (
     SELECT username, time, MIN(future_times) as next_time
     FROM joined_trans
     GROUP BY username, time)

-- 將前一次交易與下一次交易的時間差取平均值

SELECT AVG(next_time - time) as avg_time_to_next_transactionfrom next_trans;

如果你不能很好的理解這個查詢的所有細節，完全沒關係——熟能生巧。重要的一點是，複雜的問題需要分解成多個部分，並通過一系列CTEs來解決。篩選(Filter)，聚合(Aggregate)，合併(Join)當你遇到像上面這樣的難題時，先花一分鐘問問自己理想的表應該是什麼樣的，才能用一個SELECT語句來解決這個問題。在上面的例子中，理想的表是有一條包含每個交易的記錄，以及還包含下一個交易時間的一列。一旦你知道了你的最終表格是什麼樣的，你就可以逆向思考，一步一步地決定如何使用一系列CTEs將你的原始表格轉換成面試官要求的最終輸出。通常，你可以對你的CTEs字符串執行以下步驟：使用WHERE進行篩選，使用GROUP BY進行聚合，使用JOIN進行合併，重複上述步驟。在合併之前先進行篩選和聚合數據，可以編寫最高效的SQL。合併的過程需要花費一定時間，因此在將兩個表聯結在一起之前，儘可能刪減掉不需要的行，只保留有需要的行。有時，會無法先進行聚合，但是你通常可以通過至少一兩個WHERE子句來限制最後要合併的表的大小。需要注意的是，如果在同一個CTE中有一個JOIN句和一個WHERE句，那麼SQL首先處理JOIN。換句話說，以下是非常低效的，因為它將先合併兩個表，之後才對「2017年1月9日」之後的時間進行篩選:

SELECT *
FROM table_a a
INNER JOIN table_b b
ON a.username = b.username
WHERE a.day >= '2017-09-01'

正確的表達方式是在合併表之前使用CTEs進行篩選，如下:

with a as (
     SELECT *
     FROM table_a
     WHERE day >= '2017-09-01')，

b as (
     SELECT *
     FROM table_b
     WHERE day >= '2017-09-01')

SELECT *
FROM a
INNER JOIN b
ON a.username=b.username;

再強調一次，這裡的效率提高是由於先篩選到儘可能少的行，之後執行兩個表的合併。使用上述篩選Filter、聚合Aggregate、合併Join的過程可以解決大多數問題，不過有時候也會遇到棘手的SQL問題，需要使用窗口函數（Window Functions）來解決。類似GROUP BY語句，窗口函數會將數據分成幾個模塊，並分別對每個模塊進行操作。但與GROUP BY語句不同的是，行不會被合併。 以下為示例。假設你需要處理一個表格，該表格每行代表一個觀測項，觀測量包括營收情況及其所在的州，你需要計算每個觀測項的營收佔該州總收入的百分比。此問題棘手之處就在於你需要將單個值（具體某一行的營收）與匯總值（屬於某一州所有行的營收匯總）進行比較。本例中，單個值是指特定觀測項的營收，匯總值指某個州所有觀測項營收的總和。在這種情況下，解決類似問題的好方法就是使用窗口函數。代碼如下：

with state_totals as (
     SELECT state, revenue, 
           SUM(revenue) OVER (PARTITION BY state) as state_revenue
     FROM state_line_items)

SELECT state, 
     revenue/state_revenue as percent_of_state_revenue
FROM state_totals;

窗口函數包含OVER子句。本例中，通過按州劃分（PARTITION BY），可得到與每個行觀測項關聯的各州的匯總值，後續通過簡單的除法，上述營收佔比問題也就迎刃而解。窗口函數支持大多數聚合(Aggregate)函數，包括SUM、COUNT或AVG等，但也有一些特殊語句只能用作窗口函數。例如RANK、FIRST_VALUE和LAG。在準備面試時，也應重點準備以上六類函數。Union及Case語句

此外，還有一些SQL語句需要重點準備。一是Union語句，相對而言較簡單。Union語句可以看作是Join函數的垂直形式，也就是說，Join函數是將數據表或CTE水平組合在一起，而Union語句則是將兩個表堆疊在一起，形成一個包含原始表中所有行數據的表。Union語句需滿足的前提是被組合的兩個表具有相同的列，否則將無法按邏輯進行組合。例如在下面這種情況下，使用union會很有用——已知兩張分別對應兩類交易類型的數據表格，需要通過一個查詢來確定每種交易類型的數量。代碼如下：

with sales as (
     SELECT 'sale' as type
     FROM sale_transactions
     WHERE day >= '2017-09-01'),

buys as (
     SELECT 'buy' as type
     FROM buy_transactions
     WHERE day >= '2017-09-01'),

unioned as (
     SELECT type
     FROM buys
     UNION ALL
     SELECT type
     FROM sales) 

SELECT type, count(1) as num_transactions
FROM unioned
GROUP BY type;

上述操作首先從兩個表中分別選擇一個常數欄位「type」（在本例中為「 sale」或「 buy」），然後對其進行union合併，最終得到一個大表，從而可通過單個查詢進行分組及計數。總體而言，當需要將兩個表組合為一個總表時，Union語句就是答案。Case語句則是另一個相當簡單的概念，與R和Excel等軟體中的ifelse函數完全相同，通常應用於將一組預定義值映射到另一組預定義值的情況。例如，你可能希望將星期幾所在的列轉換為另一個變量，代表該天是否為周末。

SELECT 
     CASE WHEN day_of_week in ('Sat', 'Sun') 
           then 'Weekend' else 'Weekday' end as day_type
FROM table_a;

如上所述，可以將字符串所在列（如「星期幾」）轉換為二進位變量，然後對其求和以計算表中周末的天數。

SELECT 
     SUM(
           CASE WHEN day_of_week in ('Sat', 'Sun')
           THEN 1 ELSE 0 END) as num_weekend_days
FROM table_a;

Case語句的應用非常靈活，可以使用一系列WHEN語句表示映射條件，將對應值映射到目標值，再使用ELSE語句描述剩餘的情況。現在你已經初步了解了成功通過數據科學面試的SQL部分所需的所有知識。當然，練習是關鍵。試著給自己設置一些問題，然後使用上面描述的工具來解決它們。更好的辦法是，找一塊白板，在上面練習，這樣在面試的時候你會感覺更舒服。把每個問題分解成儘可能小的部分。這是清晰思考SQL問題的最佳方式，將幫助你一步一步地思考要實現每個步驟所需要的SQL命令。大聲說出你的解題過程。就像在學校裡一樣，你會因為展示你的作品而得到讚揚。如果你只是獨自埋頭在白板上寫東西，沒有與面試官交流讓他知道你在做什麼，他們將很難評估你的技能，尤其是當你沒有得到最終答案的時候。尋求幫助。無論你是否相信，但大多數面試官都希望面試者能通過面試，也樂於提供一些幫助，只要你能清楚地表達出自己的問題所在。換句話說，可以問一些諸如將整數轉換為浮點數的正確語法是什麼之類的問題，但是要避免問一些含糊不清的問題，這些問題可能表明您不知道如何處理您試圖解決的問題。最後，如果你想在學習SQL的過程中不是靠自己花費大量時間一點點摸索與判斷，而是讓專業的SQL面試官來指導你答案究竟是否正確及高效；如果你想跳出「自學的魔咒」，短時間內快速掌握SQL、應對面試，就來報名MarTechApe推出的《SQL面試衝刺課》吧——

Amazon專家授課 + 在線集中答疑 = 拿下你的下一場SQL面試！

MarTechApe聯合亞馬遜商務智能工程師（Amazon Business Intelligence Engineer）—— Cindy老師，開設了SQL特訓課，將行業內所需SQL技能和面試考點濃縮成6個小時的課程內容，讓你短時間就能實現SQL能力質的飛躍！

前3小時全方面提升你的SQL能力，用SQL完成一系列的數據分析操練，真正學會SQL程式語言，而不是「只知道個大概」。後3小時深入輔導Google、Facebook、Amazon、Apple、Uber、Airbnb等知名公司的SQL面試真題，總結不同公司的SQL出題風格。讓你從此不再膽怯SQL面試題！2小時在線集中答疑、模擬面試，並分享SQL在線面試（Live SQL Interview）與白板編程（white board coding）面試技巧。

報名成功的同學還可獲贈一場Cindy老師的《如何成為亞馬遜Business Intelligence商務智能分析師》線上講座。

課程大綱

Introduction to SQL | SQL基本概念

SQL introduction, basic concept of relational database

SQL usage in work, transactional database, analysis database

SQL generations:

Oracle

SQL Server

MySQL

Postgres

Redshift

Basic SQL | 1小時SQL基本語句與功能應用

Basic SQL -1:

Basic SQL -2: Comparison

Basic SQL -3:

Boolean ( AND, OR, NOT)

ORDER BY

Basic SQL -4:

Aggregation ( COUNT, SUM, MIN, MAX, AVG),

GROUP BY

Basic SQL -5:

Basic SQL -6: Case

Intermediate SQL | 2小時SQL進階語句與功能應用

Intermediate SQL -1: Joins

NNER

OUTER

LEFT

RIGHT

JOIN USING WHERE or ON

Intermediate SQL -2:

Intermediate SQL -3:

Data types

Data format

String function

Window function

Intermediate SQL -4:

Primary key

Index

Sort key

Distribution key

SQL Interview Questions | 3小時SQL面試真題詳解

Deep dive into SQL interview questions from major Tech companies including but not limited to:

Google

Amazon

Apple

Facebook

Cracking the SQL Interviews | 2小時SQL模擬面試、答疑、面試經驗分享

Live Q&A

Interview tips on SQL online Test (e.g., Live interview, CoderPad)

SQL challenges in the real business world

Amazon亞馬遜美國擔任商務智能工程師（Business Intelligence Engineer）

課程形式

3小時SQL系統性知識培訓 + 3小時SQL真題詳解錄播課程，所有內容均有視頻回放，一經購買，可永久回看。

只購買3小時SQL系統性知識培訓：149美元/人 

只購買3小時SQL真題特訓：149美元/人 

購買6小時全套課程：249美元/人

如果需要人民幣支付，請掃描下方二維碼，聯繫小助手進行購課。

想要快速提高你的SQL能力，就快來報名吧！

堅持學習，保持職場競爭力，選擇MarTechApe！