离线数据仓库

1. 数据仓库本质

1. 对数据的统计分析，存储的数据为中间计算结果

2. 数据仓库建模方法论

1. ER模型（业务数据库设计要点）

1. 遵循范式

1. 第一范式（1NF) 属性不可分割

2. 第二范式 (2NF) 不能存在“函数部分依赖”

3. 第三范式 (3NF) 不能存在传递函数依赖

4. 巴斯-科德范式 (BCNF)

5. 第四范式 (4NF)

6. 第五范式 (5NF)

b.维度模型（数据仓库设计要点）

3. 维度建模理论

1. 维度表设计要点

1. 确认维度（表）

如果某些维度表的维度属性很少，例如只有一个**名称，则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中，这个操作称为维度退化。

2. 确认主维度表和相关维度表

3. 确认维度属性

1. 尽可能生成丰富的维度属性

2. 尽量不要使用编码，而使用明确的文字说明，一般可以编码和文字共存

3. 尽量沉淀出通用的维度属性

星型模型、雪花模型

b.维度变化

全量快照表

离线数据仓库的计算周期通常为每天一次，所以可以每天保存一份全量的维度数据。这种方式的优点和缺点都很明显。

优点是简单而有效，开发和维护成本低，且方便理解和使用。

缺点是浪费存储空间，尤其是当数据的变化比例比较低时。

拉链表：记录每条消息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期（生效开始日期->生效结束日期）

拉链表适用于，数据会发生变化，但是变化频率并不高的维度

4. 数据仓库设计

ODS Operation Data Store 原始数据层

DWD Data Warehouse Detail 明细数据层

DIM Dimension 公共维度层

DWS Data Warehouse Summary 汇总数据层

ADS Application Data Service 数据应用层

5. ODS层（异构数据的融合）

1. 设计要点：

1. ODS层不会进行统计分析，数据格式不会发生改变，数据格式尽量贴合原始数据的格式（行式存储）

2. ODS层的数据是非常多的，因为不断同步业务数据库和日志服务器的数据(资源不够)，而且不会删除和备份，所以压缩格式选择gzip（压缩比 高）

1. 压缩格式：

1. gzip : 压缩比非常高，hadoop默认支持，压缩和解压缩的效率不高。

2. lzo : 压缩比还行，hadoop默认不支持，压缩和解压缩的效率还行。

3. snappy : 压缩比不高，hadoop默认不支持，压缩和解压缩的效率非常高。

3. ODS层命名的规则：ods_表名_单分区增量全量标识（inc/full）

b.建表

分区 partition

主要用于过滤文件目录，数据仓库必须使用分区表

分区表的分区策略 : 以天为单位

列的类型

特殊类型

数组（集合）

Map（k-v集合）

value的类型要统一

key的个数不确定，属性值的类型确定

结构体（对象）

属性个数固定，属性值的类型不确定

6. DIM层（共通维度层）

1. 设计要点

1. DIM层中保存的表都是维度表，遵循维度建模理论（尽可能的丰富）

1. 分析维度的数据来源

1. 主维表 : 主要用于分析维度的业务表

2. 相关维表: 分析维度的业务表关联的表

2. DIM层的数据存储格式为orc列式存储+snappy压缩

3. DIM层表名的命名规范为dim_表名_全量表或者拉链表标识（full/zip）

7. DWD层（Data Warehouse Detail）

1. 设计要点

1. DWD层的设计根据是维度建模理论，该层存储维度模式的事实表

2. DWD层的数据存储格式为orc列式存储+snappy压缩

3. DWD层表名的命令规范为dwd_数据域_表名_单分区增量全量标识（inc/full）

2. 事实表：用于保存业务行为的表

统计数据的本质就是统计事情发生后的结果

设计表：

站在不同的角度，将事情描述清楚

必要的维度数据 + 可选的维度数据 + 度量值（结果）

表的分类

事务型事实表（95%）(inc)

原子性的业务行为（登录成功，注册成功，下单，退单，取消订单）

周期型快照事实表(full)

存量型数据指标（库存，余额）

累积型快照事实表

累积的就是业务行为

