編輯導(dǎo)語：作為一名數(shù)據(jù)人員，需要掌握SQL嗎？掌握的程度需要到多少？作為一名專注于分析結(jié)論/項目在業(yè)務(wù)落地以實現(xiàn)增長的分析師，掌握SQL相關(guān)工作內(nèi)容，有助于更好地解決業(yè)務(wù)問題。本文作者總結(jié)了一些SQL的必備知識與應(yīng)用場景以及相關(guān)的實戰(zhàn)應(yīng)用。

數(shù)據(jù)分析人員需要掌握SQL到什么程度？

請問做一名數(shù)據(jù)分析人員，在SQL方面需要掌握到什么程度呢？會增刪改查就可以了嗎？還是說關(guān)于開發(fā)的內(nèi)容也要會？不同階段會有不同的要求嗎？

作為專注數(shù)據(jù)分析結(jié)論/項目在業(yè)務(wù)落地以實現(xiàn)增長的分析師，建議在開始學(xué)習(xí)新技能前，先明確應(yīng)用場景。有的放矢才能不枉費努力。

翻譯過來就是：先了解與SQL相關(guān)的數(shù)據(jù)分析工作有哪些？有了目標(biāo)，才能知道需要準(zhǔn)備什么知識來應(yīng)對。

按我目前與SQL相關(guān)的工作內(nèi)容，為你提供以下參考：

食用說明：根據(jù)以下場景，選擇需要重點學(xué)習(xí)的知識點。

一、SQL應(yīng)用場景及必備知識

星標(biāo)根據(jù)使用頻率標(biāo)記，而非重要性。

1. 數(shù)據(jù)查詢 ★★★

（1）業(yè)務(wù)場景

也就是常說的“提數(shù)”。

實際工作場景中，如果向IT提提數(shù)需求，一般都需要溝通+排期，所以最有效率的建議就是自己會從數(shù)據(jù)庫里提數(shù)，此簡單查詢可以應(yīng)對部分提數(shù)需求，例如運營想查看某段時間訂單數(shù)據(jù)分析師除了自身的分析工作外，有時（甚至是經(jīng)常）還需要應(yīng)付產(chǎn)品、運營等部門同事的提數(shù)需求。

（2）必備知識

① 簡單查詢

即最簡單的關(guān)鍵字組合SELECT?+FROM?+WHERE?+(BETWEEN?/IN) 是SQL查詢的地基，此簡單查詢可以應(yīng)對部分提數(shù)需求，例如運營想查看某段時間訂單。

② 多表查詢

即INNER JOIN、LEFT JOIN?等聯(lián)結(jié)關(guān)鍵字，想象中的取數(shù)可能是直接在某個表SELECT想要的字段？

NO! 實際上為了查詢效率，數(shù)據(jù)會散落到數(shù)據(jù)庫的各個角落，例如想要了解一筆訂單情況，信息存在這些表中：訂單流水表、訂單詳情表、商品詳情表、門店表、會員表等。

該部分知識的關(guān)鍵在于「明確業(yè)務(wù)分析需求→選擇合適的聯(lián)結(jié)方式」。

2. 數(shù)據(jù)更新 ★★☆

（1）業(yè)務(wù)場景

即“增刪改”，該場景之所以僅兩星的原因，是實際工作中，數(shù)據(jù)庫運維部門給到我們數(shù)據(jù)分析師的數(shù)據(jù)庫賬號多半是只讀權(quán)限，也就無法去“增刪改”。

此外，還有數(shù)據(jù)管控的原因，所以此場景可能更多在于自建數(shù)據(jù)庫中，如在電腦上新建虛擬機搭建數(shù)據(jù)庫服務(wù)器，導(dǎo)入數(shù)據(jù)后方便進(jìn)行下一步分析。

（2）必備知識數(shù)據(jù)庫與表的創(chuàng)建、刪除和更新

該部分知識點關(guān)鍵在于「字段類型的設(shè)置」要符合后續(xù)分析需求，如訂單商品數(shù)量就要設(shè)成數(shù)值類型、訂單日期設(shè)成日期類型等（因為見過都設(shè)成字符類型的表，所以就簡單提一下）。

3. 分析數(shù)據(jù) ★★★

（1）業(yè)務(wù)場景

該部分可謂是數(shù)據(jù)分析師的核心工作。面對復(fù)雜的業(yè)務(wù)問題，重點在于將其拆解、轉(zhuǎn)譯成簡單的SQL問題。

「案例」例如教育行業(yè)中某領(lǐng)導(dǎo)要求你“分析某課程的效果如何”→ 翻譯：課程效果可通過學(xué)生成績反映，即是要計算成績最大值、最小值、學(xué)生成績分布 → SQL語句。

（2）必備知識

① 匯總分析

