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

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

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

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

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

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

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

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

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

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

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

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

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

（2）必備知識(shí)

① 簡(jiǎn)單查詢

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

② 多表查詢

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

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

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

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

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

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

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

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

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

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

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

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

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

（2）必備知識(shí)

① 匯總分析

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

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

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

③ 復(fù)雜查詢

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

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

④ 窗口函數(shù)

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

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

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

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

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

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

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

（2）必備知識(shí)存儲(chǔ)過程

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

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

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

5. 項(xiàng)目部署 ★☆☆

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

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

（2）必備知識(shí)

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

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

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

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

用SQL做一份數(shù)據(jù)分析報(bào)告涉及什么哪些知識(shí)點(diǎn)？

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

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ù)實(shí)際。

常見的步驟如下：

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

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

4. 整體分析

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

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

常見的步驟如下：

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

5. 建立視圖

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

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

6. 用戶分析

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

（1）「人貨場(chǎng)」分析

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

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

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

（3）「RFM模型」分析

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

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

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

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

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

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