【黑马甄选离线数仓day08_会员主题域开发】
1. 会员主题域需求说明
1.1 各类会员数量统计
说明：公司为了对不同会员进行不同的营销策略，对各类会员的数量都非常敏感，比如注册会员、消费会员、复购会员、活跃会员、沉睡会员。不仅需要看新增数量还要看累积数量。
指标：新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度：时间
粒度：时间维度（天、周、月）
涉及库：sale、member
涉及表：
-
事实表：
-
a. sale：dwm_sell_o2o_order_i
-
b. member：member_union、user_point_log_detailed、store_amount_record
-
-
维度表：dwd_dim_date_f、dwd_dim_store_i
1.2 门店会员分析
说明：会员对于每个门店来说，都是非常重要的资源，所以每个门店需要看到会员的分析数据。包括注册情况、充值和余额情况、消费情况等。
指标：门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数、余额消费单量、余额支付金额、余额消费金额、会员消费人数、会员消费单量、会员消费金额、会员首单人数、会员首单订单量、会员首单销售额、会员非首单人数、会员非首单订单量、会员非首单销售额
维度：时间
粒度：时间维度（天、周、月）
涉及库：sale、member
涉及表：
-
事实表：
-
a. sale：dwm_sell_o2o_order_i
-
b. member：member_union、user_point_log_detailed、store_amount_record
-
-
维度表：dwd_dim_date_f、dwd_dim_store_i
1.3 其他需求分析
说明：除了分析以上两个需求外，还会分析一些零散的需求，比如会员首次充值、门店新老会员消费、会员复购统计、会员贡献等。从各种角度来分析会员的一些指标。
需求一: 会员首次充值(统计每个会员首次充值的时间, 交易单ID以及对应门店和充值金额) 需求二: 门店新老会员消费(统计每个门店每个月新会员、老会员、全部会员、非会员的数量、消费金额、消费单量(新会员指的首次消费后30天内, 老会员指的首次消费后大于30天)) 需求三: 会员复购统计(留存)(统计的指标为统计日期用户量、一日后用户量、二日后用户量、三日后用户量、四日后用户量、五日后用户量、六日后用户量) 需求四: 会员贡献(统计各个会员每天在各个门店消费单量、消费金额、消费成本、线上订单量、线上消费金额、线上消费成本、线下订单量、线下消费金额、线下消费成本)
2. 会员主题建设
2.1 ADS层建设
各类会员数量统计分析
维度指标:
指标：新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度: 时间维度（天、周、月）
涉及ADS表:
门店会员分类月表 和 门店会员分类周表
表字段的组成: 维度字段 + 指标结果字段
建表语句:
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_classify_week_i(
trade_date STRING COMMENT '周一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
reg_num_add BIGINT COMMENT '新增注册会员数',
reg_num_sum BIGINT COMMENT '累计注册会员数',
consume_num_add BIGINT COMMENT '新增消费会员数',
consume_num_sum BIGINT COMMENT '累计消费会员数',
repurchase_num_add BIGINT COMMENT '新增复购会员数',
repurchase_num_sum BIGINT COMMENT '累计复购会员数',
active_member_num BIGINT COMMENT '活跃会员数',
sleep_member_num BIGINT COMMENT '沉睡会员数',
sale_amount_bind DECIMAL(27, 2) COMMENT '会员消费金额'
)
comment '门店会员分类周表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_classify_month_i(
trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
reg_num_add BIGINT COMMENT '新增注册会员数',
reg_num_sum BIGINT COMMENT '累计注册会员数',
consume_num_add BIGINT COMMENT '新增消费会员数',
consume_num_sum BIGINT COMMENT '累计消费会员数',
repurchase_num_add BIGINT COMMENT '新增复购会员数',
repurchase_num_sum BIGINT COMMENT '累计复购会员数',
active_member_num BIGINT COMMENT '活跃会员数',
sleep_member_num BIGINT COMMENT '沉睡会员数',
sale_amount_bind DECIMAL(27, 2) COMMENT '会员消费金额'
)
comment '门店会员分类月表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');门店会员分析
维度指标:
指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额
维度: 时间维度（天、周、月）
涉及表:
门店会员统计周表 和 门店会员统计月表
涉及表字段: 维度字段 + 指标结果字段
建表语句
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_statistics_week_i(
trade_date STRING COMMENT '周一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
store_sale_amount DECIMAL(27, 2) COMMENT '门店销售金额',
store_orders_number BIGINT COMMENT '门店总订单量',
register_member_num BIGINT COMMENT '当日注册人数',
register_member_num_all BIGINT COMMENT '累计注册会员数',
register_recharge_num BIGINT COMMENT '当日注册且充值会员数',
rg_rc_td_num BIGINT COMMENT '当日注册且充值且消费会员数',
register_trade_num BIGINT COMMENT '当日注册且消费会员数',
recharge_member_num BIGINT COMMENT '充值会员数',
recharge_amount DECIMAL(27, 2) COMMENT '充值金额',
recharge_amount_all DECIMAL(27, 2) COMMENT '累计会员充值金额',
remain_member_num BIGINT COMMENT '当周最后一天有余额的会员人数',
remain_member_amount DECIMAL(27, 2) COMMENT '当周最后一天会员余额',
balance_member_num BIGINT COMMENT '余额消费人数',
balance_member_order_num BIGINT COMMENT '余额消费单量',
balance_pay_amount DECIMAL(27, 2) COMMENT '余额支付金额',
balance_member_amount DECIMAL(27, 2) COMMENT '余额消费金额',
member_num BIGINT COMMENT '会员消费人数',
member_order_num BIGINT COMMENT '会员消费单量',
member_amount DECIMAL(27, 2) COMMENT '会员消费金额',
member_first_num BIGINT COMMENT '会员首单人数',
member_first_order_num BIGINT COMMENT '会员首单订单量',
member_first_amount DECIMAL(27, 2) COMMENT '会员首单销售额',
member_nofirst_num BIGINT COMMENT '会员非首单人数',
member_nofirst_order_num BIGINT COMMENT '会员非首单订单量',
member_nofirst_amount DECIMAL(27, 2) COMMENT '会员非首单销售额'
)
comment '门店会员统计周表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_statistics_month_i(
trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
store_sale_amount DECIMAL(27, 2) COMMENT '门店销售金额',
store_orders_number BIGINT COMMENT '门店总订单量',
register_member_num BIGINT COMMENT '当日注册人数',
register_member_num_all BIGINT COMMENT '累计注册会员数',
register_recharge_num BIGINT COMMENT '当日注册且充值会员数',
rg_rc_td_num BIGINT COMMENT '当日注册且充值且消费会员数',
register_trade_num BIGINT COMMENT '当日注册且消费会员数',
recharge_member_num BIGINT COMMENT '充值会员数',
recharge_amount DECIMAL(27, 2) COMMENT '充值金额',
recharge_amount_all DECIMAL(27, 2) COMMENT '累计会员充值金额',
remain_member_num BIGINT COMMENT '当月最后一天有余额的会员人数',
remain_member_amount DECIMAL(27, 2) COMMENT '当月最后一天会员余额',
balance_member_num BIGINT COMMENT '余额消费人数',
balance_member_order_num BIGINT COMMENT '余额消费单量',
balance_pay_amount DECIMAL(27, 2) COMMENT '余额支付金额',
balance_member_amount DECIMAL(27, 2) COMMENT '余额消费金额',
member_num BIGINT COMMENT '会员消费人数',
member_order_num BIGINT COMMENT '会员消费单量',
member_amount DECIMAL(27, 2) COMMENT '会员消费金额',
member_first_num BIGINT COMMENT '会员首单人数',
member_first_order_num BIGINT COMMENT '会员首单订单量',
member_first_amount DECIMAL(27, 2) COMMENT '会员首单销售额',
member_nofirst_num BIGINT COMMENT '会员非首单人数（非去重）',
member_nofirst_order_num BIGINT COMMENT '会员非首单订单量',
member_nofirst_amount DECIMAL(27, 2) COMMENT '会员非首单销售额'
)
comment '门店会员统计月表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');其他指标需求
维度指标
需求一: 会员首次充值(统计每个会员首次充值的时间, 交易单ID以及对应门店和充值金额)
需求二: 门店新老会员消费(统计每个门店每个月新会员、老会员、全部会员、非会员的数量、消费金额、消费单量(新会员指的首次消费后30天内, 老会员指的首次消费后大于30天))
需求三: 会员复购统计(留存)(统计的指标为统计日期用户量、一日后用户量、二日后用户量、三日后用户量、四日后用户量、五日后用户量、六日后用户量)
需求四: 会员贡献(统计各个会员每天在各个门店消费单量、消费金额、消费成本、线上订单量、线上消费金额、线上消费成本、线下订单量、线下消费金额、线下消费成本)
涉及表:
会员首次充值表 和 门店新老会员消费月表 和 会员复购统计天表 以及 会员贡献天表
建设语句:
CREATE TABLE IF NOT EXISTS ads.ads_mem_member_first_recharge_i(
trade_date_time STRING COMMENT '交易时间',
trade_date STRING COMMENT '日期',
trade_order_id STRING COMMENT '对应的交易单id',
zt_id BIGINT COMMENT '中台 会员id',
store_no STRING COMMENT '门店编号',
city_id BIGINT COMMENT '城市ID',
recharge_amount DECIMAL(27, 2) COMMENT '充值金额'
)
comment '会员首次充值表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_new_old_member_month_i(
trade_date STRING COMMENT '月一时间',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
member_type BIGINT COMMENT '会员类型:1新会员,2老会员,3会员,4非会员',
member_num BIGINT COMMENT '消费会员数',
sale_amount DECIMAL(27, 2) COMMENT '消费金额',
order_num BIGINT COMMENT '消费单量'
)
comment '门店新老会员消费月表'
partitioned by (dt STRING COMMENT '消费日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS ads.ads_mem_repurchase_day_i(
trade_date STRING COMMENT '统计时间',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
member_count BIGINT COMMENT '统计日期用户量',
next_member_count_1 BIGINT COMMENT '一日后用户量',
next_member_count_2 BIGINT COMMENT '二日后用户量',
next_member_count_3 BIGINT COMMENT '三日后用户量',
next_member_count_4 BIGINT COMMENT '四日后用户量',
next_member_count_5 BIGINT COMMENT '五日后用户量',
next_member_count_6 BIGINT COMMENT '六日后用户量'
)
comment '会员复购统计天表'
partitioned by (dt STRING COMMENT '消费日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS ads.ads_mem_contribution_day_i(
trade_date STRING COMMENT '统计时间',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
zt_id BIGINT COMMENT '会员id',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
consume_times BIGINT COMMENT '消费单量',
consume_amount DECIMAL(27, 2) COMMENT '消费金额',
consume_cost DECIMAL(27, 2) COMMENT '消费成本',
online_consume_times BIGINT COMMENT '线上订单量',
online_consume_amount DECIMAL(27, 2) COMMENT '线上消费金额',
online_consume_cost DECIMAL(27, 2) COMMENT '线上消费成本',
offline_consume_times BIGINT COMMENT '线下订单量',
offline_consume_amount DECIMAL(27, 2) COMMENT '线下消费金额',
offline_consume_cost DECIMAL(27, 2) COMMENT '线下消费成本'
)
comment '会员贡献天表'
partitioned by (dt STRING COMMENT '消费日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');2.2 DWS层建设
数仓建设分析: 采用自下而上、基于需求、逐层分析出每一层工作内容 DWS层: 数据服务层 (进行最细粒度的统计分析工作)
各类会员数量统计分析
维度指标:
指标：新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额 维度: 时间维度（天、周、月） 涉及表: 门店会员分类天表 表字段的组成: 维度字段 + 指标结果字段
建表语句:
CREATE TABLE IF NOT EXISTS dws.dws_mem_store_member_classify_day_i(
trade_date STRING COMMENT '统计时间',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
reg_num_add BIGINT COMMENT '新增注册会员数',
reg_num_sum BIGINT COMMENT '累计注册会员数',
consume_num_add BIGINT COMMENT '新增消费会员数',
consume_num_sum BIGINT COMMENT '累计消费会员数',
repurchase_num_add BIGINT COMMENT '新增复购会员数',
repurchase_num_sum BIGINT COMMENT '累计复购会员数',
active_member_num BIGINT COMMENT '活跃会员数',
sleep_member_num BIGINT COMMENT '沉睡会员数',
sale_amount_bind DECIMAL(27, 2) COMMENT '会员消费金额'
)
comment '门店会员分类天表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');门店会员分析
维度指标:
指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额 维度: 时间维度（天、周、月） 涉及表: 门店会员统计天表 表字段的组成: 维度字段 + 指标结果字段
建表语句:
CREATE TABLE IF NOT EXISTS dws.dws_mem_store_member_statistics_day_i(
trade_date STRING COMMENT '统计时间',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
store_sale_amount DECIMAL(27, 2) COMMENT '门店销售金额',
store_orders_number BIGINT COMMENT '门店总订单量',
register_member_num BIGINT COMMENT '当日注册人数',
register_member_num_all BIGINT COMMENT '累计注册会员数',
register_recharge_num BIGINT COMMENT '当日注册且充值会员数',
rg_rc_td_num BIGINT COMMENT '当日注册且充值且消费会员数',
register_trade_num BIGINT COMMENT '当日注册且消费会员数',
recharge_member_num BIGINT COMMENT '充值会员数',
recharge_amount DECIMAL(27, 2) COMMENT '充值金额',
recharge_amount_all DECIMAL(27, 2) COMMENT '累计会员充值金额',
remain_member_num BIGINT COMMENT '当日有余额的会员人数',
remain_member_amount DECIMAL(27, 2) COMMENT '当日会员余额',
balance_member_num BIGINT COMMENT '余额消费人数',
balance_member_order_num BIGINT COMMENT '余额消费单量',
balance_pay_amount DECIMAL(27, 2) COMMENT '余额支付金额',
balance_member_amount DECIMAL(27, 2) COMMENT '余额消费金额',
member_num BIGINT COMMENT '会员消费人数',
member_order_num BIGINT COMMENT '会员消费单量',
member_amount DECIMAL(27, 2) COMMENT '会员消费金额',
member_first_num BIGINT COMMENT '会员首单人数',
member_first_order_num BIGINT COMMENT '会员首单订单量',
member_first_amount DECIMAL(27, 2) COMMENT '会员首单销售额',
member_nofirst_num BIGINT COMMENT '会员非首单人数',
member_nofirst_order_num BIGINT COMMENT '会员非首单订单量',
member_nofirst_amount DECIMAL(27, 2) COMMENT '会员非首单销售额'
)
comment '门店会员统计日表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');2.3 DWM层建设
DWM层(数仓中间层), 再此层我们主要进行细粒度轻度汇总工作, 以便于能够在DWS层更好的完成聚合计算
维度指标:
各类会员数量统计: 指标：新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额 维度: 时间维度（天、周、月） 门店会员分析: 指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额 维度: 时间维度（天、周、月） 说明: 由于各类会员数据统计分析和门店会员分析中, 有大量的指标存在一定的依赖关系, 所以在此处我们合并在一起进行分析, 向上抽取出一些公共的DWM层的数据表, 便于后续两个DWS层表数据的聚合统计, 本次主要涉及有四张DWM层表:会员销售订单表、会员首次消费表、会员第二次消费表、会员行为天表
建表语句:
CREATE TABLE IF NOT EXISTS dwm.dwm_mem_sell_order_i(
create_time STRING COMMENT '订单创建时间',
trade_date STRING COMMENT '交易日期',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
hourly BIGINT COMMENT '交易小时(0-23)',
quarter BIGINT COMMENT '刻钟:1.0-15,2.15-30,3.30-45,4.45-60',
quarters BIGINT COMMENT '刻钟数:hourly*4+quarters',
parent_order_no STRING COMMENT '父单订单号/源单号',
order_no STRING COMMENT '订单编号',
trade_type BIGINT COMMENT '结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易)',
source_type BIGINT COMMENT '交易来源1:线下POS;2:三方平台;3:传智鲜商城;4:黑马优选团;5:传智大客户;6:传智其他;7:黑马优选;8:优选海淘;9:优选大客户;10:优选POS;11:优选APP;12:优选H5;13:店长工具线下;14:店长工具线上;15:黑马其他',
source_type_name STRING COMMENT '交易来源名称',
sale_type BIGINT COMMENT '销售类型 1.实物,2.代客,3.优选小程序,4.离店,5.传智鲜小程序,6.第三方平台,7.其他,8.大客户',
is_online_order BIGINT COMMENT '是否为线上单:0否,1是',
member_type BIGINT COMMENT '会员类型:0非会员,1线上会员,2实体卡会员',
is_balance_consume BIGINT COMMENT '是否有余额支付:0否,1是',
order_type BIGINT COMMENT '配送类型（真正的订单类型由业务类型来决定）：1-及时送；2-隔日送；3-自提单；4-线下单',
express_type BIGINT COMMENT '配送方式：0-三方平台配送；1-自配送；2-快递；3-自提；4-线下',
store_no STRING COMMENT '店铺编码',
store_name STRING COMMENT '店铺名称',
store_sale_type BIGINT COMMENT '店铺销售类型',
store_type_code BIGINT COMMENT '分店类型',
worker_num BIGINT COMMENT '员工人数',
store_area DECIMAL(27, 2) COMMENT '门店面积',
city_id BIGINT COMMENT '城市ID',
city_name STRING COMMENT '城市名称',
region_code STRING COMMENT '区域编码',
region_name STRING COMMENT '区域名称',
is_day_clear BIGINT COMMENT '是否日清:0否,1是',
is_cancel BIGINT COMMENT '是否取消',
cancel_time STRING COMMENT '取消时间',
cancel_reason STRING COMMENT '取消原因',
last_update_time TIMESTAMP COMMENT '最新更新时间',
cashier_no STRING COMMENT '收银员编码',
cashier_name STRING COMMENT '收银员名称',
zt_id BIGINT COMMENT '中台ID',
member_id BIGINT COMMENT '会员ID',
card_no STRING COMMENT '卡号',
r_name STRING COMMENT '收货人姓名',
r_province STRING COMMENT '收货人省份',
r_city STRING COMMENT '收货人城市',
r_district STRING COMMENT '收货人区域',
is_tuan_head BIGINT COMMENT '是否为团长订单',
store_leader_id BIGINT COMMENT '团长id',
order_group_no STRING COMMENT '团单号',
settle_amount DECIMAL(27, 2) COMMENT '结算金额',
share_user_id BIGINT COMMENT '分享人用户ID',
commission_amount DECIMAL(27, 2) COMMENT '佣金',
order_total_amount DECIMAL(27, 2) COMMENT '订单总金额',
product_total_amount DECIMAL(27, 2) COMMENT '商品总金额（原价）',
pack_amount DECIMAL(27, 2) COMMENT '餐盒费/打包费',
delivery_amount DECIMAL(27, 2) COMMENT '配送费',
discount_amount DECIMAL(27, 2) COMMENT '订单优惠金额=商家承担优惠金额+平台补贴金额',
seller_discount_amount DECIMAL(27, 2) COMMENT '商家承担优惠金额',
platform_allowance_amount DECIMAL(27, 2) COMMENT '平台补贴金额',
real_paid_amount DECIMAL(27, 2) COMMENT '实付金额',
product_discount DECIMAL(27, 2) COMMENT '商品优惠金额',
real_product_amount DECIMAL(27, 2) COMMENT '商品实际金额',
round_amount DECIMAL(27, 2) COMMENT '舍分金额',
wechat_amount DECIMAL(27, 4) COMMENT '微信支付',
ali_pay_amount DECIMAL(27, 4) COMMENT '支付宝支付',
cash_amount DECIMAL(27, 4) COMMENT '现金支付',
balance_amount DECIMAL(27, 4) COMMENT '余额支付',
point_amount DECIMAL(27, 4) COMMENT '积分支付',
unionpay_amount DECIMAL(27, 4) COMMENT '银行支付',
member_card_amount DECIMAL(27, 4) COMMENT '线下实体卡支付',
gift_amount DECIMAL(27, 4) COMMENT '礼品卡支付',
czapi_amount DECIMAL(27, 4) COMMENT '传智支付',
other_pay_amount DECIMAL(27, 4) COMMENT '其他支付'
)
comment '会员销售订单表'
partitioned by (dt STRING COMMENT '销售日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS dwm.dwm_mem_first_buy_i(
zt_id BIGINT COMMENT '中台 会员id',
trade_date_time STRING COMMENT '首次消费时间',
trade_date STRING COMMENT '首次消费日期',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '消费门店',
sale_amount DECIMAL(27, 2) COMMENT '消费金额',
order_no STRING COMMENT '订单编号',
source_type BIGINT COMMENT '交易来源'
)
comment '会员首次消费表'
partitioned by (dt STRING COMMENT '消费日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS dwm.dwm_mem_second_buy_i(
zt_id BIGINT COMMENT '中台 会员id',
trade_date_time STRING COMMENT '第二次消费时间',
trade_date STRING COMMENT '第二次消费日期',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
store_no STRING COMMENT '消费门店',
sale_amount DECIMAL(27, 2) COMMENT '消费金额',
order_no STRING COMMENT '订单编号',
source_type BIGINT COMMENT '交易来源'
)
comment '会员第二次消费表'
partitioned by (dt STRING COMMENT '消费日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
CREATE TABLE IF NOT EXISTS dwm.dwm_mem_member_behavior_day_i(
trade_date STRING COMMENT '时间',
week_trade_date STRING COMMENT '周一日期',
month_trade_date STRING COMMENT '月一日期',
zt_id BIGINT COMMENT '中台 会员id',
bind_md STRING COMMENT '归属门店(绑定门店)',
reg_md STRING COMMENT '注册门店',
reg_time TIMESTAMP COMMENT '中台 注册时间',
is_register BIGINT COMMENT '当日是否注册',
is_recharge BIGINT COMMENT '当日是否充值',
recharge_times BIGINT COMMENT '充值次数，没有充值则为0',
recharge_amount DECIMAL(27, 2) COMMENT '充值金额，没有充值则为0',
is_consume BIGINT COMMENT '当日是否消费',
consume_times BIGINT COMMENT '消费次数，没有消费则为0',
consume_amount DECIMAL(27, 2) COMMENT '消费金额，没有消费则为0',
is_first_consume BIGINT COMMENT '当日是否首次消费',
first_consume_store STRING COMMENT '首次消费门店，没有则为null',
first_consume_amount DECIMAL(27, 2) COMMENT '首次消费金额，没有消费则为0',
is_balance_consume BIGINT COMMENT '当日是否余额消费',
balance_consume_times BIGINT COMMENT '余额消费次数，没有消费则为0',
balance_pay_amount DECIMAL(27, 2) COMMENT '余额支付金额，没有消费则为0',
balance_consume_amount DECIMAL(27, 2) COMMENT '余额消费金额，没有消费则为0',
is_point_consume BIGINT COMMENT '当日是否积分消费',
point_consume_times BIGINT COMMENT '积分消费次数，没有消费则为0',
point_pay_amount DECIMAL(27, 2) COMMENT '积分支付金额，没有消费则为0',
point_consume_amount DECIMAL(27, 2) COMMENT '积分消费金额，没有消费则为0',
point_add BIGINT COMMENT '增加积分，没有则为0',
point_reduce BIGINT COMMENT '减少积分，没有则为0',
point_change BIGINT COMMENT '变动积分，没有则为0',
online_consume_times BIGINT COMMENT '线上订单量',
online_consume_amount DECIMAL(27, 2) COMMENT '线上消费金额',
offline_consume_times BIGINT COMMENT '线下订单量',
offline_consume_amount DECIMAL(27, 2) COMMENT '线下消费金额'
)
comment '会员行为天表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');2.4 DWD层建设
DWD层: 数仓明细层(清洗转换、降维操作) 此层核心目标: 基于数据探查情况, 对相关表数据进行合并
会员基础信息表:
-
需要对此表进行历史数据拉链
说明: 将每日会员基础信息表中新增及更新的数据, 通过拉链的方式记录下来
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_union_i(
zt_id BIGINT COMMENT '中台会员ID',
member_id BIGINT COMMENT '会员ID',
user_id BIGINT COMMENT '用户ID',
card_no STRING COMMENT '卡号',
member_name STRING COMMENT '会员名称',
mobile STRING COMMENT '手机号',
user_email STRING COMMENT '邮箱',
sex BIGINT COMMENT '用户的性别，1男性，2女性，0未知',
birthday_date STRING COMMENT '生日',
address STRING COMMENT '地址',
reg_time TIMESTAMP COMMENT '注册时间',
reg_md STRING COMMENT '注册门店',
bind_md STRING COMMENT '绑定门店',
flag BIGINT COMMENT '0正常,1删除',
is_black BIGINT COMMENT '是否被拉黑 1被拉黑,0正常用户',
user_state BIGINT COMMENT '会员状态，0停用/注销,1正常,2冻结',
user_type STRING COMMENT '用户类型（-1:传智鲜用户;0:普通用户;1:企业用户 2:内部员工 3:黑马门店 4:商铺会员 5:大买家 6:中间商 7:军区员工）',
member_type BIGINT COMMENT '会员状态 10：未付费会员 20：付费会员',
member_status BIGINT COMMENT '付费会员状态 -1:未付费会员 1：正常 2：试用 3：过期 4:试用已过期',
expired_time TIMESTAMP COMMENT '过期时间',
user_source BIGINT COMMENT '用户来源 ',
member_level BIGINT COMMENT '会员等级',
growth BIGINT COMMENT '成长值',
invite_member_id BIGINT COMMENT '邀请人标识',
invite_type BIGINT COMMENT '邀请类型，0为内部',
register_store_leader_id BIGINT COMMENT '注册归属团长 ID',
last_update_time TIMESTAMP COMMENT '更新日期',
end_date STRING COMMENT '生效结束日期'
)
comment '会员基础信息表'
partitioned by (start_date STRING COMMENT '生效开始日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');会员积分变动表:
-
用于记录每个会员每天积分的变动情况(需统计)
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_point_change_i(
trade_date STRING COMMENT '快照时间',
zt_id BIGINT COMMENT '中台ID',
occupy_subject_id BIGINT COMMENT '占用主体ID，0为全部，101优选,102传智鲜,103传智商城',
point_add BIGINT COMMENT '增加积分，没有则为0',
point_reduce BIGINT COMMENT '减少积分，没有则为0',
point_change BIGINT COMMENT '变动积分，没有则为0'
)
comment '会员积分变动表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');线上会员每日余额变动表:
用于记录每个会员每天余额的变动情况(需统计)
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_balance_change_i(
trade_date STRING COMMENT '统计日期',
zt_id BIGINT COMMENT '中台ID',
member_id BIGINT COMMENT '会员ID',
record_type BIGINT COMMENT '记录类型，0全部,1消费,2充值,3退款,4.清退余额,5.转化,6.系统清除,7.礼品卡兑换,8.现付结余,9.结余退款,10.退卡',
times BIGINT COMMENT '次数',
change_amount DECIMAL(27, 2) COMMENT '变动金额'
)
comment '线上会员每日余额变动表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');线上会员每日余额表:
用于记录每个会员每天余额的情况(需统计)
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_balance_online_i(
trade_date STRING COMMENT '统计日期',
zt_id BIGINT COMMENT '中台ID',
member_id BIGINT COMMENT '会员ID',
member_type BIGINT COMMENT '会员类型',
member_type_name STRING COMMENT '会员类型名称',
store_no STRING COMMENT '门店编码',
city_id BIGINT COMMENT '城市ID',
balance_amount DECIMAL(27, 2) COMMENT '余额'
)
comment '线上会员每日余额表'
partitioned by (dt STRING COMMENT '统计日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');3- 会员主题开发
3.0 拉链表
1.从ODS层获取增量数据(上一天新增和更新的数据) 2.拿着DWD原始拉链表数据 left join 增量数据 ,修改原始拉链中历史数据的结束时间 3.拿着left join 的结果集 union all 增量数据 4.把最新的拉链数据优先保存到DWD对应的临时表中 5.使用insert+select 方式把临时表中数据灌入DWD拉链表中
拉链实现流程:
3.1 DWD层开发
DWD层: 数仓明细层(清洗转换、降维操作) 此层核心目标: 基于数据探查情况, 对相关表数据进行合并
会员基础信息表:
需要对此表进行历史数据拉链
说明: 将每日会员基础信息表中新增及更新的数据, 通过拉链的方式记录下来
首次导入
-- 首次导入:
insert overwrite table dwd.dwd_mem_member_union_i partition (start_date)
select
zt_id,
member_id,
user_id,
card_no,
member_name,
mobile,
user_email,
sex,
birthday_date,
address,
reg_time,
reg_md,
bind_md,
flag,
is_black,
user_state,
user_type,
member_type,
member_status,
expired_time,
user_source,
member_level,
growth,
invite_member_id,
invite_type,
register_store_leader_id,
last_update_time,
'9999-99-99' as end_date,
date_format(date_sub(current_date(),2),'yyyy-MM-dd') as start_date
from ods.ods_mem_member_union_i;后续导入
-- 步骤一: 在MySQL中, 添加增量的测试数据, 包含 新增 和 更新的数据 (测试)
insert into member.member_union (zt_id, member_id, user_id, card_no, member_name, mobile, user_email, sex, birthday_date, address,reg_time, reg_md, bind_md, flag, is_black, user_state, user_type, member_type, member_status, expired_time, user_source, member_level, growth, invite_member_id, invite_type, register_store_leader_id,last_update_time)
values ('32015926',2160344,NULL,'','32015925',114,163,0,'','不详','2023-10-05 17:09:28','W121','W121',0,0,1,-1,10,-1,NULL,-1,0,0,NULL,NULL,NULL,'2023-10-05 17:09:28');
UPDATE member.member_union SET SEX = 1, last_update_time = '2023-10-05 17:10:20' WHERE zt_id = '32015925';
注意: 日期需要写为上一天的日期
-- 步骤二: 执行DataX, 将新增数据和增量数据导入到ODS层 (应该在数据采集中执行)
说明: 此步骤详细过程参考day02实施
-- 步骤三: 执行增量数据导入
-- 先创建一张目标表的临时表, 用于放置计算后的结果
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_union_i_temp(
zt_id BIGINT COMMENT '中台会员ID',
member_id BIGINT COMMENT '会员ID',
user_id BIGINT COMMENT '用户ID',
card_no STRING COMMENT '卡号',
member_name STRING COMMENT '会员名称',
mobile STRING COMMENT '手机号',
user_email STRING COMMENT '邮箱',
sex BIGINT COMMENT '用户的性别，1男性，2女性，0未知',
birthday_date STRING COMMENT '生日',
address STRING COMMENT '地址',
reg_time TIMESTAMP COMMENT '注册时间',
reg_md STRING COMMENT '注册门店',
bind_md STRING COMMENT '绑定门店',
flag BIGINT COMMENT '0正常,1删除',
is_black BIGINT COMMENT '是否被拉黑 1被拉黑,0正常用户',
user_state BIGINT COMMENT '会员状态，0停用/注销,1正常,2冻结',
user_type STRING COMMENT '用户类型（-1:传智鲜用户;0:普通用户;1:企业用户 2:内部员工 3:黑马门店 4:商铺会员 5:大买家 6:中间商 7:军区员工）',
member_type BIGINT COMMENT '会员状态 10：未付费会员 20：付费会员',
member_status BIGINT COMMENT '付费会员状态 -1:未付费会员 1：正常 2：试用 3：过期 4:试用已过期',
expired_time TIMESTAMP COMMENT '过期时间',
user_source BIGINT COMMENT '用户来源 ',
member_level BIGINT COMMENT '会员等级',
growth BIGINT COMMENT '成长值',
invite_member_id BIGINT COMMENT '邀请人标识',
invite_type BIGINT COMMENT '邀请类型，0为内部',
register_store_leader_id BIGINT COMMENT '注册归属团长 ID',
last_update_time TIMESTAMP COMMENT '更新日期',
end_date STRING COMMENT '生效结束日期'
)
comment '会员基础信息表'
partitioned by (start_date STRING COMMENT '生效开始日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');
with t2 as (
select
t1.zt_id,
t1.member_id,
t1.user_id,
t1.card_no,
t1.member_name,
t1.mobile,
t1.user_email,
t1.sex,
t1.birthday_date,
t1.address,
t1.reg_time,
t1.reg_md,
t1.bind_md,
t1.flag,
t1.is_black,
t1.user_state,
t1.user_type,
t1.member_type,
t1.member_status,
t1.expired_time,
t1.user_source,
t1.member_level,
t1.growth,
t1.invite_member_id,
t1.invite_type,
t1.register_store_leader_id,
t1.last_update_time,
if(
t2.zt_id is null OR t1.end_date != '9999-99-99',
t1.end_date,
t2.dt
) as end_date,
t1.start_date
from dwd.dwd_mem_member_union_i t1
left join (select * from ods.ods_mem_member_union_i where dt = date_format(date_sub(current_date(),1),'yyyy-MM-dd')) as t2
on t1.zt_id = t2.zt_id
union all
select
zt_id,
member_id,
user_id,
card_no,
member_name,
mobile,
user_email,
sex,
birthday_date,
address,
reg_time,
reg_md,
bind_md,
flag,
is_black,
user_state,
user_type,
member_type,
member_status,
expired_time,
user_source,
member_level,
growth,
invite_member_id,
invite_type,
register_store_leader_id,
last_update_time,
'9999-99-99' as end_date,
date_format(date_sub(current_date(),1),'yyyy-MM-dd') as start_date
from ods.ods_mem_member_union_i
where dt = date_format(date_sub(current_date(),1),'yyyy-MM-dd')
)
insert overwrite table dwd.dwd_mem_member_union_i_temp partition (start_date)
select
*
from t2 ;
-- 将临时表数据覆盖回目标表中
insert overwrite table dwd.dwd_mem_member_union_i partition (start_date)
select * from dwd.dwd_mem_member_union_i_temp;
-- 将临时表删除
drop table dwd.dwd_mem_member_union_i_temp;情况说明:
1- 目前所做的拉链表是针对历史所有数据, 哪怕这个数据是五年前创建后, 然后五年后发生修改, 我们依然会进行维护 2- 目前所有的拉链表是针对表中所有的字段, 只要表中任何字段发生变更, 都会进行维护 但是: 在实际开发中,我们一般不需要维护历史所有数据, 也不需要维护表中所有的字段 一般维护最近一段周期的数据(一个月、一个季度、一年(最常用)) 一般维护的核心与后续指标计算相关的字段: 用哪些一般维护哪些