2020-03-19

【数仓项目】重中之重-日活&周活&月活（dws）

前情回顾

活跃用户
打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。
周（月）活跃用户
某个自然周（月）内启动过应用的用户，该周（月）内的多次启动只记一个活跃用户。

开始

总日活计算思路：

1.选取能唯一标识一个用户的列，比如本项目中使用的是mid_id
2.创建一张dws层的表用于存储日活信息

drop table if exists dws_uv_detail_day;
create external table dws_uv_detail_day
(
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识',
    `version_code` string COMMENT '程序版本号',
    `version_name` string COMMENT '程序版本名',
    `lang` string COMMENT '系统语言',
    `source` string COMMENT '渠道号',
    `os` string COMMENT '安卓系统版本',
    `area` string COMMENT '区域',
    `model` string COMMENT '手机型号',
    `brand` string COMMENT '手机品牌',
    `sdk_version` string COMMENT 'sdkVersion',
    `gmail` string COMMENT 'gmail',
    `height_width` string COMMENT '屏幕宽高',
    `app_time` string COMMENT '客户端日志产生时的时间',
    `network` string COMMENT '网络模式',
    `lng` string COMMENT '经度',
    `lat` string COMMENT '纬度'
)
partitioned by(dt string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_day'
;

3.选取dwd_start_log表根据表中的mid_id字段对表作进一步处理（按mid_id分组）放入dws中的日活表

hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
 
insert overwrite table dws_uv_detail_day
partition(dt='2019-02-10')
select 
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang))lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area,
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat
from dwd_start_log
where dt='2019-02-10'
group by mid_id;

4.对日活表做count()处理即可拿到日活，博主写了根据日期生成日活表的脚本，读者可移步shell博客查看（前提是你得有那一日的数据噢）

1	select count(*) from dws_uv_detail_day；

渠道日活计算思路：

个人思路（可能不是最佳）
1.在dwd层现根据source字段分一次组，然后用concat_ws()加collect_set()将其他字段信息拿到。
2.用explode将其炸开，成一个临时表dwd_start_tmp
3.将临时表再做普通日活的处理分组即可拿到分渠道的日活数据

上述比较麻烦，理论上分组的时候应该能够直接在mid_id加上source字段即可

lz会在之后的项目完结文章进行实操，现在先提（wa）一（ge）下（keng）

周活计算思路：

周活的计算是建立在日活表也就是dws_detail_day的基础上的，说白了就是date函数的利用，本质上还是和日活一样，当然，也可以不怕麻烦去翻着日历统计当前周七天的日活加起来，这种思路太“重”量级，不提倡，不介绍。

1.首先建立一个存储周活信息的dws_detail_wk表

drop table if exists dws_uv_detail_wk;
create external table dws_uv_detail_wk(
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识',
    `version_code` string COMMENT '程序版本号',
    `version_name` string COMMENT '程序版本名',
    `lang` string COMMENT '系统语言',
    `source` string COMMENT '渠道号',
    `os` string COMMENT '安卓系统版本',
    `area` string COMMENT '区域',
    `model` string COMMENT '手机型号',
    `brand` string COMMENT '手机品牌',
    `sdk_version` string COMMENT 'sdkVersion',
    `gmail` string COMMENT 'gmail',
    `height_width` string COMMENT '屏幕宽高',
    `app_time` string COMMENT '客户端日志产生时的时间',
    `network` string COMMENT '网络模式',
    `lng` string COMMENT '经度',
    `lat` string COMMENT '纬度',
    `monday_date` string COMMENT '周一日期',
    `sunday_date` string COMMENT  '周日日期'
) COMMENT '活跃用户按周明细'
PARTITIONED BY (`wk_dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_wk/'

ps：需要注意周活的统计并不是指指定日期的前后三天，而是通过函数计算出该日期所在周数，而去统计那个周的活跃度
2.sql编写思路：

1 2	date_add(next_day('2019-02-10','MO'),-7) date_add(next_day('2019-02-10','MO'),-1)

上述命令可以生成指定日期所在周的周一的日期以及周日的日期
通过两个日期可将其拼接为一个日期字符串作为周活表的分区指标
而周活所统计的范围就定在这两个日期之间
完整代码为

set hive.exec.dynamic.partition.mode=nonstrict;
 
insert overwrite table dws_uv_detail_wk partition(wk_dt)
select 
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang)) lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area,
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat,
    date_add(next_day('2019-02-10','MO'),-7),
    date_add(next_day('2019-02-10','MO'),-1),
    concat(date_add( next_day('2019-02-10','MO'),-7), '_' , date_add(next_day('2019-02-10','MO'),-1)
)
from dws_uv_detail_day
where dt>=date_add(next_day('2019-02-10','MO'),-7) and dt<=date_add(next_day('2019-02-10','MO'),-1)
group by mid_id;

3.count()查询周活即可

1	select count(*) from dws_uv_detail_wk;

月活设计思路

月活得计算要比周活简单，这得归功于日期格式化函数date_format()

1.创建月活表
drop table if exists dws_uv_detail_mn;
create external table dws_uv_detail_mn(
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识',
    `version_code` string COMMENT '程序版本号',
    `version_name` string COMMENT '程序版本名',
    `lang` string COMMENT '系统语言',
    `source` string COMMENT '渠道号',
    `os` string COMMENT '安卓系统版本',
    `area` string COMMENT '区域',
    `model` string COMMENT '手机型号',
    `brand` string COMMENT '手机品牌',
    `sdk_version` string COMMENT 'sdkVersion',
    `gmail` string COMMENT 'gmail',
    `height_width` string COMMENT '屏幕宽高',
    `app_time` string COMMENT '客户端日志产生时的时间',
    `network` string COMMENT '网络模式',
    `lng` string COMMENT '经度',
    `lat` string COMMENT '纬度'
) COMMENT '活跃用户按月明细'
PARTITIONED BY (`mn` string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_mn/'
;

2.同样是在日活表的基础上写sql，思路是将日期格式化（yyyy-mm），将相同格式的日期排一起即可得到每月活跃度数据

set hive.exec.dynamic.partition.mode=nonstrict;
 
insert overwrite table dws_uv_detail_mn partition(mn)
select 
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang)) lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area,
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat,
    date_format('2019-02-10','yyyy-MM')
from dws_uv_detail_day
where date_format(dt,'yyyy-MM') = date_format('2019-02-10','yyyy-MM')
group by mid_id;

3.count()即可

1	select count(*) from dws_uv_detail_mn ;

爬坑

最后要提一个博主做项目时遇到的一个大坑（还好爬出来了）
先上遇到坑的结果图：

由于我当时只录入了两天的数据，所以拿到的月活统计如上图，923

上图为周活图

迷惑点来了，为什么同是二月的两天，却周活月活不同？反而周活比月活多，当时看到人都傻了，疯狂怀疑自己代码是不是又写错了。
其实仔细思考下这个问题不难，可能是博主当时写代码写晕了

周活统计的是指定日期本周的用户活跃度，而楼主的两个日期分别是2-10和2-11，这两个日期并不在同一周
当楼主用count代码时拿到的就是两个两个分区的总和
而月活统计时，count拿到的是两天的活跃度，此时两天会用重复的用户，因为他们属于同一月，所以就会被聚合为一个用户，这也就是为什么单独运行周活的活跃度会大于月活，因为周活是两个星期的总和呀~~

赏

纯属好玩