业务背景
1. 通过使用数仓建模的方式进行数据加工处理，分析某个时间段内发生的 100万条用户消费行为，业务侧需要尝试从数据中提取出有价值的业务建议。
2. 平台增长业务团队需要统计用户在平台的操作行为路径，判断用户在各个节点的流失率情况，为用户运营提供参考。
3. 平台增长业务团队需要对用户进行分层分类管理，便于监测、提升用户活跃度。
4. 商家运营业务团队需要统计各个商品的流量情况，为平台的店铺提供日常运营数据。数仓架构
ods 层数据
这部分的数据主要有两个来源：第一个是来自用户系统的用户基础信息数据，第二个是来自平台日志系统的用户流量数据。核心表的数据结构
用户基础信息表
DROP TABLE IF EXISTS ods_user_userinfo;
CREATE TABLE IF NOT EXISTS ods_user_userinfo (
userid STRING COMMENT '用户 ID',
username STRING COMMENT '用户名称',
userpassword STRING COMMENT '密码',
sex INT COMMENT '性别',
usermoney INT COMMENT '钱包',
frozenmoney INT COMMENT '近一个月的花费的总的金额',
addressid STRING COMMENT '用户地址 ID，0 表示没有获取地址',
regtime STRING COMMENT '注册时间',
lastlogin STRING COMMENT '最后登录时间',
lasttime string COMMENT '系统下载最后时间'
) COMMENT '用户表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '#' STORED AS textfile;用户行为记录表
DROP TABLE IF EXISTS ods_userbehavior;
CREATE TABLE IF NOT EXISTS ods_userbehavior (
user_id STRING COMMENT '用户标识',
item_id STRING COMMENT '商品标识',
category_id STRING COMMENT '商品分类标识',
type STRING COMMENT '用户对商品的行为类型,浏览、收藏、加购物车、购买，对应取值分别是 1、2、3、4',
action_time STRING COMMENT '行为时间'
) COMMENT '用户行为表' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS textfile;数据探查
--1.查看用户量
select
sex
,count(userid) as user_cnt
,count(userid) as user_cnt1
from ods_commerce.ods_user_userinfo
group by sex
;
--2.查看用户行为表的分区情况
show partitions ods_commerce.ods_userbehavior
--3.查看用户行为表的枚举值分布
select type,count(*) as pv,count(distinct user_id) as uv
from ods_commerce.ods_userbehavior
group by type
;dwd 层数据
核心表的数据结构
用户表
USE dwd_commerce;
DROP TABLE IF EXISTS dwd_user_userinfo;
CREATE TABLE IF NOT EXISTS dwd_user_userinfo (
user_id STRING COMMENT '用户 ID'
,user_name STRING COMMENT '用户名称'
,sex INT COMMENT '性别'
,user_money decimal(20,4) COMMENT '钱包'
,frozen_money decimal(20,4) COMMENT '近一个月的花费的总的金额'
,address_id STRING COMMENT '用户地址 ID，0 表示没有获取地址'
,reg_time STRING COMMENT '注册时间'
,last_login STRING COMMENT '最后登录时间'
) COMMENT '用户表' STORED AS ORC;
用户行为明细表
DROP TABLE IF EXISTS dwd_commerce.dwd_user_behavior_detail_d;
CREATE TABLE IF NOT EXISTS dwd_commerce.dwd_user_behavior_detail_d (
user_id STRING COMMENT '用户标识'
,item_id STRING COMMENT '商品标识'
,category_id STRING COMMENT '商品分类标识'
,type STRING COMMENT '用户对商品的行为类型,浏览、收藏、加购物车、购买，对应取值分别是 1、2、3、4'
,action_time STRING COMMENT '行为时间'
)
COMMENT '用户行为明细表'
PARTITIONED BY (dt string comment '日期')
STORED AS orc;
dws 层数据
用户行为汇总表
--1.用户粒度的汇总模型
DROP TABLE IF EXISTS dws_commerce.dws_user_behavior_sum_d;
CREATE TABLE IF NOT EXISTS dws_commerce.dws_user_behavior_sum_d (
user_id STRING COMMENT '用户标识'
,view_cnt BIGINT COMMENT '浏览次数'
,fav_cnt BIGINT COMMENT '收藏次数'
,cart_cnt BIGINT COMMENT '加购物车次数'
,buy_cnt BIGINT COMMENT '购买次数'
)
COMMENT '用户行为汇总表(用户粒度)'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;用户行为指标统计表
--2.日期维度的汇总模型,基于用户粒度的向上汇总
DROP TABLE IF EXISTS dws_commerce.dws_user_behavior_analysis_d;
CREATE TABLE IF NOT EXISTS dws_commerce.dws_user_behavior_analysis_d (
view_cnt BIGINT COMMENT '浏览次数'
,fav_cnt BIGINT COMMENT '收藏次数'
,cart_cnt BIGINT COMMENT '加购物车次数'
,buy_cnt BIGINT COMMENT '购买次数'
,view_uv BIGINT COMMENT '浏览人数'
,fav_uv BIGINT COMMENT '收藏人数'
,cart_uv BIGINT COMMENT '加购物车人数'
,buy_uv BIGINT COMMENT '购买人数'
)
COMMENT '用户行为指标统计表'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;用户商品指标统计表
--3. user_id*item_id 粒度的用户行为统计数据
DROP TABLE IF EXISTS dws_commerce.dws_user_goods_sum_d;
CREATE TABLE IF NOT EXISTS dws_commerce.dws_user_goods_sum_d (
user_id STRING COMMENT '用户标识'
,goods_id STRING COMMENT '商品标识'
,goods_name STRING COMMENT '商品名称'
,category_id STRING COMMENT '商品分类标识'
,view_cnt BIGINT COMMENT '浏览次数'
,fav_cnt BIGINT COMMENT '收藏次数'
,cart_cnt BIGINT COMMENT '加购物车次数'
,buy_cnt BIGINT COMMENT '购买次数'
)
COMMENT '用户商品指标统计表'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;ads 层数据
用户行为业务分析表
-- 场景一：业务指标分析
DROP TABLE IF EXISTS ads_commerce.ads_user_behavior_analysis_d;
CREATE TABLE IF NOT EXISTS ads_commerce.ads_user_behavior_analysis_d (
avg_view_cnt BIGINT COMMENT '人均浏览次数'
,avg_fav_cnt BIGINT COMMENT '人均收藏次数'
,avg_cart_cnt BIGINT COMMENT '人均加购物车次数'
,avg_buy_cnt BIGINT COMMENT '人均购买次数'
)
COMMENT '用户行为业务分析表'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;用户跳失率统计表
--场景 2：跳失率:在 11 月 25 日至 12 月 3 日期间只有浏览行为的用户/总用户数 OK
DROP TABLE IF EXISTS ads_commerce.ads_user_lost_rate_d;
CREATE TABLE IF NOT EXISTS ads_commerce.ads_user_lost_rate_d (
lost_rate decimal(20,4) COMMENT '跳失率'
)
COMMENT '用户跳失率统计表'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;商品类目运营排名表
--场景 3：业务指标分析
--获取每个商品类目浏览量排名前 10 的商品、以及他们的浏览次数
DROP TABLE IF EXISTS ads_commerce.ads_goods_category_analysis_d;
CREATE TABLE IF NOT EXISTS ads_commerce.ads_goods_category_analysis_d (
category_id STRING COMMENT '商品类别 ID'
,goods_id STRING COMMENT '商品标识'
,goods_name STRING COMMENT '商品名称'
,view_cnt BIGINT COMMENT '浏览次数'
,view_rnk BIGINT COMMENT '浏览排名'
)
COMMENT '商品类目运营排名表'
PARTITIONED BY (dt string comment '日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS orc;用户分层表
--场景 4：用户分层 这里需要注意 max_dd 的字段类型
DROP TABLE IF EXISTS ads_commerce.ads_user_behavior_level_full;
CREATE TABLE IF NOT EXISTS ads_commerce.ads_user_behavior_level_full (
user_id STRING COMMENT '用户 ID'
,max_dd STRING COMMENT '最近访问日期'
,min_dd STRING COMMENT '最早访问日期'
,diff BIGINT COMMENT '浏览间隔天数'
,r_score BIGINT COMMENT '用户分层'
)
COMMENT '用户分层表'
STORED AS orc;