四個在亞馬遜工作後才知道SQL密技

13 min readApr 13, 2020

在上一篇商業分析學程與求職的五大誤區分享中，提到最大的一個誤區是有關於SQL能力的培養。在進入Amazon擔任商業分析師後，開始大量的使用SQL，雖然有點荒廢其他語言的使用和精煉，但也在這半年來對於這個歷久彌堅的資料庫語言，茁生出更多的理解。

雖然不敢說自己已經是一個全面且實力超群的SQL高手，但想要透過這篇文章，分享一些在求學與在台工作時期，比較少使用到的SQL技巧，也希望在商業分析學程中求學的讀者，可以透過這篇文章，看到SQL語言在商業界中如何彈性且廣泛的使用，且針對不同的資料表格，如何恰當的存取和進行商業分析的應用。

很多時候，在求職面試的online assessment，SQL的考題不外乎涵蓋不同Table的JOIN、Aggregation function (GROUP BY)的使用和Window Function的使用，JOIN與Aggregation function在工作後已經成為是要像呼吸般自然熟悉的使用了，而window function反而在我目前的工作中較少使用 (單純是我的習慣)，因此我會分享幾個有別於JOIN/Group by之外的幾個獨道且有趣的SQL技巧，Window Function也會提到，但是介紹一個相對少見的使用情境。

本篇適合讀者：針對商業分析學程就讀者，欲獲取額外SQL知識與小技巧，也適合一些想要一窺亞馬遜商業分析日常的讀者，我不會用公司真正的資料，畢竟有隱私規範，但會創造一些假的資料表與欄位，盡可能的帶出實際應用。

對應的資料庫語言：本篇文章的SQL語法多為Oracle SQL的沿用與適用。

第一技：日期與期間的各種應用技

日期與期間的應用是我個人覺得在求學求職期間，相對較少看到與涉獵，但在進入職場後，大量被使用的資料庫語言領域。原因不外乎在求職時，多數的SQL OA不會有太大時間範圍的資料，時間的變量比較常出現在WHERE這種過濾時間區段的應用，然而當走入商業分析的職場領域時，時間性被大大的強調了，因為在拉取報表、儀錶板和各種分析時，周、月、季度、年度的表現往往是一個商業分析師需要考量的重點。而以下介紹的應用技，就是針對大多數以「日」為單位的表格，如何提取周和月的作法。

1. 適用各種時間區段的提取：Extract

SELECT EXTRACT(month FROM DATE) AS month , 
COUNT(order_id) 
FROM order 
GROUP BY 1

EXTRACT (期間 FROM 日期欄位)

期間包含了week/month/year/day/hour/minute/second等，其中又以周、月、年最為廣泛使用，畢竟週報月報是公司內部產品或是商業單位最注重表現的週期，以此檢核成效和可以如何改進。

2. 周的提取

在美國一般公司定義的一周周期為周日到周六，所以週六作為一周的最後一天，在整理日期資料的時候，會把一周的第一天周日開始，都歸結到周六。在提取周的時候，會用NEXT_DAY這個語法。

假設TABLE “ORDER” 有個日期的欄位，ORDER_DATE，用以表達每天訂單的紀錄。那麼要將ORDER_DATE全都歸納到該周的周六，作法如下：

SELECT NEXT_DAY(order_date — 1 , ‘SAT’) FROM ORDERSELECT NEXT_DAY (order_date, ‘SUN’) — 1 FROM ORDER

以該語法，結合GROUP BY，就可以得出以周為單位的資料總和了。

3. 月的提取

至於怎麼將月份從單一日期提取出來呢，LAST_DAY這個語法可以將每個月中的日期，變成該月的最後一天(30或31號)，作法如下：

SELECT LAST_DAY(order_date) FROM ORDER

所以如果該日期是七月，所有的日期都會變成7/31；如果該日期是六月，所有日期都會變成6/30，該語法用於得到月表現是很直覺且高效的。

4. 日期的範疇

由於在商業世界中，資料的範圍非常大，每天的資料量都定期匯入，因此，在進行分析或報表製作的時候，如果沒有設定日期範圍，資料分析師可能提取到整整十年的資料，造成Query效率低落和擷取的資料量過大，因此通常我們會用WHERE進行幾周、幾個月、長到一兩年的資料篩選。