业务流程表：一张表中包含了一个业务流程中多个业务行为

8. DWS层（Data Warehouse Summary(汇总)）

1. 设计要点

1. DWS层的设计参考指标体系

2. DWS层的数据存储格式为orc列式存储+snappy压缩

3. DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期（1d/nd/td）

注意：1d表示最近1日，nd表示最近n日，td表示历史至今

9. ADS层（Application Data Service）

1. 设计要点

1. 统计结果的数据量不会很多，没有分区

2. 主要目的是为对外提供服务，所以不需要再统计分析，所以存储方式为行式存储，不用压缩

业务总结

---

1.拉链表（如何实现）

---

方法一：union all

1.先筛选出ods中昨天更新的最后一条数据（打标记筛选）

row_nuber() over(partition by data ,id order by ts desc)

2.dim中的表（dim_user_zip)与ods层中的表（ads_user_info_inc) union all

3.打标记

row_nuber()over(partition by id order by start desc)

4.进行判断

如果是2分在昨天-1的分区

如果是1分到‘9999-12-31’的分区

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

insert overwrite table dim_user_zippartition (dt)

select

`id` ,

`login_name` ,

`nick_name` ,

`name` ,

`phone_num` ,

`email` ,

`user_level` ,

`birthday` ,

`gender` ,

`create_time` ,

`operate_time` ,

`start_date` ,

if ( rn=2, date_sub('2022-06-09', 1), '9999-12-31' ) `end_date`,

if ( rn=2, date_sub('2022-06-09', 1), '9999-12-31' )

from (

select

*,

row_number() over ( partitionbyidorderbystart_datedesc) rn

from (

select

`id` ,

`login_name` ,

`nick_name` ,

`name` ,

`phone_num` ,

`email` ,

`user_level` ,

`birthday` ,

`gender` ,

`create_time` ,

`operate_time` ,

`start_date` ,

`end_date`

fromdim_user_zip

wheredt='9999-12-31'

unionall

select

*

from (

select

data.`id` ,

data.`login_name` ,

data.`nick_name` ,

data.`name` ,

data.`phone_num` ,

data.`email` ,

data.`user_level` ,

data.`birthday` ,

data.`gender` ,

data.`create_time` ,

data.`operate_time` ,

'2022-06-09'start_date,

'9999-12-31'end_date,

row_number() over (partitionbydata.idorderbytsdesc) rn1

fromods_user_info_inc

wheredt='2022-06-09'

) t0wherern1=1

) t

) t1

方法二：full join

1.先筛选出ods中昨天更新的最后一条数据（打标记筛选）

row_nuber() over (partition by data ,id order by ts desc)

2.full join 得到emp表

3.判断 emp表（new_id is not null,new _id,old_id) 得到t1表（放入9999-12-31分区）

4.过滤 old_id not null and new_id is not null 得到t2表（放入到昨天-1天分区）

5.t1 union all t2 然后分区

sethive.exec.dynamic.partition.mode=nonstrict;

with

tmpas

(

select

old.idold_id,

old.login_nameold_login_name,

old.nick_nameold_nick_name,

old.nameold_name,

old.phone_numold_phone_num,

old.emailold_email,

old.user_levelold_user_level,

old.birthdayold_birthday,

old.genderold_gender,

old.create_timeold_create_time,

old.operate_timeold_operate_time,

old.start_dateold_start_date,

old.end_dateold_end_date,

new.idnew_id,

new.login_namenew_login_name,

new.nick_namenew_nick_name,

new.namenew_name,

new.phone_numnew_phone_num,

new.emailnew_email,

new.user_levelnew_user_level,

new.birthdaynew_birthday,

new.gendernew_gender,

new.create_timenew_create_time,

new.operate_timenew_operate_time,

new.start_datenew_start_date,

new.end_datenew_end_date

from

(

select

id,

login_name,

nick_name,

name,

phone_num,

email,

user_level,

birthday,

gender,

create_time,

operate_time,

start_date,

end_date

fromdim_user_zip

wheredt='9999-12-31'

)old

fullouterjoin

(

select

id,

login_name,

nick_name,

md5(name) name,

md5(phone_num) phone_num,

md5(email) email,

user_level,

birthday,

gender,

create_time,

operate_time,

'2022-06-09'start_date,

'9999-12-31'end_date

from

(

select

data.id,

data.login_name,

data.nick_name,

data.name,

data.phone_num,

data.email,

data.user_level,

data.birthday,

data.gender,

data.create_time,

data.operate_time,

row_number() over (partitionbydata.idorderbytsdesc) rn

fromods_user_info_inc

wheredt='2022-06-09'

)t1

wherern=1

)new

onold.id=new.id

)

