MySQL查询用户行为,网站用户行为分析

语句：select day(visit_date) from bigdata_user limit 10;

行为日期

购买行为的记录数、不同用户数

语句：select count(distinct uid) from bigdata_user where behavior_type=‘4’;

按日期统计记录数、用户数

语句：select count(distinct uid),day(visit_date) from bigdata_user where behavior_type=‘4‘ group by day(visit_date) limit 10;

语句：select count(*),day(visit_date) from bigdata_user where behavior_type=‘4‘ group by day(visit_date) limit 10;

保存为表格

语句：create table day_count as select count(*),day(visit_date) from bigdata_user where behavior_type=‘4‘ group by day(visit_date);

语句：create table day_uid as select count(distinct uid),day(visit_date) from bigdata_user where behavior_type=‘4‘ group by day(visit_date);

12号+购买行为

语句：select * from bigdata_user where behavior_type=‘4‘and visit_date=‘2014-12-12‘ limit 10;

按用户编号分组

语句：select uid from bigdata_user where behavior_type=‘4‘and visit_date=‘2014-12-12‘ group by uid limit 10;

按用户分组统计

语句：select uid,count(*) from bigdata_user where behavior_type=‘4‘and visit_date=‘2014-12-12‘ group by uid limit 10;

12号，购买，4项以上

语句：select uid,count(*) from bigdata_user where behavior_type=‘4‘and visit_date=‘2014-12-12‘ group by uid having count(*)>4 limit 10;

语句：select uid,count(*) from bigdata_user where behavior_type=‘4‘and visit_date=‘2014-12-12‘ group by uid having count(behavior_type=‘4‘)>4 limit 10;

2014-12-12号当天广东购买商品数

语句：select count(*)from bigdata_user where visit_date=‘2014-12-12‘ and province=‘广东‘;

按省份统计购买数量

语句：select count(*)from bigdata_user group by province;

2014-12-12号当天的商品购买与浏览比例

语句：select count(*)from bigdata_user where visit_date=‘2014-12-12‘ and behavior_type=‘4‘ limit 10;

用户10001082在2014-12-12号当天活跃度：该用户点击行为占该天所有点击行为的比例

语句:select count(*)from bigdata_user where visit_date=‘2014-12-12‘ and uid=‘10001082‘;

2014-12-12号当天购买4件商品以上的用户

语句：select uid,count(*)from bigdata_user where behavior_type=‘4‘ and visit_date=‘2014-12-12‘ group by uid having count(*)>4;