大家好，我是陈乔怀古。
资深数据仓库工程师，捣鼓大数据、数据仓库和数据治理，分享路上的“坑”与“果”，用实战经验，助你少走弯路，共同成长。
添加v：cqhg_bigdata，备注数仓/大数据/数据治理/AI大模型，领取对应资料。
后台发送“资料”获取更多大数据资源。
送你一张优惠券👇
以典型四层数据架构为例
ODS（操作数据存储）：接近源系统的数据层，保持数据原始状态 DWD（数据仓库明细层）：进行数据清洗、标准化和维度退化 DWS（数据仓库汇总层）：按主题域构建的轻度汇总层 ADS（应用数据服务）：面向具体应用的数据集，高度聚合
起点：ODS层的原始数据
旅程从ODS层开始，这里存储着从业务系统同步的原始数据。假设有一个电商平台，ODS层包含以下关键表：
-- ODS层订单表（原始数据）
-- 存储从业务系统直接同步的原始订单数据，保持数据原貌
CREATE TABLE ods.orders (
order_id BIGINT, -- 订单ID，唯一标识符
user_id BIGINT, -- 用户ID，关联用户表
total_amount DECIMAL(10,2), -- 订单总金额
status INT, -- 订单状态（原始编码）
create_time TIMESTAMP, -- 订单创建时间
update_time TIMESTAMP, -- 订单更新时间
-- 其他原始字段...
data_source STRING COMMENT '数据来源', -- 数据来源系统标识
extract_time TIMESTAMP COMMENT '抽取时间'-- 数据抽取到ODS的时间
);
-- ODS层用户表
-- 存储用户基本信息，从用户管理系统同步
CREATE TABLE ods.users (
user_id BIGINT, -- 用户ID，主键
username STRING, -- 用户名
registration_date TIMESTAMP, -- 注册日期
last_login_time TIMESTAMP, -- 最后登录时间
-- 其他原始字段...
data_source STRING, -- 数据来源
extract_time TIMESTAMP -- 抽取时间
);
-- ODS层商品表
-- 存储商品基本信息，从商品管理系统同步
CREATE TABLE ods.products (
product_id BIGINT, -- 商品ID，主键
product_name STRING, -- 商品名称
category_id INT, -- 商品类目ID
price DECIMAL(10,2), -- 商品价格
-- 其他原始字段...
data_source STRING, -- 数据来源
extract_time TIMESTAMP -- 抽取时间
);
第一站：DWD层的数据清洗与标准化
在DWD层，对原始数据进行清洗、标准化和维度退化处理：
-- 创建DWD层订单明细宽表
-- 将多个ODS表关联，进行数据清洗、标准化和维度退化
CREATE TABLE dwd.fact_order_detail AS
SELECT
-- 订单维度字段
o.order_id, -- 订单ID
o.user_id, -- 用户ID
o.total_amount, -- 订单总金额（已清洗）
-- 订单状态标准化：将数字状态码转换为可读文本
CASE
WHEN o.status = 1 THEN '待付款'
WHEN o.status = 2 THEN '已付款'
WHEN o.status = 3 THEN '已发货'
WHEN o.status = 4 THEN '已完成'
WHEN o.status = 5 THEN '已取消'
ELSE '未知状态' -- 处理异常状态
END AS order_status,
DATE(o.create_time) AS order_date, -- 订单日期（去除时间部分）
o.create_time AS order_time, -- 订单完整时间戳
-- 用户维度字段（从用户表关联获取）
u.username, -- 用户名
-- 用户类型分类：基于注册时间与订单时间的差值
CASE
WHEN DATEDIFF(o.create_time, u.registration_date) <= 30 THEN'新用户'
WHEN DATEDIFF(o.create_time, u.registration_date) <= 365 THEN'活跃用户'
ELSE'老用户'
ENDAS user_type,
-- 时间维度字段（用于时间分区和分析）
YEAR(o.create_time) AS year, -- 年份
MONTH(o.create_time) AS month, -- 月份
DAY(o.create_time) AS day, -- 日期
HOUR(o.create_time) AS hour, -- 小时
-- 数据质量监控字段
CASE
WHEN o.total_amount < 0 THEN 1-- 标记金额为负的异常数据
ELSE 0
END AS is_amount_negative,
CASE
WHEN o.user_id IS NULL THEN 1 -- 标记用户ID缺失的数据
ELSE 0
END AS is_user_missing,
-- 数据版本控制字段
o.update_time, -- 源系统更新时间
CURRENT_TIMESTAMP AS dwd_etl_time -- DWD层ETL处理时间
FROM ods.orders o
LEFT JOIN ods.users u ON o.user_id = u.user_id -- 左连接用户表，保留没有用户信息的订单
-- 数据过滤条件
WHERE o.create_time >= '2023-01-01' -- 时间范围过滤：只处理2023年后的数据
AND o.total_amount >= 0 -- 合理性过滤：排除负金额订单
AND o.status IN (1,2,3,4,5); -- 有效性过滤：只处理已知状态订单
第二站：DWS层的轻度汇总
在DWS层，按主题域进行数据轻度汇总：
-- 创建DWS层用户行为日汇总表
-- 按用户和日期维度进行轻度汇总，便于后续分析
CREATE TABLE dws.user_behavior_daily AS
SELECT
user_id, -- 用户ID
order_date, -- 订单日期
-- 基础订单指标
COUNT(DISTINCT order_id) AS daily_order_count, -- 日订单数（去重）
SUM(total_amount) AS daily_order_amount, -- 日订单总金额
AVG(total_amount) AS avg_order_amount, -- 平均订单金额
MAX(total_amount) AS max_order_amount, -- 最大订单金额
MIN(total_amount) AS min_order_amount, -- 最小订单金额
-- 使用窗口函数计算7日移动平均
AVG(SUM(total_amount)) OVER (
PARTITION BY user_id -- 按用户分区
ORDER BY order_date -- 按日期排序
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 取最近7天（包括当天）
) AS weekly_avg_amount,
-- 用户行为分析指标
COUNT(CASE WHEN order_status = '已付款' THEN order_id END) AS paid_order_count, -- 已付款订单数
COUNT(CASE WHEN order_status = '已取消' THEN order_id END) AS canceled_order_count, -- 已取消订单数
-- 用户价值分层（基于日消费金额）
CASE
WHEN SUM(total_amount) > 1000 THEN '高价值用户'
WHEN SUM(total_amount) > 500 THEN '中价值用户'
WHEN SUM(total_amount) > 100 THEN '低价值用户'
ELSE '普通用户'
END AS user_value_segment,
-- 用户活跃度分析
DATEDIFF(MAX(order_date), MIN(order_date)) AS user_active_span, -- 用户活跃时间跨度（天）
COUNT(DISTINCT order_date) AS user_active_days, -- 用户活跃天数
CURRENT_TIMESTAMPAS dws_etl_time -- DWS层ETL处理时间
FROM dwd.fact_order_detail
WHERE order_date >= DATE_SUB(CURRENT_DATE, 365) -- 只处理最近一年的数据
GROUP BY user_id, order_date -- 按用户和日期分组
HAVING daily_order_count > 0; -- 过滤掉没有订单的日期
终点站：ADS层的应用数据服务
最终，为特定应用场景构建高度聚合的ADS表：
-- 创建ADS层用户画像表
-- 为业务应用提供完整的用户画像数据
CREATE TABLE ads.user_profile AS
-- 用户行为基础统计
WITH user_behavior AS (
SELECT
user_id,
COUNT(*) AS total_orders, -- 总订单数
SUM(daily_order_amount) AS total_amount, -- 总消费金额
MAX(daily_order_amount) AS max_daily_amount, -- 单日最高消费
AVG(daily_order_amount) AS avg_daily_amount, -- 日均消费
SUM(daily_order_count) AS total_order_count, -- 总订单数（另一种计算方式）
SUM(paid_order_count) AS total_paid_orders, -- 总付款订单数
SUM(canceled_order_count) AS total_canceled_orders, -- 总取消订单数
MAX(user_active_days) AS active_days, -- 活跃天数
MAX(user_active_span) AS active_span, -- 活跃时间跨度
MAX(user_value_segment) AS value_segment -- 价值分层
FROM dws.user_behavior_daily
GROUP BY user_id
),
-- 用户最近购买行为分析
user_recency AS (
SELECT
user_id,
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS days_since_last_order, -- 距上次购买天数
NTILE(5) OVER (ORDER BY DATEDIFF(CURRENT_DATE, MAX(order_date))) AS recency_segment -- 最近度五分位
FROM dwd.fact_order_detail
GROUP BY user_id
),
-- 用户购买频率分析
user_frequency AS (
SELECT
user_id,
COUNT(DISTINCT order_date) AS order_frequency, -- 购买频率（下单天数）
NTILE(5) OVER (ORDER BY COUNT(DISTINCT order_date)) AS frequency_segment -- 频率五分位
FROM dwd.fact_order_detail
GROUP BY user_id
),
-- 用户购买金额分析
user_monetary AS (
SELECT
user_id,
SUM(total_amount) AS monetary_value, -- 总消费金额
NTILE(5) OVER (ORDER BY SUM(total_amount)) AS monetary_segment -- 金额五分位
FROM dwd.fact_order_detail
GROUP BY user_id
),
-- RFM综合分析
rfm_analysis AS (
SELECT
r.user_id,
r.recency_segment, -- 最近度分段
f.frequency_segment, -- 频率分段
m.monetary_segment, -- 金额分段
-- RFM用户分群
CASE
WHEN r.recency_segment >= 4 AND f.frequency_segment >= 4 AND m.monetary_segment >= 4 THEN '重要价值用户'
WHEN r.recency_segment >= 3 AND f.frequency_segment >= 3 AND m.monetary_segment >= 3 THEN '重要发展用户'
WHEN r.recency_segment >= 2 AND f.frequency_segment >= 2 AND m.monetary_segment >= 2 THEN '一般保持用户'
ELSE '一般发展用户'
END AS rfm_segment
FROM user_recency r
JOIN user_frequency f ON r.user_id = f.user_id
JOIN user_monetary m ON r.user_id = m.user_id
)
-- 最终用户画像数据
SELECT
u.user_id, -- 用户ID
u.username, -- 用户名
u.registration_date, -- 注册日期
-- 用户行为指标
ub.total_orders, -- 总订单数
ub.total_amount, -- 总消费金额
ub.avg_daily_amount, -- 日均消费
ub.total_paid_orders, -- 付款订单数
ub.total_canceled_orders, -- 取消订单数
ub.active_days, -- 活跃天数
ub.active_span, -- 活跃时间跨度
ub.value_segment, -- 价值分层
-- 最近行为指标
ur.days_since_last_order, -- 距上次购买天数
-- RFM分析结果
rfm.recency_segment, -- 最近度分段
rfm.frequency_segment, -- 频率分段
rfm.monetary_segment, -- 金额分段
rfm.rfm_segment, -- RFM分群
-- 用户生命周期状态判断
CASE
WHEN ur.days_since_last_order > 180 THEN '流失用户'
WHEN ur.days_since_last_order > 90 THEN '濒临流失用户'
WHEN ur.days_since_last_order > 30 THEN '一般活跃用户'
ELSE '高活跃用户'
END AS user_lifecycle_status,
-- 用户价值预测
CASE
WHEN rfm.monetary_segment = 5 AND rfm.frequency_segment >= 4 THEN '超高价值用户'
WHEN rfm.monetary_segment >= 4 AND rfm.frequency_segment >= 3 THEN '高价值用户'
WHEN rfm.monetary_segment >= 3 THEN '中价值用户'
ELSE '一般价值用户'
END AS user_value_prediction,
-- 交叉销售潜力评估
CASE
WHEN ub.avg_daily_amount > 500 AND rfm.frequency_segment >= 4 THEN '高交叉销售潜力'
WHEN ub.avg_daily_amount > 200 AND rfm.frequency_segment >= 3 THEN '中交叉销售潜力'
ELSE '低交叉销售潜力'
END AS cross_sell_potential,
CURRENT_TIMESTAMP AS ads_etl_time -- ADS层ETL处理时间
FROM ods.users u
LEFT JOIN user_behavior ub ON u.user_id = ub.user_id -- 关联用户行为数据
LEFT JOIN user_recency ur ON u.user_id = ur.user_id -- 关联最近行为数据
LEFT JOIN rfm_analysis rfm ON u.user_id = rfm.user_id -- 关联RFM分析数据
WHERE u.registration_date >= '2020-01-01'; -- 只处理2020年后注册的用户
数据质量监控与治理
在整个数据流转过程中需要确保数据质量：
-- 数据质量监控SQL
-- 监控各数据层的数据质量指标
SELECT
'DWD层'AS data_layer, -- 数据层标识
COUNT(*) AS total_records, -- 总记录数
SUM(is_amount_negative) AS negative_amount_count, -- 负金额记录数
SUM(is_user_missing) AS missing_user_count, -- 缺失用户记录数
COUNT(DISTINCT user_id) AS unique_users, -- 唯一用户数
MIN(order_date) AS earliest_date, -- 最早日期
MAX(order_date) AS latest_date -- 最晚日期
FROM dwd.fact_order_detail
UNION ALL
-- DWS层数据质量监控
SELECT
'DWS层'AS data_layer,
COUNT(*) AS total_records,
0 AS negative_amount_count, -- DWS层已清洗，无负金额
0 AS missing_user_count, -- DWS层已清洗，无缺失用户
COUNT(DISTINCT user_id) AS unique_users,
MIN(order_date) AS earliest_date,
MAX(order_date) AS latest_date
FROM dws.user_behavior_daily
UNION ALL
-- ADS层数据质量监控
SELECT
'ADS层'AS data_layer,
COUNT(*) AS total_records,
0 AS negative_amount_count, -- ADS层已高度聚合
0 AS missing_user_count, -- ADS层已高度聚合
COUNT(DISTINCT user_id) AS unique_users,
MIN(registration_date) AS earliest_date, -- 最早注册日期
MAX(registration_date) AS latest_date -- 最晚注册日期
FROM ads.user_profile;
性能优化策略
在整个数据处理过程中采用多种性能优化策略：
分区策略：所有表都按时间分区 索引优化：对常用查询字段建立索引 数据压缩：使用列式存储和压缩算法 计算优化：使用窗口函数减少JOIN操作
-- 创建优化后的DWD表
-- 使用分区和列式存储优化性能
CREATE TABLE dwd.fact_order_detail_optimized (
order_id BIGINT, -- 订单ID
user_id BIGINT, -- 用户ID
total_amount DECIMAL(10,2), -- 订单金额
order_status STRING, -- 订单状态
order_date DATE, -- 订单日期
-- 其他字段...
)
PARTITIONED BY (year INT, month INT) -- 按年月分区，提高查询性能
STORED AS ORC -- 使用ORC列式存储格式
TBLPROPERTIES ("orc.compress"="SNAPPY"); -- 使用SNAPPY压缩算法
-- 创建位图索引优化用户查询
-- 位图索引适合低基数字段，如状态字段
CREATE INDEX idx_user_id ON TABLE dwd.fact_order_detail_optimized(user_id) AS 'BITMAP';
总结：数据的价值升华
通过这条从ODS到ADS的数据奇幻漂流，我们见证了数据如何从原始的、杂乱的状态，经过层层加工和转化，最终成为支持业务决策的宝贵资产。这个过程不仅仅是技术实现，更是数据价值的升华：
从原始到精炼：数据经过清洗、标准化，变得可靠可用 从分散到整合：多源数据被整合为统一的视图 从历史到预测：从描述历史现象到预测未来趋势 从数据到洞察：最终转化为驱动业务增长的行动洞察
这条SQL的数据漂流之旅展示了现代数据架构的核心思想：通过分层处理和数据加工，将原始数据转化为真正的商业价值。
据统计，99%的大咖都关注了这个公众号👇
猜你喜欢👇
文章转载自陈乔数据观止，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
