TL;DR
- 场景：离线数仓统计 1/2/3 日会员留存数与留存率，基于新增与启动日志
- 结论：DWS 先做”新增日×留存天数”的留存明细，ADS 再按新增日聚合并补齐分母算留存率
- 产出：Hive DWS/ADS 表结构、装载脚本、计算链路与关键连线修正点
留存会员概念
- 留存会员与留存率：某段时间的新增会员，经过一段时间后，仍继续使用应用认为是留存会员，这部分会员占当时新增会员的比例为留存率
- 需求：1日、2日、3日的会员留存率和会员留存数
DWS 层作用
- 统一数据模型：将原始数据（ODS 层）按照一定的逻辑模型进行整合、清洗、加工，形成标准化的数据结构
- 支持业务场景：满足企业对历史数据的查询和分析需求，支持 OLAP 操作
- 数据细化与分类：将数据按照主题域分类，便于管理和查询
- 数据准确性与一致性：经过处理的数据经过校验，确保逻辑关系正确
创建 DWS 层表
-- 会员留存明细
drop table if exists dws.dws_member_retention_day;
create table dws.dws_member_retention_day
(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`add_date` string comment '会员新增时间',
`retention_date` int comment '留存天数'
)COMMENT '每日会员留存明细'
PARTITIONED BY (`dt` string)
stored as parquet;加载 DWS 层数据脚本
vim /opt/wzk/hive/dws_load_member_retention_day.sh写入内容：
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
drop table if exists tmp.tmp_member_retention;
create table tmp.tmp_member_retention as
(
select t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
1
from dws.dws_member_start_day t1 join dws.dws_member_add_day
t2 on t1.device_id=t2.device_id
where t2.dt=date_add('$do_date', -1)
and t1.dt='$do_date'
union all
select t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
2
from dws.dws_member_start_day t1 join dws.dws_member_add_day
t2 on t1.device_id=t2.device_id
where t2.dt=date_add('$do_date', -2)
and t1.dt='$do_date'
union all
select t2.device_id,
t2.uid,
t2.app_v,
t2.os_type,
t2.language,
t2.channel,
t2.area,
t2.brand,
t2.dt add_date,
3
from dws.dws_member_start_day t1 join dws.dws_member_add_day
t2 on t1.device_id=t2.device_id
where t2.dt=date_add('$do_date', -3)
and t1.dt='$do_date'
);
insert overwrite table dws.dws_member_retention_day
partition(dt='$do_date')
select * from tmp.tmp_member_retention;
"
hive -e "$sql"ADS 层作用
- 聚合和简化数据：将 DWS 层中多表、多主题域的数据聚合成简单易用的表或视图
- 面向业务应用：通过设计宽表或高性能视图，直接支持具体的业务场景和报表需求
- 数据分发与集成：为前端的 BI 工具、报表系统或 API 服务提供高效的查询接口
- 轻量化与高性能：尽量减少数据量，保留业务最关心的关键指标
创建 ADS 层表
-- 会员留存数
drop table if exists ads.ads_member_retention_count;
create table ads.ads_member_retention_count
(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数'
) COMMENT '会员留存数'
partitioned by(dt string)
row format delimited fields terminated by ',';
-- 会员留存率
drop table if exists ads.ads_member_retention_rate;
create table ads.ads_member_retention_rate
(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数',
`new_mid_count` bigint comment '当日会员新增数',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '会员留存率'
partitioned by(dt string)
row format delimited fields terminated by ',';加载 ADS 层数据脚本
vim /opt/wzk/hive/ads_load_member_retention.sh写入内容：
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table ads.ads_member_retention_count
partition (dt='$do_date')
select add_date, retention_date,
count(*) retention_count
from dws.dws_member_retention_day
where dt='$do_date'
group by add_date, retention_date;
insert overwrite table ads.ads_member_retention_rate
partition (dt='$do_date')
select t1.add_date,
t1.retention_day,
t1.retention_count,
t2.cnt,
t1.retention_count/t2.cnt*100
from ads.ads_member_retention_count t1 join
ads.ads_new_member_cnt t2 on t1.dt=t2.dt
where t1.dt='$do_date';
"
hive -e "$sql"重要备注：最后一条 SQL 的连线条件应该为：t1.add_date=t2.dt
DWS 与 ADS 的关系
- DWS 是 ADS 的上游：DWS 提供的标准化、可复用的数据服务是 ADS 构建的基础
- 功能定位不同：DWS 更偏向数据整合与标准化，而 ADS 更关注业务需求的个性化满足
- 数据粒度：DWS 的数据粒度更细，ADS 通常是基于 DWS 的聚合和过滤，面向特定需求输出
错误速查
| 症状 | 根因 | 定位 | 修复 |
|---|---|---|---|
| ADS 留存率算不出来/留存率异常偏大偏小 | 留存率 SQL join 条件写错，把 t1.dt=t2.dt 误当新增日对齐 | 对比 ads_member_retention_count 的 add_date 与 ads_new_member_cnt 的 dt 是否一致 | 将连线改为 t1.add_date = t2.dt |
| DWS 留存明细数据异常膨胀 | dws_member_add_day 与 start_day 可能存在同一 device_id 多条记录，join 产生倍增 | 检查两张明细表在 dt 分区内 device_id 是否唯一 | 在 join 前做去重/主键约束 |
| 留存天数统计缺失（例如只算到 3 日） | 脚本写死 union all 1/2/3 | 看 dws_load_member_retention_day.sh 中 union 段 | 抽象为 N 日留存：用维表/数字表生成 retention_day |
| 留存口径偏差（跨设备、跨账号） | 用 device_id 作为主键，会员维度可能应以 uid 为准 | 对比业务定义 | 明确口径：若以 uid 留存，join key 改为 uid |
| 留存率小数精度/百分比显示不正确 | retention_count/t2.cnt*100 可能触发整除或精度不足 | 看 Hive 中表达式类型推断与最终 retention_ratio | 使用 cast：cast(t1.retention_count as decimal(18,4))/cast(t2.cnt as decimal(18,4))*100 |
| 分区覆盖/数据丢失 | insert overwrite 覆盖分区；脚本默认 do_date=-1，误跑会覆盖历史 | 看 insert overwrite + dt=‘$do_date’ | 保留 overwrite 但加跑批保护：参数必填或写保护 |
| 临时表重复创建影响性能 | 每天 drop/create tmp 表，且 union all 三次扫描 start_day/add_day | explain/执行日志观察扫描次数与 shuffle | 改为一次扫描：先取 do_date 启动集合，再与 add_day 通过 case when 计算 retention_day |
| Parquet 与行格式表混用导致性能不一致 | DWS 用 parquet，ADS 用 textfile | 观察 ADS 查询性能、压缩率、文件数 | ADS 也用 parquet/orc |