即GROUP BY關(guān)鍵字。

② 解決業(yè)務(wù)問題

如計算每個課程學(xué)生的平均成績：SELECT avg(成績) FROM 成績表 GROUP BY 課程

③ 復(fù)雜查詢

如嵌套子查詢、標(biāo)量子查詢、關(guān)聯(lián)子查詢，可應(yīng)對更復(fù)雜的業(yè)務(wù)問題：

如找出每個課程最高分的學(xué)生 → 需要按課程分組后找到最高成績記錄，可以應(yīng)用關(guān)聯(lián)子查詢：SELECT 學(xué)生名字 FROM 成績表 a WHERE 成績 = ( SELECT max(成績) FROM 成績表 b WHERE a.課程=b.課程)

④ 窗口函數(shù)

聚合/排序函數(shù) ( ) OVER (PARTITION BY…ORDER BY…)

此函數(shù)可解決復(fù)雜業(yè)務(wù)問題，如常見的TOP N問題：

找出每個課程成績前三的學(xué)生 → 按課程分組對學(xué)生按成績排名，再從中找出排名前三的學(xué)生：SELECT 學(xué)生名字 FROM ( SELECT 學(xué)生名字, dense_rank( ) over (partitionby 課程 orderby 成績 desc) as ‘成績排名’ FROM 成績表 ) as t WHERE t.成績排名<4

4. 數(shù)據(jù)產(chǎn)品 ★☆☆

（1）業(yè)務(wù)場景

對于部分崗位，如我在的集團(tuán)用戶數(shù)據(jù)中心，需要負(fù)責(zé)搭建如CDP這樣的數(shù)據(jù)產(chǎn)品，雖然多數(shù)情況下是由開發(fā)負(fù)責(zé)數(shù)據(jù)庫工作，但是對于里面核心的功能如運營指標(biāo)體系、模型報表等，背后的計算邏輯、數(shù)據(jù)流，要求數(shù)據(jù)分析師了如指掌。

此外，對于剛開始建立數(shù)據(jù)分析團(tuán)隊的部門，還存在「數(shù)據(jù)同步」的需求，即要從ERP、CRM等系統(tǒng)將需要分析的原數(shù)據(jù)同步到自己的數(shù)據(jù)庫里便于分析，而此需求需要通過存儲過程實現(xiàn)。

（2）必備知識存儲過程

即PROCEDURE，可以將某業(yè)務(wù)需求，或者數(shù)據(jù)產(chǎn)品中的報表對應(yīng)的所有SQL語句放在一起，方便一鍵執(zhí)行，如RFM模型里的語句可以寫成存儲過程，計算結(jié)果實時同步到前端。

「SQL SERVER」計劃：面對「數(shù)據(jù)同步」需求，有了存儲過程后，還需要進(jìn)行定時任務(wù)，在非業(yè)務(wù)時間執(zhí)行數(shù)據(jù)同步的存儲過程。

如是使用SQL SERVER版本，可以通過“計劃”實現(xiàn)定時任務(wù)。

5. 項目部署 ★☆☆

（1）業(yè)務(wù)場景

數(shù)據(jù)分析結(jié)論在業(yè)務(wù)場景測試有效后，就需要通過報表、模型等方式落地形成業(yè)務(wù)常態(tài)。而這個項目落地，可能交給開發(fā)處理，但更有效的方式是分析師可以參與到部署的過程中。這個過程，其中一個重要的部分就是數(shù)據(jù)庫的設(shè)計：如何設(shè)計表格以提高計算效率。

（2）必備知識

數(shù)據(jù)庫設(shè)計與「SQL三范式」：SQL三范式的目的在于解決數(shù)據(jù)冗余、計算效率低等問題，另一方面對數(shù)據(jù)增加、修改更友好。

以上部分從業(yè)務(wù)場景出發(fā)，討論業(yè)務(wù)問題的解決方案與SQL知識點的關(guān)系，解決學(xué)習(xí)了SQL之后可以做什么的問題。

下面將從實戰(zhàn)分析場景出發(fā)，講解分析報告中需要掌握哪些SQL知識？

二、實戰(zhàn)：如何分析用戶？

用SQL做一份數(shù)據(jù)分析報告涉及什么哪些知識點？

在工作中，每個數(shù)據(jù)分析師都離不開做數(shù)據(jù)分析報告，而一份可落地的報告更是要求靈活地應(yīng)用工具及理論知識。接下來，我們從工具應(yīng)用的角度，看看如何用SQL做一份完整的數(shù)據(jù)分析報告。

1. 數(shù)據(jù)導(dǎo)入

（1）新建數(shù)據(jù)庫

