DWS層存在的意義：
通過(guò)指標(biāo)體系，分析并整合出通用的连锯、重復(fù)使用的派生指標(biāo)归苍，減少?gòu)模―WD、DIM）==>（ADS）重復(fù)計(jì)算構(gòu)建指標(biāo)體系：
原子指標(biāo) = 業(yè)務(wù)過(guò)程+度量值+聚合邏輯
派生指標(biāo) = 原子指標(biāo)+統(tǒng)計(jì)周期+業(yè)務(wù)限定+統(tǒng)計(jì)粒度
衍生指標(biāo) = 多個(gè)派生指標(biāo)的整合（比例萎庭、比率等）-
使用Excel匯總每一個(gè)派生指標(biāo)的具體構(gòu)成（基本上就是指標(biāo)體系）霜医，包括上述等式中的每一部分，例如下圖驳规。從已匯總的表格中提煉公共點(diǎn)
部分派生指標(biāo)的統(tǒng)計(jì) 公共三要素：業(yè)務(wù)過(guò)程肴敛、統(tǒng)計(jì)周期、統(tǒng)計(jì)粒度：
1吗购、業(yè)務(wù)過(guò)程：對(duì)應(yīng)sql中 FROM医男，對(duì)應(yīng)DWD中不同的事實(shí)表
2、統(tǒng)計(jì)周期：對(duì)應(yīng)sql中 WHERE 的時(shí)間篩選部分捻勉，對(duì)應(yīng)DWS中不同的表（以命名規(guī)范區(qū)分）
3镀梭、統(tǒng)計(jì)粒度：對(duì)應(yīng)sql中 GROUP BY
確認(rèn)上述三要素相同的派生指標(biāo)將放置于同一張DWS表中，具體的踱启，會(huì)根據(jù)不同的度量值和聚合邏輯設(shè)計(jì)表中的select字段报账。DWS層表格命名規(guī)范：
dws--數(shù)據(jù)域--統(tǒng)計(jì)粒度--業(yè)務(wù)過(guò)程--統(tǒng)計(jì)周期（1d/nd/td）
設(shè)計(jì)（指標(biāo)分析）舉例
1研底、指標(biāo)需求（ADS層）
品牌相關(guān)需求
品類相關(guān)需求
2、指標(biāo)定義
拿到需求后透罢，按照派生指標(biāo)公式（派生指標(biāo) = 原子指標(biāo)+統(tǒng)計(jì)周期+業(yè)務(wù)限定+統(tǒng)計(jì)粒度）榜晦，對(duì)需求進(jìn)行拆分、定義羽圃，舉例如下：
派生指標(biāo)定義舉例
衍生指標(biāo)定義舉例
3乾胶、匯總派生指標(biāo)
指標(biāo)定義完畢后，建立Excel匯總指標(biāo)體系朽寞，將三要素相同的指標(biāo)用相同底色標(biāo)注识窿。
相同底色的指標(biāo)將在同一個(gè)DWS表中體現(xiàn)。
指標(biāo)體系
4脑融、建表 & 裝載（以品牌指標(biāo)為例）
- 最近1天下單——建表
1喻频、將Excel表中的字段寫入
2、根據(jù)業(yè)務(wù)過(guò)程（下單）肘迎，參考對(duì)應(yīng)的DWD層事實(shí)表（dwd_order_detail_inc）半抱，添加與主鍵（粒度）相關(guān)的其他字段，用以豐富表字段內(nèi)容
3膜宋、粒度細(xì)化
由于“最近n天”的數(shù)據(jù)來(lái)源于“最近1天”，在統(tǒng)計(jì)部分累加型指標(biāo)時(shí)（最近n天下單人數(shù)）炼幔，是按照每天進(jìn)行讀取秋茫，這樣就會(huì)出現(xiàn)因時(shí)間范圍劃分不準(zhǔn)確而產(chǎn)生的重復(fù)數(shù)據(jù)問題（應(yīng)按照n天進(jìn)行劃分并去重，最后累加乃秀。但是實(shí)際上是將n個(gè)獨(dú)立去重的結(jié)果相累加）肛著。這種問題是因?yàn)榱６葻o(wú)法滿足統(tǒng)計(jì)需求而產(chǎn)生的，故要解決此問題跺讯，我們需要對(duì)粒度做出進(jìn)一步的細(xì)化枢贿，從單一的（tm_id），變成（user_id, tm_id）刀脏。粒度越細(xì)局荚，表通用性越強(qiáng)
CREATE EXTERNAL TABLE dws_trade_user_tm_order_1d
(
`user_id` STRING COMMENT '用戶id',
`tm_id` STRING COMMENT '品牌id',
`tm_name` STRING COMMENT '品牌名稱',
`order_count` BIGINT COMMENT '最近1日訂單數(shù)',
-- `order_user_count` BIGINT COMMENT '最近1日訂單人數(shù)',
`order_num` BIGINT COMMENT '最近1日商品下單件數(shù)',
`order_original_amount` DECIMAL(16, 2) COMMENT '最近1日下單原始價(jià)格',
`order_activity_amount` DECIMAL(16, 2) COMMENT '最近1日下單活動(dòng)優(yōu)惠',
`order_coupon_amount` DECIMAL(16, 2) COMMENT '最近1日下單優(yōu)惠券優(yōu)惠',
`order_total_amount` DECIMAL(16, 2) COMMENT '最近1日下單最終價(jià)格'
) COMMENT '交易域用戶品牌粒度訂單最近1日匯總事實(shí)表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/xxx'
TBLPROPERTIES ('orc.compress' = 'snappy');
- 最近1天下單——裝載
從相關(guān)表格中拉取數(shù)據(jù)，按照分區(qū)規(guī)劃愈污、數(shù)據(jù)流動(dòng)情況進(jìn)行裝載
insert overwrite table dws_trade_user_tm_order_1d partition(dt='2020-06-14')
select
user_id,
tm_id,
tm_name,
count(*) order_count,
sum(sku_num) order_num,
sum(split_original_amount) order_original_amount,
sum(nvl(split_activity_amount,0.0)) order_activity_amount,
sum(nvl(split_coupon_amount,0.0)) order_coupon_amount,
sum(split_total_amount) order_total_amount
from
(
select
sku_id,
user_id,
sku_num,
split_original_amount,
split_activity_amount,
split_coupon_amount,
split_total_amount
from dwd_trade_order_detail_inc
where dt = '2020-06-14'
)od
left join
(
select
id,
tm_id,
tm_name
from dim_sku_full
where dt='2020-06-14'
)sku
on od.sku_id=sku.id
group by user_id,tm_id,tm_name;
- 最近n天下單——建表
DROP TABLE IF EXISTS dws_trade_user_tm_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_tm_order_nd
(
`user_id` STRING COMMENT '用戶id',
`tm_id` STRING COMMENT '品牌id',
`tm_name` STRING COMMENT '品牌名稱',
`order_count_7d` BIGINT COMMENT '最近7日訂單數(shù)',
`order_num_7d` BIGINT COMMENT '最近7日商品下單件數(shù)',
`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下單原始價(jià)格',
`order_activity_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下單活動(dòng)優(yōu)惠',
`order_coupon_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下單優(yōu)惠券優(yōu)惠',
`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下單最終價(jià)格',
`order_count_30d` BIGINT COMMENT '最近30日訂單數(shù)',
`order_num_30d` BIGINT COMMENT '最近30日商品下單件數(shù)',
`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下單原始價(jià)格',
`order_activity_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下單活動(dòng)優(yōu)惠',
`order_coupon_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下單優(yōu)惠券優(yōu)惠',
`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下單最終價(jià)格'
) COMMENT '交易域用戶品牌粒度訂單最近n日匯總事實(shí)表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/xxx'
TBLPROPERTIES ('orc.compress' = 'snappy');
- 最近n天下單——裝載
1耀态、最近1天表格已搭建完畢，最近n天直接使用最近1天進(jìn)行裝載
2暂雹、最近n天實(shí)際上是“最近7天”與“最近30天”的合并首装，那么，在最外層的篩選中杭跪，必須按照“最近30天”的范圍進(jìn)行篩選仙逻，以保證數(shù)據(jù)準(zhǔn)確性驰吓。
select
user_id,
tm_id,
tm_name,
sum(if(dt>=date_add('2020-06-14',-6),order_count,0)) order_count_7d,
sum(if(dt>=date_add('2020-06-14',-6),order_num,0)) order_num_7d,
sum(if(dt>=date_add('2020-06-14',-6),order_original_amount,0)) order_original_amount_7d,
sum(if(dt>=date_add('2020-06-14',-6),order_activity_amount,0)) order_activity_amount_7d,
sum(if(dt>=date_add('2020-06-14',-6),order_coupon_amount,0)) order_coupon_amount_7d,
sum(if(dt>=date_add('2020-06-14',-6),order_total_amount,0)) order_total_amount_7d,
sum(order_count) order_count_30d,
sum(order_num) order_num_30d,
sum(order_original_amount) order_original_amount_30d,
sum(order_activity_amount) order_activity_amount_30d,
sum(order_coupon_amount) order_coupon_amount_30d,
sum(order_total_amount) order_total_amount_30d
from dws_trade_user_tm_order_1d
where dt>=date_add('2020-06-14',-29)
group by user_id,tm_id,tm_name;
最近1日、最近n日設(shè)計(jì)思想總結(jié)
- 品牌表系奉、品類表兩者數(shù)據(jù)的來(lái)源均為DWD層的dwd_trade_order_detail_inc檬贰，他們之間的主要區(qū)別就在于sql語(yǔ)句中的GROUP BY聚合字段。
- 即便是當(dāng)前品牌表粒度(user_id, tm_id)喜最、品類表粒度(user_id, cate_id)已經(jīng)做了細(xì)化偎蘸，兩者的聚合過(guò)程仍不可避免的存在冗余。（冗余并不準(zhǔn)確瞬内，但實(shí)在是找不到別的詞來(lái)形容了迷雪。實(shí)際上這一整段都描述的都不準(zhǔn)確，將就看吧）
- 為了解決這樣的問題虫蝶，提高整個(gè)DWS章咧、ADS層統(tǒng)計(jì)性能，我們?nèi)孕枰獙?duì)粒度做出更加進(jìn)一步的細(xì)化能真。
- 最終細(xì)化結(jié)果為（user_id, sku_id）赁严。至此，將品牌粉铐、品類指標(biāo)匯總為一張DWS表疼约，在需要時(shí)進(jìn)行二次聚合。以下為“用戶商品粒度訂單最近1日”的最終建表語(yǔ)句蝙泼。
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(
`user_id` STRING COMMENT '用戶id',
`sku_id` STRING COMMENT 'sku_id',
`sku_name` STRING COMMENT 'sku名稱',
`category1_id` STRING COMMENT '一級(jí)分類id',
`category1_name` STRING COMMENT '一級(jí)分類名稱',
`category2_id` STRING COMMENT '一級(jí)分類id',
`category2_name` STRING COMMENT '一級(jí)分類名稱',
`category3_id` STRING COMMENT '一級(jí)分類id',
`category3_name` STRING COMMENT '一級(jí)分類名稱',
`tm_id` STRING COMMENT '品牌id',
`tm_name` STRING COMMENT '品牌名稱',
`order_count_1d` BIGINT COMMENT '最近1日下單次數(shù)',
`order_num_1d` BIGINT COMMENT '最近1日下單件數(shù)',
`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下單原始金額',
`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活動(dòng)優(yōu)惠金額',
`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日優(yōu)惠券優(yōu)惠金額',
`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下單最終金額'
) COMMENT '交易域用戶商品粒度訂單最近1日匯總事實(shí)表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/xxx'
TBLPROPERTIES ('orc.compress' = 'snappy');
歷史至今匯總表設(shè)計(jì)思想
我是菜雞程剥，沒弄明白
需求舉例如下：
歷史匯總表需求舉例
