MySQL入門學習筆記——七周數據分析師實戰作業

本篇推送主要涉及SQL語言中較為複雜的子查詢與函數嵌套。

雖然這個MySQL系列取名為MySQL基礎入門，但是個人不打算做單個函數的用法總結，或者說簡單羅列，（這些內容你可以通過很多途徑了解）因為一方面以前有過SQL基礎方面的學習經歷（本科的計算機必修課以及計算機等級考試）現在應該更加深入一些，另一方面SQL是一門數據分析語言，單純的一個兩個函數基本很少能解決問題。

SQL語言不像R語言和Python那種面向對象的語言，提供了各種靈活多變的的可用方法以及成千上萬的高效解決工具，更沒有提供像管道函數那樣的參數傳遞工具，所以多重任務想要一次性解決大多數時候需要藉助子查詢和函數嵌套。

（如果你是第一次接觸SQL語言，最好能夠通過瀏覽一兩本入門書或者系統了解一下SQL的查詢語法之後再來看此文）

本文的練習數據素材取自天善智能大數據模塊的暢銷課程——「七周成為數據分析師」，主講老師是在職場混跡多年的數據大咖，老司機秦路老師。

秦老師的課程針對數據分析師所需要的業務知識、分析技能、編程技能等各個模塊都做了非常精彩的總結和案例分享，推薦喜歡或者感興趣的小夥伴兒入手。

https://edu.hellobi.com/course/205

由於是付費課程，這裡不便提供原始數據，還請各位見諒，但是文中所有的代碼輸出均會在適當的地方提供數據預覽和欄位描述，此文僅是我學習其中的MySQL模塊的課程大作業，用自己的思路實現一遍，同時又按照老師的思路整理出代碼，通過思路的對比查漏補缺、提升sql的代碼實踐能力。

同時我會把這份大作業使用R語言和Python中的常用分析工具實現，這樣讀者可以對比三種工具之間實現相同需求的過程差異以及各自優缺點，加深數據處理過程的理解。

首先大致介紹這兩份數據：

userinfo 客戶信息表 userId 客戶id gender 性別 brithday 出生日期 orderinfo 訂單信息表 orderId 訂單序號（虛擬主鍵） userId 客戶id isPaid 是否支付 price 商品價格 paidTime 支付時間

以上兩個表格是本次分析的主要對象，其中匹配欄位是userId。

本次分析的五個問題：

1、統計不同月份的下單人數；

4、統計多次消費的用戶，第一次和最後一次消費間隔是多少？

1、統計不同月份的下單人數；

第一道題目比較簡單，僅需將日期欄位通過日期函數轉換為月份標籤，然後根據月份標籤聚合出單月下單的人數即可！

我的思路是使用DATE_FORMAT函數輸出購買記錄的月度標籤，然後使用聚合函數group by函數對月度標籤進行聚合（計數）,使用count計數時要考慮重複購買的情況，進行客戶去重，獲取真實人數。

SELECT DATE_FORMAT(paidTIme, %Y-%m) AS MT, count(DISTINCT userId) AS scale FROM orderinfo WHERE isPaid = 已支付GROUP BY MT

因為購買日期欄位都是同一個年份的，所有老師直接使用MONTH函數，這樣更加簡便！

SELECT MONTH (paidTIme) AS MT, count(DISTINCT userId) AS scale FROM orderinfo WHERE isPaid = 已支付GROUP BY MT

2、統計用戶三月份回購率和復購率

第二道題目需要理解回購率和復購率的業務含義（我之前都搞混了，後來去百度查的），復購率等於當月消費者中消費次數多於一次的人數佔比，回購率則是上一個月消費者中在當月再次消費的佔比。

計算復購率（復購率的計算思路，自己的與老師的差不多）：

先計算三月份購買人數，並作為一個子查詢返回，外層查詢使用count+if函數計算大於一次消費的購買者人數，將其與總人數相除，即可得到復購率。

SELECT COUNT(if (thr.scale!=1,1,null)) as useful, COUNT(*) as Fulln, COUNT(if( thr.scale!=1,1,null))/count(*) as ratio FROM ( SELECT userId, count(userId) AS scale FROM db1.orderinfo WHERE MONTH (paidTime) = 3 AND isPaid = 已支付 GROUP BY userId ) AS thr

計算回購率（自己的思路）：

對三月份購買者進行去重，使用count計算三月份購買者中有多少出現在四月份購買者中（通過在where中使用子查詢作為過濾條件），將返回結果記錄數與三月份購買者總人數相除即可得到回購率。

SELECT count(DISTINCT userId) AS allfunn, count(DISTINCT userId) / ( SELECT count(DISTINCT userId) FROM orderinfo WHERE MONTH (paidTime) = 3 AND isPaid = 已支付 ) AS ratio FROM db1.orderinfo WHERE MONTH (paidTime) = 3AND isPaid = 已支付AND userId IN ( SELECT DISTINCT userId FROM db1.orderinfo WHERE MONTH (paidTime) = 4 AND isPaid = 已支付)

