數(shù)據(jù)表：
（由于習(xí)題的原數(shù)據(jù)量過(guò)大，節(jié)選頭部10條，查看基礎(chǔ)信息，會(huì)寫(xiě)sql語(yǔ)句即可）
userinfo：
orderinfo：
數(shù)據(jù)字典：
orderinfo 訂單詳情表
| orderid 訂單id
| userid 用戶id
| isPaid 是否支付
| price 付款價(jià)格
| paidTime 付款時(shí)間
userinfo 用戶信息表
| userid 用戶id
| sex 用戶性別
| birth 用戶出生日期
要求：
1、統(tǒng)計(jì)不同月份的下單人數(shù)
2、統(tǒng)計(jì)用戶三月份的回購(gòu)率和復(fù)購(gòu)率
3、統(tǒng)計(jì)男女用戶消費(fèi)頻次是否有差異
4、統(tǒng)計(jì)多次消費(fèi)的用戶，第一次和最后一次消費(fèi)間隔是多少天
5、統(tǒng)計(jì)不同年齡段，用戶的消費(fèi)金額是否有差異
6、統(tǒng)計(jì)消費(fèi)的二八法則，消費(fèi)的top20%用戶，貢獻(xiàn)了多少消費(fèi)額
基本信息了解：
1.用戶信息表通過(guò)userid(一)與訂單詳情表的userid(多)相連接
2.用戶信息表是信息表，訂單詳情表是流水表
參考答案：
1、統(tǒng)計(jì)不同月份的下單人數(shù)：
select year(paidTime),month(paidTime),count(distinct userid) as cons
from orderinfo
where isPaid ='已支付'
group by year(paidTime),month(paidTime)
2、統(tǒng)計(jì)用戶3月份的回購(gòu)率和復(fù)購(gòu)率
- 復(fù)購(gòu)率：當(dāng)月購(gòu)買(mǎi)了多次的用戶占當(dāng)月用戶的比例
3月的復(fù)購(gòu)率 =3月多次購(gòu)買(mǎi)的用戶數(shù)/3月購(gòu)買(mǎi)的總用戶數(shù) - 解題思路：
根據(jù)題意，數(shù)據(jù)表確定是訂單詳情表，然后考慮如下：
1.篩選3月購(gòu)買(mǎi)情況
select userid
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid
2.統(tǒng)計(jì)每個(gè)用戶在3月的購(gòu)買(mǎi)次數(shù)
select userid,count(userid) as cons
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid
3.用case when判斷并篩選3月多次購(gòu)買(mǎi)客戶，并統(tǒng)計(jì)復(fù)購(gòu)率
select count(userid) as userid_cons, #總購(gòu)買(mǎi)人數(shù)
sum(case when cons>1 then 1 else 0 end)as fugou_cons #復(fù)購(gòu)人數(shù)
sum(case when cons>1 then 1 else 0 end)/count(userid) as fugou_rate #復(fù)購(gòu)率 =復(fù)購(gòu)人數(shù)/ 總購(gòu)買(mǎi)人數(shù)
from
(select userid,count(userid) as cons
from orderinfo
where isPaid="已支付"and month(paidTime)="03"
group by userid) as a; #子表要有命名否則執(zhí)行報(bào)錯(cuò)
- 回購(gòu)率：本月購(gòu)買(mǎi)用戶中有多少用戶下個(gè)月又再次購(gòu)買(mǎi)
3月份的回購(gòu)率 = 3月用戶中4月又再次購(gòu)買(mǎi)的人數(shù) / 3月的用戶總數(shù) - 解題思路：
根據(jù)題意，數(shù)據(jù)表確定是訂單詳情表，然后考慮如下：
1.統(tǒng)計(jì)每年每月的一個(gè)用戶消費(fèi)情況 （拿到3月、4月的每個(gè)用戶購(gòu)買(mǎi)次數(shù)）
select userid,date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01');
2.相鄰月份進(jìn)行關(guān)聯(lián)，能關(guān)聯(lián)上的用戶說(shuō)明就是回購(gòu)用戶
select *
from
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as a
left join
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) =a.month_dt
PS：注意date_sub用法，以及 left join on 之后 and 和where用法的區(qū)別！
3.統(tǒng)計(jì)每個(gè)月份的消費(fèi)人數(shù)情況即可得到回購(gòu)率
select
a.month_dt, #各月份
count(a.userid), #購(gòu)買(mǎi)人數(shù)
count(b.userid), #回購(gòu)人數(shù)（例如：3月購(gòu)買(mǎi)用戶中4月又再次購(gòu)買(mǎi)的用戶）
count(b.userid)/count(a.userid) as huigou_rate #回購(gòu)率=回購(gòu)人數(shù)/購(gòu)買(mǎi)人數(shù)
from
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as a
left join
(select userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(userid) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) as b
on a.userid = b.userid
and date_sub(b.month_dt,interval 1 month) =a.month_dt
3、統(tǒng)計(jì)男女用戶消費(fèi)頻次是否有差異
select c.sex,avg(cons) as avg_cons
from
(select a.userid,b.sex,count(1) as cons
from orderinfo as a
inner join
(select *
from userinfo
where sex<>'') as b
on a.userid =b.userid
group by a.userid,b.sex) as c
group by c.sex
4、統(tǒng)計(jì)多次消費(fèi)的用戶，第一次和最后一次消費(fèi)間隔是多少天
select userid,min(paidTime),max(paidTime),datediff(max(paidTime),min(paidTime)) as dt
from orderinfo
where isPaid ='已支付'
group by userid
having count(userid)>1;
5、統(tǒng)計(jì)不同年齡段，用戶的消費(fèi)金額是否有差異
1.計(jì)算每個(gè)用戶的年齡，并對(duì)年齡進(jìn)行分層:0-10:1,11-20:2,21-30:3...依次類(lèi)推間隔10為一類(lèi)
select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00'
2.關(guān)聯(lián)訂單信息，獲取不同年齡段的一個(gè)消費(fèi)頻次和消費(fèi)金額
select a.userid,b.age,count(1)as cons,sum(a.price) as price
from orderinfo as a
left join
(select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-0') as b
on a.userid = b.userid
group by b.age
3.再對(duì)年齡分層進(jìn)行聚合，得到不同年齡層的消費(fèi)情況
select age,avg(cons) ,avg(sums)
from
(select a.userid,b.age,count(1)as cons,sum(a.price) as sums
from orderinfo as a
inner join
(select userid,birth,now(),ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-0') as b
on a.userid = b.userid
group by b.age) as c
group by age
6、統(tǒng)計(jì)消費(fèi)的二八法則，消費(fèi)的top20%用戶，貢獻(xiàn)了多少消費(fèi)額
1.計(jì)算每個(gè)用戶的消費(fèi)金額，并進(jìn)行一個(gè)降序排序
select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid
2.統(tǒng)計(jì)一下一共有多少用戶，以及總消費(fèi)金額是多少
select count(userid),sum(sums) as total_price
from
(select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid) as a
3.取出前20%的用戶進(jìn)行金額統(tǒng)計(jì)
select count(userid),sum(sums) as total_price
from
(select userid,sum(price) as sums
from orderinfo
where ispaid='已支付'
group by userid
order by sums desc
limit 20%(用戶數(shù)) as a #用戶數(shù)從2中count(userid)中可以得出數(shù)據(jù)