用優(yōu)秀的數(shù)據(jù)庫管理工具Navicat 連接數(shù)據(jù)庫，通過Navicat 將數(shù)據(jù)（如Excel、SQL腳本等格式）導(dǎo)入數(shù)據(jù)庫。

2. 數(shù)據(jù)清洗

數(shù)據(jù)清洗的目的是為了將數(shù)據(jù)按照業(yè)務(wù)分析需求，剔除異常值、離群值，使分析結(jié)果更準(zhǔn)確反映業(yè)務(wù)實際。

常見的步驟如下：

是否存在空值：WHERE`字段名`is null

是否存在重復(fù)數(shù)據(jù)：通過GROUP BY關(guān)鍵字實現(xiàn)

SELECT COUNT(*) FROM 表名 GROUP BY 字段名 HAVING COUNT(*) >1

是否存在業(yè)務(wù)定義以外的數(shù)據(jù)：比如需要分析華南區(qū)域數(shù)據(jù)，而數(shù)據(jù)中出現(xiàn)華北數(shù)據(jù)

3. 數(shù)據(jù)格式化

這一步是要根據(jù)后續(xù)分析需求，調(diào)整表格結(jié)構(gòu)、數(shù)據(jù)格式等，如出于數(shù)據(jù)存放原因，拿到的數(shù)據(jù)表格可能是一維表，不滿足分析需求，需要將其調(diào)整為二維表。常見的步驟如下：

1. 時間函數(shù)：如將「時間戳」格式化為日期、時間、月份、周幾（常見于周分析）等，可通過「FROM_UNIXTIME」「DATE_FORMAT」等函數(shù)實現(xiàn)
2. 行列互換：如解決上述的一維表轉(zhuǎn)為二維表的問題，可通過關(guān)鍵字「CASE WHEN」實現(xiàn)
3. 字段的拆分與合并：如將收貨地址字段拆為省、市、鎮(zhèn)等字段，可通過「CONCAT」「LEFT」「RIGHT」「SUBSTRING」等函數(shù)實現(xiàn)

4. 整體分析

在開始真正的分析之前，需要進(jìn)行探索性數(shù)據(jù)分析(Exploratory Data Analysis，EDA)，也就是對現(xiàn)有數(shù)據(jù)進(jìn)行整體分析，對現(xiàn)狀有大體的了解。

更重要的是，通過整體分析，找出業(yè)務(wù)運營存在的問題，進(jìn)而提出業(yè)務(wù)目標(biāo)，展開后續(xù)的深度分析。

常見的步驟如下：

漏斗分析：如海盜模型AARRR，阿里營銷模型AIPL等，通過簡單的「COUNT」函數(shù)，直接數(shù)就可實現(xiàn)

5. 建立視圖

面對復(fù)雜的業(yè)務(wù)分析，SQL語句也會變得復(fù)雜，往往需要不斷嵌套。為了減少分析時語句的復(fù)雜性、避免重復(fù)執(zhí)行相同語句，可以采用新建視圖的方式，將重復(fù)性高的語句固定為視圖，再在此基礎(chǔ)上進(jìn)行復(fù)雜查詢。

新建視圖：CREATE VIEW 視圖名 AS SELECT…

6. 用戶分析

從整體分析中，明確業(yè)務(wù)問題、目標(biāo)后，便可開始進(jìn)行用戶分析。根據(jù)分析目的的不同，采用不同的分析方法，而常見的分析方法如下：

（1）「人貨場」分析

（2）「復(fù)購」分析

核心問題在于如何計算“復(fù)購”：用「窗口函數(shù)+DENSE_RANK()」統(tǒng)計每個訂單是該用戶的第幾次消費，命名為’N_CONSUME’。

第一次消費即為用戶“首購訂單”，大于等于第二次消費的訂單則為“復(fù)購訂單“，針對復(fù)購訂單進(jìn)行統(tǒng)計，即可進(jìn)行復(fù)購分析。

（3）「RFM模型」分析

核心問題在于如何定義閾值及人群劃分：通過【窗口函數(shù)】可計算出每個用戶的RFM值：

• R：每個用戶最后消費日期，與分析日期相減的天數(shù)即為R
• F：通過復(fù)購分析中得出的N_CONSUME，計算最大消費次數(shù)即為F
• M：簡單地SUM用戶所有消費金額，即為M

閾值：可通過計算所有用戶的R,F,M平均值獲得

所有用戶的RFM值與閾值比較，通過「CASE WHEN」轉(zhuǎn)為 ‘高’、’低’ 兩個值，根據(jù)RFM高低值通過「CASE WHEN」將所有用戶劃分到八個人群中。

本文由 @餅干哥哥 原創(chuàng)發(fā)布于人人都是產(chǎn)品經(jīng)理，未經(jīng)作者許可，禁止轉(zhuǎn)載。

題圖來自Unsplash，基于CC0協(xié)議