Hive用户行为数仓(二)

Hive用户行为数仓(二)

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
业务术语
1.用户
用户以设备为判断标准,在移动统计中,每个独立设备认为是一个独立用户。Android系统根据IMEI号,IOS系统根据OpenUDID来标识一个独立用户,每部手机一个用户。

2.新增用户
首次联网使用应用的用户。如果一个用户首次打开某APP,那这个用户定义为新增用户;卸载再安装的设备,不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。

3.活跃用户
打开应用的用户即为活跃用户,不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。

4.周(月)活跃用户
某个自然周(月)内启动过应用的用户,该周(月)内的多次启动只记一个活跃用户。

5.月活跃率
月活跃用户与截止到该月累计的用户总和之间的比例。

6.沉默用户
用户仅在安装当天(次日)启动一次,后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。

7.版本分布
不同版本的周内各天新增用户数,活跃用户数和启动次数。利于判断APP各个版本之间的优劣和用户行为习惯。

8.本周回流用户
上周未启动过应用,本周启动了应用的用户。

9.连续n周活跃用户
连续n周,每周至少启动一次。

10.忠诚用户
连续活跃5周以上的用户

11.连续活跃用户
连续2周及以上活跃的用户

12.近期流失用户
连续n(2<= n <= 4)周没有启动应用的用户。(第n+1周没有启动过)

13.留存用户
某段时间内的新增用户,经过一段时间后,仍然使用应用的被认作是留存用户;这部分用户占当时新增用户的比例即是留存率。

例如,5月份新增用户200,这200人在6月份启动过应用的有100人,7月份启动过应用的有80人,8月份启动过应用的有50人;则5月份新增用户一个月后的留存率是50%,二个月后的留存率是40%,三个月后的留存率是25%。

14.用户新鲜度
每天启动应用的新老用户比例,即新增用户数占活跃用户数的比例。

15.单次使用时长
每次启动使用的时间长度。

16.启动次数计算标准
IOS平台应用退到后台就算一次独立的启动;Android平台我们规定,两次启动之间的间隔小于30秒,被计算一次启动。用户在使用过程中,若因收发短信或接电话等退出应用30秒又再次返回应用中,那这两次行为应该是延续而非独立的,所以可以被算作一次使用行为,即一次启动。业内大多使用30秒这个标准,但用户还是可以自定义此时间间隔。

系统函数
1 collect_set函数
1)创建原数据表
hive (gmall)>

drop table if exists student ;
create table student 
(name string, area string, course string, score int);

2)向原数据表中插入数据
hive (gmall)>

insert into table student values('zhang3','bj','math',88);
insert into table student values('li4','bj','math',99);
insert into table student values('wang5','sh','chinese',92);
insert into table student values('zhao6','sh','chinese',54);
insert into table student values('tian7','bj','chinese',91);

3)查询表中数据
hive (gmall)>

select * from student ;

student.name  student.area student.course  student.score
zhang3  bj       math    88
li4     bj       math    99
wang5   sh      chinese  92
zhao6   sh      chinese  54
tian7   bj      chinese  91

4)把同一分组的不同行的数据聚合成一个集合

hive (gmall)> 
select course, collect_set(area), avg(score) 
from student group by course;


chinese ["sh","bj"]     79.0
math    ["bj"]          93.5

5) 用下标可以取某一个
hive (gmall)>

select course, collect_set(area)[0], avg(score) 
from student group by course;

chinese sh      79.0
math    bj      93.5

2 日期处理函数
1)date_format函数(根据格式整理日期)

hive (gmall)> select date_format('2019-02-10','yyyy-MM');

2019-02

2)date_add函数(加减日期)

hive (gmall)> select date_add('2019-02-10',1);
2019-02-11

hive (gmall)> select date_add('2019-02-10',-1);
2019-02-09

3)next_day函数
(1)取当前天的下一个周一

hive (gmall)> select next_day('2019-02-12','MO');
2019-02-18

说明:星期一到星期日的英文
(Monday,Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday)

(2)取当前周的周一

hive (gmall)> select date_add(next_day('2019-02-12','MO'),-7);
2019-02-11

4)last_day函数(求当月最后一天日期)

hive (gmall)> select last_day('2019-02-10');
2019-02-28

需求一:用户活跃主题

1.1 DWS层

目标:统计当日、当周、当月活动的每个设备明细

1.1.1 每日活跃设备明细 dws_uv_detail_day

在这里插入图片描述

1.1.2 周用户访问明细 dws_uv_detail_wk

根据日用户访问明细,获得周用户访问明细。
在这里插入图片描述

1.1.3 每月活跃设备明细 dws_uv_detail_mn

