DWS的功能
面向主题,进行主题需求分析,轻度汇总,细粒度汇总
基本步骤
-
step01:需求解读 如果不明白的指标和维度要和需求进行讨论
-
step02 创建主题宽表
-
step02 抽取字段
-
step04 技术实现
注意 当用preosto查询 插入到表数据的时候 hive当中是没有的
这是因为这两个引擎的问题,hive底层是分区的 presto 插入之后 hive解析不了 但是的确在hdfs上存储着
相当于你跳出了hive 这一层 以后只能用presto来分析
主题1:dws_sale_daycount 销售主题宽表
在业务当中 需求分析是十分重要的 ,遇到指标比较模糊的一定要沟通
主题需求: 根据主题进行需求分析 得出指标和维度
-
指标
销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量
--共计: 16个指标</pre>
-
维度
日期、城市、商圈、店铺、品牌、商品大类、商品中类、商品小类
--共计: 8个维度
--cube所有组合: 2^8=256个注意，其中日期这个维度很特殊，特殊在我们的表就是根据日期分区的，分区的字段是day天。
而dws这一层我们需要统计的也是按day统计，日统计宽表嘛
这也就意味着一个分区就是一天。</pre>通过分析 我们发现八个维度和16个指标 在dwb层三个表中 可以找到
步骤0 hive 根据表建表
注意优雅起名字
--维度
city_id string COMMENT '城市id',
city_name string COMMENT '城市name',
trade_area_id string COMMENT '商圈id',
trade_area_name string COMMENT '商圈名称',
store_id string COMMENT '店铺的id',
store_name string COMMENT '店铺名称',
brand_id string COMMENT '品牌id',
brand_name string COMMENT '品牌名称',
max_class_id string COMMENT '商品大类id',
max_class_name string COMMENT '大类名称',
mid_class_id string COMMENT '中类id',
mid_class_name string COMMENT '中类名称',
min_class_id string COMMENT '小类id',
min_class_name string COMMENT '小类名称',
group_type string COMMENT '分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all',
-- =======日统计=======
-- 销售收入
sale_amt DECIMAL(38,2) COMMENT '销售收入',
-- 平台收入
plat_amt DECIMAL(38,2) COMMENT '平台收入',
-- 配送成交额
deliver_sale_amt DECIMAL(38,2) COMMENT '配送成交额',
-- 小程序成交额
mini_app_sale_amt DECIMAL(38,2) COMMENT '小程序成交额',
-- 安卓APP成交额
android_sale_amt DECIMAL(38,2) COMMENT '安卓APP成交额',
-- 苹果APP成交额
ios_sale_amt DECIMAL(38,2) COMMENT '苹果APP成交额',
-- PC商城成交额
pcweb_sale_amt DECIMAL(38,2) COMMENT 'PC商城成交额',
-- 成交单量
order_cnt BIGINT COMMENT '成交单量',
-- 参评单量
eva_order_cnt BIGINT COMMENT '参评单量comment=>cmt',
-- 差评单量
bad_eva_order_cnt BIGINT COMMENT '差评单量negtive-comment=>ncmt',
-- 配送成交单量
deliver_order_cnt BIGINT COMMENT '配送单量',
-- 退款单量
refund_order_cnt BIGINT COMMENT '退款单量',
-- 小程序成交单量
miniapp_order_cnt BIGINT COMMENT '小程序成交单量',
-- 安卓APP订单量
android_order_cnt BIGINT COMMENT '安卓APP订单量',
-- 苹果APP订单量
ios_order_cnt BIGINT COMMENT '苹果APP订单量',
-- PC商城成交单量
pcweb_order_cnt BIGINT COMMENT 'PC商城成交单量'
)
COMMENT '销售主题日统计宽表'
PARTITIONED BY(dt STRING)
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');</pre>
step1:进行字段抽取
进行字段抽取 :presto 把查询语句插入到 主题销售宽表当中 形成一个tmp 临时子表
-
分析表关系
一切的前提是，先了解原始数据的结构和关系。
对于<mark style="box-sizing: border-box; background: rgb(255, 255, 0); color: rgb(0, 0, 0);">销售主题宽表</mark>来说，其当中的<mark style="box-sizing: border-box; background: rgb(255, 255, 0); color: rgb(0, 0, 0);">指标和维度字段分别来源</mark>于DWB层：订单明细宽表、店铺明细宽表、商品明细宽表。
比如商圈、店铺等维度来自于店铺明细宽表；大中小分类来自于商品明细宽表；而成交额等指标需要依赖订单明细宽表。
-
字段抽取
指标 维度 每个指标是什么意思 关联那些字段
列如 先把三张表 关联起来,分析指标列如 订单量是什么 ,用到了那些字段 ,先不做统计和维度分组用到了order表的ordr字段
step2:(可选):row_number 去重
可以在这里使用rn结合业务指定自己的去重标准
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
后面查询的时候 可以根据业务需求想要的 进行去重</pre>
step3:grouping sets分组
- 使用CTE表达式针对上面抽取字段、分组去重的结果进行引导
with temp as ( select 抽取字段、row_number去重)
- 根据业务需求进行维度组合，使用grouping sets进行分组。
根据主题给的维度
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺
日期+品牌
日期+大类
日期+大类+中类
日期+大类+中类+小类</pre>
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n59" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select *
from tmp group by
--step3:增强聚合维度组合
grouping sets(
(dt), --日期
(dt,city_id,city_name), --日期+城市
(dt,city_id,city_name,trade_area_id,trade_area_name),--日期+城市+商圈
(dt,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name), --日期+城市+商圈+店铺
(dt,brand_id,brand_name), --日期+品牌
(dt,max_class_id,max_class_name),--日期+大类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name),----日期+大类+中类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)--日期+大类+中类+小类
);</pre>
step4--维度字段判断返回操作
提示：可以根据待插入的目标表yp_dws.dws_sale_daycount的字段顺序，把结果返回。
--step1:字段抽取
--维度字段
o.dt, --日期
s.city_id,
s.city_name, --城市
s.trade_area_id,
s.trade_area_name,--商圈
s.id as store_id,
s.store_name,--店铺
g.brand_id,
g.brand_name, --品牌
g.max_class_id,
g.max_class_name,--商品大类
g.mid_class_id,
g.mid_class_name,--商品中类
g.min_class_id,
g.min_class_name,--商品小类
--指标字段
--订单量指标
o.order_id,--订单id
o.goods_id,--商品id
--跟钱相关的指标
o.order_amount,--订单金额
o.total_price,--商品金额（商品单价*商品数量）
o.plat_fee,--平台分润
o.dispatcher_money,--配送费
--判断条件
o.order_from, --用于判断订单来源渠道：安卓、IOS..
o.evaluation_id,--评价单ID 如果不为空 表示该笔订单有评价
o.geval_scores,--综合评分 用于判断好中差评 具体需要业务指定何为好中差
o.delievery_id,--配送单ID 如果不为空 表示是配送单 其他方式也可以商家配送 用户自提等
o.refund_id, --退款单ID 如果不为空 表示订单有退款
from yp_dwb.dwb_order_detail o
left join yp_dwb.dwb_goods_detail g on o.goods_id = g.id
left join yp_dwb.dwb_shop_detail s on o.store_id = s.id)
select
--维度表
-- 可以判断一下 是否为空
--也可以直接写 dt 是毕竟特殊 在插入的时候 作为分区字段输入
case when grouping(city_id) = 0 then city_id
else null end as city_id,
city_name, --城市
trade_area_id,
trade_area_name,--商圈
store_id, -- 店铺id
store_name,--店铺
brand_id,
brand_name, --品牌
max_class_id,
max_class_name,--商品大类
mid_class_id,
mid_class_name,--商品中类
min_class_id,
min_class_name,--商品小类
dt
from tmp
-- step3 实现增强聚合函数
group by grouping sets (
)
;</pre>
### step5 分组类型的标记
group_type 便于后续根据标记取值
* grouping (八个维度) 十进制转二进制判断
* case when grouping 判断在不在 由小到大
case when grouping(store_id,store_name) = 0
then 'store'
when grouping(trade_area_id,trade_area_name) =0
then 'trade_area'
when grouping(city_id,city_name) =0
then 'city'
when grouping(brand_id,brand_name) =0
then 'brand'
when grouping(min_class_id,min_class_name) =0
then 'min_class'
when grouping(mid_class_id,mid_class_name) =0
then 'mid_class'
when grouping(max_class_id,max_class_name) =0
then 'max_class'
when grouping(dt) =0
then 'all'
else 'others' end as group_type,
店铺 商圈 城市
品牌
小 中 大</pre>
step6 销售收入指标sale_amt 统计
--指标计算 注意每个指标都对应着8个分组维度的计算
group by grouping sets (
(dt), --日期
(dt,city_id,city_name), --日期+城市
(dt,city_id,city_name,trade_area_id,trade_area_name),--日期+城市+商圈
(dt,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name), --日期+城市+商圈+店铺
(dt,brand_id,brand_name), --日期+品牌
(dt,max_class_id,max_class_name),--日期+大类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name),----日期+大类+中类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)--日期+大类+中类+小类
image.png
-
指标分析 销售收入sale_amt
-
如果按照日期.城市.商圈.店铺 算销售收入 就要使用 订单收入 order_amount
注意很多细节 日期+城市+商圈+店铺 -- 按照每天每个城市商圈每个店铺 是 order_amount case when grouping(store_id,store_name)=0 then sum(if(store_id is not null,coalesce (order_amount ,0 ),0 )) -- 城市 商圈每个店铺的订单收入 -- todo then sum(order_amount) else null end as sale_amt, -- 扣一扣细节 如果storeid 不为null 代表有这个数据 为空 加的值为 0 -- 但是 订单金额可能为null 所以用coalesce(order_amount,0)处理 -- -if() 处理指标空值 -- -coalesce(v1，v2，v3.....) 功能：返回列表中第一个非空值 -- -nvl(order_amount,0) 空值转换函数 注意：presto是否支持 when grouping(trade_area_id,trade_area_name)=0 then sum(if(trade_area_id is not null,coalesce (order_amount ,0 ),0 )) -- 每个商圈的订单收入 when grouping ( city_id ,city_name) =0 then sum(if(city_id is not null,coalesce (order_amount ,0 ),0 ))--每个城市的 </pre> -
如果是根据品牌 类别维度分组 就要按照订单内商品的收入 具体到订单的细节 每种订单的的销售收入
-
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n127" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;"> -- 按照品牌和种类的话 要具体到细节 具体到商品
-- total_price '购买商品的价格 = 商品的数量 * 商品的单价 ',
when grouping (brand_id,brand_name)=0
then sum(if(brand_id is not null,coalesce (total_price ,0 ),0 )) -- 品牌id 和 城市id 和大类是同级别
when grouping (min_class_id,min_class_name)=0
then sum(if(min_class_id is not null,coalesce (total_price ,0 ),0 )) -- 小类
when grouping (mid_class_id,min_class_name)=0
then sum(if(min_class_id is not null,coalesce (total_price ,0 ),0 )) -- 中类
when grouping (max_class_id)=0
then sum(if(max_class_id is not null,coalesce (total_price ,0 ),0 )) -- 大类
else null end as sale_amount,</pre>
直到销售收入实现代码
剩下的 指标 按个实现
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n400" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">`--销售主题 日统计宽表的实现
--step0:根据主题需要 梳理确定表关系
with tmp as (select
--step1:字段抽取
--维度字段
o.dt, --日期
s.city_id,
s.city_name, --城市
s.trade_area_id,
s.trade_area_name,--商圈
s.id as store_id,
s.store_name,--店铺
g.brand_id,
g.brand_name, --品牌
g.max_class_id,
g.max_class_name,--商品大类
g.mid_class_id,
g.mid_class_name,--商品中类
g.min_class_id,
g.min_class_name,--商品小类
--指标字段
--订单量指标
o.order_id,--订单id
o.goods_id,--商品id
--跟钱相关的指标
o.order_amount,--订单金额
o.total_price,--商品金额（商品单价*商品数量）
o.plat_fee,--平台分润
o.dispatcher_money,--配送费
--判断条件
o.order_from, --用于判断订单来源渠道：安卓、IOS..
o.evaluation_id,--评价单ID 如果不为空 表示该笔订单有评价
o.geval_scores,--综合评分 用于判断好中差评 具体需要业务指定何为好中差
o.delievery_id,--配送单ID 如果不为空 表示是配送单 其他方式也可以商家配送 用户自提等
o.refund_id, --退款单ID 如果不为空 表示订单有退款
--step2(可选):可以在这里使用rn结合业务指定自己的去重标准
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
from yp_dwb.dwb_order_detail o
left join yp_dwb.dwb_goods_detail g on o.goods_id = g.id
left join yp_dwb.dwb_shop_detail s on o.store_id = s.id)
select
--step4:维度字段返回判断
--标准：使用grouping进行判断 分组中有没有指定的维度 如果有返回 如果没有返回null
case when grouping(city_id) = 0 --分组中包含city_id
then city_id
else null end as city_id,
case when grouping(city_id) = 0
then city_name
else null end as city_name, --城市
-- city_id,
-- city_name --也可以直接不判断取值
case when grouping(trade_area_id) = 0 --商圈
then trade_area_id
else null end as trade_area_id,
case when grouping(trade_area_id) = 0
then trade_area_name
else null end as trade_area_name,
case when grouping(store_id) = 0 --店铺
then store_id
else null end as store_id,
case when grouping(store_id) = 0
then store_name
else null end as store_name,
case when grouping(brand_id) = 0 --品牌
then brand_id
else null end as brand_id,
case when grouping(brand_id) = 0
then brand_name
else null end as brand_name,
case when grouping(max_class_id) = 0 --大类
then max_class_id
else null end as max_class_id,
case when grouping(max_class_id) = 0
then max_class_name
else null end as max_class_name,
case when grouping(mid_class_id) = 0 --中类
then mid_class_id
else null end as mid_class_id,
case when grouping(mid_class_id) = 0
then mid_class_name
else null end as mid_class_name,
case when grouping(min_class_id) = 0 --小类
then min_class_id
else null end as min_class_id,
case when grouping(min_class_id) = 0
then min_class_name
else null end as min_class_name,
--step5:分组类型的标记问题 便于后续根据标记来取值 group_type group_id
---方式1：使用grouping针对8个维度字段一起判断 根据返回的结果转换为8位2进制数 来实现精准判断【通用】
-- grouping(dt,city_id,trade_area_id,store_id,brand_id,max_class_id,mid_class_id,min_class_id) as type
--方式2：使用case when寻找不同分组之间的差异（该方法不一定通用）
--利用case when执行顺序 把维度从小到大过滤筛选
case when grouping(store_id,store_name) = 0
then 'store'
when grouping(trade_area_id,trade_area_name) =0
then 'trade_area'
when grouping(city_id,city_name) =0
then 'city'
when grouping(brand_id,brand_name) =0
then 'brand'
when grouping(min_class_id,min_class_name) =0
then 'min_class'
when grouping(mid_class_id,mid_class_name) =0
then 'mid_class'
when grouping(max_class_id,max_class_name) =0
then 'max_class'
when grouping(dt) =0
then 'all'
else 'others' end as group_type,
--step6：销售收入sale_amount计算
case when grouping(store_id,store_name) = 0 --店铺销售收入
then sum(if(store_id is not null,coalesce(order_amount,0),0))
-- then sum(order_amount)
--todo 如果订单金额指标为null 替换成为0
---if() 处理指标空值
---coalesce(v1，v2，v3.....) 功能：返回列表中第一个非空值
---nvl(order_amount,0) 空值转换函数 注意：presto是否支持
when grouping(trade_area_id,trade_area_name) = 0 --商圈销售收入
then sum(if(trade_area_id is not null,order_amount,0))
when grouping(city_id,city_name) = 0 --城市销售收入
then sum(if(city_id is not null,order_amount,0))
when grouping(brand_id,brand_name) = 0 --品牌销售收入
then sum(if(brand_id is not null,total_price,0))
when grouping(min_class_id,min_class_name) = 0 --小类销售收入
then sum(if(min_class_id is not null,total_price,0))
when grouping(mid_class_id,mid_class_name) = 0 --中类销售收入
then sum(if(mid_class_id is not null,total_price,0))
when grouping(max_class_id,max_class_name) = 0 --大类销售收入
then sum(if(max_class_id is not null,total_price,0))
when grouping(dt) = 0 --日期（每天）销售收入
then sum(if(dt is not null,order_amount,0))
else null end as sale_amt---销售收入
from tmp group by
--step3:增强聚合维度组合
grouping sets(
(dt), --日期
(dt,city_id,city_name), --日期+城市
(dt,city_id,city_name,trade_area_id,trade_area_name),--日期+城市+商圈
(dt,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name), --日期+城市+商圈+店铺
(dt,brand_id,brand_name), --日期+品牌
(dt,max_class_id,max_class_name),--日期+大类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name),----日期+大类+中类
(dt,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)--日期+大类+中类+小类
);</pre>```
## 最终插入到yp_dws.dws_sale_daycount表中
网友评论