# 1. 环境准备
– 服务器：2U机架服务器
– CPU：16核Intel Xeon
– 内存：64GB RAM
– 存储：8×2TB SSD，RAID 10

# 2. 数据库配置
$ sudo vi /postgresql/fgapp/14/data/postgresql.conf
shared_buffers = 16GB
work_mem = 128MB
maintenance_work_mem = 4GB
effective_cache_size = 48GB
random_page_cost = 1.1
seq_page_cost = 1.0
max_connections = 200

# 3. 数据模型设计
# 事实表：销售事实表
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_sales_fact (sale_id SERIAL PRIMARY KEY, product_id INT, customer_id INT, sale_date DATE, amount NUMERIC, quantity INT);”

# 维度表：产品维度表
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_product_dim (product_id SERIAL PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price NUMERIC);”

# 维度表：客户维度表
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_customer_dim (customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(100), city VARCHAR(50), country VARCHAR(50));”

# 维度表：日期维度表
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_date_dim (date_id SERIAL PRIMARY KEY, date DATE, year INT, month INT, day INT, quarter INT);”

# 4. 索引创建
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_fact_product_id_idx ON fgedu_sales_fact(product_id);”
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_fact_customer_id_idx ON fgedu_sales_fact(customer_id);”
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_fact_sale_date_idx ON fgedu_sales_fact(sale_date);”

# 5. 验证数据模型
$ psql -U fgedu -d fgedu_dw -c “\dt”

# 1. 提取数据
# 从ERP系统提取销售数据
$ psql -U fgedu -d erp_db -c “COPY (SELECT * FROM sales) TO ‘/tmp/sales.csv’ CSV HEADER;”

# 2. 转换数据
# 使用Python脚本转换数据
$ cat transform.py
import pandas as pd

# 读取数据
sales = pd.read_csv(‘/tmp/sales.csv’)

# 数据清洗
sales = sales.dropna()
sales = sales[sales[‘amount’] > 0]

# 数据转换
sales[‘sale_date’] = pd.to_datetime(sales[‘sale_date’])

# 保存转换后的数据
sales.to_csv(‘/tmp/sales_transformed.csv’, index=False)

$ python transform.py

# 3. 加载数据
# 将数据加载到数据仓库
$ psql -U fgedu -d fgedu_dw -c “COPY fgedu_sales_fact(product_id, customer_id, sale_date, amount, quantity) FROM ‘/tmp/sales_transformed.csv’ CSV HEADER;”

# 4. 更新维度表
$ psql -U fgedu -d fgedu_dw -c “INSERT INTO fgedu_product_dim (product_id, product_name, category, price) SELECT DISTINCT product_id, product_name, category, price FROM sales;”

# 5. 验证数据加载
$ psql -U fgedu -d fgedu_dw -c “SELECT COUNT(*) FROM fgedu_sales_fact;”

# 1. 分区表优化
$ psql -U fgedu -d fgedu_dw -c “ALTER TABLE fgedu_sales_fact ADD COLUMN sale_date DATE;”
$ psql -U fgedu -d fgedu_dw -c “ALTER TABLE fgedu_sales_fact PARTITION BY RANGE (sale_date);”
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_sales_fact_y2023 PARTITION OF fgedu_sales_fact FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);”
$ psql -U fgedu -d fgedu_dw -c “CREATE TABLE fgedu_sales_fact_y2024 PARTITION OF fgedu_sales_fact FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);”

# 2. 物化视图优化
$ psql -U fgedu -d fgedu_dw -c “CREATE MATERIALIZED VIEW fgedu_sales_summary AS SELECT DATE_TRUNC(‘month’, sale_date) AS month, SUM(amount) AS total_amount FROM fgedu_sales_fact GROUP BY month;”
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_summary_month_idx ON fgedu_sales_summary(month);”

# 3. 索引优化
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_fact_product_id_sale_date_idx ON fgedu_sales_fact(product_id, sale_date);”
$ psql -U fgedu -d fgedu_dw -c “CREATE INDEX fgedu_sales_fact_customer_id_sale_date_idx ON fgedu_sales_fact(customer_id, sale_date);”

# 4. 并行查询优化
$ sudo vi /postgresql/fgapp/14/data/postgresql.conf
max_parallel_workers = 16
max_parallel_workers_per_gather = 8

# 5. 验证优化效果
$ psql -U fgedu -d fgedu_dw -c “EXPLAIN ANALYZE SELECT DATE_TRUNC(‘month’, sale_date) AS month, SUM(amount) AS total_amount FROM fgedu_sales_fact WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’ GROUP BY month;”

# 输出示例
Execution time: 125.321 ms