在这里插入图片描述

1.2 ADS层

目标:当日、当周、当月活跃设备数

1.2.1 活跃设备数 ads_uv_count

在这里插入图片描述
1)建表语句

hive (gmall)>
drop table if exists gmall.ads_uv_count;
create external table gmall.ads_uv_count( 
`dt` string COMMENT '统计日期',
`day_count` bigint COMMENT '当日用户数量',
`wk_count`  bigint COMMENT '当周用户数量',
`mn_count`  bigint COMMENT '当月用户数量',
`is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
`is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
) COMMENT '活跃设备数' 
row format delimited fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_uv_count/'
;

2)导入数据

hive (gmall)>
insert into table gmall.ads_uv_count 
select  
  '2019-02-10' dt,
   daycount.ct,
   wkcount.ct,
   mncount.ct,
   if(date_add(next_day('2019-02-10','MO'),-1)='2019-02-10','Y','N') ,
   if(last_day('2019-02-10')='2019-02-10','Y','N') 
from 
(
   select  
      '2019-02-10' dt,
       count(*) ct
   from gmall.dws_uv_detail_day
   where dt='2019-02-10'  
)daycount join 
( 
   select  
     '2019-02-10' dt,
     count (*) ct
   from gmall.dws_uv_detail_wk
   where wk_dt=concat(date_add(next_day('2019-02-10','MO'),-7),'_' ,date_add(next_day('2019-02-10','MO'),-1) )
) wkcount on daycount.dt=wkcount.dt
join 
( 
   select  
     '2019-02-10' dt,
     count (*) ct
   from gmall.dws_uv_detail_mn
   where mn=date_format('2019-02-10','yyyy-MM')  
)mncount on daycount.dt=mncount.dt
;

1.2.2 ADS层加载数据脚本

1)在node03的/home/hadoop/bin目录下创建脚本

[hadoop@node03 bin]$ vim ads_uv_log.sh

在脚本中编写如下内容

#!/bin/bash

# 定义变量方便修改
APP=gmall
hive=/kkb/install/hive-1.1.0-cdh5.14.2/bin/hive

# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else 
    do_date=`date -d "-1 day" +%F`  
fi 

sql="
  set hive.exec.dynamic.partition.mode=nonstrict;

insert into table "$APP".ads_uv_count 
select  
  '$do_date' dt,
   daycount.ct,
   wkcount.ct,
   mncount.ct,
   if(date_add(next_day('$do_date','MO'),-1)='$do_date','Y','N') ,
   if(last_day('$do_date')='$do_date','Y','N') 
from 
(
   select  
      '$do_date' dt,
       count(*) ct 
   from "$APP".dws_uv_detail_day
   where dt='$do_date'  
)daycount   join 
( 
   select  
     '$do_date' dt,
     count (*) ct
   from "$APP".dws_uv_detail_wk
   where wk_dt=concat(date_add(next_day('$do_date','MO'),-7),'_' ,date_add(next_day('$do_date','MO'),-1) )
)  wkcount  on daycount.dt=wkcount.dt
join 
( 
   select  
     '$do_date' dt,
     count (*) ct
   from "$APP".dws_uv_detail_mn
   where mn=date_format('$do_date','yyyy-MM')  
)mncount on daycount.dt=mncount.dt;
"

$hive -e "$sql"

2)增加脚本执行权限

[hadoop@node03 bin]$ chmod 777 ads_uv_log.sh

3)脚本使用

[hadoop@node03 bin]$ ./ads_uv_log.sh 2019-02-11

需求二:用户新增主题

首次联网使用应用的用户。如果一个用户首次打开某APP,那这个用户定义为新增用户;卸载再安装的设备,不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。

2.1 DWS层 (每日新增设备明细表 dws_new_mid_day)

在这里插入图片描述
在这里插入图片描述
用每日活跃用户表Left Join每日新增设备表,关联的条件是mid_id相等。如果是每日新增的设备,则在每日新增设备表中为null。

2.2 ADS层(每日新增设备表 ads_new_mid_count)

在这里插入图片描述

需求三:用户留存主题

3.1 需求目标

在这里插入图片描述
在这里插入图片描述

3.2 DWS层

3.2.1 每日留存用户明细表 dws_user_retention_day

在这里插入图片描述

3.2.2 n天留存用户明细表 dws_user_retention_day

1)导入数据(每天计算前1,2,3,n天的新用户访问留存明细)

