利用SQL進行用戶消費行為分析

在MySQL環境下，對userinfo和orderinfo兩個表格進行用戶消費行為分析。

userinfo和orderinfo數據信息如下：

userinfo 客戶信息表 userId 客戶id sex 性別 birth 出生年月日orderinfo 訂單信息表 orderId 訂單序號 userId 客戶id isPaid 是否支付 price 商品價格 paidTime 支付時間

待分析的六個問題：

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

5、統計不同年齡段，用戶的消費頻次是否有差異

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

查看orderinfo發現支付時間欄位都是同一年份的，所以直接使用month函數提取月份，這樣更加簡便。

select month(paidTime) ,count(distinct userId) from data.orderinfowhere isPaid = 已支付group by month(paidTime)

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

復購率指當月消費者中消費次數多於一次的人數佔比

回購率指本月消費者中在下月再次消費的佔比

復購率：

先統計三月份每個購買者的購買次數，作為一個子查詢返回，

外層使用count+if函數統計大於一次消費的購買者人數，將其與總人數相除，即可得到復購率。

select count(ct) ,count(if(ct>1,1,null)),count(if(ct>1,1,null))/count(ct) as ratiofrom ( select userId,count(userId) as ct from data.orderinfo where isPaid = 已支付 and month(paidTime) = 3 group by userId) t

回購率：

使用自連接，對比兩個月份是否相差一個月，統計本月購買並在下月繼續購買的客戶數量

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

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

過濾空值，連接兩個表，通過count統計單個購買者的購買次數，

根據性別分組，統計均值，得到男女平均消費頻次。

select sex,avg(ct) from ( select o.userId,sex,count(1) as ct from data.orderinfo o inner join ( select * from data.userinfo where sex!= ) t on o.userId = t.userId group by userId,sex) t2group by sex

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

提取多次消費用戶，用datediff計算max和min的差值

select userId,max(paidTime),min(paidTime), datediff(max(paidTime),min(paidTime)) from data.orderinfowhere isPaid = 已支付group by userId having count(1) > 1

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

年齡/10作為年齡段

select age,avg(ct) from ( select o.userId,age,count(o.userId) as ct from data.orderinfo o inner join ( select userId,ceil((year(now()) - year(birth)) / 10) as age from data.userinfo where birth > 1901-00-00) t on o.userId = t.userId group by o.userId,age) t2group by age

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

按消費金額排序，使用limit參數限制輸出前17000個記錄

select count(userId),sum(total) from ( select userId,sum(price) as total from data.orderinfo o where isPaid = 已支付 group by userId order by total desc limit 17000)t

推薦閱讀：