所需數據：ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV

提取碼:yu63

我的數據庫版本是Mysql 8.0

本文章參考 秦路老師七周成為數據分析師 的視頻，和視頻中講解的這部分內容有少許差異。

這篇文章的目的是練習 怎么導入數據 以及 學習實際業務中的SQL邏輯，寫出可以復用的SQL語句，不對數據做過多的清洗。

目錄：

一、將數據導入數據庫

1.建表

2.導入數據

二、理解數據

1.表字段及含義

2.表信息中的業務問題

三、數據清洗

四、SQL--用戶消費行為分析

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

2-統計用戶不同月份的回購率和復購率

3-統計不同性別的消費頻次是否有差異

4-統計多次消費的用戶，第一次和最后一次消費時間的間隔

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

6-統計不同性別的用戶消費金額是否有差異

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

一、將數據導入數據庫

目的：將兩份csv文件導入數據庫

步驟：建表、導入數據

1.建表.數據庫data建表orderinfo

2.導入數據

前面給出的數據較大，分別是10萬和50萬條數據，如果用數據庫自帶的導數工具比較緩慢，這里介紹用cmd命令行方式導入。導入USER_INFO_UTF.CSV秦路老師導這兩個文件的方法以及對表字段類型的定義，在我這里一直報錯(秦路老師講解這部分的視頻地址：

二、理解數據

1.表字段及含義用戶明細表：userinfo

userinfo 客戶信息表 userId 用戶id

sex 性別 birth 出生年月日訂單明細表：orderinfo

orderinfo 訂單信息表 orderId 訂單id userId 客戶id isPaid 是否支付 price 商品價格 paidTime 支付時間

兩個表是通過 userid 進行聯結的。

2.表信息中的業務問題用戶明細表 userinfo 可以分析用戶畫像

訂單明細表 orderinfo 可以分析用戶不同月份回購率和復購率

訂單明細表 orderinfo 可以分析不同支付方式占比

兩表聯結，分析不同性別、年齡段的用戶消費頻次和消費金額差異

倆表聯結，粗略統計用戶生命周期

三、數據清洗

userinfo 中的 birth 和 orderinfo 中的 paidtime 是varchar類型，應該轉為日期格式。paidtime字段處理

首先將 paidtime 中的 / 替換為 -，這里用到 replace 函數：birth字段處理

首先將 birth 中的 / 替換成 - ，這里用到 replace 函數：

我用上述 paidtime 處理方式時，會報錯，顯示部分 birth 數據是有時分秒的，顯然時分秒在這里是用不到的，我們只取出年月日即可，這里用 left 函數來處理：

當然還有很多空數據，還有異常的消費數據，在這里不做處理了。

四、SQL--用戶消費行為分析

這里我們分析以下幾個問題：

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

2-統計用戶不同月份的回購率和復購率

3-統計不同性別的消費頻次是否有差異

4-統計多次消費的用戶，第一次和最后一次消費時間的間隔

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

6-統計不同性別的用戶消費金額是否有差異

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

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

分析：每個月---應該按照 月份 分組，但是數據里面有不同年份的數據，所以這里應該是按照 年、月 分組；下單人數---對 用戶id 計數 count ；下面的語句還用到 concat ，這是為了將數據寫成 年-月 形式。

2-統計用戶不同月份的回購率和復購率

這里明確 復購率 和 回購率 的概念復購：某個時間段內 購買次數>1次 的用戶

回購：某個時間段內 用戶 有購買行為，過段時間后，用戶 又購買，這叫做回購。回購和復購的差別是，跨了兩個時間維度。

例如三月份一共有1000名用戶購買，其中10個用戶在三月份購買次數超過1次，那么復購率是10/1000；三月份購買的人中有100個人在4月份又購買，那么回購率是100/1000。

(1)復購率

增加一列月度數據

這里面的所有購買都是至少一次，因為我們是從月份去選取，如果為0就不會出現這個值了，所以不需要過濾。

再通過這個子查詢，用條件if來選出符合條件的個數，如果不符合就是null，就不會記錄到count里面，同時將其除以當月的總數(這步有可能想不到用null，那么可以用sum，然后不符合就是0也可以的)。回購率

這里介紹兩種方法。

方法1

首先，添加一列 月數據

我們要篩選的是什么樣的數據？

我們希望篩選出來的是當月有購買，下個月還購買的，從上圖的數據發現，我們希望篩選出來的是ordermonth1 比 ordermonth 大1個月份 的 數據。

這里如果只想得到回購人數的話，其實用on和where篩選都可以的，但是如果還想得到當月購買總人數，那么就不要用where來篩選，因為where會把不符合的這個 月份差規則 的數據過濾掉。

這里我們用on來關聯，關聯條件是 t1.ordermonth=date_sub(t2.ordermonth,interval 1 month)

從上圖中我們可以看出 orderid1 和 ordermonth1 不是null的就是回購的，那么我們就可以對 月份分組 然后對 orderid1 即 t2.userid 求count，就應該是回購人數；對 userid 也就是 t1.userid 來求 count 就應該是當月購買總人數。

回購率就是用 回購人數/當月購買人數：

方法2

倆表聯結之前方法都一樣。如果這里我們用內連接，其實和 left 聯結 結果一樣，只是后面我們篩選 月份規則 的時候不采用on聯結的方式。我們先看內聯結結果是不是和 left 聯結一樣。

下面我仿照之前求復購率的方式，用 count if 來篩選出 符合月份規則 的數據。

3-統計不同性別的消費頻次是否有差異

兩個表內連接，然后按照userid 和 sex 分組，求出每個 userid 的消費次數，在求出平均消費次數

從上圖可以看出有性別為空的數據，所以這里我們篩選出性別不為空的數據，可以在join之前的userinfo篩選，也可以在join之后的大表篩選，這里為了方便我在大表篩選。

4-統計多次消費的用戶，第一次和最后一次消費時間的間隔

分析：按照用戶id分組，現將每個用戶第一次和最后一次消費的時間 select，這里還要注意一個having篩選條件，用戶購買次數一定要>1，然后當做子查詢的臨時表，從這個臨時表中再求出時間間隔。

這里第一次消費和最后一次消費時間也就是min和max時間。

原日期是年-月-日-時-分-秒的形式，用datediff求差時得到的是差多少秒，所以這里將日期寫成年-月-日的形式。

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

將兩個表聯結，然后select price和年齡段，年齡段是用用case when 劃分的，篩選條件是已支付訂單的金額，將這個表當做一個臨時表，臨時表對年齡段分組，然后求出平均金額。

年齡是用的現在的年份-出生的年份求出來的；

平均金額 用round 保留兩位小數。

6-統計不同性別的用戶消費金額是否有差異

分析方法和第5個問題類似。

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

分析：從已支付用戶中篩選，對用戶id進行分組，對 每個用戶的 price求和，然后用窗口函數對sum price 排序，查詢出來的結果當做臨時表，從臨時表中篩選排名top20%的用戶，對這部分用戶的 金額求和。

窗口函數是可以對分組的結果進行排序，很方便。

窗口函數 可以參考我之前的一篇文章：Foina：SQL高級功能--窗口函數及面試題TOPN和組內比較問題?zhuanlan.zhihu.com

總結

以上是生活随笔為你收集整理的mysql求回购率_SQL-- 用户行为(复购率、回购率等）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。