hive (gmall)>
insert overwrite table gmall.dws_user_retention_day
partition(dt="2019-02-11")
select
    nm.mid_id,
    nm.user_id,
    nm.version_code,
    nm.version_name,
    nm.lang,
    nm.source,
    nm.os,
    nm.area,
    nm.model,
    nm.brand,
    nm.sdk_version,
    nm.gmail,
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    1 retention_day 
from gmall.dws_uv_detail_day ud join gmall.dws_new_mid_day nm  on ud.mid_id =nm.mid_id 
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-1)

union all
select  
    nm.mid_id,
    nm.user_id , 
    nm.version_code , 
    nm.version_name , 
    nm.lang , 
    nm.source, 
    nm.os, 
    nm.area, 
    nm.model, 
    nm.brand, 
    nm.sdk_version, 
    nm.gmail, 
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    2 retention_day 
from  gmall.dws_uv_detail_day ud join gmall.dws_new_mid_day nm 
      on ud.mid_id =nm.mid_id 
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-2)

union all
select  
    nm.mid_id,
    nm.user_id , 
    nm.version_code , 
    nm.version_name , 
    nm.lang , 
    nm.source, 
    nm.os, 
    nm.area, 
    nm.model, 
    nm.brand, 
    nm.sdk_version, 
    nm.gmail, 
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    3 retention_day 
from  gmall.dws_uv_detail_day ud join gmall.dws_new_mid_day nm   on ud.mid_id =nm.mid_id 
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-3);

(1)union会将联合的结果集去重,效率较union all差
(2)union all不会对结果集去重,所以效率高

3.3 ADS层

3.3.1 留存用户数 ads_user_retention_day_count

在这里插入图片描述

3.3.2 留存用户比率 ads_user_retention_day_rate

在这里插入图片描述

需求四:沉默用户数

沉默用户:指的是只在安装当天启动过,且启动时间是在一周前

4.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

4.2 ADS层

4.2.1 沉默用户数 ads_silent_count

在这里插入图片描述

需求五:本周回流用户数

本周回流=本周活跃-本周新增-上周活跃

5.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

5.2 ADS层

5.2.1 本周回流用户数 ads_back_count

在这里插入图片描述

需求六:流失用户数

流失用户:最近7天未登录我们称之为流失用户

6.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

6.2 ADS层

在这里插入图片描述

需求七:最近连续三周活跃用户数

最近3周连续活跃的用户:通常是周一对前3周的数据做统计,该数据一周计算一次。

7.1 DWS层

使用周活明细表dws_uv_detail_wk作为DWS层数据

7.2 ADS层

在这里插入图片描述
1)建表语句

hive (gmall)>
drop table if exists  gmall.ads_continuity_wk_count;
create external table gmall.ads_continuity_wk_count( 
    `dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日期',
    `wk_dt` string COMMENT '持续时间',
    `continuity_count` bigint
) 
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_wk_count';

2)导入2019-02-20所在周的数据

hive (gmall)>
insert into table gmall.ads_continuity_wk_count
select 
     '2019-02-20',
     concat(date_add(next_day('2019-02-20','MO'),-7*3),'_',date_add(next_day('2019-02-20','MO'),-1)),
     count(*)
from 
(
    select mid_id
    from gmall.dws_uv_detail_wk
    where wk_dt>=concat(date_add(next_day('2019-02-20','MO'),-7*3),'_',date_add(next_day('2019-02-20','MO'),-7*2-1)) 
    and wk_dt<=concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1))
    group by mid_id
    having count(*)=3
)t1;

需求八:最近七天内连续三天活跃用户数

说明:最近7天内连续3天活跃用户数

8.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

8.2 ADS层

在这里插入图片描述
1)建表语句

hive (gmall)>
drop table if exists gmall.ads_continuity_uv_count;
create external table gmall.ads_continuity_uv_count( 
    `dt` string COMMENT '统计日期',
    `wk_dt` string COMMENT '最近7天日期',
    `continuity_count` bigint
) COMMENT '连续活跃设备数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_uv_count';

2)写出导入数据的SQL语句

hive (gmall)>
insert into table gmall.ads_continuity_uv_count
select
    '2019-02-12',
    concat(date_add('2019-02-12',-6),'_','2019-02-12'),
    count(*)
from
(
    select mid_id
    from
    (
        select mid_id      
        from
        (
            select 
                mid_id,
                date_sub(dt,rank) date_dif
            from
            (
                select 
                    mid_id,
                    dt,
                    rank() over(partition by mid_id order by dt) rank
                from gmall.dws_uv_detail_day
                where dt>=date_add('2019-02-12',-6) and dt<='2019-02-12'
            )t1
        )t2 
        group by mid_id,date_dif
        having count(*)>=3
    )t3 
    group by mid_id
)t4;
  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值