這篇簡單討論結構化查詢語言(SQL)在概念上跟命令式程式語言如 Python 有什麼不同之處,以及在什麼樣的情況下我們會想要利用 SQL 做資料分析。
這篇注重在為何你會想要使用 SQL 做資料分析,而非 SQL 本身功能的教學。如果要學習 SQL 本身,可以參考最後面的推薦閱讀。
SQL 是資料科學家與資料庫(Database)溝通的語言
在沒接觸過 SQL 之前,你可能會想
- 「做為一個程式語言,為何 SQL 有那麼多人在使用?」
- 「我們有 Python、R,不學 SQL 應該也沒關係吧?」
- 「又要學一個程式語言好麻煩。」
為了釐清這些疑問,讓我們做一個假想實驗。比方說我們現在想要知道某個特定顧客過去的所有購買記錄。
如果你熟悉 SQL 的話,可以對資料庫下一個簡單的查詢(Query):
SELECT c.name AS customer,
o.totalprice,
o.orderdate
FROM customer AS c
INNER JOIN orders AS o
ON c.custkey = o.custkey
WHERE c.name = 'Customer#000000001'
ORDER BY o.orderdate;
上面這個查詢翻為白話就是:
- 從顧客清單
customer
還有購賣紀錄orders
裡頭- FROM customer AS c INNER JOIN orders AS o ON c.custkey = o.custkey
- 找出名為 Customer#000000001 的顧客的所有購買紀錄
- WHERE c.name = 'Customer#000000001'
- 並把那些紀錄依照購買日期排序
- ORDER BY o.orderdate
- 最後只回傳顧客名稱、總購買金額、購買日期幾個項目
- SELECT c.name AS customer, o.totalprice, o.orderdate
這個查詢對第一次寫 SQL 的人可能會覺得很複雜,但注意,我們並沒有告訴資料庫「如何」取得這些資料,比方說:
- 怎麼合併顧客跟購買紀錄?
- 怎麼過濾特定顧客?
- 怎麼排序?
我們只告訴它該給我們「什麼資料」。而得到的結果是:
customer | totalprice | orderdate
--------------------+------------+------------
Customer#000000001 | 152411.41 | 1993-06-05
Customer#000000001 | 165928.33 | 1995-10-29
Customer#000000001 | 270087.44 | 1997-03-04
如同我們預期,只有該顧客的購買紀錄被回傳,且依照購買日期 orderdate
從早排到晚。
實際上,資料庫可能需要做以下運算來取得資料:
- 將顧客表格
customer
以及購買紀錄的表格orders
分別命名為c
及o
- 依照共通的鍵值
custkey
合併(JOIN
)兩表格 - 找出特定顧客
Customer#000000001
的購買記錄 - 將該紀錄依照購買日期
orderdate
排序 - 選出要顯示的欄位
這些運算最後都得依照「某個」順序執行,但是我們不需要考慮這些事情,完全依靠資料庫的查詢最佳化器(Query Optimizer)來幫我們決定。
寫 SQL 敘述時,你可以理解成我們是指定「要的資料」,而查詢最佳化器會依照此需求,找出一個最佳路徑來取得必要的資料。
換句話說,當我們在寫 SQL 的時候,是在進行宣告式程式設計(Declarative Programming):我們只告訴資料庫,我們想要什麼資料(What),而不是怎麼取得(How)它們。
這跟一般常見的命令式程式語言(Imperative Programming)如 Python、Java 有所不同。在寫 SQL 時,我們告訴資料庫它該達成的目標 - 取得什麼資料(What);在寫 Python 時,我們得告訴程式該怎麼達成該目標(How)。
為了進一步闡述這個概念,接著讓我們試著使用 Python 來取得跟上面的 SQL 查詢一樣的結果。
用 Python 達到 SQL 查詢效果¶
首先先假設所有顧客資料是透過一個 list
儲存,裡頭包含多個 dict
。每個 dict
則代表一個顧客的資料:
customers = [
{"name": "Customer#000000001", "custkey": "1"},
{"name": "Customer#000000002", "custkey": "2"}
]
而購買記錄則是一個 dict
,dict
的鍵值為所有顧客的 custkey
;鍵值對應的值則是包含該顧客所有購買紀錄的 list
:
orders = {
"1": [{"totalprice": 152411.41, "orderdate": "1993-06-05"},
{"totalprice": 270087.44, "orderdate": "1997-03-04"},
{"totalprice": 165928.33, "orderdate": "1995-10-29"}
]
}
所以 orders["1"]
就代表 custkey = 1
的顧客的購買紀錄。
了解背後的資料結構以後,我們可以寫一段 Python 程式碼來取得資料:
print("customer | totalprice| orderdate ")
print("------------------ | ----------| --------- ")
# 從所有顧客找符合條件的人
for c in customers:
# 跳過我們沒興趣的顧客
if c['name'] != 'Customer#000000001':
continue
# 利用 custkey 取德該顧客的購買紀錄
c_orders = orders[c['custkey']]
# 依照 orderdate 排序購買紀錄
c_orders_sorted = sorted(c_orders, key=lambda x: x['orderdate'])
# 將所有排序後的記錄回傳
for o in c_orders_sorted:
values = [c['name'], str(o['totalprice']), str(o['orderdate'])]
print(" | ".join(values))
# 已經找到該顧客,提早結束迴圈以減少處理時間
break
所以我們使用 Python 達到跟上面的 SQL 查詢一樣的結果了。但兩者在執行上有什麼差異?
使用命令式程式語言來處理資料時,我們需要:
- 了解資料結構以操作資料(顧客是存在
list
還是dict
?) - 明確地定義執行步驟(先排序購買記錄
orders
還是先把顧客customers
跟購買紀錄合併?) - 最佳化(如最後的
break
)
再看一次先前的 SQL 查詢(+註解):
-- 給我以下幾個欄位:顧客名稱、總購買金額、購買日期
SELECT c.name AS customer,
o.totalprice,
o.orderdate
-- 將有相同 custkey 的顧客跟購買紀錄合併
FROM customer AS c
INNER JOIN orders AS o
ON c.custkey = o.custkey
-- 只需要此顧客的購買紀錄
WHERE c.name = 'Customer#000000001'
-- 依照購買日期排序
ORDER BY o.orderdate;
這裡頭我們不需要了解資料被以什麼形式儲存,也不需要定義要以什麼順序執行查詢,更不用做最佳化。這些事情全部交給背後的資料庫處理,使得資料科學家可以專注在更高層次的問題:「我們需要什麼資料?」
而這正是 SQL 最強大的地方:
SQL 讓資料科學家可以專注在需要「什麼」資料而非要「怎麼」取得。
結語¶
雖然我們這篇只舉了一個十分簡單的例子,但一般來說 SQL 非常適合以下的使用情境:
- 將多個資料來源(例:表格)合併起來並依照一些條件篩選結果
- 依照取得的資料做一些簡易的 aggregation (如:加總、平均、最大值)
- 簡單的資料轉換(例:把 datetime 欄位取出年份)
如果需要十分複雜的資料轉換或者計算時,一般我還是推薦使用 Python 或 R。但是下次當你有機會使用 SQL 取得想要的資料時,不妨試著專注在「想要什麼資料」而不是「怎麼取得資料」。說不定一個 SQL 查詢就能幫你省下一些花在搜集資料的時間。
推薦閱讀¶
跟資料科學相關的最新文章直接送到家。 只要加入訂閱名單,當新文章出爐時, 你將能馬上收到通知