Hive用户行为数仓（二）

最新推荐文章于 2023-03-31 16:01:55 发布
最新推荐文章于 2023-03-31 16:01:55 发布
阅读量1.2k 收藏 6
点赞数 1
分类专栏： 实战案例 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/TU_JCN/article/details/107286944
版权

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;
TUJC
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Hive on Spark 离线数仓生成的原始数据
02-12
这些数据包含了1月7日至10日的历史业务活动记录，这意味着它们可能包括了各种业务相关的指标，如交易量、用户行为、销售数据等。这些数据通常是经过ETL（提取、转换、加载）流程加工后的，以确保数据质量和一致性，...
hive面试题——求连续活跃天数
qq_30414737的博客
01-03 1296
求一个月用户连续活跃3天以上的用户
sql 不同条件返回多个count值_HIVE了解及SQL基础命令
weixin_39640395的博客
12-17 914
hive(数据仓库工具)Hive是一个数据仓库基础工具在Hadoop中用来处理结构化数据。它架构在Hadoop之上，总归为大数据，并使得查询和分析方便。并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。术语“大数据”是大型数据集，其中包括体积庞大，高速，以及各种由与日俱增的数据的集合。使用传统的数据管理系统，它是难以加工大型数据。因此，Apache软件基金...
HIVE统计函数
weixin_44839345的博客
09-30 3402
（1)count(*) 作用：统计检索出的行的个数，包括NULL值的行 count(t) 作用：返回指定字段的非空值的个数 count(distinct t) 作用：返回指定字段的不同的非空值的个数 (2)sum(t) 作用：统计结果集中col的相加的结果 sum(distinct t) 作用：统计结果中col不同值相加的结果 (3)avg(t) 作用：统计结果集中col的平均值 avg(dist...
hive经典面试题2--次日留存、3日、7日留存怎么计算
热门推荐
程老师的博客
11-25 1万+
背景：互联网公司，分析app上线后的效果，通常会通过很多指标来分析，其中一个指标就是留存率，留存率中最重要的就是次日留存，如果次日留存很高，说明这个app很成功，用户粘性高。 留存是怎么定义的呢？ 一个用户第一天来了，第二天又来了，说明这个用户留存了。次日留存率就是100% 第一天来了100个用户，第二天这100用户中只有50个用户过来了，第三天这100个用户中只有30个用户过来了；那么次日留存率就是50/100100=50%，3日留存率就是30/100100=30% 我们来看看通过代码怎么计算留存率：
hive实现连续N天登录用户计算
weixin_37790309的博客
09-02 1375
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录前言一、pandas是什么？二、使用步骤1.引入库2.读入数据总结 前言 提示：实际在利用Apache Hive 进行数据计算时，有些业务changjing/ 提示：以下是本篇文章正文内容，下面案例可供参考 一、pandas是什么？ 示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。 二、使用步骤 1.引入库 代码如下（示例）： import numpy as np import panda
尚硅谷大数据项目之电商数仓用户行为采集平台1
08-04
【尚硅谷大数据项目之电商数仓用户行为采集平台1】是一个综合性的教程，旨在教授如何构建一个电商领域的数据仓库，特别关注用户行为数据的采集。本项目涵盖了从数据仓库的基本概念到具体实施的各个阶段，包括项目...
大数据项目之电商数仓（2用户行为数据仓库）.docx
11-18
大数据项目之电商数仓（用户行为数据仓库） 本资源摘要信息是关于大数据项目之电商数仓（用户行为数据仓库）的知识点总结。电商数仓是指基于大数据技术的电商平台数据仓库，旨在提供数据分析和数据挖掘的解决方案。...
尚硅谷大数据项目之电商数仓（2用户行为数据仓库）.rar
09-16
在电商数仓中，Hive可以帮助我们快速构建数据模型，定义表结构，执行ETL（提取、转换、加载）任务，并对用户行为数据进行汇总、分组、过滤等各种分析操作。 用户行为数据仓库的构建通常包括以下步骤： 1. 数据收集...
找出连续登陆用户(hive sql实现)
weixin_43695091的博客
05-17 396
问题：找出最近一个月内连续登陆超过7天的用户。 思路分析：连续登陆的用户具有连续性，若连续登陆：日期+日期排名 = 固定值； 表和字段说明：user_number,dt; user_active_log表 假设用户登陆事件一天只有一条记录,dt 表示用户登陆日期，格式为yyyyMMdd hive SQL如下： select user_number from ( select user_number, dt, row_number() over(partition by user_numb
hive求活跃天数
qq_29376831的博客
03-28 521
需求1：截止到当天的用户连续活跃的天数 需求2：某段时间内用户的连续活跃区间 需求3：某段时间用户的最大连续活跃天数 需求1：截止到当天的用户连续活跃的天数 思路： 按照日期倒序排序,另一个字段为与当天相差的天数，如果连续的话，则前两者之差为1 select uid, "当天", sum(case when rn-day_num=1 then 1 else 0 end ) as active_days from ( select uid, dt, row_number() ov
HiveSQL新增交易用户统计
asd623444055的博客
06-19 204
HiveSQL新增交易用户统计
Hive数仓项目之数仓分层、数仓工具的使用
黑马程序员官方博客
10-11 885
​ sqoop是隶属于Apache旗下的, 最早是属于cloudera公司的,是一个用户进行数据的导入导出的工具, 主要是将关系型的数据库(MySQL, oracle...)导入到hadoop生态圈(HDFS,HIVE,Hbase...) , 以及将hadoop生态圈数据导出到关系型数据库中。请问: 如何实现一个工作流呢?​ 核心: 是将各类hadoop生态圈的软件的操作界面集成在一个软件中 (大集成者)请问, 大数据的工作流程是否可以使用工作流来解决呢?​ 建模: 如何在hive中构建各个层次的表。
Hive保姆级教程(万字长文，建议收藏)
浅谈
05-22 2787
Hive的设计原理、Hive的分区设计、Hive的DDL和DML、Hive中常见的函数以及自定义函数、Hive的Join、Hive的窗口函数和分析函数、Hive的优化、数据仓库的设计、
Android学习笔记-基本控件介绍
u010186280的博客
10-02 148
TextView TextView主要用于在界面上显示一段文本信息。 <TextView android:id="@+id/textView" android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="center" android:textColor="#00ff00" android
HIVE日活、周活、月活、新增业务指标分析
hufei
05-13 4865
目录 一、前提 二、dws层日活、周活、月活 1、数据的走向分析 2、日活sql 3、dws_uv_detail_day日活跃的数据 4、周活跃sql 5、dws_uv_detail_wk周活跃的数据 6、月活跃的sql： 7、dws_uv_detail_wk月活跃的数据 三、ads层活跃用户数统计 1、活跃用户表的创建 2、活跃用户数的sql 一、前提 数据仓库...
Hive 交互事件分析
最新发布
Mogeko1的博客
03-31 237
Hive 交互事件分析
电商数据仓库（四）
qq_43494678的博客
03-19 249
基于Apache的电商数据仓库（四）欢迎第8章 ODS层8.1 创建数据库8.2 创建start日志表8.2 创建event日志表8.3 生成脚本第9章 DWD层9.1 start表操作9.2 event表操作9.3 将event_name,event_json,event_time里面的数据打开到表中第8章 ODS层搭建 欢迎 你好！这是我历经1个半月的学习（Apache和CDH），做完的一个项目，本次和你们分享一下Apache版。 感谢您的阅读！ 第1章~第4章在基于Apache的电商数据仓库（一） 第
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值