數(shù)據(jù)來源于某網(wǎng)站的銷售信息統(tǒng)計(jì)，主要由訂單信息及用戶信息兩個(gè)部分組成，分析過程中可通過對兩部分的單獨(dú)分析或聯(lián)結(jié)分析來進(jìn)行相關(guān)指標(biāo)的綜合分析：
1、訂單數(shù)據(jù)：鏈接：https://pan.baidu.com/s/1HLbzfXWTUgREFLyu3Mwe0Q 提取碼：529n
2、用戶數(shù)據(jù)：鏈接：https://pan.baidu.com/s/1lgncrR-DviysaD3WhHDoIg 提取碼：6vmq
主要內(nèi)容：
![]()
主要內(nèi)容.png
1、數(shù)據(jù)導(dǎo)入
1）在 MySQL Workbench 的操作頁面上創(chuàng)建一個(gè)新的數(shù)據(jù)庫schema。
2）創(chuàng)建兩個(gè)新表order_info和user_info,并設(shè)置好相應(yīng)的主鍵key和所有列名columns。
3）通過MySQL的Command Line Client將數(shù)據(jù)導(dǎo)入到對應(yīng)的表中。
導(dǎo)入數(shù)據(jù)時(shí)，出現(xiàn)報(bào)錯(cuò)的各點(diǎn)總結(jié)：
a、語句出錯(cuò)：把文件路徑導(dǎo)入到 cmd 黑窗口得到的路徑斜桿是‘\’ ,要修改為‘/’，路徑不能帶有中文。
b、要有fields terminated by ',' 因?yàn)閏sv 文件是以逗號為分割符的。
4）數(shù)據(jù)導(dǎo)入完成
2、數(shù)據(jù)分析：
1）統(tǒng)計(jì)不同月份的下單人數(shù)
思路：先篩選出“已支付”，再對月份進(jìn)行分組，最后對不同月份的不同下單用戶的數(shù)量進(jìn)行統(tǒng)計(jì)，注意對用戶的去重。
SELECT month(paidtime) as 月份, count(distinct userId) as 用戶數(shù) FROM czy.order_info
where isPaid = '已支付'
group by month(paidtime) ;
2）統(tǒng)計(jì)用戶三月份的復(fù)購率和回購率
2.1）復(fù)購率：自然月內(nèi)，購買多次的用戶占該月總用戶數(shù)的比例
思路：先篩選出‘三月份’及‘已支付’的數(shù)據(jù)，再對用戶Id進(jìn)行分組，求得用戶id及其對應(yīng)的購買次數(shù)，最后通過子查詢的方式，嵌套一個(gè)select語句，令購買次數(shù) >1的用戶的購買次數(shù)變成1并相加求和以便求出購買多次的用戶數(shù)，而購買次數(shù)不大于1的購買次數(shù)變成null，不參與計(jì)數(shù)，最后求得購買多次的用戶數(shù)及當(dāng)月有消費(fèi)的用戶總數(shù)。
●在三月份用戶總數(shù)已提前計(jì)算得知時(shí)：
select concat((count(userId)/54799)*100,'%') as 三月份復(fù)購率 from
(select month(paidtime), userId ,count(userId) as uc from czy.order_info
where month(paidtime) = 3
and isPaid = '已支付'
group by userId having count(userId) > 1) as fu;
●在三月份用戶總數(shù)未提前計(jì)算得知時(shí)（可通過求得該月用戶總數(shù)及復(fù)購的用戶數(shù)來求得）：
select count(userId) as 三月份用戶總數(shù),count(if(uc>1,1,null)) as 三月份復(fù)購用戶數(shù) from
(select userId ,count(userId) as uc from czy.order_info
where month(paidtime) = 3 and isPaid = '已支付'
group by userId) as fg;
●拓展思維：當(dāng)需要考慮更多月份的更實(shí)際、更復(fù)雜繁多的計(jì)算時(shí)（此處以三個(gè)月來舉例）：
select md as 月份, count(userId) as 各月份用戶總數(shù),count(if(uc>1,1,null)) as 各月份復(fù)購用戶數(shù) from
(select userId, month(paidtime) as md, count(userId) as uc from czy.order_info
where isPaid = '已支付'
group by month(paidtime),userId) as fg
group by md ;
2.2）回購率：該時(shí)期內(nèi)有消費(fèi)的用戶在連續(xù)的下一個(gè)時(shí)期內(nèi)仍舊有消費(fèi)的用戶的總數(shù)占該時(shí)期的消費(fèi)的用戶總數(shù)的比率。
●只考慮三月份的回購率時(shí)：
思路：先篩選出‘三月份’‘已支付’的‘用戶id’，再篩選出‘四月份’‘已支付’的用戶中與三月份已支付的用戶相同的‘用戶id',對這些相同的’用戶id‘進(jìn)行去重、計(jì)數(shù)，并除以三月份的用戶總數(shù)即可得出。
select concat((count(distinct userId)/54799)*100,'%') as 三月份回購率 from czy.order_info
where month(paidtime) = 4
and userId in (select distinct userId from czy.order_info
where isPaid = '已支付' and month(paidtime) = 3
group by userId);
●拓展思維：和復(fù)購率同理，當(dāng)考慮更多月份時(shí)：
思路：先篩選出各個(gè)月份’已支付‘的’用戶id‘，此處需將各個(gè)月份中用戶購買的時(shí)間點(diǎn)統(tǒng)一成同一個(gè)月份的第一天，對各個(gè)月份和‘用戶id‘進(jìn)行分組，以便操作，再通過left join的方式，讓其’用戶id'一一對應(yīng)，但前后的月份相差一個(gè)月，最后計(jì)算出當(dāng)月的用戶數(shù) 及回購的用戶數(shù)。
select t1.m as 月份,count(t1.m) as 用戶總數(shù),count(t2.m) as 回購用戶數(shù),
concat((count(t2.m)/count(t1.m))*100,'%') as 回購率 from
(select userId , date_format(paidtime,'%Y-%m-01') as m from czy.order_info
where isPaid = '已支付'
group by userId , date_format(paidtime,'%Y-%m-01')) as t1
left join
(select userId , date_format(paidtime,'%Y-%m-01') as m from czy.order_info
where isPaid = '已支付'
group by userId , date_format(paidtime,'%Y-%m-01')) as t2
on t1.userId = t2.userId and t1.m = date_sub(t2.m,interval 1 month)
group by t1.m;
3）統(tǒng)計(jì)男女用戶的消費(fèi)頻次是否有差異
思路：將order_info表與user_info表進(jìn)行內(nèi)部聯(lián)結(jié)inner join，對’用戶id‘和對應(yīng)的’性別sex‘進(jìn)行分組，并統(tǒng)計(jì)每個(gè)用戶的消費(fèi)次數(shù)，最后再對性別進(jìn)行分組，求出’男‘&’女‘對應(yīng)的平均消費(fèi)次數(shù)
select sex as 性別,avg(ct) as 平均消費(fèi)次數(shù) from
(select userId,sex,count(userId) as ct from czy.order_info as o
inner join
(SELECT * FROM czy.userinfo
where sex <> '') as s
on o.userId = s.useId
where isPaid = '已支付'
group by userId,sex) as b
group by sex;
4）統(tǒng)計(jì)多次消費(fèi)，第一次消費(fèi)和最后一次消費(fèi)的間隔是多少；
思路：先篩選出‘已支付’的‘用戶id’，并對用戶進(jìn)行分組group by,再挑選出消費(fèi)次數(shù)>1的用戶id，最后得出第一次消費(fèi)和最后一次消費(fèi)的時(shí)間點(diǎn)及時(shí)間差。
select userId as 用戶,max(paidtime) as 最后一次消費(fèi),min(paidtime) as 第一次消費(fèi),datediff(max(paidtime),min(paidtime)) as 兩次消費(fèi)的時(shí)間差 from czy.order_info
where isPaid = '已支付'
group by userId having count(userId) > 1;
●平均間隔
select avg(jg) as 平均間隔 from
(select userId,max(paidtime),min(paidtime),datediff(max(paidtime),min(paidtime)) as jg from czy.order_info
where isPaid = '已支付'
group by userId having count(userId) > 1) j
5）統(tǒng)計(jì)不同年齡段，用戶的消費(fèi)金額是否有差異；
思路：先對數(shù)據(jù)進(jìn)行篩選，再以10年為時(shí)間間隔進(jìn)行劃分各個(gè)年齡段，最后再挑出各用戶、年齡段及各年齡段的人數(shù)，最后再進(jìn)行統(tǒng)計(jì)分析。
select age, avg(sp) from
(select u.userId, age,sum(price) as sp from czy.order_info as u
inner join
(select useId, ceil((year(now())-year(birth))/10) as age from czy.userinfo
where birth > '1901-00-00') as ad
on u.userId = ad.useId
where isPaid = '已支付'
group by u.userId, age) as n
group by age;
6）統(tǒng)計(jì)消費(fèi)的二八法則：消費(fèi)top20%的用戶貢獻(xiàn)了多少額度；
思路：先統(tǒng)計(jì)全部的消費(fèi)用戶數(shù)，計(jì)算出其20%的用戶數(shù)大致是多少
select (count(distinct userId) * 0.2)as 百分之20的用戶數(shù) from czy.order_info
where isPaid = '已支付'
思路：根據(jù)上一步得到的20%的用戶數(shù)，計(jì)算用戶的消費(fèi)總額并進(jìn)行排序
select userId, sum(price) as total from czy.order_info
where isPaid = '已支付'
group by userId
order by total desc
limit 17129;
思路：根據(jù)上步得到的結(jié)果，算出錢20%的用戶的消費(fèi)總額
select sum(total) as top20的消費(fèi)總額 from
(select userId, sum(price) as total from czy.order_info
where isPaid = '已支付'
group by userId
order by total desc
limit 17129) u ;
思路：最后計(jì)算全部的已支付用戶的消費(fèi)總額
select sum(price) as 全部消費(fèi)總額 from czy.order_info
where isPaid = '已支付'
3、分析總結(jié)
1、各月份的下單人數(shù)分別為54799、43967、6；
2、三月份的復(fù)購率30.8692%，回購率23.9402%；
3、男女的平均消費(fèi)頻次為1.8035、1.7827，可見男女在消費(fèi)頻次上并不會(huì)存在較大的差異；
4、由消費(fèi)間隔的統(tǒng)計(jì)可知，多次消費(fèi)的用戶的平均消費(fèi)間隔為15天左右；
5、由‘不同年齡段的平均消費(fèi)金額’結(jié)果可知，青年及中年的消費(fèi)力度較高，而少年及中老年可能因?yàn)榻?jīng)濟(jì)等相關(guān)原因而導(dǎo)致消費(fèi)力度相對較低；
6、由消費(fèi)的二八法則統(tǒng)計(jì)得知：消費(fèi)top20%的用戶貢獻(xiàn)了近272202457元的消費(fèi)額度，占到了總消費(fèi)額度的85.46%，是值得該公司重點(diǎn)維護(hù)的主要客戶。
