# 1. 分层架构设计
– ODS层（操作数据存储层）
– 存储原始数据
– 数据格式与源系统一致
– 数据实时更新
– DW层（数据仓库层）
– 存储清洗和转换后的数据
– 数据按照主题组织
– 数据定期更新
– DM层（数据集市层）
– 存储面向业务的数据
– 数据按照业务需求组织
– 数据定期更新
# 2. 主题域设计
– 客户主题
– 客户基本信息
– 客户行为信息
– 客户交易信息
– 产品主题
– 产品基本信息
– 产品销售信息
– 产品库存信息
– 订单主题
– 订单基本信息
– 订单明细信息
– 订单支付信息
# 3. 实际示例
– 创建ODS层表
SQL> CREATE TABLE ods_fgedu_user (
user_id INT, 学习交流加群风哥QQ113257174
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
create_time TIMESTAMP,
update_time TIMESTAMP
);
– 创建DW层表
SQL> CREATE TABLE dw_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
create_date DATE,
update_date DATE
);
– 创建DM层表
SQL> CREATE TABLE dm_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
create_date DATE,
update_date DATE
);

# 1. 维度建模
– 星型模型
– 一个事实表
– 多个维度表
– 维度表直接连接事实表
– 雪花模型
– 一个事实表
– 多个维度表
– 维度表可以连接其他维度表
– 星座模型
– 多个事实表
– 多个维度表
– 维度表可以共享 更多视频教程www.fgedu.net.cn
# 2. 事实表设计
– 事实表类型
– 事务事实表：记录事务数据
– 周期快照事实表：记录周期性数据
– 累积快照事实表：记录累积数据
– 事实表设计
SQL> CREATE TABLE fact_fgedu_order (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
order_amount DECIMAL(10,2),
order_date DATE,
create_time TIMESTAMP
);
# 3. 维度表设计
– 维度表类型
– 缓慢变化维度（SCD）
– SCD Type 1：覆盖更新
– SCD Type 2：历史记录
– SCD Type 3：历史字段
– 维度表设计
SQL> CREATE TABLE dim_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
start_date DATE,
end_date DATE,
is_current INT
);
# 4. 实际示例
– 创建事实表
SQL> CREATE TABLE fact_fgedu_order (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
order_amount DECIMAL(10,2),
order_date DATE,
create_time TIMESTAMP
); 更多学习教程公众号风哥教程itpux_com
– 创建维度表
SQL> CREATE TABLE dim_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
start_date DATE,
end_date DATE,
is_current INT
);
– 创建维度表
SQL> CREATE TABLE dim_fgedu_product (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_category VARCHAR(50),
product_price DECIMAL(10,2),
create_date DATE
);

# 1. 全量抽取
– 抽取所有数据
SQL> INSERT INTO ods_fgedu_user
SELECT * FROM source_fgedu_user;
– 验证抽取结果
SQL> SELECT COUNT(*) FROM ods_fgedu_user;
# 2. 增量抽取
– 抽取增量数据 from DB视频:www.itpux.com
SQL> INSERT INTO ods_fgedu_user
SELECT * FROM source_fgedu_user
WHERE update_time > (SELECT MAX(update_time) FROM ods_fgedu_user);
– 验证抽取结果
SQL> SELECT COUNT(*) FROM ods_fgedu_user;
# 3. 实际示例
– 全量抽取
SQL> INSERT INTO ods_fgedu_user
SELECT * FROM source_fgedu_user;
# 输出结果
# 1000 rows inserted
– 验证抽取结果
SQL> SELECT COUNT(*) FROM ods_fgedu_user;
# 输出结果
# COUNT(*)
# ———-
# 1000
– 增量抽取
SQL> INSERT INTO ods_fgedu_user
SELECT * FROM source_fgedu_user
WHERE update_time > (SELECT MAX(update_time) FROM ods_fgedu_user);
# 输出结果
# 10 rows inserted

# 1. 数据清洗
– 清洗空值
SQL> INSERT INTO dw_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(create_time) AS create_date,
TRUNC(update_time) AS update_date
FROM ods_fgedu_user
WHERE user_id IS NOT NULL
AND user_name IS NOT NULL;
– 清洗重复值
SQL> INSERT INTO dw_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(create_time) AS create_date,
TRUNC(update_time) AS update_date
FROM (
SELECT user_id, user_name, user_email, user_status, create_time, update_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) AS rn
FROM ods_fgedu_user
) t
WHERE rn = 1;
# 2. 数据转换
– 转换数据格式
SQL> INSERT INTO dw_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TO_DATE(TO_CHAR(create_time, ‘YYYY-MM-DD’), ‘YYYY-MM-DD’) AS create_date,
TO_DATE(TO_CHAR(update_time, ‘YYYY-MM-DD’), ‘YYYY-MM-DD’) AS update_date
FROM ods_fgedu_user;
– 转换数据结构
SQL> INSERT INTO dw_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(create_time) AS create_date,
TRUNC(update_time) AS update_date
FROM ods_fgedu_user;
# 3. 实际示例
– 数据清洗和转换
SQL> INSERT INTO dw_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(create_time) AS create_date,
TRUNC(update_time) AS update_date
FROM (
SELECT user_id, user_name, user_email, user_status, create_time, update_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) AS rn
FROM ods_fgedu_user
WHERE user_id IS NOT NULL
AND user_name IS NOT NULL
) t
WHERE rn = 1;
# 输出结果
# 1000 rows inserted
– 验证转换结果
SQL> SELECT COUNT(*) FROM dw_fgedu_user;
# 输出结果
# COUNT(*)
# ———-
# 1000

