数据仓库之用户行为数仓开发
数据仓库分为 4层：ods层、dwd层、dws层、app层，
我们先来构建第一层：ods层
ods 层
在 ods_mall中需要创建以下针对用户行为数据的表
表名 解释
ods_user_active 用户主动活跃表(act=1)
ods_click_good 点击商品表(act=2)
ods_good_item 商品详情页表(act=3)
ods_good_list 商品列表页表(act=4)
ods_app_close APP崩溃数据表(act=5)
注意：
- 由于在构建数据仓库的时候我们会创建多个数据库，所以在创建以及使用表的时候最好都在表名前面带上对应的数据库名称，否则可能会出现一些不必要的问题，可能会把 ods层的表建到 dwd 层。
- 考虑到 SQL 重跑的情况，需要在 SQL 语句中添加 if not exists
- hive 中可以用 string、date 和 timestamp 表示日期时间，date 用 yyyy-MM-dd 的形式表示，timestamp 用 yyyy-MM-dd hh:mm:ss 的形式表示，string 可以表示 yyyy-MM-dd 和yyyy-MM-dd hh:mm:ss
这三种格式之间可以互相转换，不过在用的时候建议格式统一，String 可以表示另外两种格式，并且也支持日期的大小比较，所以在这里针对时间统一使用 String 表示。
dwd 层
针对 ods层表中的数据进行清洗，参考数据清洗规则，按照实际情况对数据进行清洗
注意：如果清洗规则使用 SQL可以实现，那么就使用 SQL实现数据清洗，如果清洗的规则使用 SQL实现起来非常麻烦，或者使用 SQL压根无法实现，此时就可以考虑需要使用MapReduce代码或者 Spark代码对数据进行清洗了。
由于我们这里采集的数据还是比较规整的，可以使用 SQL实现，所以我们就直接使用 SQL实现数据清洗了。
创建 dwd层的表
注意：
- 原始 json数据中的用户 id字段名称为 uid，但是在商品订单数据中用户 id字段名称为user_id，这块需要注意一下，在实际工作中会有这种情况，客户端数据和服务端数据的个别字段名称不一致，所以我们在使用的时候最好是统一一下，后期使用起来比较方便，所以在这里我会通过 uid解析数据，解析之后，给字段起别名为 user_id。
- hive 中的 timestamp 只能解析 yyyy-MM-dd HH:MM:SS 格式的数据，所以针对这里面的acttime 字段我们使用 bigint 类型
- 为了考虑到 SQL 重跑的情况，在使用 insert into table 的时候最好改为 insert overwrite
table，否则 SQL 重复执行的时候会重复写入数据
dwd_user_active(存储解析清洗之后的用户主动活跃数据，对数据进行去重，并 且 过 滤 掉xaid为 空 的 数据)
dwd_good_item
dwd_click_good
dwd_good_item
dwd_good_list
dwd_app_close
以dwd_user_active为例，建表语句和插入数据语句如下
create external table if not exists dwd_mall.dwd_user_active(
user_id bigint,
xaid string,
platform tinyint,
ver string,
vercode string,
net bigint,
brand string,
model string,
display string,
osver string,
acttime bigint,
ad_status tinyint,
loading_time bigint
)partitioned by(dt string)
row format delimited
fields terminated by '\t'
location 'hdfs://bigdata01:9000/data/dwd/user_active/';
insert overwrite table dwd_mall.dwd_user_active partition(dt='20220309') select
get_json_object(log,'$.uid') as user_id,
get_json_object(log,'$.xaid') as xaid,
get_json_object(log,'$.platform') as platform,
get_json_object(log,'$.ver') as ver,
get_json_object(log,'$.vercode') as vercode,
get_json_object(log,'$.net') as net,
get_json_object(log,'$.brand') as brand,
get_json_object(log,'$.model') as model,
get_json_object(log,'$.display') as display,
get_json_object(log,'$.osver') as osver,
get_json_object(log,'$.acttime') as acttime,
get_json_object(log,'$.ad_status') as ad_status,
get_json_object(log,'$.loading_time') as loading_time
from
(
select log from ods_mall.ods_user_active where dt = '20220309' group by log
) as tmp
where get_json_object(log,'$.xaid') !='';
需求分析
前面的两层中的表和需求一般没什么关系，就是把已有的数据接入进来，然后对数据进行清洗处理
但是后面的 dws层和 app层是和业务有关联的，所以在构建这两层中的表的时候，我们需要根据一些典型的业务场景来进行分析，在根据具体业务建表的时候尽可能把表设计的更加通用，可以满足后期一些类似业务需求
就是说我们在基于业务构建表的时候，不要直接一个 SQL搞定，可以把一些复杂的 SQL基于一些维度进行拆分，拆分出来一些中间表，再基于这些中间表统计最终的结果。这样这个中间表里面的数据，我们后期针对一些类似的业务需求还是可以服用的。
需求一：每日新增用户相关指标
需求二：每日活跃用户相关指标
需求三：用户 7日流失 push提醒
需求四：每日启动 App次数相关指标
需求五：操作系统活跃用户相关指标
需求六：APP崩溃相关指标
需求一：每日新增用户相关指标
在统计新增用户时，用户是以设备标识(xaid 字段)来判断的，每一个设备都有一个唯一设备码，因为会存在用户不登录的情况，以及多人共用一个账号的情况，所以根据用户 id 进行统计是不准确的。
新增用户是指第一次安装并且使用 app 的用户，后期卸载之后再使用就不算新用户了
这个新增用户其实也可以称为新增设备，一个设备对应一个用户。
- 每日新增用户量
- 每日新增用户量的日环比和周同比
每日新增用户量
在实际工作中通过这个指标可以衡量我们产品的用户增长速度，如果每日新增用户量一直是上升的，说明我们的产品势头正好，如果在一段时间内增速减缓或者下降，这个时候需要考虑如何获取新用户。
咱们前面分析了，新增用户是指第一次安装并且使用 APP 的用户，咱们有一个埋点会上报用户打开 APP 这个行为，所以计算新增用户量就使用这一份数据
实现思路如下：
- 我们基于清洗之后的打开 app 上报的数据创建一个历史表，这个表里面包含的有 xaid 字段，针对每天的数据基于 xaid 进行去重
- 如果我们要计算 2026 年 02 月 1 日的新增用户量的话，就拿这一天上报的打开 app 的数据，和前面的历史表进行 left join，使用 xaid 进行关联，关联不上的数据则为新增数据。
每日新增用户量的日环比和周同比
同比一般是指本期统计数据和往年的同时期的统计数据比较，例如 2026 年 2 月和 2025 年 2月相比较；这个统计周期也可以是按月或者周
环比一般是指本期统计数据和上一期的统计数据作比较，例如 2026 年 2 月和 2026 年 1 月相比较；这个统计周期也可以是按周或者日
在实际工作中通过同比和环比是可以衡量某一个指标的变化速度，供产品经理做一些决策的时候使用。
日环比=(本期的数据-上一期的数据)/上一期的数据
日环比中的时间单位是天
周同比=(本期的数据-上一期的数据)/上一期的数据
周同比中的时间单位是周(7 天)
实现思路
直接基于 app_user_new_count 进行统计即可，可以统计出来某一天的日环比和周同比生成一个新表 app_user_new_count_ratio
里面包含日期、新增用户量、日环比、周同比
需求二：每日活跃用户(主活)相关指标
活跃用户的定义是指只要当天使用过 APP 就算是活跃用户，使用 APP 这种操作属于主动操作，所以这种活跃我们也会称为主动活跃，简称主活
针对这个需求统计的指标和新增用户的指标类似
- 每日主活用户量
- 每日主活用户量的日环比和周同比
每日主活用户量
主活的概念和定义我们知道了，其实就是统计每天使用过 app 的用户，所以我们可以直接使用 dws 层的 dws_user_active_history 这个表
直 接 求 和 即 可 获 取 到 当 日 的 主 活 用 户 量 ， 将 最 终 的 结 果 保 存 到 app 层 的app_user_active_count 表中
每日主活用户量的日环比和周同比
这个指标直接基于每日主活用户量的表(app_user_active_coun)进行计算即可，把最终的结果保存到 app 层的 app_user_active_count_ratio 表中
需求三：用户 7 日流失 push 提醒
什么是流失呢？
假设这个用户在 2026年 2月 2 日是新增用户，如果他在后续的 7 天内，也就是在 2 月 9日内没有再使用 app，则认为是流失用户，具体多少天属于流失用户，这个是需要产品经理根据对应产品的特点来定的，一般业内使用比较多的是 7天这个时间点。
push是什么意思呢
大家平时是不是深受各种 app的提醒轰炸，我针对大部分的 app都禁用了消息推送，要不然每天手机上会有各种各样的推送消息，很烦，这个其实就是软件给你 push的消息。
实现思路：
- 基于 dws_user_active_histo表ry，获取表中最近 8 天的数据，根据 xaid进行分组，这样可以获取 xaid以及 xaid对应的多个日期(dt)
- 接着需要对 xaid对应的 dt进行过滤，获取 xaid中最大的 dt，判断这个 dt是否等于(当天日期-7)，如果满足条件，则说明这个用户最近 7日内没有使用 app，就认为他属于 7 日流失用户
需求四：每日启动 App 次数相关指标
这个需求就是对每日打开 app上报的数据进行统计
针对这个需求我们需要统计两个指标
- 每日人均启动 App次数
- 每日 APP启动次数分布（1次，2 次，3 次及以上）
每日人均启动 App次数
每日人均启动 App 次数=当日所有用户启动 APP 总次数/当日所有人数
针对这种需求，我们在计算结果的时候最好是把这个指标的分子和分母保存起来，这样这份数据后期还有可能被复用，如果直接保存最终的结果，这个数据就没办法复用了。
实现思路如下：
- 基于 dws_user_active_history 表，统计当日的数据，根据 times 字段的值求 pv 和 uv 即可
- 将计算的结果保存到 app 层的 app_user_open_app_count 表中
每日 APP启动次数分布（1次，2 次，3 次及以上）
这个指标也需要基于 dws_user_active_history 表
实现思路如下：
对这里面的 times 字段进行统计，计算 times=1 的数据条数、times=2 的数据条数以及 times>=3的数据条数即可，将最终的结果保存到 app 层的 app_user_open_app_distrib 中即可
需求五：操作系统活跃用户相关指标
这个需求是统计一下我们产品的目前主要用户群体是使用什么类型的操作系统
因为我们产品的 app 是有 Android 端和 ios 端的
如果我们的用户 80%以上使用的都是 Android，那么我们肯定要针对 Android 端的 APP 做更多的优化支持，这样可以保证大部分用户的使用体验。
还有就是获取用户使用的手机型号，分辨率信息，这样可以更好的做适配。
针对这个需求我们主要统计以下指标：
- 操作系统活跃用户分布（安卓、IOS）
- 安卓系统版本活跃用户分布
- IOS 系统版本活跃用户分布
- 设备品牌活跃用户分布
- 设备型号活跃用户分布
- 网络类型活跃用户分布
针对这些指标统一分析，其实可以看出来，他们是有相似之处的。都是基于用户使用 app时上报的数据相关的一些指标
其实主要就是针对 dwd_user_active 表中的这些相关维度字段进行分组聚合统计
需求六：APP 崩溃相关指标
这个需求是统计在不同平台系统不同版本下 APP 崩溃的情况，统计这个数据可以方便排查定位问题，如果发现某一个版本的 APP 崩溃频繁，则需要及时修复问题，推送新版本，提升用户体验。
针对这个需求主要统计下面几个指标
- 每日操作系统崩溃总计（安卓、IOS）
- 每日安卓系统-不同 APP 版本崩溃量
- 每日 IOS 系统-不同 APP 版本崩溃量
这里面这三个指标是有关联的，第一个是总的统计，第二个和第三个是不同维度的统计
实现思路：
针对第一个指标，使用 dwd_app_close 表中的数据，根据 platform 进行分组统计即可
但是注意：第二个指标和第三个指标，也需要根据不同的 platform 进行统计，但是又多了一个操作系统的维度，如果按照我们刚才的分析，直接基于 platform 进行分组的话，针对后面两个指标还需要重新计算中间表，没有体现出来数据仓库的好处。
所以我们可以这样做：
针对 dwd_app_close 表中的数据，使用 platform 和 vercode 进行分组，做轻度聚合，将数据保存到 dws 层的 dws_app_close_platform_vercode 表中
基于 dws_app_close_platform_vercode 表中的数据就可以计算出来这三个指标了。
用户行为数据数仓总结
数据库和表梳理
任务脚本梳理
