一、数据加载
MYSQL加载数据有两种方式,一种是通过MYSQL WORKBENCH加载,适合加载少量数据;另一种是通过MYSQL command line client加载,适合加载数据较多的文件,节省时间。
1.MYSQL WORKBENCH加载
(1)启动workbench(2)在左边栏找到Tables,右键creat table,填写数据格式,表头信息等,点击右下按钮apply(3)鼠标停在新建的table标签上会显示三个图标,选择第三个,进入后在中间偏右位置选择带加号的图标,添加数据(4)检查数据格式是否正确,删掉重复的表格数据抬头。
2.MYSQL command line client加载
(1)启动workbench(2)在左边栏找到Tables,右键creat table,填写数据格式,表头信息等,点击右下按钮apply(3)启动client,输入MYSQL登录密码(4)代码写路径加载文件 ,如下图
其中,固定模板为
mysql> load data local infile '此处为文件路径,注意斜杠的方向,否则报错' into table table的具体名称.数据文件的具体名称
-> fields terminated ' , ' ;
其中注意末尾要加符号“;”
(5)点开workbench查看数据。
(6)说明:我选择加载的数据文件格式为utf-utf_general.ci,而文件为Excel文件,因此现将其另存为.csv格式的,然后用txt文本打开.csv文件,最后另存为utf-8格式的.txt文件。
二、数据分析
1.统计不同月份的下单人数
一般先过滤 where.........再分组group by.......
count()函数计数的,加入不要重复的内部嵌套一个distinct,count(distinct userId)
date_format( )函数,注意内部格式,时间格式 ' %Y-%m-%d ' ,记得加引号
2.统计用户在3月份的回购率和复购率
2.1 复购率
即买了一次以后再买,同一个userId在3月份的购买次数大于1次,
先统计分组在3月份购买过的,
where isPaid = '已支付' and month(paidTime) = 3
group by userId
再统计购买次数大于1的
SELECT count(userId)>1,count(if count(userId) > 1,1,null) from(
........嵌套一个select
) ct
2.2 回购率
回购率即3月份买了,4月份再次购买。
如上图计算会比较麻烦,而且对于连续性,时间差距较大的数据计算不方便。
采用多表关联嵌套,left join .... on...t1.userId = t2.userId(表头连接)
先将3月份数据分组,再关联4月份数据,相同格式(统一为'%Y-%m-01',尤其将日期天统一为-01,这样方便作差),月份作差,即得到回购过的相关userId。left join 两个相同的列时,列的排列顺序是不一样的,一个按照重复排序另一个按照大小排序
3.统计男女用户的消费频次是否有差异
统计男女消费差异,首先要将用户表和消费数据表关联起来。
select .... from (
left join (
select * from...
where sex <> ' ‘(#去除空值)) as t
on o.userId = u.userId
group by o.userId)as t2(#从这个大表里再筛选男女的消费次数))
group by....(从这个大表里计算男女的平均消费)
#count(1)统计行数时包括null值,= count(主键),只扫描主键;
count(*)统计行数时包括null值,=count(非主键),扫描整个表格
4. 统计多次消费的用户第一次和最后一次的消费间隔
首先提取出消费次数大于1的userId,count(1)>1,然后选max(datetime)-min(datetime)
5.统计不同年龄段,用户的消费金额是否有差异
5.1 年龄换算 [ year(now()) - year(birth)],再除以10换算成年龄段,向上取整,确定年龄段。
5.2先统计不同年龄的消费数据,再统计不同年龄段的平均消费数据。
6 统计消费的二八法则,消费的top20%用户,贡献了多少额度
先算出消费者的消费金额,然后按照降序方法进行排序。选择特定函数直接确定前20%是哪些。