還沒等老板開口，我已經把 ADS 層建好了
作者：甜獲師兄
ADS層是數據倉庫與業務系統之間的橋梁，將經過清洗、轉換和匯總的數據以業務友好的方式呈現給最終用戶。今天以我們項目中的實際案例為例進行ADS層建設思路介紹。
這次老板沒催我，我提前就把ADS層給建設好了。ADS（Application Data Store）層是數據倉庫的最頂層，直接面向業務應用，為數據分析、報表展示和業務決策提供數據支持。它是數據倉庫與業務系統之間的橋梁，將經過清洗、轉換和匯總的數據以業務友好的方式呈現給最終用戶。
今天以我們項目中的實際案例為例進行ADS層建設思路介紹。數倉代碼可訪問：這次老板沒催我，我提前就把ADS層給建設好了。ADS（Application Data Store）層是數據倉庫的最頂層，直接面向業務應用，為數據分析、報表展示和業務決策提供數據支持。它是數據倉庫與業務系統之間的橋梁，將經過清洗、轉換和匯總的數據以業務友好的方式呈現給最終用戶。今天以我們項目中的實際案例為例進行ADS層建設思路介紹。
數倉代碼可訪問：
- github：https://github.com/Mrkuhuo/data-warehouse-learning
- gitee：https://gitee.com/wzylzjtn/data-warehouse-learning
一、ADS層建設思路
1. 設計原則
- 業務導向：ADS層設計應以業務需求為核心，確保數據能夠直接支持業務決策和運營分析。
- 性能優化：針對高頻查詢場景進行優化，包括合理的分區策略、索引設計和物化視圖。
- 數據一致性：確保數據口徑統一，避免不同報表之間的數據不一致。
- 可擴展性：設計時考慮未來業務擴展需求，預留足夠的擴展空間。
- 易用性：提供簡單直觀的數據結構，降低業務人員使用門檻。
2. 數據模型設計
ADS層通常采用星型模型或雪花模型，主要包含以下幾類表：
- 匯總事實表：按不同維度（時間、地區、產品等）匯總的事實數據。
- 維度表：包含業務實體的屬性信息，如客戶、產品、地區等。
- 指標表：存儲預計算的業務指標，如轉化率、留存率等。
- 報表表：直接面向報表展示的寬表，包含多個維度的指標。
3. 數據更新策略
- 增量更新：對于大表，采用增量更新策略，只處理新增或變更的數據。
- 全量刷新：對于小表或需要保證數據一致性的場景，采用全量刷新策略。
- 定時調度：根據業務需求設置合理的調度周期，如每日、每周或每月。
二、ADS層應用場景
- 業務報表：為管理層提供決策支持的各類報表。
- 運營分析：支持運營人員進行用戶行為分析和營銷效果評估。
- 風險控制：提供風險監控和預警數據。
- 客戶服務：支持客服人員進行客戶畫像分析和精準服務。
- 產品優化：為產品團隊提供用戶反饋和使用數據，指導產品迭代。
三、實戰案例：用戶價值分析報表
1. 業務背景
電商平臺需要對用戶進行價值分層，以便進行精準營銷和個性化服務?；赗FM模型（Recency、Frequency、Monetary）對用戶進行價值評估，并計算用戶的生命周期價值，為運營決策提供數據支持。 02
2. 數據來源
- DWS層：用戶交易寬表（dws_trade_user_order_td）和用戶登錄寬表（dws_user_user_login_td）
- ADS層：歷史用戶價值分析數據（用于計算價值發展趨勢）
3. 實現方案
(1) 建表(ads.ads_user_value_analysis)
-- 用戶價值分析表
CREATE TABLE IF NOT EXISTS ads.ads_user_value_analysis
(
dt DATE COMMENT '統計日期',
user_id BIGINT COMMENT '用戶ID',
order_count_td BIGINT COMMENT '累計下單次數',
order_amount_td DECIMAL(20,2) COMMENT '累計下單金額',
order_last_date DATE COMMENT '最近下單日期',
order_first_date DATE COMMENT '首次下單日期',
login_count_td BIGINT COMMENT '累計登錄次數',
login_last_date DATE COMMENT '最近登錄日期',
average_order_amount DECIMAL(20,2) COMMENT '平均客單價',
purchase_cycle_days INT COMMENT '平均購買周期(天)',
account_days INT COMMENT '賬號存續天數',
life_time_value DECIMAL(20,2) COMMENT '生命周期價值(LTV)',
recency_score INT COMMENT '最近活躍度評分(R)',
frequency_score INT COMMENT '活動頻次評分(F)',
monetary_score INT COMMENT '消費金額評分(M)',
rfm_score INT COMMENT 'RFM總分',
user_value_level STRING COMMENT '用戶價值分層',
active_status STRING COMMENT '活躍狀態',
life_cycle_status STRING COMMENT '生命周期狀態',
shopping_preference STRING COMMENT '購物偏好',
growth_trend STRING COMMENT '價值發展趨勢'
)
COMMENT '用戶價值分層分析報表，基于RFM模型計算用戶價值'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');(2) 核心指標計算
RFM模型評分：
- Recency（最近購買時間）：根據最近一次購買距今的天數評分（1-5分）
- Frequency（購買頻率）：根據累計購買次數評分（1-5分）
- Monetary（消費金額）：根據累計消費金額評分（1-5分）
用戶價值分層：
- 高價值：RFM總分≥13分
- 中高價值：RFM總分10-12分
- 中價值：RFM總分7-9分
- 低價值：RFM總分4-6分 流失風險：RFM總分≤3分
生命周期價值(LTV) ：
- 計算公式：平均客單價 × 年購買頻率 × 預期客戶生命周期（年）
- 年購買頻率：總購買次數×365/賬號存續天數
用戶狀態分類：
- 活躍狀態：基于最近交易和登錄時間
- 生命周期狀態：基于交易行為和訂單歷史
- 購物偏好：基于購買頻率和金額
- 價值發展趨勢：比較當前RFM評分與30天前的評分
(3) 具體邏輯實現
INSERT INTO ads.ads_user_value_analysis
(dt, user_id, order_count_td, order_amount_td, order_last_date, order_first_date,
login_count_td, login_last_date, average_order_amount, purchase_cycle_days, account_days,
life_time_value, recency_score, frequency_score, monetary_score, rfm_score,
user_value_level, active_status, life_cycle_status, shopping_preference, growth_trend)
SELECT
-- 基礎日期統計
date('${pdate}') AS dt, -- 統計日期，使用調度日期參數
t1.user_id AS user_id, -- 用戶ID
t1.order_count_td, -- 累計下單次數，來自交易寬表
t1.total_amount_td, -- 累計下單金額，來自交易寬表
-- 格式化日期為yyyy-MM-dd格式
date_format(t1.order_last_date, '%Y-%m-%d') AS order_last_date, -- 最近下單日期
date_format(t1.order_first_date, '%Y-%m-%d') AS order_first_date, -- 首次下單日期
t2.login_count_td, -- 累計登錄次數，來自用戶登錄寬表
date_format(t2.login_last_date, '%Y-%m-%d') AS login_last_date, -- 最近登錄日期
-- 計算衍生指標
-- 計算平均客單價 = 總金額/訂單數
CASE WHEN t1.order_count_td > 0 THEN t1.total_amount_td/t1.order_count_td ELSE 0 END AS average_order_amount,
-- 計算平均購買周期(天) = (最后訂單日期-首次訂單日期)/(訂單數-1)
CASE WHEN t1.order_count_td > 1
THEN datediff(t1.order_last_date, t1.order_first_date)/(t1.order_count_td-1)
ELSE NULL END AS purchase_cycle_days,
-- 計算賬號存續天數 = 當前日期-注冊日期
datediff(current_date(), t2.register_date) AS account_days,
-- 計算生命周期價值(LTV) = 平均客單價 * 年購買頻率 * 預期客戶生命周期（年）
-- 年購買頻率計算方式: 總購買次數*365/賬號存續天數，即年化購買頻率
-- 預期客戶生命周期取3年作為默認預估
CASE WHEN t1.order_count_td > 0 AND datediff(current_date(), t2.register_date) > 0
THEN (t1.total_amount_td/t1.order_count_td) * (t1.order_count_td*365/datediff(current_date(), t2.register_date)) * 3
ELSE 0 END AS life_time_value,
-- RFM模型計算 - 為每個維度打分(1-5分)
-- Recency(最近購買時間)評分: 越近分數越高
CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5 -- 30天內
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4 -- 31-60天
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3 -- 61-90天
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2 -- 91-180天
ELSE 1 -- 180天以上
END AS recency_score,
-- Frequency(購買頻率)評分: 購買次數越多分數越高
CASE
WHEN t1.order_count_td >= 20 THEN 5 -- 20次及以上
WHEN t1.order_count_td >= 10 THEN 4 -- 10-19次
WHEN t1.order_count_td >= 5 THEN 3 -- 5-9次
WHEN t1.order_count_td >= 2 THEN 2 -- 2-4次
ELSE 1 -- 1次
END AS frequency_score,
-- Monetary(消費金額)評分: 總消費金額越高分數越高
CASE
WHEN t1.total_amount_td >= 10000 THEN 5 -- 1萬元及以上
WHEN t1.total_amount_td >= 5000 THEN 4 -- 5千-1萬元
WHEN t1.total_amount_td >= 2000 THEN 3 -- 2千-5千元
WHEN t1.total_amount_td >= 500 THEN 2 -- 500-2千元
ELSE 1 -- 500元以下
END AS monetary_score,
-- 計算RFM總分 = R分 + F分 + M分
(CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END) +
(CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END) +
(CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) AS rfm_score,
-- 用戶價值分層: 根據RFM總分(3-15分)進行分層
CASE
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 13 THEN '高價值' -- 13-15分
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 10 THEN '中高價值' -- 10-12分
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 7 THEN '中價值' -- 7-9分
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 4 THEN '低價值' -- 4-6分
ELSE '流失風險' -- 3分
END AS user_value_level,
-- 活躍狀態: 基于最近交易和登錄時間
CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 OR datediff(current_date(), t2.login_last_date) <= 7 THEN '活躍' -- 30天內有交易或7天內有登錄
WHEN datediff(current_date(), t1.order_last_date) <= 90 OR datediff(current_date(), t2.login_last_date) <= 30 THEN '沉默' -- 90天內有交易或30天內有登錄
ELSE '流失' -- 超過90天未交易且超過30天未登錄
END AS active_status,
-- 生命周期狀態: 基于交易行為和訂單歷史
CASE
WHEN datediff(current_date(), t1.order_first_date) <= 30 AND t1.order_count_td <= 2 THEN '新用戶' -- 30天內首次購買且購買次數<=2次
WHEN t1.order_count_td >= 3 AND (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END) >= 4 THEN '成長期' -- 購買>=3次且近期活躍(60天內)
WHEN t1.order_count_td >= 5 AND (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END) >= 3 THEN '成熟期' -- 購買>=5次且90天內有購買
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END) <= 2 AND (CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END) >= 3 THEN '衰退期' -- 購買次數>=5但超過90天未購買
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END) >= 3 AND datediff(t1.order_last_date, t0.lag_order_date) > 90 THEN '回流' -- 最近90天內有購買但之前超過90天未購買
ELSE '新用戶' -- 默認為新用戶
END AS life_cycle_status,
-- 購物偏好: 基于購買頻率和金額
CASE
WHEN (CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END) >= 4 AND (CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) <= 3 THEN '高頻低額' -- 高頻率低金額: 購買頻繁但單價較低
WHEN (CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END) <= 3 AND (CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 4 THEN '低頻高額' -- 低頻率高金額: 購買較少但大額消費
WHEN (CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END) >= 4 AND (CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) >= 4 THEN '高頻高額' -- 高頻率高金額: 高價值客戶，頻繁且大額
ELSE '低頻低額' -- 低頻率低金額: 低價值客戶
END AS shopping_preference,
-- 價值發展趨勢: 比較當前RFM評分與30天前的評分
CASE
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) > COALESCE(t3.previous_rfm_score, 0) THEN '上升' -- 當前分數高于30天前，趨勢上升
WHEN (CASE
WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
ELSE 1
END +
CASE
WHEN t1.order_count_td >= 20 THEN 5
WHEN t1.order_count_td >= 10 THEN 4
WHEN t1.order_count_td >= 5 THEN 3
WHEN t1.order_count_td >= 2 THEN 2
ELSE 1
END +
CASE
WHEN t1.total_amount_td >= 10000 THEN 5
WHEN t1.total_amount_td >= 5000 THEN 4
WHEN t1.total_amount_td >= 2000 THEN 3
WHEN t1.total_amount_td >= 500 THEN 2
ELSE 1
END) < COALESCE(t3.previous_rfm_score, 0) THEN '下降' -- 當前分數低于30天前，趨勢下降
ELSE '穩定' -- 分數相等，趨勢穩定
END AS growth_trend
FROM
(
-- 訂單數據: 獲取用戶交易相關信息
SELECT
user_id,
k1,
order_date_last,
LAG(order_date_last, 1, NULL) OVER(PARTITION BY user_id ORDER BY k1) AS lag_order_date -- 獲取上一次最近下單日期，用于計算回流狀態
FROM dws.dws_trade_user_order_td
WHERE k1 = date('${pdate}') -- 取當天分區數據
) t0
JOIN
(
-- 訂單數據: 獲取用戶交易相關信息
SELECT
user_id,
SUM(order_count_td) AS order_count_td, -- 累計下單次數
SUM(total_amount_td) AS total_amount_td, -- 累計下單金額
MAX(order_date_last) AS order_last_date, -- 最近下單日期
MIN(order_date_first) AS order_first_date -- 首次下單日期
FROM dws.dws_trade_user_order_td
WHERE k1 = date('${pdate}') -- 取當天分區數據
GROUP BY user_id
) t1
ON t0.user_id = t1.user_id
JOIN
(
-- 登錄數據: 獲取用戶登錄相關信息
SELECT
user_id,
SUM(login_count_td) AS login_count_td, -- 累計登錄次數
MAX(login_date_last) AS login_last_date, -- 最近登錄日期
date('2020-01-01') AS register_date -- 注冊日期，使用默認值
FROM dws.dws_user_user_login_td
WHERE k1 = date('${pdate}') -- 取當天分區數據
GROUP BY user_id
) t2
ON t1.user_id = t2.user_id
LEFT JOIN
(
-- 上月RFM評分數據: 用于計算價值發展趨勢
SELECT
user_id,
recency_score + frequency_score + monetary_score AS previous_rfm_score -- 30天前的RFM總分
FROM ads.ads_user_value_analysis
WHERE dt = date_sub(date('${pdate}'), 30) -- 取30天前的數據
) t3
ON t1.user_id = t3.user_id;ADS（Application Data Store）層是數據倉庫的最頂層，直接面向業務應用。其建設核心是以業務需求為導向，將經過清洗、轉換和匯總的數據以業務友好的方式呈現。
ADS層設計應遵循業務導向、性能優化、數據一致性、可擴展性和易用性五大原則。主要包含匯總事實表、維度表、指標表和報表表等數據模型。數據更新策略包括增量更新和全量刷新，需根據業務場景選擇。ADS層實現需要高性能存儲引擎（如Doris）和計算框架，并建立完善的數據質量控制機制。通過ADS層，企業可以實現精準營銷、會員運營、風險控制、客戶服務和產品優化等業務目標，為決策提供數據支持。
責任編輯：趙寧寧
來源：
大數據技能圈