關於回購率，老師使用了一個自連接，勉強能理解大致思路，通過對比兩個月份的月度標籤是否相差一個月，相差一個月則為老客戶重複購買，這樣在月份多時具有更好地適用性。

select t1.m,count(t1.m),count(t2.m) from ( select userId,DATE_FORMAT(paidTime,%Y-%m-01) as m from db1.orderinfo where isPaid = 已支付 group by userId,date_format(paidTime,%Y-%m-01)) t1 left join ( select userId,date_format(paidTime,%Y-%m-01) as m from db1.orderinfo where isPaid = 已支付 group by userId,date_format(paidTime,%Y-%m-01)) t2 on t1.userId = t2.userId and t1.m = date_sub(t2.m,interval 1 month) group by t1.m

3、統計男女用戶消費頻次是否有差異

這個問題被我給複雜化了，我分別求了一次男性消費頻次和女性消費頻次！思路就是先將用戶表和訂單表做聯結，然後過濾性別為男的記錄並通過分組返回單一消費者記錄。（女性的計算類比男性）

-- 男性消費頻次SELECT SUM(mmg.mean) / count(*) AS mam_mFROM ( SELECT orderinfo.userId, ROUND(COUNT(orderinfo.userId), 1) AS mean FROM orderinfo INNER JOIN userinfo ON orderinfo.userId = userinfo.userId WHERE userinfo.gender = 男 GROUP BY orderinfo.userId ) AS mmg-- 女性消費頻次：SELECT SUM(mmg.mean) / count(*) AS mam_mFROM ( SELECT orderinfo.userId, ROUND(COUNT(orderinfo.userId), 1) AS mean FROM orderinfo INNER JOIN userinfo ON orderinfo.userId = userinfo.userId WHERE userinfo.gender = 女 GROUP BY orderinfo.userId ) AS mmg 男女消費頻次（老師版）：老師首先在連接兩個表的基礎上，剔除了無效記錄，通過count做了單個購買者的購買數量統計，然後使用了基於性別的分組均值聚合，輸出男女性平均消費頻次。這個思路太棒了，我特么的就是想不到~_~SELECT gender, AVG(ct)FROM ( SELECT o.userId, gender, count(1) AS ct FROM orderinfo o INNER JOIN ( SELECT * FROM db1.userinfo WHERE gender != ) t ON o.userId = t.userId GROUP BY userId, gender ) t2GROUP BY gender

4、統計多次消費的用戶，第一次和最後一次消費間隔是多少？

這個題目是我耗費我時間最長的一道題目，其實邏輯上肯定大家都知道需要篩選出那些消費次數大於1次的記錄，然後通過單個購買者所有消費記錄中最遠的消費時間與最近的消費時間做時間差即可。說起來簡單可以做起來並不簡單。於是我把代碼寫成了下面這個樣子！

SELECT * FROM ( SELECT userId, DATEDIFF( myresult.uptime, myresult.odtime ) AS difftime FROM ( SELECT lowf.userId, lowf.odtime, UPf.uptime FROM ( SELECT userId, min(ldd.ltime) AS odtime FROM ( SELECT userId, paidTime AS ltime FROM orderinfo WHERE isPaid = 已支付 ORDER BY userId, Ltime ) AS ldd GROUP BY userId ) AS lowf INNER JOIN ( SELECT userId, max(pdd.ptime) AS uptime FROM ( SELECT userId, paidTime AS ptime FROM orderinfo WHERE isPaid = 已支付 ORDER BY userId, ptime DESC ) AS pdd GROUP BY userId ) AS UPf ON lowf.userId = UPf.userId ) AS myresult ) AS myresult1WHERE difftime != 0

我的大體思路是，最內層的邏輯是先篩選出來消費者距今最遠消費記錄，最近消費記錄，並將兩次輸出做內連接。在輸出的表基礎上，做時間差，如果時間為0則說明只有一次消費，直接使用difftime != 0過濾掉即可。

以下是老師給出的思路，看完之後大呼自愧不如，可以看到我上面的那個內連接是多此一舉，使用max、min兩個函數並列欄位就可以解決，但是我寫的太複雜了！居然也能跑出來。

SELECT userId, min(paidTime) as mintime, max(paidTime) as maxtime, datediff( max(paidTime), min(paidTime) ) as difftime FROM db1.orderinfo WHERE isPaid = 已支付GROUP BY userId HAVING count(1) > 1

5、統計不同年齡段用戶消費金額是否有差異

這個問題乍一看，我不太理解，最初想著這個年齡段怎麼定義（沒有給出精確的定義），然後我就想著平時一說到年齡代購就說什麼70後、80後、90後什麼的，就以為這種就可以做年齡段依據。

我個人的大體思路就是，最內層首先做兩個表的聯結（聯結的同時過濾掉缺失值和未支付記錄），然後中間層對出生日期進行分類編碼（1970~1979為70後，以此類推）。