更甚者，在去自動化query的流程裡面，那樣的時間範疇不會是固定的，而是隨著新的週數來臨，time window應該是要隨著時間推移，因此不會用hard-coding的方式進行where的篩選，如：WHERE order date between ‘2019/01/01’ and {data_run_date}。如果直接hard-coding，你選取的資料勢必會越來越多。更好的做法如下：

周的Window：

BETWEEN DATE_TRUNC(‘WEEK’, {data_run_date} — {7*N Days}) — 1 AND {data_run_date}

N代表周數，所以如果你想要四個禮拜的資料就是 -28，想要13周的資料則是-91

月的Window：使用add_month加上DATE_TRUNC的應用

BETWEEN ADD_MONTH(‘2020/04/13’, -1) AND ‘2020/04/13’ 
>> 選取2020/03/13到2020/04/13’所有的資料點BETWEEN ADD_MONTH(DATE TRUNC(‘Month’, ‘2020/04/13’), -1) AND ‘2020/04/13’ 
>> 選取2020/03/01到2020/04/13之間所有的資料點。

由上面範例可見，單純使用ADD_MONTH，-N個月的用法，可以剛好取到整數月的資料，但如果加上DATE_TRUNC & Month語法，則會從前N個月的一號開始取值。

讀者可以視情況進行應用。通常為了能取得完整月分的資料，我較傾向使用ADD_MONTH與DATE_TRUNC的合體技法。

第二技：TEMP TABLE與 Common Table Expression (WITH)

這兩種技法也是在進入職場後，很常使用到的工具。因為不同資料源位屬於不同的資料庫，作為商業分析師，想要避免過多的JOIN，我通常會先把一些我需要提取的部分資料使用TEMP TABLE或是CTE的形式在主要Query區塊前進行提取。

TEMP TABLE的作法：

CREATE TEMP TABLE table_1 AS
SELECT columns FROM table A;CREATE TEMP table_2 AS
SELECT columns FROM table B;SELECT table_1.columns, table_2.columns, c.columns
FROM table C
JOIN table_1
JOIN table_2;

CTE的作法：

WITH table_1 AS(
SELECT columns FROM table A
),
Table_2 AS(
SELECT columns FROM table B)
SELECT table_1.columns, table_2.columns, c.columns
FROM table C
JOIN table_1
JOIN table_2;

可以看到TEMP TABLE和CTE WITH的用法其實非常類似，目的都是為了讓你的Query更加一目了然且優雅簡潔，也避免過多資料的提取和使用而減少效率。在跟一些求職者練習SQL的評鑑時，很多求職者會習慣將所有的Query寫在單一的區塊裡面，用過多的JOIN或SUBQUERY，導致最後邏輯丟失且自己也搞不清楚寫到哪裡，適時的使用TEMP TABLE和CTE作為輔助，絕對是很加分的。另外一個小補充，在Redshift裡面，TEMP TABLE的效率仍是高於CTE一點，兩種方法都不錯，但如果加入效率的這個變因，可以將TEMP TABLE放在CTE前做優先選擇。

第三技：Aggregation 與 CASE WHEN的結合使用

將Aggregation function (SUM/COUNT/COUNT DISTINCT/MIN/MAX) 結合 CASE WHEN應該是我個人在這幾個月熟悉SQL語法的過程中，覺得最強大且最有趣的應用技法。這樣的使用創造出一種類似EXCEL中SUMIF/COUNTIF的效果，可以用這個應用技做出很多高效的分析。

以下示範幾個這兩種技法結合的分析應用

1. CASE WHEN 時間，進行留存率/使用率的分析

Table Name: orders
Column: register_date, order_date, user_id, country, order_sales, order_id

SELECTCountry,Next_day(register_date, ‘SUN’)-1 AS week_end,COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date AND register_date + 6 THEN user_id END) AS first_week_order,COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date + 7 AND register_date + 13 THEN user_id END) AS sencod_week_order,COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date + 14 AND register_date + 20 THEN user_id END) as third_week_orderFROM ordersGROUP BY 1,2

上面的範例可以得知到消費者在註冊之後，有沒有創造訂單的行為，究竟是註冊後第一周有幾個ID創造訂單、第二周有幾個、第三周有幾個。可以依此獲取的Data檢查消費者註冊後的使用行為是否健康。

2. CASE WHEN 時間，進行每個使用者消費量的分析

