案例来源： 知乎《使用 MySQL 对淘宝用户行为进行分析》 by StarLau
总结：
MySQL编码问题需要重新学习，配置环境也要重新学习
原作者对数据类型没有合理设置，MySQL数据类型的选择对检索性能影响较大。
学习到的几点：
1.日期和时间格式拆分
2.从日转为周date_format()函数，同时，group by 可以使用select 中的字段（别名）（group by 后的字段一定要是select中的字段，不然你怎么分组显示呢？）
3.描述性分析
4.用MySQL做RFM模型的语法
5.case when 的用法
没有涉及的知识点：表的关联
1.背景
数据为淘宝APP 2014年11月-2014年12月的用户行为数据
包含字段：
数据已做脱敏处理，地理位置大多为NULL，且加密处理过无法分析。
2数据加载
下载的数据是CSV格式，102万条记录，500M，初步尝试导入出现了几个问题。
问题1：数据编码不符
文件原始编码为ASCII，需要转为utf8，常见方法为用记事本的方法打开CSV文件，另存的时候修改保存编码。
另存为-修改编码方式
但第二问题就出现了：文件太大，电脑打不开。使用分块读取，并直接保存为utf8格式，利用的Python中的pandas，代码如下
import pandas as pd
f = open('路径')
reader = pd.read_csv(f, sep=',', iterator=True)
loop =True
chunkSize =100000
chunks = []
while loop:
try:
chunk = reader.get_chunk(chunkSize)
chunks.append(chunk)
exceptStopIteration:
loop =False
print("Iteration is stopped.")
df = pd.concat(chunks, ignore_index=True)
df.to_csv('路径',encoding=utf8,ignore_index=false)
问题3：成功转为utf8格式的文件后，使用load导入MySQL时，数据量出错。
原文件只有102万条记录，导入数据库后，select count(*) 后发现返回了1200万条记录。查看前1000条记录，没有发现数据格式有问题，那就只可能是重复导入了。但导入代码是没有问题，不知道问题在哪。
load导入语言
load data infile '路径' into table user_behavior fields terminated by',' optionally enclosed by '"' lines terminated by '\r\n' ignore 1 lines
最后放弃全部导入，只读取了前10万条数据，导入MySQL，成功导入。
导入小结：
MySQL导入可以使用一些图形化界面的软件，比如Navicat。但作为新手，如果使用图形化软件，可能就不会遇到这些问题了，也就不能了解导入存在的问题。常见问题：编码，导入语句，路径书写（使用反斜杠）
3.数据一致化处理：
1.字段behavior_type 原值标签为1234，对应为pv(浏览量)、fav（收藏）、cart（加入购物车）、buy（支付）。为了便于分析，修改为对应行为。
UPDATE user SET behavior_type = REPLACE(behavior_type, 1, 'pv');
UPDATE user SET behavior_type = REPLACE(behavior_type, 2, 'fav');
UPDATE user SET behavior_type = REPLACE(behavior_type, 3, 'cart');
UPDATE user SET behavior_type = REPLACE(behavior_type, 4, 'buy');
2.将date-time字段拆分为date和time
-- 重命名 time 字段名称为 date ALTER TABLE user CHANGE time date VARCHAR(255);
-- 添加 time 列 ALTER TABLE user ADD time VARCHAR(20);
-- 复制 date 列到 time 列 UPDATE user SET time = date;
-- 在 date 列截取（年-月-日）信息UPDATE user SET date = REPLACE(date, date, SUBSTRING_INDEX(date,' ',1));
-- 在 time 列截取小时信息UPDATE user SET time = REPLACE(time, time, SUBSTRING_INDEX(time,' ',-1));
4.数据分析
1.整体用户访问情况
对应指标：pv（浏览量） 两种都可以计算出
①SELECT COUNT(behavior_type) AS '总访问量' FROM user GROUP BY behavior_type HAVING behavior_type='pv';
②select count（*） from user_behavior where behavior_type='pv'
2.日均访问量
对应指标：每日pv
①SELECT date,COUNT(behavior_type) AS '日均访问量' FROM user WHERE behavior_type='pv' GROUP BY date ORDER BY date;
3.访客数
对应指标：uv
①SELECT COUNT( DISTINCT user_id) AS '用户总数' FROM user;
错误：select count(user_id) from user_behavior group by user_id
想法：每个用户id都是独立的，那将id分组，然后统计有多少个分组也可以实现。问题出在上面的一段SQL返回的是明细，每个user_id的重复次数
②正确： select count(*) from (select count(user_id) from user_behavior group by user_id) as t
要点：使用了临时表，要注意必须为临时表指定名称，不然报错。
4.用户中有购买行为的人数
SELECT COUNT( DISTINCT user_id) AS '购买用户数量' FROM user WHERE behavior_type='buy';
5.用户的购物情况
作者建立了视图。相对于临时表，视图可以保存在数据库中，当下次使用时，可以直接引用，同时视图可以随着数据的更新而更新，比较方便。
原数据是以单条用户行为为记录，视图是汇总单个用户的行为数
CREATE VIEW user_behaviorASSELECT
user_id, count(behavior_type),
SUM(CASE WHEN behavior_type='pv' THEN 1 ELSE 0 END) AS 'pv',
SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END) AS 'fav',
SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END) AS 'cart',
SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END) AS 'buy'
FROM userGROUP BY user_idORDER BY COUNT(behavior_type) DESC;
问题：初次建立视图，字段的别名都是汉字，比如（访问量），在后期使用的时候又报出编码错误，无法识别。没有找到解决办法，使用英文单词代替。
6.复购率
对应指标：re_purchases 购买>2/购买人群
SELECT CONCAT(ROUND(SUM(case when 购买次数>=2 then 1 else 0 end)/SUM(case when 购买次数 >0 then 1 else 0 end)*100),'%') AS '复购率' FROM user_behavior;
问题：自己当时想的是直接select count两种人群数，然后做除法。事实上既费劲，运行时间又长
select (select count(user_id) from user_behavior_details where buy>=2)/(select count(*) from user_behavior_detail where buy>0) as percentage
7.转化漏斗
从访问——>加购物车——>购买三个环节用户流失的情况分析
计算各个环节与上个环节的比值
SELECT
CONCAT(ROUND(SUM(点击次数)/SUM(点击次数)*100,2),'%') AS 'pv',
CONCAT(ROUND((SUM(加购数)+SUM(收藏次数))/SUM(点击次数)*100,2),'%') AS 'pv_to_favCart',
CONCAT(ROUND(SUM(购买次数)/SUM(点击次数)*100,2),'%') AS 'pv_to_buy'
FROM user_behavior;
8.查看购买率高和购买率低的用户在其他行为上有何区别
SELECT user_id,点击次数,收藏次数,加购数,购买次数,ROUND(购买次数/点击次数*100,2) AS '购买率' FROM user_behavior GROUP BY user_id ORDER BY 购买率 DESC;
9.一天中用户行为特征
SELECT time, COUNT(behavior_type),
SUM(CASE WHEN behavior_type='pv' THEN 1 ELSE 0 END) AS '点击次数',
SUM(CASE WHEN behavior_type='fav' THEN 1 ELSE 0 END) AS '收藏次数',
SUM(CASE WHEN behavior_type='cart' THEN 1 ELSE 0 END) AS '加购数',
SUM(CASE WHEN behavior_type='buy' THEN 1 ELSE 0 END) AS '购买次数'
FROM user GROUP BY time ORDER BY time;
10.一周内用户行为特征
我第一想法：除了case then 外可以使用其他吗？比如 count(pv) from behavior_type where behavior_type='pv' 只显示pv的，然后用uion联合fav，cart，buy。因为select的字段是一致的，所以应该可以
select data_format(time,'%W') as week ,
sum(case when behavior_type='pv' then 1 eles 0 end) as 'pv'
sum(case when behavior_type='fav' then 1 else 0) as 'fav'
sum(case when behavior_type='cart' then 1 else 0) as 'cart'
sum(case then behavior_type='buy' then 1 else 0) as 'buy'
from behavior_type
group by week
order by date_format(time,'%W')#order by 不可以使用别名？
11.基于FRM 模型用户细分
从来没有用过MySQL做用户细分，语句如下
SELECT r.user_id,f.frequency,recent_rank,freq_rank,
CONCAT(CASE WHEN recent_rank<=(4330)/4 THEN '4'
WHEN recent_rank>(4330)/4 AND recent_rank<=(4330)/2 THEN '3'
WHEN recent_rank>(4330)/2 AND recent_rank<=4330/4*3 THEN '2' ELSE '1' END,
CASE WHEN freq_rank<=(4330)/4 THEN '4'
WHEN freq_rank>(4330)/4 AND freq_rank<=(4330)/2 THEN '3'
WHEN freq_rank>(4330)/2 AND freq_rank<=4330/4*3 THEN '2' ELSE '1' END)
AS user_value
FROM (
SELECT a.*,(@rank:=@rank+1) AS recent_rank FROM(SELECT user_id,DATEDIFF('2014-12-19',max(date)) AS recent
FROM user AS t1 WHERE behavior_type='buy' GROUP BY user_id ORDER BY recent)AS a,
(SELECT @rank:=0) AS b) AS r,(SELECT *,(@rank2:=@rank2+1) AS freq_rank
FROM(SELECT user_id,count(*) AS frequency FROM user WHERE behavior_type='buy' GROUP BY user_id ORDER BY frequency DESC)AS a,(SELECT @rank2:=0)AS b) AS f WHERE r.user_id=f.user_id;
网友评论