MySQL电商数据查询分析实战一

随着数字化时代的不断发展，人们越来越倾向于在网上进行购物，因此电商零售业也日益成熟。而作为一个电商零售人，在做完运营销售之后的总结是非常重要的，那么如何总结提升以形成整个营销产业链的良性闭环呢？我认为对营销时产生的数据进行统计分析是很重要的。

这里以某电商平台用户记录数据为例，通过MySQL语言，借助Navicat平台进行搭建分析。

数据具体信息如下：包括了daily_active_user 日活跃用户表、dim_user_level 用户会员等级表、dim_channel 渠道维度表等等。

以daily_active_user 日活跃用户表为例，它包含了以下信息：

在了解了基本表信息之后，我们才能对数据进行查询分析操作

1.首先我们可以查询出月活跃天数分布和基础数据量级 SQL代码如下：

select 
ds,count(1) as num
from daily_active_user
group by ds
order by ds
limit 1000;

查询结果如下： 可以看到一共30条数据，分别记录了每一个日期ds的活跃记录数，即ds1 20190901有254条记录

2.接着我们查询找出低活/中活/高活用户数为目标，从数据中找出我们想要的数据 具体低活/中活/高活用户数要求如下：

低活用户：月活跃天数 < 10 天 低活用户：月活跃天数 >= 10 天且 < 20 天，且时长 10 分钟以上的天数 < 5 天 中活用户：月活跃天数 >= 10 天且 < 20 天，且时长 10 分钟以上的天数 >= 5 天 中活用户：月活跃天数 >= 20 天，且时长30 分钟以上的天数 < 10 天 高活用户：月活跃天数 >= 20 天，且时长30 分钟以上的天数 >=10 天

具体的SQL代码如下

select 
      case when active_days<10 then 低活
		   when active_days>=10 and active_days<20 and durations_10days<5 then 低活
		   when active_days>=10 and active_days<20 and durations_10days>=5 then 中活
		   when active_days>=20 and durations_30days<10  then 中活
		   when active_days>=20 and durations_30days>=10 then 高活
			end as user_type,count(distinct uid) as usernum
from 
 (select uid,count(distinct ds) as active_days,
		     count(distinct if(duration>600,ds,null) as durations_10days,
			 count(distinct if(duration>1800,ds,null) as durations_30days,   #得到每个用户uid的活跃天数和活跃时长
        from daily_active_user
        group by uid) a
 group by
	   case when active_days<10 then 低活
		    when active_days>=10 and active_days<20 and durations_10days<5 then 低活
		    when active_days>=10 and active_days<20 and durations_10days>=5 then 中活
		    when active_days>=20 and durations_30days<10  then 中活
			when active_days>=20 and durations_30days>=10 then 高活
			end 
order by field(user_type,低活,中活,高活);

查询结果如下：

3.接着我们引入dim_user_level 用户会员等级表和dim_channel 渠道维度表，它们所包含的信息如下： 会员等级表 渠道维度表 结合上面的daily_active_user 日活跃用户表，我们可以进一步查询不同渠道的人均会员值 具体代码如下：

select  ds,channel_name,
        count(distinct uid) as usernum,
        avg(user_current_value) as avg_user_value
from
        (select t1.ds,t1.uid,
        t2.user_value-t1.value_gap as user_current_value,  #查询计算出表一表二结合后的用户uid 现有积分值  !重点
        t3.channel_name
     from(select  ds,uid,channel,user_level,value_gap  #表一daily_active_user 查询字段 ！！首要
		      from daily_active_user) t1
   left join (
              select user_level,user_value      #表二dim_user_level 查询字段 ！其次
		      from dim_user_level) t2
	          on t1.user_level+1=t2.user_level    #表一表二连接条件
		
   left join (
             select channel,channel_name  #再次连接表三dim_channel 
		     from dim_channel) t3
	         on t1.channel=t3.channel
) t
	 
  group by  ds,channel_name;

查询结果如下： 可以看到不同频道每一天的人均的会员值。