数据建模案例

1. 业务场景与粒度声明

业务过程： 交易下单与支付
事实表粒度： 每个交易订单项（即一笔订单中的一个商品）产生一条记录。这是最常用的粒度，能同时满足订单分析和商品销售分析的需求。

2. 星型模型设计

该模型以一个中心事实表为核心，周围连接多个维度表。

3. 维度表设计

a. 日期维度 (Dim_Date)

这是数据仓库中最重要的维度之一，用于按时间进行切片和切块分析。

字段名	数据类型	说明	示例
Date_Key (PK)	INT	代理键，自然键为Date	20230827
Full_Date	DATE	日期	2023-08-27
Day_Of_Week	TINYINT	一周中的第几天 (1-7)	1 (星期一)
Day_Name	STRING	星期几名称	Monday
Month_Number	TINYINT	月份数字 (1-12)	8
Month_Name	STRING	月份名称	August
Quarter	TINYINT	季度 (1-4)	3
Year	SMALLINT	年份	2023
Is_Weekend	BOOLEAN	是否是周末	true/false
Is_Holiday	BOOLEAN	是否是节假日	true/false

b. 顾客维度 (Dim_Customer)

用于基于顾客特征进行分析。

字段名	数据类型	说明	示例
Customer_Key (PK)	BIGINT	代理键	123456789
Customer_ID	STRING	业务系统中的客户ID	"CUST-10001"
Customer_Name	STRING	客户姓名	"张三"
Gender	STRING	性别	"男"
Age	INT	年龄	32
Age_Group	STRING	年龄段	"30-39"
City	STRING	所在城市	"北京"
Province	STRING	所在省份	"北京市"
Member_Level	STRING	会员等级	"黄金会员"
Is_Active	BOOLEAN	是否有效客户	true

c. 商品维度 (Dim_Product)

用于基于商品特征进行分析。

字段名	数据类型	说明	示例
Product_Key (PK)	BIGINT	代理键	987654321
Product_ID	STRING	业务系统中的商品ID	"PROD-20045"
Product_Name	STRING	商品名称	"iPhone 14 Pro"
Category	STRING	商品类别	"电子产品"
Subcategory	STRING	商品子类	"手机"
Brand	STRING	品牌	"Apple"
Price	DECIMAL(10,2)	商品单价	7999.00
Cost	DECIMAL(10,2)	商品成本	5500.00
Is_Active	BOOLEAN	是否有效商品	true

d. 门店/渠道维度 (Dim_Store)

用于分析不同门店或销售渠道的表现。

字段名	数据类型	说明	示例
Store_Key (PK)	INT	代理键	1001
Store_ID	STRING	业务系统中的门店ID	"STORE-10"
Store_Name	STRING	门店名称	"北京西直门店"
City	STRING	所在城市	"北京"
Region	STRING	大区	"华北"
Store_Type	STRING	门店类型（直营/加盟/在线）	"直营"

e. 订单维度 (Dim_Order)

这是退化维度，将订单级别的属性从事实表中分离出来，减少事实表冗余。

字段名	数据类型	说明	示例
Order_Key (PK)	BIGINT	代理键	112233445566
Order_ID	STRING	业务系统中的订单ID	"ORD-5000001"
Order_Status	STRING	订单状态	"已完成"
Payment_Method	STRING	支付方式	"支付宝"
Shipping_Method	STRING	配送方式	"快递"

4. 事实表设计 (Fact_Transactions)

这是星型模型的核心，存储可度量的业务数据。

字段名	数据类型	说明	示例
Transaction_Key (PK)	BIGINT	代理键	999888777666
Date_Key	INT	日期外键	20230827
Customer_Key	BIGINT	顾客外键	123456789
Product_Key	BIGINT	商品外键	987654321
Store_Key	INT	门店外键	1001
Order_Key	BIGINT	订单外键	112233445566
Quantity	INT	销售数量	2
Unit_Price	DECIMAL(10,2)	商品单价	7999.00
Sales_Amount	DECIMAL(12,2)	销售金额 = Quantity * Unit_Price	15998.00
Cost_Amount	DECIMAL(12,2)	成本金额 = Quantity * Cost	11000.00
Profit_Amount	DECIMAL(12,2)	利润金额 = Sales_Amount - Cost_Amount	4998.00
Tax_Amount	DECIMAL(10,2)	税费金额	800.00

5. 在Hive中的DDL示例

sql

-- 创建日期维度表
CREATE TABLE dim_date (
    date_key INT,
    full_date DATE,
    day_of_week TINYINT,
    day_name STRING,
    month_number TINYINT,
    month_name STRING,
    quarter TINYINT,
    year SMALLINT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN
)
STORED AS PARQUET;

-- 创建事实表
CREATE TABLE fact_transactions (
    transaction_key BIGINT,
    date_key INT,
    customer_key BIGINT,
    product_key BIGINT,
    store_key INT,
    order_key BIGINT,
    quantity INT,
    unit_price DECIMAL(10,2),
    sales_amount DECIMAL(12,2),
    cost_amount DECIMAL(12,2),
    profit_amount DECIMAL(12,2),
    tax_amount DECIMAL(10,2)
)
PARTITIONED BY (year INT, month INT)
STORED AS PARQUET;

6. 示例查询

查询2023年8月各品类的销售总额和利润：

sql

SELECT 
    p.category,
    SUM(f.sales_amount) AS total_sales,
    SUM(f.profit_amount) AS total_profit
FROM fact_transactions f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.year = 2023 AND d.month_number = 8
GROUP BY p.category
ORDER BY total_sales DESC;

查询北京地区黄金会员的消费习惯：

sql

SELECT 
    p.category,
    COUNT(*) AS transaction_count,
    SUM(f.quantity) AS total_quantity,
    SUM(f.sales_amount) AS total_sales
FROM fact_transactions f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE c.city = '北京' AND c.member_level = '黄金会员'
GROUP BY p.category
ORDER BY total_sales DESC;

7. 模型特点与优势

1. 查询性能高：星型模型通过减少表连接数量大幅提高查询性能

2. 理解简单：业务人员更容易理解维度模型

3. 扩展性强：可以轻松添加新的维度属性或新的维度表

4. 适应变化：代理键的使用使系统能够处理缓慢变化的维度

这种设计能够支持大多数交易分析场景，包括销售分析、客户行为分析、商品绩效分析、门店运营分析等。