淘宝app用户分析（Mysql）
前言：
本文针对淘宝app的运营数据，以行业常见指标对用户行为进行分析，包括 日pv,uv分析，付费率分析，复购行为分析，留存分析，漏斗流失分析和用户价值RFM分析 等，来挖掘用户的行为模式。本文主要用于对SQL语言逻辑的运用和代码分析，作为对之前用python进行分析的项目的补充，对查询结果不会有过多分析，如果需要查看详细分析，可参考本人用python做的更完整的项目报告：
数据集描述：
该数据包含12256906行，6列，文件名：tianchi_mobile_recommend_train_user.zip，文件大小约0.1GB。数据为淘宝APP2014年11月18日至2014年12月18日的用户行为数据，用户数量10000.数据下载链接：
列字段包含以下：
user_id ：用户身份，脱敏
item_id ：商品ID，脱敏
behavior_type ：用户行为类型（包含点击、收藏、加购物车、支付四种行为，分别用数字1、2、3、4表示）
user_geohash ：地理位置
item_category ：品类ID（商品所属的品类）
time ：用户行为发生的时间
工具描述：
本文主要用的分析工具主要是 mysql 语言 ， 除了涉及常见的 分组汇总，引用变量，视图 知识点外 ， 还涉及到 关联查询，存储函数，游标，循环操作等内容。 由于个别语言比较复杂，本文会对必要环节进行拆分说明。
正文：
一.数据清洗
本文数据清洗过程用python相关库进行处理，主要是对时间列进行处理，相关内容可参考 ： 探索未知：淘宝app用户行为分析 。最终处理结果如下：
二.用户行为分析
1.日pv
select time,count(*)as'日pv' from taobao_app where behavior_type=1 group by time
2.uv
select time,count(DISTINCT user_id)as'日uv' from taobao_app where behavior_type=1 group by time
3.每天付费率
-- 每天付费人数
CREATE view paid
select time,count(distinct user_id) as paid_people from taobao_app where behavior_type=4 group by time;
-- 每天活跃人数
CREATE view activate
select time,count(distinct user_id)as activate_people from taobao_app group by time;
-- 每天付费率
select a.time, (paid_people/activate_people) as '付费率' from paid as p join activate as a on p.time=a.time;
4.复购率
-- 创建用户消费次数和时间视图
CREATE view rebuy
select time,user_id,count(*) as times_of_pay from taobao_app
where behavior_type=4 group by time,user_id order by time;
-- 复购率计算=有复购行为用户（即消费两天以上）/10000（总用户数10000已知）
select count(*)/10000 as '复购率'
(select user_id,count(distinct time) as '消费天次数' from rebuy
group by user_id having count(distinct time) >=2)as c
5.留存计算（用到 游标 和 储存函数 ）
1）先建立函数
drop PROCEDURE if exists users_p;
drop PROCEDURE if exists users_p2;
drop table if exists users_table;
CREATE table users_table(time date,user_id char(255));
CREATE PROCEDURE users_p(d date)
BEGIN
declare o date;
DECLARE p char(255);
declare done boolean ;
DECLARE user_id_b CURSOR
select time,user_id from users
where time=d and user_id not in
(select distinct user_id from users where time<d);
DECLARE continue handler for SQLSTATE'02000' set done =1;
open user_id_b;
REPEAT
fetch user_id_b into o,p;
insert into users_table (time,user_id)values(o,p);
until done end repeat;
close user_id_b;
CREATE PROCEDURE users_p2()
begin
declare d date default '2014-11-19';
REPEAT
call users_p(d);
set d=DATE_ADD(d,INTERVAL 1 day);
until d='2014-12-19' end repeat;
call users_p2()步骤说明：
1.由于该过程进行反复更改，因此用drop if exists 方式删除错误的储存。
2.先建立第一个函数 users_p（） ,该函数作用为找出某天新增用户数量，并用 repeat 和 游标 将每行数据（包括日期和相应增加用户）查询结果写入新建表 users_table。
3.再建第二个函数 users_p2 ()，该函数引用 users_p（ ），针对数据集中每一天循环查找出当天的新增用户。
注意：
1.由于declare必须位于函数最前面，所以通过拆解方法来避开定义变量顺序不能再其他语句后面的问题。
2.对于函数中的变量都只能指示一个结果，所以对于查询结果为多行多列的，目前本人以至能想到用游标的方法对把数据保存到新建表中。
2）对users_table进行进行留存率查询
由于代码运行时间长，本次只引用次日留存做展示，需要n日留存秩序更改参数n即可
-- 留存率=n日后活跃人数/当日新活跃人数
-- 留存函数（n为n日的留存）
CREATE PROCEDURE retention( in n int)
BEGIN
select time,
(select count(user_id) from users as b where b.time = DATE_add(a.time,INTERVAL n day) and
user_id in (select user_id from users_table as c where c.time=a.time))
/ count(user_id) as '留存率'
from users_table as a group by time order by time;
-- 查找次日留存
call retention(1)
6.漏斗模型探寻每日流失率
-- 漏斗流失
-- 'click_to_car_loss'为点击到加入购物车流失率，'car_to_collect_loss'为加入购物车到收藏商品流失率，'collect_to_paid_loss'为收藏商品到购买流失率
select time,
(select count(*)from taobao_app as t2 where t2.time=t1.time and t2.behavior_type=3)/
(select count(*)from taobao_app as t3 where t3.time=t1.time and t3.behavior_type=1) as 'click_to_car_loss',
(select count(*)from taobao_app as t4 where t4.time=t1.time and t4.behavior_type=2)/
(select count(*)from taobao_app as t5 where t5.time=t1.time and t5.behavior_type=3) as 'car_to_collect_loss',
(select count(*)from taobao_app as t6 where t6.time=t1.time and t6.behavior_type=4)/
(select count(*)from taobao_app as t7 where t7.time=t1.time and t7.behavior_type=2) as 'collect_to_paid_loss'
from taobao_app as t1 group by time order by time
7.用户价值R/F/M
由于没有购买金额数据只能做到R/F.
select r.user_id,r.recent,f.frequency,recent_rank,freq_rank,
concat(
case when recent_rank<=(8886)/4 then '4'
when recent_rank>(8886)/4 and recent_rank<=(8886)/2 then '3'
when recent_rank>(8886)/2 and recent_rank<=8886/4*3 then '2'
else '1' end,
case when freq_rank<=(8886)/4 then '4'
when freq_rank>(8886)/4 and freq_rank<=(8886)/2 then '3'
when freq_rank>(8886)/2 and freq_rank<=8886/4*3 then '2'
else '1' end
)as user_value
(select a.*,(@rank:=@rank+1) as recent_rank from
select user_id,
DATEDIFF('2014-12-19',max(time))as recent