# 1. 星型模型
– 创建事实表
SQL> CREATE TABLE fact_fgedu_order (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
order_amount DECIMAL(10,2),
order_date DATE,
create_time TIMESTAMP
);
– 创建维度表
SQL> CREATE TABLE dim_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
start_date DATE,
end_date DATE,
is_current INT
);
SQL> CREATE TABLE dim_fgedu_product (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_category VARCHAR(50),
product_price DECIMAL(10,2),
create_date DATE
);
SQL> CREATE TABLE dim_fgedu_date (
date_id INT PRIMARY KEY,
date_value DATE,
year INT,
month INT,
day INT,
quarter INT,
week INT
);
# 2. 雪花模型
– 创建维度表
SQL> CREATE TABLE dim_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
region_id INT,
start_date DATE,
end_date DATE,
is_current INT
);
SQL> CREATE TABLE dim_fgedu_region (
region_id INT PRIMARY KEY,
region_name VARCHAR(50),
country_id INT
);
SQL> CREATE TABLE dim_fgedu_country (
country_id INT PRIMARY KEY,
country_name VARCHAR(50)
);
# 3. 实际示例
– 创建星型模型
SQL> CREATE TABLE fact_fgedu_order (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
order_amount DECIMAL(10,2),
order_date DATE,
create_time TIMESTAMP
);
SQL> CREATE TABLE dim_fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT,
start_date DATE,
end_date DATE,
is_current INT
);
SQL> CREATE TABLE dim_fgedu_product (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_category VARCHAR(50),
product_price DECIMAL(10,2),
create_date DATE
);
– 加载事实表
SQL> INSERT INTO fact_fgedu_order
SELECT order_id, user_id, product_id, order_amount, order_date, create_time
FROM ods_fgedu_order;
– 加载维度表
SQL> INSERT INTO dim_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(create_time) AS start_date,
TO_DATE(‘9999-12-31’, ‘YYYY-MM-DD’) AS end_date,
1 AS is_current
FROM ods_fgedu_user;

# 1. SCD Type 1（覆盖更新）
– 更新维度表
SQL> UPDATE dim_fgedu_user
SET user_name = ‘new_name’,
user_email = ‘new_email’,
user_status = 0
WHERE user_id = 1;
# 2. SCD Type 2（历史记录）
– 插入新记录
SQL> INSERT INTO dim_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(SYSDATE) AS start_date,
TO_DATE(‘9999-12-31’, ‘YYYY-MM-DD’) AS end_date,
1 AS is_current
FROM ods_fgedu_user
WHERE user_id = 1;
– 更新旧记录
SQL> UPDATE dim_fgedu_user
SET end_date = TRUNC(SYSDATE) – 1,
is_current = 0
WHERE user_id = 1
AND is_current = 1;
# 3. SCD Type 3（历史字段）
– 更新维度表
SQL> ALTER TABLE dim_fgedu_user ADD (prev_user_status INT);
SQL> UPDATE dim_fgedu_user
SET prev_user_status = user_status,
user_status = 0
WHERE user_id = 1;
# 4. 实际示例
– SCD Type 2实现
SQL> INSERT INTO dim_fgedu_user
SELECT user_id, user_name, user_email, user_status,
TRUNC(SYSDATE) AS start_date,
TO_DATE(‘9999-12-31’, ‘YYYY-MM-DD’) AS end_date,
1 AS is_current
FROM ods_fgedu_user
WHERE user_id = 1;
SQL> UPDATE dim_fgedu_user
SET end_date = TRUNC(SYSDATE) – 1,
is_current = 0
WHERE user_id = 1
AND is_current = 1;
– 验证结果
SQL> SELECT * FROM dim_fgedu_user WHERE user_id = 1;
# 输出结果
# USER_ID USER_NAME USER_EMAIL USER_STATUS START_DATE END_DATE IS_CURRENT
# ——– ———- ——————– ———— ———– ———– ———–
# 1 fgedu_user1 fgedu_user1@fgedu.net.cn 1 2024-01-01 2024-01-09 0
# 1 fgedu_user1 fgedu_user1@fgedu.net.cn 0 2024-01-10 9999-12-31 1