数据分为两个表，一个是订单表，一个是用户信息表
一、将.sql文件导入到数据库
- 连接到mysql主机
在cmd窗口或者Mysql命令行窗口，使用管理员权限打开，连接到mysql数据库
语法命令：mysql 【-h 主机名 -P端口号】 -u用户名 -p密码
如果连接到local host本地主机，则【】括号内的可省略 - 创建数据库
如果sql文件的内容已有数据库或者文件中有创建数据库的语句，则无需创建数据库；如果没有的话需要新建数据库
show databases;#查看数据库中的表
create database sale_data charset utf8; #建立数据库
use sale_data; #使用这个数据库
- 导入sql文件，语法：source sql文件路径，注意如果路径为复制路径，则需要将“\”替换为“/”
source C:/Users/Administrator/Documents/Navicat/MySQL/Servers/practise/orderinfo.sql
source C:/Users/Administrator/Documents/Navicat/MySQL/Servers/practise/userinfo.sql
注意：在navicat查询窗口运行以上两句source导入语句，执行报错，但是在cmd命令行窗口输入是可以导入的
数据表查看
-- 查看数据表
show databases; #查询数据库有哪些
use qing_sale_data; # 进入指定数据库
show tables; # 查看该数据库中有哪些表
desc orderinfo; #查看表结构
desc userinfo;
-- 题目预览
-- 1、统计不同月份的下单人数
-- 2、统计用户三月份的回购率和复购率
-- 3、统计男女用户消费频次是否有差异
-- 4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
-- 5、统计不同年龄段，用户的消费金额是否有差异
-- 6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
开始做题：
-- 1、统计不同月份的下单人数
-- 分析点：是否支付要筛选已支付部分，月份的分组要按照年月分组，一个用户可能购买多笔，用户ID要去重
-- 从如下查询中可知，当isPaid为未支付时，时间都是'0000-00-00'，但是为了避免异常，还是筛选下 00:00:00'，所以过滤时可以通过是否支付来筛选
select year(paidTime) 'year', month(paidTime) 'month', count(distinct userid) 'user_count'
from orderinfo
where isPaid = '已支付' and paidTime <> '0000-00-00 00:00:00'
group by year(paidTime), month(paidTime);
-- 2、统计用户三月份的回购率和复购率
-- 回购率：3月份有多少用户购买了后，在下个月又购买了的占3月总购买人数比例
-- 复购率：当月购买了多次的用户占当月用户的比例
-- 回购率
-- 注意点：不要用原数据表进行join，非常耗内存，使用汇总后的数据再join速度会快些
-- 思路: 先统计出来每个月用户的购买次数,再使用date_sub函数将月份关联,统计每月的回购率
SELECT
a.date,
sum( IF ( b.cons > 0, 1, 0 ) ) 'huigou',
sum( IF ( b.cons > 0, 1, 0 ) ) / count( a.userid )
FROM
(
SELECT
date_format( paidTime, '%Y-%m-01' ) 'date',
userid,
count( 1 ) 'cons'
FROM
orderinfo
WHERE
isPaid = '已支付'
AND paidTime <> '0000-00-00 00:00:00'
GROUP BY
userid,
date_format( paidTime, '%Y-%m-01' )
) a
LEFT JOIN (
SELECT
date_format( paidTime, '%Y-%m-01' ) 'date',
userid,
count( 1 ) 'cons'
FROM
orderinfo
WHERE
isPaid = '已支付'
AND paidTime <> '0000-00-00 00:00:00'
GROUP BY
userid,
date_format( paidTime, '%Y-%m-01' )
) b ON a.userid = b.userid
AND b.date = date_add( a.date, INTERVAL 1 MONTH )
GROUP BY
a.date;
-- 复购率，思路：先求出每个用户3月的购买次数，计算复购的人数，复购人数除以总人数
SELECT
sum( IF ( a.cons > 1, 1, 0 ) ) 'fugou',
sum( IF ( a.cons > 1, 1, 0 ) ) / count( 1 ) 'fugou_rate'
FROM
( SELECT userid, count( 1 ) cons FROM orderinfo WHERE isPaid = '已支付' AND paidTime BETWEEN '2016-03-01 00:00:00' AND '2016-04-01 00:00:00' GROUP BY userid ) a;
-- 3、统计男女用户消费频次是否有差异
-- 思路:先计算出每个用户的购买次数, 再筛选出user表中有性别的数据,
-- 两个表内联后统计分组统计男女平均消费次数
-- 注意点:当用户未支付时,用户表中的性别为空,所以可以根据这个特性区筛选已经消费了的用户,不知道为啥用is not null筛选不出来了，所以用的<>''
SELECT
c.sex,
avg( c.cons ) 'freq'
FROM
(
SELECT
a.userid,
count( 1 ) 'cons',
sex
FROM
orderinfo a
INNER JOIN ( SELECT userid, sex FROM userinfo WHERE sex <> '' ) b ON a.userid = b.userid
GROUP BY
a.userid
) c
GROUP BY
c.sex;
-- 4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
-- 思路:By用户筛求出每个用户消费的最大时间和最小时间,再使用datediff求差,最后再筛选消费次数大于1的用户
SELECT
userid,
min( paidTime ) 'min_time',
max( paidTime ) 'max_time',
DATEDIFF( max( paidTime ), min( paidTime ) ) AS 'user_life'
FROM
orderinfo
WHERE
isPaid = '已支付'
AND paidTime <> '0000-00-00 00:00:00'
GROUP BY
userid
HAVING
count( 1 ) > 1;
-- 5、统计不同年龄段，用户的消费金额是否有差异
-- 思路：①先对用户的年龄进行分层，②再对订单表里用户的消费金额求和，③两个表内连，④再分层统计用户的消费金额
注意点：除了使用case when函数外，if和年龄直接除以10也是可以对年龄进行分层的
SELECT
t.label,
sum( t.money ) 't_money'
FROM
(
SELECT
a.userid,
a.birth,
a.sex,
b.money,
timestampdiff( YEAR, birth, now( ) ) 'age',
CASE
WHEN timestampdiff( YEAR, birth, now( ) ) < 11 THEN
'1-10'
WHEN timestampdiff( YEAR, birth, now( ) ) <= 25 AND timestampdiff( YEAR, birth, now( ) ) >= 11 THEN
'10-24'
WHEN timestampdiff( YEAR, birth, now( ) ) <= 40 AND timestampdiff( YEAR, birth, now( ) ) > 25 THEN
'25-40' ELSE '>40'
END AS 'label'
FROM
userinfo a
INNER JOIN ( SELECT userid, sum( price ) 'money' FROM orderinfo GROUP BY userid ) b ON a.userid = b.userid
WHERE
a.birth > '1900-01-01'
) t
GROUP BY
t.label;
-- 6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额(20%的用户贡献了80%的消费额)
-- 思路：首先统计出每位用户的消费额，按消费额降序排序，再统计总共多少人，算出20%的用户贡献了多少消费额
select sum(a.money)/318503081.4470062
from
(
select
userid,
sum(price) 'money'
from orderinfo
where isPaid = '已支付'
group by userid
order by sum(price) desc
limit 17130
) a;
计算总人数和总销售额如下：
select
count(distinct userid)*0.2 'p',
sum(price) 'm'
from orderinfo
where isPaid='已支付';
