案例： 使用mysql 進(jìn)行數(shù)據(jù)分析
老師指路->http://www.reibang.com/u/1f32f227da5f
使用工具：MySQL、Navicat
一、準(zhǔn)備好訂單數(shù)據(jù)和用戶數(shù)據(jù)：
a剃斧、打開命令窗口或者 navicat 、workbench 等數(shù)據(jù)庫(kù)軟件
b境输、任意選擇一個(gè)數(shù)據(jù)庫(kù)，或者創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)并進(jìn)入數(shù)據(jù)庫(kù)
create database data charset utf8;
use data;
c颖系、執(zhí)行以下source 命令嗅剖，注意改成自己的路徑并且不能包含中文
#將.sql文件拖到命令行source后 即可顯示文件路徑
source C:\Users\data\Desktop\Mysql_case\orderinfo.sql
source C:\Users\data\Desktop\Mysql_case\userinfo.sql
注意：后面不需要加分號(hào)，上面的命令會(huì)把 **.sql 文件的語(yǔ)句都執(zhí)行一遍
desc orderinfo;--顯示表結(jié)構(gòu)
select * from orderinfo limit 10; #顯示10條記錄
select count(*) from orderinfo;#顯示記錄條數(shù)
二嘁扼、題目要求：
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)額
涉及表：
orderinfo 訂單詳情表
| orderid 訂單id
| userid 用戶id
| isPaid 是否支付
| price 付款價(jià)格
| paidTime 付款時(shí)間
userinfo 用戶信息表
| userid 用戶id
| sex 用戶性別
| birth 用戶出生日期
1栅哀、統(tǒng)計(jì)不同月份的下單人數(shù)
觀察數(shù)據(jù)，過濾未支付數(shù)據(jù),去重
不同月份下單人數(shù)
select year(paidTime)as year_ ,
month(paidTime) as month_ ,
count(distinct userid) as cons
from orderinfo
where isPaid='已支付'
group by year(paidTime),month(paidTime);
2称龙、統(tǒng)計(jì)用戶三月份的回購(gòu)率和復(fù)購(gòu)率
三月份交易量
select count(orderid)as all_order from orderinfo
where month(paidTime)=3;
三月份下單總?cè)藬?shù)
select
count(distinct userid) as cons
from orderinfo
where month(paidTime)=3;
復(fù)購(gòu)率：當(dāng)月購(gòu)買了多次用戶占當(dāng)月用戶比例
回購(gòu)率：上月購(gòu)買用戶中有多少用戶本月又再次購(gòu)買
(回購(gòu)率：本月購(gòu)買用戶中有多少用戶下個(gè)月又再次購(gòu)買)
復(fù)購(gòu)率：
a留拾、先篩選出3月份消費(fèi)情況
select
*
from orderinfo
where isPaid='已支付' and month(paidTime)=3;
b、統(tǒng)計(jì)每個(gè)用戶在3月份消費(fèi)了多少次
select
userid, count(1) as cons
from orderinfo
where isPaid='已支付'
and month(paidTime)=3
group by userid;
c鲫尊、對(duì)購(gòu)買次數(shù)進(jìn)行判斷痴柔，統(tǒng)計(jì)出消費(fèi)多次的用戶數(shù)（大于1次）
對(duì)用戶購(gòu)買次數(shù)進(jìn)行判斷，并sum求和疫向，如果購(gòu)買次數(shù)大于1咳蔚，用戶數(shù)加1
復(fù)購(gòu)率：當(dāng)月購(gòu)買了多次用戶占當(dāng)月用戶比例
select
count(1) as userid_cons,
sum(if (cons>1,1,0)) as fugou_cons,
sum(if (cons>1,1,0))/count(1) as fugou_rate
from(select
userid,
count(1) as cons
from orderinfo
where isPaid='已支付'
and month(paidTime)=3
group by userid
) a;
回購(gòu)率：本月購(gòu)買用戶中有多少用戶下個(gè)月又再次購(gòu)買
計(jì)算所有月份回購(gòu)率
舉例：三月份的回購(gòu)率=3月用戶中4月又再次購(gòu)買的人數(shù)/3月用戶總數(shù)
a、統(tǒng)計(jì)每年每月的用戶消費(fèi)情況
#分組：每個(gè)月 有哪些用戶ID
select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid='已支付'
group by userid,date_format(paidTime,'%Y-%m-01');
b搔驼、相鄰月份 進(jìn)行關(guān)聯(lián)谈火，能關(guān)聯(lián)上的用戶為回購(gòu)
自己與自己關(guān)聯(lián)，用戶對(duì)應(yīng)舌涨，月份對(duì)應(yīng)
此處使用left join 糯耍，保留用戶總數(shù)；
inner join 會(huì)刪掉關(guān)聯(lián)不上的用戶囊嘉，三月用戶總數(shù)會(huì)減少
select
*
from(select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid='已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
) a
left join (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid='已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
) b on a.userid=b.userid
and date_sub(b.month_dt,interval 1 month)=a.month_dt;
c温技、用統(tǒng)計(jì)出四月份有關(guān)聯(lián)的 用戶數(shù)據(jù)量 除以 三月份用戶數(shù)據(jù)量
count(a.userid)>count(b.userid) 4月份有null情況
select
a.month_dt,
count(a.userid),
count(b.userid),
count(b.userid)/ count(a.userid) as '回購(gòu)率'
from(select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid='已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
) a
left join (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid='已支付'
group by userid,date_format(paidTime,'%Y-%m-01')
) b on a.userid=b.userid
and date_sub(b.month_dt,interval 1 month)=a.month_dt
group by a.month_dt;
3、統(tǒng)計(jì)男女用戶消費(fèi)頻次是否有差異
獲取用戶 性別 消費(fèi)次數(shù) 男生用戶消費(fèi)總次數(shù)/男生人數(shù)
a哗伯、統(tǒng)計(jì)每個(gè)用戶消費(fèi)次數(shù)荒揣、帶性別,篩選出性別不為空的用戶
select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex!='') b
on a.userid=b.userid
group by a.userid,sex ;
b、對(duì)性別做一個(gè)消費(fèi)次數(shù)平均計(jì)算
select
sex,
avg(cons) as avg_cons
from(select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex!='') b
on a.userid=b.userid
group by a.userid,sex
) c
group by sex;
4焊刹、統(tǒng)計(jì)多次消費(fèi)的用戶，第一次和最后一次消費(fèi)間隔是多少天
a恳蹲、取出多次消費(fèi)用戶
select
userid
from orderinfo
where isPaid='已支付'
group by userid
having count(1)>1;
b虐块、取出第一次與第二次購(gòu)物時(shí)間
計(jì)算時(shí)間差
select
userid,
min(paidTime),
max(paidTime),
datediff(max(paidTime),min(paidTime)) as jiange
from orderinfo
where isPaid='已支付'
group by userid
having count(1)>1;
5、統(tǒng)計(jì)不同年齡段嘉蕾，用戶的消費(fèi)金額是否有差異
a贺奠、計(jì)算每個(gè)用戶年齡，并對(duì)用戶年齡進(jìn)行分層
0-10:1 11-20:2 21-30:3 31-40 41-50 10歲為一層
ceil 向上取整
select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth> '1900-01-01';
b错忱、關(guān)聯(lián)訂單信息儡率，獲取不同年齡段的消費(fèi)頻次和消費(fèi)數(shù)據(jù)
select
a.userid,
age,
count(1) as cons ,
sum(price) as prices
from orderinfo a
inner join (
select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth> '1900-01-01'
) b
on a.userid=b.userid
group by b.userid,b.age;
c挂据、對(duì)不同年齡層 進(jìn)行聚合 最終得到不同年齡層消費(fèi)情況
select
age,
avg(cons),
avg(prices)
from (
select
a.userid,
age,
count(1) as cons ,
sum(price) as prices
from orderinfo a
inner join (
select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth> '1900-01-01'
) b
on a.userid=b.userid
group by b.userid,b.age
) c
group by age ;
6、統(tǒng)計(jì)消費(fèi)的二八法則儿普，消費(fèi)的top20%用戶崎逃，貢獻(xiàn)了多少消費(fèi)額
用戶消費(fèi)占比，20%用戶 消費(fèi)貢獻(xiàn)占80%
a眉孩、統(tǒng)計(jì)每個(gè)用戶消費(fèi)金額个绍，并進(jìn)行降序排序
select
userid,
sum(price) as total_prices
from orderinfo a
where isPaid='已支付'
group by userid
order by total_prices desc ;
b、統(tǒng)計(jì)一共有多少用戶浪汪，以及用戶總消費(fèi)金額
select
count(1) as cons,
sum(total_prices) as all_prices
from ( select
userid,
sum(price) as total_prices
from orderinfo a
where isPaid='已支付'
group by userid ) b;
c巴柿、取出消費(fèi)前20%用戶，進(jìn)行金額統(tǒng)計(jì)
8萬(wàn)用戶去20%-17000名
select
count(1) as cons,
sum(total_prices) as all_prices
from (
select
userid,
sum(price) as total_prices
from orderinfo a
where isPaid='已支付'
group by userid
order by total_prices desc
limit 17000) b;
select (2.7/3.1);
