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