DWS
-
名称:数据服务层 service
-
功能:按主题划分,形成日统计的宽表,轻度汇总提前聚合操作。
-
解释:轻度提前聚合说的是先聚合出日的指标,后续可以上卷出周、月、年的指标。
dws这里,主题终于出现了~~~
一个主题所需要的指标、维度,可能往往需要多个DWB层的宽表共同参与计算才能得出。甚至还需要之前层如dwd等参与计算。
轻度、重度 :描述干活的多少 如果都做 叫做重度 只做某一部分叫做轻度
细粒度、粗粒度:越往下粒度越细
举个栗子:1、计算每年的销售额 2、计算每天的销售额
上面这个两个都是根据时间维度计算的 哪个粒度细? 天的粒度细 年粒度粗
时间维度:年 月 日 时 分 秒
使用DataGrip在Hive中创建dws层
注意,对于建库建表操作,需直接使用Hive,因为Presto只是一个数据分析的引擎,其语法不一定支持直接在Hive中建库建表。
销售收入、平台收入、配送成交额、
小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、
订单量、参评单量、差评单量、
配送单量、退款单量、
小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量
--共计: 16个指标
日期、城市、商圈、店铺、品牌、商品大类、商品中类、商品小类
--共计: 8个维度
--cube所有组合: 2^8=256个
注意,其中日期这个维度很特殊,特殊在我们的表就是根据日期分区的,分区的字段是day天。
而dws这一层我们需要统计的也是按day统计,日统计宽表嘛
这也就意味着一个分区就是一天。
知识点10:DWS层搭建--销售主题宽表--step1--字段抽取
表关系
一切的前提是,先了解原始数据的结构和关系。
对于销售主题宽表来说,其当中的指标和维度字段分别来源于DWB层:订单明细宽表、店铺明细宽表、商品明细宽表。
比如商圈、店铺等维度来自于店铺明细宽表;大中小分类来自于商品明细宽表;而成交额等指标需要依赖订单明细宽表。
--以订单为准,以goods_id关联商品,以store_id关联店铺
select *
from dwb_order_detail o
left join dwb_goods_detail g on o.goods_id = g.id
left join dwb_shop_detail s on o.store_id = s.id;
字段抽取
关联之后,字段非常多,但是并不意味着每一个字段都是销售主题宽表统计需要的;
因此需要根据销售主题宽表的计算指标和维度,把相关的字段抽取出来
知识点11:DWS层搭建--销售主题宽表--step2--row_number去重(可选)
使用row_number分组去重的时候需要注意:
1、对于城市、商圈、店铺等维度的成交额计算,根据订单order_amount汇总求和即可;
2、而对于品牌、大类、中类、小类等维度成交额计算,需要根据goods_id计算。
--上述表的
数据中,如果计算不同品牌的成交额,就不能再根据订单金额相加了
--而是必须根据每个订单中,这个品牌的金额进行计算
--因为订单中可以有不同的商品品牌。
row_number() over(partition by order_id) as order_rn,
row_number() over(partition by order_id,g.brand_id) as brand_rn,
row_number() over(partition by order_id,g.max_class_name) as maxclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name) as midclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name) as minclass_rn,
--下面分组加入goods_id
row_number() over(partition by order_id,g.brand_id,o.goods_id) as brand_goods_rn,
row_number() over(partition by order_id,g.max_class_name,o.goods_id) as maxclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,o.goods_id) as midclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name,o.goods_id) as minclass_goods_rn
知识点12:DWS层搭建--销售主题宽表--step3--grouping sets分组
-
使用CTE表达式针对上面抽取字段、分组去重的结果进行引导
with temp as ( select 抽取字段、row_numbe