老板又讓我負責數(shù)倉 DWS 層建設(shè)了...
又來活了，今天開始DWS層的建設(shè)。DWS（Data Warehouse Service）層是數(shù)據(jù)倉庫中的服務(wù)數(shù)據(jù)層，它基于DWD層的明細數(shù)據(jù)，按照業(yè)務(wù)主題對數(shù)據(jù)進行輕度匯總，形成主題寬表，主要包含了1d（最近1日）、nd（最近n日）、td（歷史至今）等時間維度的指標，是數(shù)據(jù)倉庫中承上啟下的關(guān)鍵環(huán)節(jié)，既保證了數(shù)據(jù)的可復(fù)用性，又為上層應(yīng)用提供了標準化的數(shù)據(jù)服務(wù)。
今天以我們項目中的實際案例為例進行DWS層建設(shè)思路介紹。數(shù)倉代碼可訪問：
- github：https://github.com/Mrkuhuo/data-warehouse-learning
- gitee：https://gitee.com/wzylzjtn/data-warehouse-learning
一、DWS層定位與價值
1. DWS角色定位
DWS（Data Warehouse Service）層是數(shù)據(jù)倉庫的服務(wù)數(shù)據(jù)層，位于DWD層之上，ADS層之下。它主要承擔了將明細數(shù)據(jù)進行主題聚合的職責，是數(shù)據(jù)倉庫中承上啟下的關(guān)鍵環(huán)節(jié)。
2. 業(yè)務(wù)價值
業(yè)務(wù)價值：
- 降低計算成本：預(yù)計算常用指標，避免重復(fù)計算
- 統(tǒng)一指標口徑：確保全公司指標計算規(guī)則統(tǒng)一
- 提升分析效率：提供主題化的寬表，方便業(yè)務(wù)分析
- 支持多維分析：保留完整維度，支持靈活查詢
二、DWS層設(shè)計思路
1. 主題劃分
主題是DWS層最重要的設(shè)計要素，需要從以下幾個方面考慮：
(1) 業(yè)務(wù)維度劃分：
- 交易域：訂單、支付、退款等
- 用戶域：注冊、登錄、畫像等
- 流量域：訪問、瀏覽、跳轉(zhuǎn)等
- 商品域：商品、類目、品牌等
(2) 分析維度考慮
- 時間維度：日、周、月、年
- 地理維度：省份、城市、區(qū)域
- 用戶維度：設(shè)備、渠道、等級
- 業(yè)務(wù)維度：品類、品牌、店鋪
2. 粒度設(shè)計
(1) 基礎(chǔ)粒度
- 用戶粒度：用戶行為分析
- 商品粒度：商品銷售分析
- 店鋪粒度：店鋪運營分析
- 訂單粒度：交易過程分析
(2) 時間粒度
- 1d：最近1日匯總，日常監(jiān)控
- nd：最近n日匯總，趨勢分析
- td：歷史至今匯總，累計分析
3. 指標體系
(1) 指標類型
- 統(tǒng)計指標：數(shù)量、金額等
- 比率指標：占比、轉(zhuǎn)化率等
- 環(huán)比指標：增長率、變化率等
- 復(fù)合指標：加權(quán)分數(shù)、綜合評分等
(2) 計算方式
- 累計值：SUM、COUNT等
- 去重值：COUNT DISTINCT等
- 最新值：MAX、LAST_VALUE等
- 平均值：AVG、MEDIAN等
三、實戰(zhàn)案例：交易行為分析
1. 業(yè)務(wù)場景
分析用戶購物行為，包括：
- 商品購買情況
- 用戶消費習慣
- 品類偏好分析
- 支付方式分析
2. 表設(shè)計方案
-- 用戶商品交易行為匯總表（最近N日）
CREATE TABLE dws.dws_trade_user_sku_order_nd (
user_id STRING COMMENT '用戶ID',
sku_id STRING COMMENT '商品ID',
k1 DATE COMMENT '數(shù)據(jù)日期',
-- 商品維度冗余
sku_name STRING COMMENT '商品名稱',
category1_id STRING COMMENT '一級品類ID',
category1_name STRING COMMENT '一級品類名稱',
tm_id STRING COMMENT '品牌ID',
tm_name STRING COMMENT '品牌名稱',
-- 7日匯總指標
order_count_7d BIGINT COMMENT '7日下單次數(shù)',
order_num_7d BIGINT COMMENT '7日購買件數(shù)',
order_amount_7d DECIMAL(16,2) COMMENT '7日下單金額',
-- 30日匯總指標
order_count_30d BIGINT COMMENT '30日下單次數(shù)',
order_num_30d BIGINT COMMENT '30日購買件數(shù)',
order_amount_30d DECIMAL(16,2) COMMENT '30日下單金額'
) COMMENT '交易域用戶商品粒度訂單最近N日匯總表'
PARTITION BY k1;3. 實現(xiàn)方案
(1) 維度關(guān)聯(lián)
-- 1. 關(guān)聯(lián)商品維度信息
SELECT
od.user_id, od.sku_id, od.k1,
-- 2. 冗余維度屬性
COALESCE(sku.sku_name, '未知商品') as sku_name,
COALESCE(sku.category1_id, '-1') as category1_id,
COALESCE(sku.category1_name, '未知品類') as category1_name,
COALESCE(sku.tm_id, '-1') as tm_id,
COALESCE(sku.tm_name, '未知品牌') as tm_name,
-- 3. 統(tǒng)計指標
od.order_count_1d,
od.order_num_1d,
od.order_amount_1d
FROM order_detail od
LEFT JOIN dim.dim_sku_full sku
ON od.sku_id = sku.id
AND sku.k1 = (
SELECT MAX(k1)
FROM dim.dim_sku_full
WHERE k1 <= DATE('${pdate}')
);(2) 指標計算
-- 1. 計算匯總指標
SELECT
user_id, sku_id, k1,
sku_name, category1_id, category1_name,
tm_id, tm_name,
-- 2. 計算7日累計
SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
order_count_1d, 0)) AS order_count_7d,
SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
order_num_1d, 0)) AS order_num_7d,
SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
order_amount_1d, 0)) AS order_amount_7d,
-- 3. 計算30日累計
SUM(order_count_1d) AS order_count_30d,
SUM(order_num_1d) AS order_num_30d,
SUM(order_amount_1d) AS order_amount_30d
FROM dws.dws_trade_user_sku_order_1d
WHERE k1 >= DATE_ADD(DATE('${pdate}'), -29)
GROUP BY
user_id, sku_id, k1,
sku_name, category1_id, category1_name,
tm_id, tm_name;通過以上案例分析，我們可以看到DWS層在實際業(yè)務(wù)中的應(yīng)用方式和實現(xiàn)細節(jié)。這些設(shè)計方案和實現(xiàn)方法可以作為參考，在實際項目中根據(jù)具體需求進行調(diào)整和優(yōu)化。
