day10_会员主题域开发
会员主题_DWS和ADS层
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');数据导入:
活跃会员：30天内有消费
沉睡会员：90天内有消费，30天内没有消费
这个需求的难点在于计算累计值。思路是 使用天进行聚合，得到每天的值，然后使用sum () over 窗口，得到累加值，对于每天的累积情况，这里需要使用拉链表的思想，即构造一个生效日期，这里使用lead() over 窗口函数，取到下一个日期，然后再用这个当日时间去卡，即可得到当日对应的累计值。
-- DWS层: 门店会员分类天
-- 注意: 以下内容仅仅以2023-11-14为例,实际需要把14-20日的所有数据都要导入对应表中
with t1 as (
select
trade_date as start_date,
store_no,
reg_num_add, -- 新增注册会员数
sum(reg_num_add) over(partition by store_no order by trade_date) as reg_num_sum, -- 累计注册会员数
lead(trade_date,1,'9999-99-99') over (partition by store_no order by trade_date) as end_date
from
( -- 先统计每天注册会员数
select
trade_date,
reg_md as store_no,
count(1) as reg_num_add
from dwm.dwm_mem_member_behavior_day_i
where is_register = 1
group by
trade_date, reg_md
) temp1
),
t2 as (
select
trade_date as start_date,
store_no,
consume_num_add, -- 新增消费会员数
sum(consume_num_add) over(partition by store_no order by trade_date) as consume_num_sum, -- 累计消费会员数
lead(trade_date,1,'9999-99-99') over (partition by store_no order by trade_date) as end_date
from (
select
trade_date,
store_no,
count(1) as consume_num_add
from dwm.dwm_mem_first_buy_i
group by trade_date, store_no
) temp2
),
t3 as (
select
trade_date as start_date,
store_no,
repurchase_num_add, -- 新增充值会员数
sum(repurchase_num_add) over(partition by store_no order by trade_date) as repurchase_num_sum, -- 累计充值会员数
lead(trade_date,1,'9999-99-99') over (partition by store_no order by trade_date) as end_date
from (
select
trade_date,
store_no,
count(1) as repurchase_num_add
from dwm.dwm_mem_second_buy_i
group by trade_date, store_no
) temp2
),
t4 as (
-- 活跃会员数(最近30天有消费) 2023-11-14
select
'2023-11-14' as trade_date,
bind_md as store_no,
count(distinct zt_id) as active_member_num
from dwm.dwm_mem_member_behavior_day_i
where trade_date <= '2023-11-14' and trade_date >= date_sub('2023-11-14',30) and is_consume = 1
group by bind_md
),
t5 as (
-- 沉睡会员数: 最近90天有消费 , 但是最近30天无消费
select
'2023-11-14' as trade_date,
temp3.bind_md as store_no,
count(temp3.zt_id) as sleep_member_num
from
(
select
bind_md,
zt_id
from dwm.dwm_mem_member_behavior_day_i
where trade_date <= '2023-11-14' and trade_date >= date_sub('2023-11-14',90) and is_consume = 1
group by bind_md,zt_id
) temp3
LEFT JOIN
(
select
bind_md,
zt_id
from dwm.dwm_mem_member_behavior_day_i
where trade_date <= '2023-11-14' and trade_date >= date_sub('2023-11-14',30) and is_consume = 1
group by bind_md,zt_id
) temp4 on temp3.bind_md = temp4.bind_md and temp3.zt_id = temp4.zt_id
where temp4.zt_id is null
group by temp3.bind_md
),
t6 as (
select
trade_date,
store_no,
sum(real_paid_amount) as sale_amount_bind
from dwm.dwm_mem_sell_order_i
where trade_date = '2023-11-14'
group by trade_date,store_no
),
t7 as (
select
'2023-11-14' as trade_date,
store_no,
if(start_date = '2023-11-14',reg_num_add,0) as reg_num_add,
reg_num_sum,
0 as consume_num_add,
0 as consume_num_sum,
0 as repurchase_num_add,
0 as repurchase_num_sum,
0 as active_member_num,
0 as sleep_member_num,
0 as sale_amount_bind
from t1
where start_date <= '2023-11-14' and end_date >= '2023-11-14'
union all
select
'2023-11-14' as trade_date,
store_no,
0 reg_num_add,
0 as reg_num_sum,
if( start_date = '2023-11-14',consume_num_add,0) as consume_num_add,
consume_num_sum,
0 as repurchase_num_add,
0 as repurchase_num_sum,
0 as active_member_num,
0 as sleep_member_num,
0 as sale_amount_bind
from t2
where start_date <= '2023-11-14' and end_date >= '2023-11-14'
union all
select
'2023-11-14' as trade_date,
store_no,
0 reg_num_add,
0 as reg_num_sum,
0 as consume_num_add,
0 as consume_num_sum,
if(start_date = '2023-11-14',repurchase_num_add,0) as repurchase_num_add,
repurchase_num_sum,
0 as active_member_num,
0 as sleep_member_num,
0 as sale_amount_bind
from t3
where start_date <= '2023-11-14' and end_date >= '2023-11-14'
union all
select
trade_date,
store_no,
0 reg_num_add,
0 as reg_num_sum,
0 as consume_num_add,
0 as consume_num_sum,
0 as repurchase_num_add,
0 as repurchase_num_sum,
active_member_num,
0 as sleep_member_num,
0 as sale_amount_bind
from t4
union all
select
trade_date,
store_no,
0 reg_num_add,
0 as reg_num_sum,
0 as consume_num_add,
0 as consume_num_sum,
0 as repurchase_num_add,
0 as repurchase_num_sum,
0 as active_member_num,
sleep_member_num,
0 as sale_amount_bind
from t5
union all
select
trade_date,
store_no,
0 reg_num_add,
0 as reg_num_sum,
0 as consume_num_add,
0 as consume_num_sum,
0 as repurchase_num_add,
0 as repurchase_num_sum,
0 as active_member_num,
0 as sleep_member_num,
sale_amount_bind
from t6
)
-- insert overwrite table dws.dws_mem_store_member_classify_day_i partition (dt)
select
t7.trade_date,
t8.week_trade_date,
t8.month_trade_date,
t7.store_no,
t9.store_name,
t9.store_sale_type,
t9.store_type_code,
t9.city_id,
t9.city_name,
t9.region_code,
t9.region_name,
t9.is_day_clear,
sum(t7.reg_num_add) as reg_num_add,
sum(t7.reg_num_sum) as reg_num_sum,
sum(t7.consume_num_add) as consume_num_add,
sum(t7.consume_num_sum) as consume_num_sum,
sum(t7.repurchase_num_add) as repurchase_num_add,
sum(t7.repurchase_num_sum) as repurchase_num_sum,
sum(t7.active_member_num) as active_member_num,
sum(t7.sleep_member_num) as sleep_member_num,
sum(t7.sale_amount_bind) as sale_amount_bind,
t7.trade_date as dt
from t7
left join dim.dwd_dim_date_f t8 on t7.trade_date = t8.trade_date
-- 注意: 一定要检查自己的dwd_dim_store_i分区目录,此处填写自己的分区目录时间
left join dim.dwd_dim_store_i t9 on t7.store_no = t9.store_no and t9.dt ='2023-11-23'
group by
t7.trade_date,
t8.week_trade_date,
t8.month_trade_date,
t7.store_no,
t9.store_name,
t9.store_sale_type,
t9.store_type_code,
t9.city_id,
t9.city_name,
t9.region_code,
t9.region_name,
t9.is_day_clear;门店会员统计天表:
维度指标:
指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额 维度: 时间维度（天、周、月） 涉及表: 门店会员统计天表 表字段的组成: 维度字段 + 指标结果字段
建表语句:
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');数据导入:
门店的消费情况可以从dwm_sell_o2o_order_i表中出，
注册、充值、消费这些数据可以从dwm_mem_member_behavior_day_i中出，
余额数据可以从dwd_mem_balance_online_i中出。
需要注意的是，这里有新增的指标还有累计的指标，为了方便计算，可以分开求解。
新增指标可以大部分从dwm_mem_member_behavior_day_i中出，因为 dwm_mem_member_behavior_day_i是会员粒度的表，记录了会员的各种行为。在计算会员指标的时候，很多需要count()来计算的指标，可以转化成sum（1），根据条件进行判断即可。
-- DWS 门店会员统计宽表
-- 注意: 以下内容仅仅以2023-11-14为例,实际需要把14-20日的所有数据都要导入对应表中
with t1 as (
select
trade_date,
store_no,
sum(real_paid_amount) as store_sale_amount,
count(if(trade_type = 0,parent_order_no,NULL)) - count(if(trade_type = 5,parent_order_no,NULL)) as store_orders_number,
0 as register_member_num,
0 as register_member_num_all,
0 as register_recharge_num,
0 as rg_rc_td_num,
0 as register_trade_num,
0 as recharge_member_num,
0 as recharge_amount,
0 as recharge_amount_all,
0 as remain_member_num,
0 as remain_member_amount,
0 as balance_member_num,
0 as balance_member_order_num,
0 as balance_pay_amount,
0 as balance_member_amount,
0 as member_num,
0 as member_order_num,
0 as member_amount,
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
