直接用SQL來分析數據?怎麼沒用python/R?3個面向來考量分析工具的選擇

最近收到一個臉書朋友的詢問,以下原文:

我最近有在leetcode 上做SQL的exercise,我覺得有些題目蠻困難的,有些問題看了解法感覺是把SQL當做程式語言,並且直接用SQL來分析資料,我想問說在真實的業界,會用到那麼fancy 的SQL寫法嗎(有些問題感覺用程式語言來解相對方便許多)?

在我的認知,SQL只是抓取需要的資料,後續的資料分析或是更近一步的探索是在像是python 的pandas 或是R的一些套件來完成

以上的wording萃取出來就是:『分析項目的開發中,是如何考量該用的工具』?不知道大家有沒有過這種疑問?

我很喜歡這問題,因為我自己也沒很系統的思考過。我想可以從(1)數據量級、(2)開發環境、(3)應用場景 來考量。

先看看這個exercise,接著從這三個方向來回答臉書朋友的問題、最下面放上exercise解答。

Image for post

1. 數據量有多大?

從取數起,”分析”就已經開始了

在比較有規模的公司,數據量千萬上億是滿正常的,關聯2~3張這種量級的表也滿正常。如果downlload raw data到電腦,內存夠嗎?

在分析要進展前,腦中大概都已經會有分析維度的構想了、甚至結論可能都已經能猜到大概。

再反推在SQL中是否要先做什麼初步處理;例如哪些數據是雜訊在取數時就可以剔除、分析過程可能會遇到什麼違反邏輯現象,是不是要多建一套任務流用來做校驗,等等

這邊想到一個小故事,上次有一個PM想用powerBI做可視化看板,但是他不會寫SQL,對表的信息也沒去了解,直接幾十億條數據就這樣import,電腦永遠死機,然後說要加內存…..(不是這樣搞的好嗎)。

出現一個心得:對數據的敏感度,乃至分析過程的思考、解決方案思考,也是一個分析師的專業點差異。

2. 支持的開發環境?

這分析的頻率如何、一次性的嗎?半年一次嗎?如果頻率不高,那在本地分析出結果也就可以了。例如一個月度的簡單指標監測,那可以先做成定時任務,每月csv導入本地、執行R。

但也有項目是需要每日刷新。用RFM分群來舉例吧(因為這應該比較多人熟悉),RFM model對user分了N個等級,但用戶行為是會改變的,同一用戶可以隨著時間、隨著行為而丟入不同的等級,那必須丟到線上環境每天更新。

線上環境如果沒支持R/python,那還要找工程師配置開發環境、環境測試、分析包部署、腳本測試等等的環節。

那既然RFM model用SQL也能做出來(來雖然可能比較粗糙但也夠用),何必給自己找麻煩呢?

像目前公司就只有支持python,那我Python又沒這麼熟,我就會選擇SQL。最近就遇到SQL做起來太麻煩的case,只好用python來開發。

在本地寫python測試運行ok,但到開發環境就各種報錯,一下端口權限不足、一下py版本太低、一下數據量太大要寫循環插入,天天查這句報錯到底啥意思…

3. 什麼應用場景?

假設公司開發環境都搭的很ok,我們也會根據應用場景來考量實現方式

Image for post
生意參謀_人群圈選

例如生意參謀,有個功能是讓賣家可以針對”用戶標籤”來圈選人群,並對這群人做行銷。可以看看這兩個標籤,"店舖N天有訪問“這種分析就用SQL寫、"折扣敏感度"用R/python寫機器學習腳本,是綜合型的場景。

4. 結論

Leetcode這題是有點難度(因為用到開窗函數跟子查詢),但在真正工作時,比這詭異的查詢雖不能說比比皆是,但還是不少。

我以前觀念也是SQL不就是取數嗎?接觸了比較多的分析工作後,才體會SQL的重要性跟實用性有多大。

創業公司 CitusData的雲產品負責人 Craig Kerstiens 曾發表了一篇文章SQL: One of the Most Valuable Skills,裡面提到一句話

SQL是我所知道的最神奇的概念之一。它已經有近五十年的歷史,而且沒有被其他東西替代的迹象。我們已經創建了無數種科技來存儲和處理數據,但似乎總是嘗試在這些科技中重新實現SQL(例如Hive、Presto、KSQL)。

<插播小廣告> 因此我自己寫的hive內容跟作業,也一直在思考怎麼設計出滿常用到且比較複雜的例子,並且設計出通用的數據表,而且絕對不是幾百筆數據,怎麼說也要10萬級別起跳的數據量才有感。另外為了不是紙上談兵,也在開發一個可以真正操作的大數據平台。

註. 解答: HQL腳本

Image for post

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.