這6道sql題都很好，建議都過一遍；

考察知識點：

• 回購率、復購率的理解
• 子查詢
• inner join
• 重點推薦第2題，第5題，第6題
• 理解需求、理解題意 （★★★★★）
• datediff
• ceil 函數(shù)
• row_number() 、 子查詢內(nèi)容
• 二八定律的應(yīng)用

復購率和回購率分析：
復購率：復購率是指重復購買的頻率，用于反映用戶的付費頻率。復購率指的是一定時間內(nèi)，消費兩次以上的用戶數(shù)/付費人數(shù)。
回購率：曾經(jīng)購買過的用戶在某一時期內(nèi)再次購買的占比

– lulu_Course

附上源碼：

-- lulu_Course -- 1.統(tǒng)計不同月份的下單人數(shù) select month(paidTime),count(distinct userid) from data.orderinfo where isPaid = "未支付" group by month(paidTime)-- 2.統(tǒng)計用戶三月份的回購率和復購率 /* 名詞解釋： 復購率：在這個月里面，所有的消費人數(shù)中有多少個是消費一次以上人數(shù)的占比； 回購率：上月購買的人數(shù)，在下一個月依舊購買； */-- 復購率： select count(ct)，count(if(ct>1,1,null)) from(select userid,count(userid) as ct from order_infowhere isPaid = "已支付"and month(paidTime) = 3group by userid)t -- 回購率：涉及跨月份# 法1：代碼適合一次性需求 select count(1) from where userid in (子查詢，算出3月份的userid) and month(paidTime) = 4 group by userid;# 法2： -- step1: select * from(select userid,date_format(paidTime,"%Y-%m-01") as mfrom order_info where ispaid = "已支付"group by userid,date_format(paidTime,"%Y-%m-01"))t1 left join(select userid,date_format(paidTime,"%Y-%m-01") as mfrom order_info 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) -- where t1.m = date_sub(t2.m,interval 1 month)亦可-- step2:select t1.m,count(t1.m) as 購買人數(shù),count(t2.m) as 回購人數(shù) from(select userid,date_format(paidTime,"%Y-%m-01") as mfrom order_info where ispaid = "已支付"group by userid,date_format(paidTime,"%Y-%m-01"))t1 left join(select userid,date_format(paidTime,"%Y-%m-01") as mfrom order_info 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) -- where t1.m = date_sub(t2.m,interval 1 month) group by t1.m-- 3.統(tǒng)計男女用戶的消費頻次是否有差異 /* 理解：求消費頻次？總計、求平均 (當然篇平均數(shù)未必是靠譜的，這只是一個其中的分析思路吧)*/select sex,avg(ct) from(select t1.userid,sex,count(1) as ct from order_info t1inner info(select * from user_infowhere sex <> "" )t2on o.userid = t.useridgroup by userid,sex)t3 group by sex；-- 4.統(tǒng)計多次消費的用戶，第一次和最后一次消費間隔是多少？ -- 操作1 select userid,max(paidTime),min(paidTime) from order_info where ispaid = '已支付' group by userid having count(1)>1;-- 操作2：（lulu：勉強估計一下生命周期） select avg(interval) as avg_interval from(select userid,max(paidTime),min(paidTime),datediff(max(paidTime),min(paidTime)) as interval from order_info where ispaid = '已支付' group by userid having count(1)>1) tepmt;-- 5.統(tǒng)計不同年齡段，用戶的消費金額是否有差異 -- 計算每個用戶的消費頻次 select age,avg(ct) from（select o.userid,age,count(o.userid) as ct from order_info o where ispaid = '已支付' inner join(select userid,ceil((year(now())-year(birth))/10) as agefrom userinfowhere birth > '1901-00-00')t -- 過濾掉117 on o.userid = t.userid group by o.userid,age）t2 group by age；-- 補充知識：ceil()函數(shù)和floor()函數(shù) -- ceil(n) 取大于等于數(shù)值n的最小整數(shù)； -- floor(n) 取小于等于數(shù)值n的最小整數(shù)；-- 6.統(tǒng)計消費的二八法則，消費的top20%用戶，貢獻了多少額度-- 方法1：取巧做法，見lulu-- 方法2：row_number/子查詢方法 select sum(total) as 'top20%貢獻額度' from(select userid,sum(price) as total,row_number()over(order by sum(price) desc) as 排名from order_info o where ispaid = '已支付'group by userid) t where 排名 < (select count(1) from order_info where ispaid = '已支付' group by userid) * 0.2; -- 取巧做法：select count(userid)*0.2 得到 17000m-- row_number()/ 注意臨時表不能復用

總結(jié)

以上是生活随笔為你收集整理的如何用sql计算回购率、复购率指标的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。