insertoverwritetabledim_user_zippartition(dt)

select

if(new_idisnotnull,new_id,old_id),

if(new_idisnotnull,new_login_name,old_login_name),

if(new_idisnotnull,new_nick_name,old_nick_name),

if(new_idisnotnull,new_name,old_name),

if(new_idisnotnull,new_phone_num,old_phone_num),

if(new_idisnotnull,new_email,old_email),

if(new_idisnotnull,new_user_level,old_user_level),

if(new_idisnotnull,new_birthday,old_birthday),

if(new_idisnotnull,new_gender,old_gender),

if(new_idisnotnull,new_create_time,old_create_time),

if(new_idisnotnull,new_operate_time,old_operate_time),

if(new_idisnotnull,new_start_date,old_start_date),

if(new_idisnotnull,new_end_date,old_end_date),

if(new_idisnotnull,new_end_date,old_end_date) dt

fromtmp

unionall

select

old_id,

old_login_name,

old_nick_name,

old_name,

old_phone_num,

old_email,

old_user_level,

old_birthday,

old_gender,

old_create_time,

old_operate_time,

old_start_date,

cast(date_add('2022-06-09',-1) asstring) old_end_date,

cast(date_add('2022-06-09',-1) asstring) dt

fromtmp

whereold_idisnotnull

andnew_idisnotnull;

2.最近7天连续三天下单（登录）用户数

---

1. 指标拆建

建模

在ods层需要下单（登录）的数据

在dwd中创建下单（登录）事务事实表

在dim层没有维度

在dws（轻度聚合）创建1d下单表

在ads写逻辑：

打标记：获取后两行下单时间

作差：后两行的数据-当前行的数据

筛选：过滤出减的值等于二

insertoverwritetableads_order_continuously_user_count

select*fromads_order_continuously_user_count

union

select

'2022-06-08',

7,

count(distinct(user_id))

from

(

select

user_id,

datediff(lead(dt,2,'9999-12-31') over(partitionbyuser_idorderbydt),dt) diff

fromdws_trade_user_order_1d

wheredt>=date_add('2022-06-08',-6)

)t1

wherediff=2;

总结：连续问题解决方案：

方法一：获取后行日期相减，在过滤出连续的天数

方法二：先进行排序，日期减排序的序号，在分组

select

t3.id,

t3.days+1aslianxu_day

from (

select

t2.id,

datediff(max(t2.dt),min(t2.last_dt)) as days

from (

select

t1.id,

t1.dt,

t1.last_dt,

datediff( t1.dt,t1.last_dt) chazhi

from (

select

id,

dt,

lag(dt,1,'1970-01-01') over(partitionbyidorderbydt ) last_dt

fromgame_user

) t1

) t2

wheret2.chazhi<=2

groupbyt2.id

) t3;

方法三：使用二进制的方式

3.各个省份的交易额

---

为什么会选择这个指标呢：因为这个会产生数据倾斜，需要解释怎么避免数据倾斜

指标拆建：

建模

ods层需要下单和省份相关的数据

dwd层需要下单事务实时表

dim需要省份维度的数据

dws需要下单和省份join出1/7/30d轻度聚合

ads union dws中的表

insertoverwritetableads_order_by_province

select*fromads_order_by_province

union

select

'2022-06-08'dt,

1recent_days,

province_id,

province_name,

area_code,

iso_code,

iso_3166_2,

order_count_1d,

order_total_amount_1d

fromdws_trade_province_order_1d

wheredt='2022-06-08'

