2.1 ODS层（原始数据层）
作用：存储原始数据，保持数据原貌
sql
-- 创建ODS层表
CREATE EXTERNAL TABLE mini_dw_beauty.ods_beauty_order(
order_id STRING, -- 订单ID
user_id STRING, -- 用户ID
user_name STRING, -- 用户名
brand STRING, -- 品牌
product STRING, -- 产品
unit_price DOUBLE, -- 单价
quantity INT, -- 数量
original_amount DOUBLE, -- 原始金额
province STRING, -- 省份
city STRING, -- 城市
order_time STRING, -- 订单时间（字符串格式）
gender STRING, -- 性别
age_group STRING, -- 年龄分组
coupon DOUBLE -- 优惠券金额
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/mini_dw_beauty.db/ods_beauty_order/';
数据示例：
text
MEI202510110830459876,U0001234,用户3827,雅诗兰黛,小棕瓶精华,345.50,2,691.00,广东,广州市,2025-10-11 08:30:45,女,26-35,25.00
特点：
数据与源文件完全一致
使用TEXTFILE格式，便于Flume直接写入
不做任何数据清洗和处理
2.2 DWD层（数据清洗层）
作用：数据清洗、转换、标准化
sql
-- 创建DWD层表
CREATE TABLE mini_dw_beauty.dwd_beauty_order(
order_id STRING, -- 订单ID
user_id STRING, -- 用户ID
user_name STRING, -- 用户名
brand STRING, -- 品牌
product STRING, -- 产品
unit_price DOUBLE, -- 单价
quantity INT, -- 数量
original_amount DOUBLE, -- 原始金额
net_amount DOUBLE, -- 净金额（新增计算字段）
province STRING, -- 省份
city STRING, -- 城市
order_date DATE, -- 订单日期（从order_time解析）
order_hour INT, -- 订单小时（从order_time解析）
gender STRING, -- 性别
age_group STRING, -- 年龄分组
coupon DOUBLE -- 优惠券金额
)
PARTITIONED BY (dt STRING) -- 按日期分区
STORED AS ORC; -- 使用ORC列式存储
ETL处理逻辑：
sql
-- ODS到DWD的数据清洗转换
INSERT OVERWRITE TABLE dwd_beauty_order PARTITION(dt)
SELECT
order_id,
user_id,
user_name,
brand,
product,
unit_price,
quantity,
original_amount,
original_amount - coupon AS net_amount, -- 计算净金额
province,
city,
to_date(cast(order_time as timestamp)) AS order_date, -- 字符串转日期
hour(cast(order_time as timestamp)) AS order_hour, -- 提取小时
gender,
age_group,
coupon,
to_date(cast(order_time as timestamp)) AS dt -- 分区字段
FROM ods_beauty_order
WHERE length(order_id) > 0 -- 数据清洗：去除空订单号
AND unit_price > 0 -- 数据清洗：去除0或负单价
AND quantity > 0; -- 数据清洗：去除0数量
数据示例：
text
order_id: MEI202510110830459876
net_amount: 666.00 (691.00 - 25.00)
order_date: 2025-10-11
order_hour: 8
dt: 2025-10-11
特点：
数据清洗和标准化
增加衍生字段（net_amount, order_date, order_hour）
使用ORC格式，提高查询性能
按日期分区，便于数据管理
2.3 DWS层（数据服务层）
作用：轻度聚合，按维度汇总数据
2.3.1 省份维度聚合表
sql
CREATE TABLE mini_dw_beauty.dws_province_day(
province STRING, -- 省份
order_date DATE, -- 订单日期
order_cnt BIGINT, -- 订单数量
total_amount DOUBLE, -- 总销售额
avg_amount DOUBLE, -- 平均订单金额
user_cnt BIGINT -- 用户数量（UV）
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
聚合逻辑：
sql
INSERT OVERWRITE TABLE dws_province_day PARTITION(dt)
SELECT
province,
order_date,
COUNT(*) AS order_cnt, -- 订单总数
SUM(net_amount) AS total_amount, -- 销售总额
ROUND(AVG(net_amount), 2) AS avg_amount, -- 平均客单价
COUNT(DISTINCT user_id) AS user_cnt, -- 去重用户数
order_date AS dt
FROM dwd_beauty_order
GROUP BY province, order_date;
数据示例：
text
province: 广东
order_date: 2025-10-15
order_cnt: 1850
total_amount: 1245680.50
avg_amount: 673.34
user_cnt: 1523
2.3.2 品牌维度聚合表
sql
CREATE TABLE mini_dw_beauty.dws_brand_day(
brand STRING, -- 品牌
order_date DATE, -- 订单日期
order_cnt BIGINT, -- 订单数量
total_amount DOUBLE, -- 总销售额
avg_amount DOUBLE -- 平均订单金额
)
PARTITIONED BY (dt STRING)
STORED AS ORC;
聚合逻辑：
sql
INSERT OVERWRITE TABLE dws_brand_day PARTITION(dt)
SELECT
brand,
order_date,
COUNT(*) AS order_cnt,
SUM(net_amount) AS total_amount,
ROUND(AVG(net_amount), 2) AS avg_amount,
order_date AS dt
FROM dwd_beauty_order
GROUP BY brand, order_date;
数据示例：
text
brand: 海蓝之谜
order_date: 2025-10-15
order_cnt: 980
total_amount: 1856780.50
avg_amount: 1894.67
2.4 ADS层（应用数据层）
作用：面向业务的最终报表，直接支持决策
2.4.1 省份统计报表
sql
CREATE TABLE mini_dw_beauty.ads_province_stats(
stat_date DATE, -- 统计日期
province STRING, -- 省份
order_cnt BIGINT, -- 订单数量
total_amount DOUBLE, -- 总销售额
avg_amount DOUBLE, -- 平均订单金额
user_cnt BIGINT, -- 用户数量
province_rank INT -- 省份排名
);
生成逻辑：
sql
INSERT OVERWRITE TABLE ads_province_stats
SELECT
order_date AS stat_date,
province,
order_cnt,
total_amount,
avg_amount,
user_cnt,
ROW_NUMBER() OVER(ORDER BY total_amount DESC) AS province_rank -- 按销售额排名
FROM dws_province_day;
数据示例：
text
stat_date: 2025-10-15
province: 广东
order_cnt: 1850
total_amount: 1245680.50
avg_amount: 673.34
user_cnt: 1523
province_rank: 1
2.4.2 品牌统计报表
sql
CREATE TABLE mini_dw_beauty.ads_brand_stats(
stat_date DATE, -- 统计日期
brand STRING, -- 品牌
order_cnt BIGINT, -- 订单数量
total_amount DOUBLE, -- 总销售额
avg_amount DOUBLE, -- 平均订单金额
brand_rank INT -- 品牌排名
);
生成逻辑：
sql
INSERT OVERWRITE TABLE ads_brand_stats
SELECT
order_date AS stat_date,
brand,
order_cnt,
total_amount,
avg_amount,
ROW_NUMBER() OVER(ORDER BY total_amount DESC) AS brand_rank -- 按销售额排名
FROM dws_brand_day;
数据示例：
text
stat_date: 2025-10-15
brand: 海蓝之谜
order_cnt: 980
total_amount: 1856780.50
avg_amount: 1894.67
brand_rank: 1ADS层 根据DWS层的数据对省份排名 平均客单价 最受欢迎品牌最受欢迎产品
ADS层 根据DWS层的数据品牌排名 销量 销售额 增长率 市场份额
ADS层 计算各年龄层top3的品牌偏好 并计算出在品牌在年龄层中的占比
ADS层 对库存健康进行分析 包含产品名称、品牌、当前库存、日均销量、库存覆盖天数、库存状态、安全库存水平、滞销比例、缺货风险等级
最新发布
扫一扫
8723
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