最後最外層通過對年齡段進行分組聚合，求不同年齡段下的支付價格的均值。

SELECT trend, round(avg(price), 2) AS means FROM ( SELECT userId, btdate, price, CASE WHEN btdate BETWEEN 1970-01-01 AND 1979-12-31 THEN 70後 WHEN btdate BETWEEN 1980-01-01 AND 1989-12-31 THEN 80後 WHEN btdate BETWEEN 1990-01-01 AND 1999-12-31 THEN 90後 WHEN btdate BETWEEN 2001-01-01 AND 2009-12-31 THEN 00後 ELSE 10後 END AS trend FROM ( SELECT o.userId, price, date(brith) AS btdate FROM orderinfo o LEFT JOIN ( SELECT * FROM db1.userinfo WHERE gender != ) t ON o.userId = t.userId WHERE isPaid = 已支付 AND date(brith) != 0000-00-00 ) AS mt ORDER BY userId ) AS outtableGROUP BY trendORDER BY means

關於年齡段消費金額差異，老師給出的思路：

SELECT age, AVG(ct)FROM ( SELECT o.userId, age, count(o.userId) AS ct FROM db1.orderinfo o INNER JOIN ( SELECT userId, CEIL((YEAR(now()) - YEAR(brith)) / 10) AS age FROM db1.userinfo WHERE brith > 1901-00-00 ) AS t ON o.userId = t.userId GROUP BY o.userId, age ) t2 GROUP BY age

以上老師計算了各年齡段購買者消費消費頻次的平均值。這裡老師使用日期函數替代了分別編碼工作，使得整體代碼看起來很簡潔易懂。（自己需要學的還有很多！）

6、統計消費的二八法則，消費的top20%用戶，貢獻了多少額度？

其實這個二八法則的問題邏輯很簡單，就是按照單個消費者總消費金額排序，計算出那些前20%的的購買者消費金額佔總體消費金額的比例。雖然邏輯很簡單，但是在MySQL中想要寫出次邏輯卻並不是一件容易的事情，因為MySQL不支持 top n 這種函數，想要過濾前n個記錄只能通過 追加 limit參數才可以。

所以我自己寫了兩段代碼才解決：

首先按照單個消費者總購買金額排序，計算出前總支出排在前top20的消費者數量。（一共是17130）

SELECT ceil(count(*) / 5)FROM ( SELECT userId, sum(price) AS allsp FROM orderinfo WHERE date(paidTime) != 0000-00-00 AND isPaid = 已支付 GROUP BY userId ORDER BY allsp DESC ) AS spend -- 17130

然後再次運行次查詢，使用limit參數限制輸出前17130 個記錄並計算其總金額占所有消費金額的比例即可。

SELECT ( SELECT sum(allsp) AS top20 FROM ( SELECT userId, sum(price) AS allsp FROM orderinfo WHERE date(paidTime) != 0000-00-00 AND isPaid = 已支付 GROUP BY userId ORDER BY allsp DESC LIMIT 17130 ) AS spend ) / ( SELECT sum(allsp) AS entry FROM ( SELECT userId, sum(price) AS allsp FROM orderinfo WHERE date(paidTime) != 0000-00-00 AND isPaid = 已支付 GROUP BY userId ORDER BY allsp DESC ) AS spend ) as top20ratio

計算結果是85.46%左右。

由於篇幅所限，關於這五個問題的R語言版、Python版，期待下一篇推送吧！

說幾點個人感想：

1、因為之前關於數據清洗和數據處理技能，全部都是在R語言中練習的，突然使用SQL來做，即便很簡單的需求邏輯，寫起來都感覺磕磕碰碰，總之就是無法靈活運用，簡單問題往往被複雜化。

2、SQL中查詢語句有固定的模式，所有的輸出都要嚴格依賴select …… from…… where group by語句，甚至連各種函數都無法單獨使用，這一點兒導致很多需要多步完成需求無法分割成多個中間步驟，必須藉助子查詢。

3、SQL沒有像R語言一樣的管道操作符或者Python中的方法調用，多任務步驟在一個句子中只能依賴子查詢進行嵌套，稍微複雜些的需求，如果基礎函數使用不夠靈活的話，可能會寫的很繁雜。

SQL查詢語法需要在深刻理解表關係的基礎上，盡量使用自帶函數解決，這樣既高效、又可以節省代碼，以上自己寫的代碼中，有特別多的地方有冗餘，以後還需要勤加練習，加強各種場景下的實踐，靈活運用才能寫出來簡潔、高效、可復用性高的任務代碼。

以下鏈接是秦路老師在天善學院所主講的七周成為數據分析師系列課程！

https://edu.hellobi.com/course/205

七周課程，七種數據分析師必備技能，循序漸進、逐個擊破，推薦給對數據分析、商業分析感興趣的小夥伴兒學習！

推薦閱讀：