SELECTCountry,User_id,SUM (CASE WHEN order_date BETWEEN register_date AND register_date + 6 THEN order_sales END) AS first_week_amount,SUM (CASE WHEN order_date BETWEEN register_date + 7 AND register_date + 13 THEN order_sales END) AS second_week_amountFROM ordersGROUP BY 1,2

透過篩選出註冊與消費的時間，並且進行花費金額的加總，每個消費者在每段時間段(註冊後第一周、第二周…以此類推)的消費金額，可以藉次觀察消費者是否有持續維持消費習慣或是消費金額變低等分析。

3. CASE WHEN數量，消費金額超過某一定額的數量分析

SELECTCountry,User_id,COUNT(DISTINCT CASE WHEN order_sales >= 1000 THEN order_id END) AS count_of_order_greateer_than_1000FROM ordersGROUP BY 1,2

以上的query就是類似countif的用法，針對每個使用者，唯有訂單金額超過一千元，才會記入最後的結果，也可以用這個分析去篩選出高價值的顧客。

4. CASE WHEN數量，加上時間的用法

SELECTCountry,User_id,MIN(CASE WHEN order_sales > 1000 THEN order_date END) AS first_order_date_over1000,MAX(CASE WHEN order_sales > 1000 THEN order_date END) AS recent_order_date_over1000FROM ordersGROUP BY 1,2

CASE WHEN加上MIN/MAX時間，我們可以得出該使用者，在他的使用者生命中，哪個時間點首次購買超越一千元的訂單，以及最近一次購買超過一千元訂單的時間點。

以上四種情境就是我針對CASE WHEN加上Aggregation Function搭配使用的幾種分析技巧分享。相信聰明的讀者一定可以舉一反三延伸出更多有趣的分析。

第四技：Window Function (PARTITION BY)的另種用途

Window Function作為SQL考題中相對難的技巧，想必很多讀者在求職找工作的過程中碰過很多，也跟我一樣兢兢業業的準備相關的考題並且嚴正以待。大多數的題目都是圍繞著在不同類別中、不同市場中、不同時期，進行訂單ID、使用者ID藉由金額多寡由大到小的排序。

而這邊要介紹的另外一個用法，是Partition by可以作為分析網站或是應用程式的使用行為

Table Name: visit
Columns: user_id, visit_id, page_url, time

通常要分析使用者在一個網站或是應用程式的使用行為，最基礎的單位為一個Session/一個visit，Google Analytics定義一個Session的基本長度為半小時，如果使用者在一個頁面瀏覽後，半小時沒有和網站或是應用程式互動，其訪問行為則為終止。

範例的資料表如上，有使用者、訪次和頁面的連結和時間。以下則使用partition by來表達每個使用者在不同訪次之間的瀏覽行為。

SELECTUser_id,Visit_Id,Page_url,DENSE_RANK() OVER (PARTITION BY user_Id, visit_id ORDER BY time ASC) AS page_order,MIN(time) OVER (PARTITION BY user_id, visit_id) AS session_start_time,MAX(time) OVER (PARTITION BY user_id, visit_id) AS session_finisht_timeFROM visit

上面的Query會返回針對每個使用者、每次的到訪，瀏覽頁面行為的先後次序，以及該訪次開始與結束的時間，以此為基礎就可以將這個結果存入TEMP TABLE或是CTE，進行更進一步的分析，例如登入頁面通常是第幾個行為、Check out頁面是第幾個步驟等等。

這個技法是我覺得在申請網路產業的商業分析師職缺可能在第一時間無法直接想到解法的，尤其如果你志在成為產品分析師，這個技巧一定要多加熟悉。

小結

透過這四種技法的介紹，搭配一些範例虛構的簡易Table，相信這篇文章能讓讀者思索SQL語言的各種變異和實際應用，除了在求職期間好好將基礎的SQL語言扎實打底，如果能對這些隱藏用法有更深入的了解，相信不僅在面試時能展現出更有效率的SQL撰寫，更能在找到工作後無縫將SQL技法使用於探索職場中碰到的各種資料。

也希望大家能喜歡這次有關於SQL語言的分享，寫SQL的經驗不是很長，也在努力摸索怎麼樣能成為一個更好的商業分析師，歡迎前輩也指教，留言有趣的SQL應用，彼此學習切磋。讓我們一起Happy SQL querying 😊。

如果喜歡這篇文章也歡迎多拍幾次手和多分享文章給也在求職找分析師工作的朋友吧！

有想要練習SQL的朋友請參考：

Career Service 4 BA

Edit description

sites.google.com