所需数据：ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV
数据链接:https://pan.baidu.com/s/11ZtjKnv5-nwyf6cMyk_3JQ
提取码:yu63
目录
1.数据的导入
1.1创建表结构
1.1.1创建订单表
2.对数据进行分析
2.1 统计每个月的下单人数
2.2 统计复购率和回购率
2.3 统计消费者性别与消费频次的关系
2.4 3、4月份每日下单人数,每日下单数
2.5 统计消费者性别与平均消费金额的关系
2.6 统计不同年龄段的消费金额的占比
2.7 统计每个时间段的下单人数
2.8 统计消费的二八法则，消费的top20%用户消费额占总消费额的占比
1.导入csv数据
1.1创建表结构
1.1.1创建订单表
CREATE TABLE ORDERINFO
(ORDERID VARCHAR(10) NULL,#订单ID，主键
USERID VARCHAR(10) NULL,#用户ID，可以和用户表进行关联
ISPAID VARCHAR(10) NULL,#是否支付
PRINCE VARCHAR(10) NULL,#订单价格
PAIDTIME DATETIME NULL #订单支付时间)
1.1.2创建用户信息表
CREATE TABLE USER_INFO_ULF
(USERID varchar(10) NULL,#用户ID，主键
SEX varchar(10) NULL,#性别
BIRTH DATE NULL #出生日期)
1.1.3为表创建索引，提高查询速度
#为user_info_utf创建主键索引
ALTER TABLE `user_info_utf` MODIFY COLUMN userid VARCHAR(10) PRIMARY KEY；
#为表orderinfo创建主键索引
ALTER TABLE `orderinfo` MODIFY COLUMN `ORDERID` VARCHAR(10) PRIMARY KEY；
#为表orderinfo创建外键索引
ALTER TABLE orderinfo ADD FOREIGN KEY(userid) REFERENCES user_info_utf(userid)
1.2导入csv数据
电脑安装了sqlyog，导入使用本地加载的csv数据，速度很快，一两秒就导入成功了
2.数据处理
#不对未支付订单进行分析，为提高查询效率，删除未支付订单
DELETE
FROM `orderinfo`
WHERE ispaid='未支付'
#5月份数据只有7条，难以进行分析，故删除5月份数据
DELETE FROM `orderinfo` WHERE paidtime>='2016-05-01'
2.1统计每个月的下单人数
SELECT SUBSTRING(paidtime,6,2) '下单月份',COUNT(DISTINCT(`USERID`)) AS '月下单总人数'
FROM`orderinfo`
GROUP BY SUBSTRING(paidtime,1,7)
2.2统计复购率和回购率
2.2.1 3月份复购率
SELECT COUNT(*) AS '下单人数', COUNT(IF(pt>1,1,NULL)) AS '重复下单人数', paidmonth AS '月份', (COUNT(IF(pt>1,1,NULL))/COUNT(*)) AS '复购率'
FROM (SELECT userid,COUNT(*) AS pt,MONTH(`PAIDTIME`) AS paidmonthFROM orderinfoGROUP BY userid,paidmonth) AS uc
GROUP BY paidmonth
四月份复购率下降：四月份的下单人数与复购率均下滑，从复购率来看，不是很高，可将提升的方向主要放在新用户的获取。
2.2.1 3月份回购率
#创建3月份下单用户id与下单次数的视图
CREATE VIEW 3_userid
AS
SELECT COUNT(*) AS co1,userid
FROM `orderinfo`
WHERE SUBSTRING(paidtime,6,2)='03'
GROUP BY userid;
#创建4月份下单用户id与下单次数的视图
CREATE VIEW 4_userid AS
SELECT COUNT(*) AS co2,userid
FROM `orderinfo`
WHERE SUBSTRING(paidtime,6,2)='04'
GROUP BY userid;
#.四月份复购订单数
SELECT (SELECT COUNT(co2) AS co3 FROM 4_userid)AS '四月份的订单数',COUNT(*) AS '四月份的用户复购订单数',(COUNT(*)/(SELECT COUNT(co2) AS co3 FROM 4_userid)) AS '复购率'
FROM 3_userid
INNER JOIN 4_userid
ON 3_userid.userid=4_userid.userid
2.3 3、4月份每日下单人数,每日下单数
#3.4月份每日下单人数,每日下单数
SELECT SUBSTRING(paidtime,6,5) AS '日期',COUNT(DISTINCT(userid)) AS '每日下单人数',COUNT(*) AS '每日下单人数'
FROM orderinfo
GROUP BY SUBSTRING(paidtime,6,5);
#导出sql数据，用excel透视表制作
周日至周四消费者购买数较活跃，周五周六较低迷：周五，周六订单数明显呈下降势，周六下单人数为一周中最少。2016年第15周的星期一为清明节，许多人外出，订单数明显减少。
2.4统计消费者性别与消费频次的关系
思路：建立临时表csu统计每个用户的购买次数及其性别，用avg和group by分组计算不同性别的消费频次
SELECT AVG(csu.co2),sex
FROM (SELECT COUNT(o.userid) AS co2,sex,o.userid
FROM `orderinfo` AS o,`user_info_utf` AS u
WHERE o.userid=u.userid AND sex!=' '
GROUP BY o.userid,sex) AS csu
GROUP BY sex
小结：男性与女性平均购买次数差别不大，但三四月份下单总人数未8万多，而下单用户没有性别信息的用户有5万多，所以计算出来的不同性别的平均购买次数参考意义不大
2.5统计消费者性别与平均消费金额的关系
思路：建立临时表csc统计每个用户的购买次数及其性别，用avg和group by分组计算不同性别的平均消费金额
小结：从数据中可看出男性与女性平均购买金额差别不大，但三四月份下单总人数未8万多，而下单用户没有性别信息的用户有5万多，所以计算出来的不同性别的平均购买金额参考意义不大
2.6统计不同年龄段的消费金额的占比
SELECT COUNT(*),ROUND(SUM(`PRINCE`),2) AS '总消费额',ROUND(AVG(`PRINCE`),2) AS '平均消费额',
CASE
WHEN aget.age BETWEEN 0 AND 9 THEN '0-9岁'
WHEN aget.age BETWEEN 10 AND 19 THEN '10-19岁'
WHEN aget.age BETWEEN 20 AND 29 THEN '20-29岁'
WHEN aget.age BETWEEN 30 AND 39 THEN '30-39岁'
WHEN aget.age BETWEEN 40 AND 49 THEN '40-49岁'
WHEN aget.age BETWEEN 50 AND 59 THEN '50-59岁'
WHEN aget.age BETWEEN 60 AND 69 THEN '60-69岁'
ELSE '70岁及以上'
END 'age_range'
FROM (SELECT o.`PRINCE`,IF(MONTH(NOW())<MONTH(birth) AND DATE(NOW())<DATE(birth),
ROUND(YEAR(NOW())-YEAR(birth)),ROUND(YEAR(NOW())-YEAR(birth))-1) AS age,u.userid
FROM user_info_utf AS u
INNER JOIN orderinfo AS o
ON u.`userid`=o.`USERID`
WHERE birth>1900-00-00) AS aget #年龄表
GROUP BY age_range
不同年龄段消费总额占比
不同年龄段人数占比
30-39年龄段的消费者为消费主力:30-39岁年龄段的消费人数占比为44.4，消费总额占比为45.3，是主要消费人群，需重点关注。20-29岁与40-49岁的消费总额占比也比较可观，
2.7 统计每个时间段的下单人数
SELECT COUNT(*),SUBSTRING(TIME(paidtime),1,2) AS '时间段'
FROM `orderinfo`
GROUP BY SUBSTRING(TIME(paidtime),1,2)
早上11点和晚上10点是下单高峰：下单时间主要为上午10点到23点，上午11-12点与21-23点之间消费者较为活跃，19点左右下单人数明显下降，这个时间段是下班高峰。
2.8 统计消费的二八法则，消费的top20%用户，贡献了多少额度
SELECT SUM(sprince),SUM(sprince)/(SELECT SUM(prince) FROM `orderinfo`) AS 'top20%的用户消费额占总消费额的占比'
FROM (SELECT *,(@RowNum := @RowNum + 1) AS RowNum
FROM (SELECT userid,SUM(prince) AS sprince
FROM `orderinfo`
GROUP BY userid
ORDER BY sprince DESC) AS prince_r,(SELECT @RowNum := 0) AS myRows
WHERE @RowNum+1<(SELECT COUNT(DISTINCT(userid))*0.2 AS 'top20%的用户数'
FROM `orderinfo`
ORDER BY prince)) AS prince_range
消费的top20%用户贡献了85%的总消费额：从数据上可看出top20%用户对消费总额上的贡献很大，应重点关注，可为其提供更高品质，个性化的服务。
