DWS层建设实战-2
Day08_DWS层建设实战-商品主题统计宽表
知识点01：课程内容大纲与学习目标
#课程内容大纲
1、DWS层构建
商品主题统计宽表的实现
核心：表关系梳理、字段抽取、结果合并
用户主题统计宽表的实现思路
#学习目标
掌握主题需求的分析
掌握表关系梳理与字段抽取知识点02：DWS层搭建--商品主题宽表
- 主题需求
- 指标
下单次数、下单件数、下单金额
被支付次数、被支付件数、被支付金额
被退款次数、被退款件数、被退款金额
被加入购物车次数、被加入购物车件数
被收藏次数
好评数、中评数、差评数
--总共15个指标- 维度
日期（day）+商品- 本主题建表操作
注意：建表操作需要在hive中执行，presto不支持hive的建表语法。
知识点03：DWS层搭建--商品主题宽表--需求分析
- 确定指标字段与表的关系
当需求提出指标和维度之后，我们需要做的就是确定通过哪些表能够提供支撑。
思考：是不是意味着数仓分层之后，上一层的表只能查询下一层的，能不能跨层？
答案是不一定的。
#维度
dwb_order_detail
时间、商品
#下单次数、下单件数、下单金额
dwb_order_detail
order_id: 下单次数相当于下了多少订单（有多少个包含这个商品的订单） ----> 下单次数
buy_num : 下单件数相当于下了多少商品 ----> 下单件数
total_price(无运费)/goods_amount（有运费） 每个商品下单金额指的是订单金额还是商品金额？应该是商品金额(订单中可能会包含其他商品) ----> 下单金额
#被支付次数、被支付件数、被支付金额
dwb_order_detail
#支付状态的判断
order_state: 只要不是1和7 就是已经支付状态的订单
is_pay: 这个字段也可以 0表示未支付，1表示已支付。#推荐使用这个字段来判断
#次数 件数 金额
order_id ----> 被支付次数
buy_num ----> 被支付件数
total_price ----> 被支付金额
#被退款次数、被退款件数、被退款金额
dwb_order_detail
#退款的判断
refund_id: 退款单号 is not null的就表明有退款
#次数 件数 金额
order_id ----> 被退款次数
buy_num ----> 被退款件数
total_price ----> 被退款金额
#被加入购物车次数、被加入购物车件数
yp_dwd.fact_shop_cart(能够提供购物车相关信息的只有这张表)
id: 次数
buy_num: 件数
#被收藏次数
yp_dwd.fact_goods_collect
id: 次数
#好评数、中评数、差评数
yp_dwd.fact_goods_evaluation_detail
geval_scores_goods:商品评分0-10分
#如何判断 好 中 差 （完全业务指定）
得分: >= 9 好
得分: >6 <9 中
得分：<= 6 差概况起来，计算商品主题宽表，需要参与计算的表有：
yp_dwb.dwb_order_detail 订单明细宽表
yp_dwd.fact_shop_cart 购物车表
yp_dwd.fact_goods_collect 商品收藏表
yp_dwd.fact_goods_evaluation_detail 商品评价表
知识点04：DWS层搭建--商品主题宽表--step1--下单、支付、退款统计
- 大前提：使用row_number对数据进行去重
基于dwb_order_detail表根据商品（goods_id）进行统计各个指标的时候；
为了避免同一笔订单下有多个重复的商品出现（正常来说重复的应该合并在一起了）；
应该使用row_number对order_id和goods_id进行去重。
- 下单次数、件数、金额统计
基于上述的order_base进行查询
- 支付次数、件数、金额统计
计算支付相关指标之前，可以先使用is_pay进行订单状态过滤
然后基于过滤后的数据进行统计
可以继续使用CTE引导
- 退款次数、件数、金额统计
可以先使用refund_id is not null查询出退款订单，然后进行统计
知识点05：DWS层搭建--商品主题宽表--step2--购物车、收藏统计
思考：为什么下面这两个查询需要考虑拉链的状态？上面的下单、支付等统计为什么不需要？
- 购物车次数、件数统计
-- 购物车次数、件数
cart_count as (
select substring(create_time, 1, 10) dt, goods_id sku_id,
count(id) cart_count,
sum(buy_num) cart_num
from yp_dwd.fact_shop_cart
where end_date = '9999-99-99'
group by substring(create_time, 1, 10), goods_id
),- 收藏次数统计
-- 收藏次数
favor_count as (
select substring(c.create_time, 1, 10) dt, goods_id sku_id,
count(c.id) favor_count
from yp_dwd.fact_goods_collect c
where end_date='9999-99-99'
group by substring(c.create_time, 1, 10), goods_id
),知识点06：DWS层搭建--商品主题宽表--step3--好评、中评、差评
- 好评、中评、差评次数
根据评分来判断好评、中评、差评
老师上课SQL
insert into yp_dws.dws_sku_daycount
select * from (
with t1 as (
select
-- 维度字段
dt,
goods_id ,
goods_name ,
--指标字段
order_id ,
buy_num ,
total_price ,
-- 判断字段:
order_state ,
is_pay ,
refund_id,
refund_state,
--去重处理
row_number() over(partition by order_id,goods_id) as rn1
from yp_dwb.dwb_order_detail
),
t2 as(
select
dt,
goods_id,
goods_name,
count(distinct order_id) as order_count, -- 下单次数
sum(buy_num) as order_num, -- 下单件数,
sum(total_price) as order_amount -- 下单金额
from t1 where rn1 = 1
group by dt,goods_id ,goods_name
),
t3 as (
select
dt,
goods_id,
goods_name,
count(distinct order_id) as payment_count, -- 被支付次数
sum(buy_num) as payment_num, -- 被支付件数,
sum(total_price) as payment_amount -- 被支付金额
from t1 where rn1 = 1 and order_state not in(1,7) and is_pay = 1
group by dt,goods_id ,goods_name
),
t4 as (
select
dt,
goods_id,
goods_name,
count(distinct order_id) as refund_count , -- 被退款次数
sum(buy_num) as refund_num , -- 被退款件数,
sum(total_price) as refund_amount -- 被退款金额
from t1 where rn1 = 1 and refund_id is not null
group by dt,goods_id ,goods_name
),
t5 as (
select
substr(sc.create_time,1,10)as dt,
od.goods_id,
goods_name,
count(id) as cart_count , -- 被加入购物车次数
sum(sc.buy_num) as cart_num -- 被加入购物车件数,
from yp_dwd.fact_shop_cart sc left join yp_dwb.dwb_order_detail od on sc.goods_id = od.goods_id
group by substr(sc.create_time,1,10), od.goods_id ,goods_name
),
t6 as (
select
substr(gc.create_time,1,10) as dt,
od.goods_id,
goods_name,
count(id) as favor_count -- 收藏次数
from yp_dwd.fact_goods_collect gc left join yp_dwb.dwb_order_detail od on gc.goods_id = od.goods_id
group by substr(gc.create_time,1,10), od.goods_id,goods_name
),
t7 as (
select
substr(ged.create_time,1,10) as dt,
od.goods_id,
goods_name,
count(if(geval_scores_goods >= 9,1,null)) as evaluation_good_count, -- 好评数
count(if(geval_scores_goods >= 6 and geval_scores_goods < 9,1,null)) as evaluation_mid_count, -- 好评数
count(if(geval_scores_goods < 6,1,null)) as evaluation_bad_count -- 好评数
from yp_dwd.fact_goods_evaluation_detail ged left join yp_dwb.dwb_order_detail od on ged.goods_id = od.goods_id
group by substr(ged.create_time,1,10), od.goods_id,goods_name
)
,
t8 as (
select
coalesce(t2.dt, t3.dt, t4.dt, t5.dt, t6.dt, t7.dt) as dt,
coalesce(t2.goods_id, t3.goods_id, t4.goods_id, t5.goods_id, t6.goods_id,
t7.goods_id) as sku_id,
coalesce(t2.goods_name, t3.goods_name, t4.goods_name, t5.goods_name, t6.goods_name,
t7.goods_name) as sku_name,
coalesce(t2.order_count, 0) as order_count,
coalesce(t2.order_num, 0) as order_num,
coalesce(t2.order_amount, 0) as order_amount,
coalesce(t3.payment_count, 0) as payment_count,
coalesce(t3.payment_num, 0) as payment_num,
coalesce(t3.payment_amount, 0) as payment_amount,
coalesce(t4.refund_count, 0) as refund_count,
coalesce(t4.refund_num, 0) as refund_num,
coalesce(t4.refund_amount, 0) as refund_amount,
coalesce(t5.cart_count, 0) as cart_count,
coalesce(t5.cart_num, 0) as cart_num,
coalesce(t6.favor_count, 0) as favor_count,
coalesce(t7.evaluation_good_count, 0) as evaluation_good_count,
coalesce(t7.evaluation_mid_count, 0) as evaluation_mid_count,
coalesce(t7.evaluation_bad_count, 0) as evaluation_bad_count
from t2
full join t3 on t2.dt = t3.dt and t2.goods_id = t3.goods_id
full join t4 on t3.dt = t4.dt and t3.goods_id = t4.goods_id
full join t5 on t4.dt = t5.dt and t4.goods_id = t5.goods_id
full join t6 on t5.dt = t6.dt and t5.goods_id = t6.goods_id
full join t7 on t6.dt = t7.dt and t6.goods_id = t7.goods_id
)
select
dt,
sku_id,
sku_name,
sum(order_count),
sum(order_num),
sum(order_amount),
sum(payment_count),
sum(payment_num),
sum(payment_amount),
sum(refund_count),
sum(refund_num),
sum(refund_amount),
sum(cart_count),
sum(cart_num),
sum(favor_count),
sum(evaluation_good_count),
sum(evaluation_mid_count),
sum(evaluation_bad_count)
from t8
group by dt,sku_id,sku_name
)参考代码
insert into hive.yp_dws.dws_sku_daycount
select * from (
with order_t1 as (
select
-- 维度字段
dt,
goods_id ,
goods_name ,
--指标字段
order_id ,
buy_num ,
total_price ,
-- 判断字段:
order_state ,
is_pay ,
refund_id,
refund_state,
--去重处理
row_number() over(partition by order_id,goods_id) as rn1
from hive.yp_dwb.dwb_order_detail
),
-- 下单次数、下单件数、下单金额
order_t2 as(
select
dt,
goods_id,
goods_name,
count(order_id) as order_count,
sum(buy_num) as order_num,
sum(total_price) as order_amount
from order_t1 where rn1 = 1
group by dt,goods_id,goods_name
),
-- 被支付次数、被支付件数、被支付金额
payment_t3 as (
select
dt,
goods_id,
goods_name,
count(order_id) as payment_count,
sum(buy_num) as payment_num,
sum(total_price) as payment_amount
-- 过滤出已经支付的订单数据: order_state 不能为 1和7 并且 is_pay =1
from order_t1 where rn1 = 1 and order_state not in(1,7) and is_pay = 1
group by dt,goods_id,goods_name
),
-- 被退款次数、被退款件数、被退款金额
refund_t4 as (
select
dt,
goods_id,
goods_name,
count(order_id) as refund_count,
sum(buy_num) as refund_num,
sum(total_price) as refund_amount
-- 过滤出已经退款的订单数据: refund_id is not null 并且 refund_state = 5
from order_t1 where rn1 = 1 and refund_id is not null and refund_state = 5
group by dt,goods_id,goods_name
),
-- 被加入购物车次数、被加入购物车件数
cart_t5 as (
select
substr(sc.create_time ,1,10) as dt,
sc.goods_id ,
g.goods_name ,
count(sc.id) as cart_count,
sum(sc.buy_num) as cart_num
from hive.yp_dwd.fact_shop_cart sc
left join hive.yp_dwb.dwb_goods_detail g
on sc.goods_id = g.id
group by substr(sc.create_time ,1,10) ,sc.goods_id ,g.goods_name
),
-- 被收藏次数
favor_t6 as(
select
substr(gc.create_time,1,10) as dt,
gc.goods_id ,
g.goods_name ,
count(gc.id) as favor_count
from hive.yp_dwd.fact_goods_collect gc
left join hive.yp_dwb.dwb_goods_detail g
on gc.goods_id = g.id
group by substr(gc.create_time,1,10), gc.goods_id ,g.goods_name
),
-- 好评, 中评 和 差评
evaluation_t7 as (
select
substr(ed.create_time,1,10) as dt,
ed.goods_id ,
g.goods_name,
count(
if(ed.geval_scores_goods is null or ed.geval_scores_goods >8 , ed.id, null)
) as evaluation_good_count,
count(
if(ed.geval_scores_goods is not null and ed.geval_scores_goods between 6 and 8, ed.id, null)
) as evaluation_mid_count,
count(
if(ed.geval_scores_goods is not null and ed.geval_scores_goods < 6, ed.id, null)
) as evaluation_bad_count
from hive.yp_dwd.fact_goods_evaluation_detail ed
left join hive.yp_dwb.dwb_goods_detail g
on ed.goods_id = g.id
group by substr(ed.create_time,1,10), ed.goods_id ,g.goods_name
),
t8 as(
select
coalesce(t2.dt,t3.dt,t4.dt,t5.dt,t6.dt,t7.dt) as dt,
coalesce(t2.goods_id,t3.goods_id,t4.goods_id,t5.goods_id,t6.goods_id,t7.goods_id) as sku_id,
coalesce(t2.goods_name,t3.goods_name,t4.goods_name,t5.goods_name,t6.goods_name,t7.goods_name) as sku_name,
coalesce(t2.order_count,0) as order_count,
coalesce(t2.order_num,0) as order_num,
coalesce(t2.order_amount,0) as order_amount,
coalesce(t3.payment_count,0) as payment_count,
coalesce(t3.payment_num,0) as payment_num,
coalesce(t3.payment_amount,0) as payment_amount,
coalesce(t4.refund_count,0) as refund_count,
coalesce(t4.refund_num,0) as refund_num,
coalesce(t4.refund_amount,0) as refund_amount,
coalesce(t5.cart_count,0) as cart_count,
coalesce(t5.cart_num,0) as cart_num,
coalesce(t6.favor_count,0) as favor_count,
coalesce(t7.evaluation_good_count,0) as evaluation_good_count,
coalesce(t7.evaluation_mid_count,0) as evaluation_mid_count,
coalesce(t7.evaluation_bad_count,0) as evaluation_bad_count
from order_t2 t2
full join payment_t3 t3 on t2.dt = t3.dt and t2.goods_id = t3.goods_id
full join refund_t4 t4 on t2.dt = t4.dt and t2.goods_id = t4.goods_id
full join cart_t5 t5 on t2.dt = t5.dt and t2.goods_id = t5.goods_id
full join favor_t6 t6 on t2.dt = t6.dt and t2.goods_id = t6.goods_id
full join evaluation_t7 t7 on t2.dt = t7.dt and t2.goods_id = t7.goods_id
)
select
dt,
sku_id,
sku_name,
sum(order_count) as order_count,
sum(order_num) as order_num,
sum(order_amount) as order_amount,
sum(payment_count) as payment_count,
sum(payment_num) as payment_num,
sum(payment_amount) as payment_amount,
sum(refund_count) as refund_count,
sum(refund_num) as refund_num,
sum(refund_amount) as refund_amount,
sum(cart_count) as cart_count,
sum(cart_num) as cart_num,
sum(favor_count) as favor_count,
sum(evaluation_good_count) as evaluation_good_count,
sum(evaluation_mid_count) as evaluation_mid_count,
sum(evaluation_bad_count) as evaluation_bad_count
from t8 group by dt,sku_id,sku_name
)DWS层搭建--用户主题宽表--需求分析
- 主题需求
- 指标
登录次数、收藏店铺数、收藏商品数、加入购物车次数、加入购物车金额、下单次数、下单金额、支付次数、支付金额- 维度
用户、日期- 建表操作
注意：建表操作需要在hive中执行，presto不支持hive的建表语法。
- 确定字段与表关系
#登录次数
yp_dwd.fact_user_login
id
login_user
#收藏店铺数、收藏商品数
yp_dwd.fact_store_collect
id
user_id
#加入购物车次数、加入购物车金额
yp_dwd.fact_shop_cart
yp_dwb.dwb_goods_detail
#因为购物车中没有金额，因此需要和商品详情表进行关联
goods_promotion_price: 商品促销价格(售价)
#下单次数、下单金额
yp_dwd.fact_shop_order
yp_dwd.fact_shop_order_address_detail
#通过订单主副表可以提供
#支付次数、支付金额
yp_dwd.fact_trade_record- 最终实现合并的方式
- 使用full join合并
用户主题的统计宽表，作为项目练习由大家自己完成。
所用到的技术点之前已经完成铺垫完毕。
----------------------------------课堂代码----------------------------------
insert into yp_dws.dws_user_daycount
select * from
(
-- 登录次数
with login_count as (
select
login_user as user_id,
count(id) as login_count,
dt
from yp_dwd.fact_user_login
group by login_user, dt
),
-- 店铺收藏数
store_collect_count as (
select
user_id,
count(id) as store_collect_count,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_store_collect
where end_date='9999-99-99'
group by user_id, substring(create_time, 1, 10)
),
-- 商品收藏数
goods_collect_count as (
select
user_id,
count(id) as goods_collect_count,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_goods_collect
where end_date='9999-99-99'
group by user_id, substring(create_time, 1, 10)
),
-- 加入购物车次数和金额
cart_count_amount as (
select
count(cart.id) as cart_count,
sum(coalesce(g.goods_promotion_price,0)) as cart_amount,
buyer_id as user_id,
substring(cart.create_time, 1, 10) as dt
from yp_dwd.fact_shop_cart cart, yp_dwb.dwb_goods_detail g
where cart.end_date='9999-99-99' and cart.goods_id=g.id
group by buyer_id, substring(cart.create_time, 1, 10)
),
-- 订单宽表，为后面的下单次数和金额准备数据
order_base as (
select
buyer_id,
create_date, order_id,
order_amount,
row_number() over(partition by orderd.order_id) rn
from yp_dwb.dwb_order_detail orderd
where orderd.is_valid=1
),
-- 下单次数和金额
order_count_amount as (
select
o.buyer_id user_id,
substring(o.create_date,1,10) dt,
count(o.order_id) order_count,
sum(coalesce(o.order_amount,0)) order_amount
from order_base o
where o.rn=1
group by o.buyer_id, substring(o.create_date,1,10)
),
-- 支付次数和金额
payment_count_amount as (
select
count(id) as payment_count,
sum(coalesce(order_amount,0)) as payment_amount,
create_user user_id,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_shop_order_address_detail
where is_valid = 1 and pay_time is not null and end_date='9999-99-99'
group by create_user, substring(create_time, 1, 10)
),
fulljoin as (
select
coalesce(lc.dt, scc.dt, gcc.dt, cc.dt, oc.dt, pc.dt) dt ,
coalesce(lc.user_id, scc.user_id, gcc.user_id, cc.user_id, oc.user_id, pc.user_id) user_id ,
coalesce(login_count,0) login_count ,
coalesce(store_collect_count,0) store_collect_count ,
coalesce(goods_collect_count,0) goods_collect_count ,
coalesce(cart_count,0) cart_count ,
coalesce(cart_amount,0) cart_amount ,
coalesce(order_count,0) order_count ,
coalesce(order_amount,0) order_amount ,
coalesce(payment_count,0) payment_count ,
coalesce(payment_amount,0) payment_amount
from login_count lc
full join store_collect_count scc on lc.dt=scc.dt and lc.user_id=scc.user_id
full join goods_collect_count gcc on lc.dt=gcc.dt and lc.user_id=gcc.user_id
full join cart_count_amount cc on lc.dt=cc.dt and lc.user_id=cc.user_id
full join order_count_amount oc on lc.dt=oc.dt and lc.user_id=oc.user_id
full join payment_count_amount pc on lc.dt=pc.dt and lc.user_id=pc.user_id
)
select
dt,
user_id,
-- 登录次数
sum(coalesce(login_count,0)) login_count,
-- 店铺收藏数
sum(coalesce(store_collect_count,0)) store_collect_count,
-- 商品收藏数
sum(coalesce(goods_collect_count,0)) goods_collect_count,
-- 加入购物车次数和金额
sum(coalesce(cart_count,0)) cart_count,
sum(coalesce(cart_amount,0)) cart_amount,
-- 下单次数和金额
sum(coalesce(order_count,0)) order_count,
sum(coalesce(order_amount,0)) order_amount,
-- 支付次数和金额
sum(coalesce(payment_count,0)) payment_count,
sum(coalesce(payment_amount,0)) payment_amount
from fulljoin
group by dt, user_id
)
----------------------------------参考代码----------------------------------
insert into yp_dws.dws_user_daycount
-- 登录次数
with login_count as (
select
login_user as user_id,
count(id) as login_count,
dt
from yp_dwd.fact_user_login
group by login_user, dt
),
-- 店铺收藏数
store_collect_count as (
select
user_id,
count(id) as store_collect_count,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_store_collect
where end_date='9999-99-99'
group by user_id, substring(create_time, 1, 10)
),
-- 商品收藏数
goods_collect_count as (
select
user_id,
count(id) as goods_collect_count,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_goods_collect
where end_date='9999-99-99'
group by user_id, substring(create_time, 1, 10)
),
-- 加入购物车次数和金额
cart_count_amount as (
select
count(cart.id) as cart_count,
sum(coalesce(g.goods_promotion_price,0)) as cart_amount,
buyer_id as user_id,
substring(cart.create_time, 1, 10) as dt
from yp_dwd.fact_shop_cart cart, yp_dwb.dwb_goods_detail g
where cart.end_date='9999-99-99' and cart.goods_id=g.id
group by buyer_id, substring(cart.create_time, 1, 10)
),
-- 订单宽表，为后面的下单次数和金额准备数据
order_base as (
select
buyer_id,
create_date, order_id,
order_amount,
row_number() over(partition by orderd.order_id) rn
from yp_dwb.dwb_order_detail orderd
where orderd.is_valid=1
),
-- 下单次数和金额
order_count_amount as (
select
o.buyer_id user_id,
substring(o.create_date,1,10) dt,
count(o.order_id) order_count,
sum(coalesce(o.order_amount,0)) order_amount
from order_base o
where o.rn=1
group by o.buyer_id, substring(o.create_date,1,10)
),
-- 支付次数和金额
payment_count_amount as (
select
count(id) as payment_count,
sum(coalesce(order_amount,0)) as payment_amount,
create_user user_id,
substring(create_time, 1, 10) as dt
from yp_dwd.fact_shop_order_address_detail
where is_valid = 1 and pay_time is not null and end_date='9999-99-99'
group by create_user, substring(create_time, 1, 10)
),
fulljoin as (
select
coalesce(lc.dt, scc.dt, gcc.dt, cc.dt, oc.dt, pc.dt) dt ,
coalesce(lc.user_id, scc.user_id, gcc.user_id, cc.user_id, oc.user_id, pc.user_id) user_id ,
coalesce(login_count,0) login_count ,
coalesce(store_collect_count,0) store_collect_count ,
coalesce(goods_collect_count,0) goods_collect_count ,
coalesce(cart_count,0) cart_count ,
coalesce(cart_amount,0) cart_amount ,
coalesce(order_count,0) order_count ,
coalesce(order_amount,0) order_amount ,
coalesce(payment_count,0) payment_count ,
coalesce(payment_amount,0) payment_amount
from login_count lc
full join store_collect_count scc on lc.dt=scc.dt and lc.user_id=scc.user_id
full join goods_collect_count gcc on lc.dt=gcc.dt and lc.user_id=gcc.user_id
full join cart_count_amount cc on lc.dt=cc.dt and lc.user_id=cc.user_id
full join order_count_amount oc on lc.dt=oc.dt and lc.user_id=oc.user_id
full join payment_count_amount pc on lc.dt=pc.dt and lc.user_id=pc.user_id
)
select
dt,
user_id,
-- 登录次数
sum(coalesce(login_count,0)) login_count,
-- 店铺收藏数
sum(coalesce(store_collect_count,0)) store_collect_count,
-- 商品收藏数
sum(coalesce(goods_collect_count,0)) goods_collect_count,
-- 加入购物车次数和金额
sum(coalesce(cart_count,0)) cart_count,
sum(coalesce(cart_amount,0)) cart_amount,
-- 下单次数和金额
sum(coalesce(order_count,0)) order_count,
sum(coalesce(order_amount,0)) order_amount,
-- 支付次数和金额
sum(coalesce(payment_count,0)) payment_count,
sum(coalesce(payment_amount,0)) payment_amount
from fulljoin
group by dt, user_id ; -- order by dt, user_id ;