union

select

'2022-06-08'dt,

recent_days,

province_id,

province_name,

area_code,

iso_code,

iso_3166_2,

caserecent_days

when7thenorder_count_7d

when30thenorder_count_30d

endorder_count,

caserecent_days

when7thenorder_total_amount_7d

when30thenorder_total_amount_30d

endorder_total_amount

fromdws_trade_province_order_ndlateralviewexplode(array(7,30)) tmpasrecent_days

wheredt='2022-06-08';

4.加购到支付的一个平均使用时长

---

ods层需要下单和支付相关的数据

dwd层需要下单事务实时表和支付事务事实表

dim没有

dws需要下单1d表和支付1d表

ads 下单和支付join

5.路径问题（桑基图）

---

ods层需要日志行为数据

dwd层需要流量域页面浏览事务事实表

dim没有

ads 先打标记（后一个页面以及排序）（进行group by 一页页面+后一页页面）然后在count

insertoverwritetableads_page_path

select*fromads_page_path

union

select

'2022-06-08'dt,

source,

nvl(target,'null'),

count(*) path_count

from

(

select

concat('step-',rn,':',page_id) source,

concat('step-',rn+1,':',next_page_id) target

from

(

select

page_id,

lead(page_id,1,null) over(partitionbysession_idorderbyview_time) next_page_id,

row_number() over (partitionbysession_idorderbyview_time) rn

fromdwd_traffic_page_view_inc

wheredt='2022-06-08'

)t1

)t2

groupbysource,target;

6.用户留存率

---

留存分析一般包含新增留存和活跃留存分析。

新增留存分析是分析某天的新增用户中，有多少人有后续的活跃行为。活跃留存分析是分析某天的活跃用户中，有多少人有后续的活跃行为。

留存分析是衡量产品对用户价值高低的重要指标。

此处要求统计新增留存率，新增留存率具体是指留存用户数与新增用户数的比值，例如2022-06-08新增100个用户，1日之后（2022-06-09）这100人中有80个人活跃了，那2022-06-08的1日留存数则为80，2022-06-08的1日留存率则为80%。

用户域用户注册事务事实表

用户域用户粒度登录历史至今汇总表

insertoverwritetableads_user_retention

select*fromads_user_retention

union

select

'2022-06-08'dt,

login_date_firstcreate_date,

datediff('2022-06-08',login_date_first) retention_day,

sum(if(login_date_last='2022-06-08',1,0)) retention_count,

count(*) new_user_count,

cast(sum(if(login_date_last='2022-06-08',1,0))/count(*)*100asdecimal(16,2)) retention_rate

from

(

select

user_id,

date_idlogin_date_first

fromdwd_user_register_inc

wheredt>=date_add('2022-06-08',-7)

anddt<'2022-06-08'

)t1

join

(

select

user_id,

login_date_last

fromdws_user_user_login_td

wheredt='2022-06-08'

)t2

ont1.user_id=t2.user_id

groupbylogin_date_first;

7.离线指标：

---

沉默用户占比：只在当天启动过，且启动时间在一周前

流量主题：

各渠道流量统计

用户主题：

用户行为漏斗分析

最近7/30日品牌复购率

新增下单用户统计

商品主题：

最近30日各品牌复购率

各品牌商品下单统计

各品类商品下单统计

交易主题：

下单到支付时间间隔平均值

各省份交易统计

优惠券主题：

优惠券使用统计

8.dws有哪些表

---

最近1日汇总表

交易域用户商品粒度订单最近1日汇总表

工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表

互动域商品粒度收藏商品最近1日汇总表

流量域会话粒度页面浏览最近1日汇总表

最近n日汇总表

交易域用户商品粒度订单最近n日汇总表

历史至今汇总表

交易域用户粒度订单历史至今汇总表

9.dwd有哪些表

---

交易域加购事务事实表

交易域下单事务事实表

交易域支付成功事务事实表

工具域优惠券使用(支付)事务事实表

互动域收藏商品事务事实表

10.dim有哪些表

---

商品维度表

优惠券维度表

活动维度表

地区维度表

用户维度表