PHP数据仓库数据模型设计

数据仓库是数据分析的基础。好的数据模型设计让查询更高效、维护更简单。今天说说PHP项目中数据仓库的数据模型设计。

数据仓库的维度建模包括事实表和维度表。事实表存储度量数据，维度表存储描述信息。

```php
class DataModelDesigner
{
private PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}

// 创建星型模型
public function createStarSchema(): void
{
// 时间维度
$this->pdo->exec("
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
date DATE NOT NULL,
year INT,
quarter INT,
month INT,
month_name VARCHAR(20),
week INT,
day_of_week INT,
day_name VARCHAR(20),
is_weekend BOOLEAN,
is_holiday BOOLEAN DEFAULT FALSE
)
");

// 产品维度
$this->pdo->exec("
CREATE TABLE dim_product (
product_key INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
sku VARCHAR(50),
name VARCHAR(200),
category VARCHAR(100),
subcategory VARCHAR(100),
brand VARCHAR(100),
unit_price DECIMAL(10, 2),
cost_price DECIMAL(10, 2),
effective_date DATE,
expiration_date DATE,
is_current BOOLEAN DEFAULT TRUE
)
");

// 客户维度
$this->pdo->exec("
CREATE TABLE dim_customer (
customer_key INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
name VARCHAR(100),
email VARCHAR(255),
city VARCHAR(100),
province VARCHAR(100),
country VARCHAR(100),
membership_tier VARCHAR(20),
registration_date DATE
)
");

// 销售事实表
$this->pdo->exec("
CREATE TABLE fact_sales (
sale_key BIGINT AUTO_INCREMENT PRIMARY KEY,
date_key INT,
product_key INT,
customer_key INT,
quantity INT,
unit_price DECIMAL(10, 2),
discount DECIMAL(10, 2),
total_amount DECIMAL(10, 2),
cost_amount DECIMAL(10, 2),
profit_amount DECIMAL(10, 2),
FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key)
)
");
}

// 填充日期维度
public function populateDateDimension(int $startYear, int $endYear): void
{
$start = new \DateTime("{$startYear}-01-01");
$end = new \DateTime("{$endYear}-12-31");
$interval = new \DateInterval('P1D');

$stmt = $this->pdo->prepare("
INSERT INTO dim_date (date_key, date, year, quarter, month, month_name, week, day_of_week, day_name, is_weekend)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");

foreach (new \DatePeriod($start, $interval, $end) as $date) {
$key = (int)$date->format('Ymd');
$stmt->execute([
$key,
$date->format('Y-m-d'),
(int)$date->format('Y'),
(int)ceil($date->format('n') / 3),
(int)$date->format('n'),
$date->format('F'),
(int)$date->format('W'),
(int)$date->format('N'),
$date->format('l'),
$date->format('N') >= 6 ? 1 : 0,
]);
}
}

// ETL加载销售数据
public function loadSalesData(\DateTime $date): int
{
$sourcePdo = new PDO('mysql:host=localhost;dbname=source_db', 'root', '');

$stmt = $sourcePdo->prepare("
SELECT o.id, o.order_date, o.customer_id, oi.product_id, oi.quantity, oi.price, oi.discount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE DATE(o.order_date) = ?
");
$stmt->execute([$date->format('Y-m-d')]);
$orders = $stmt->fetchAll();

$insertStmt = $this->pdo->prepare("
INSERT INTO fact_sales (date_key, product_key, customer_key, quantity, unit_price, discount, total_amount, cost_amount, profit_amount)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
");

$count = 0;
foreach ($orders as $order) {
$dateKey = (int)$date->format('Ymd');
$total = $order['quantity'] * $order['price'];
$discount = $order['discount'] ?? 0;
$finalAmount = $total * (1 - $discount);
$cost = $total * 0.6;

$insertStmt->execute([
$dateKey,
$order['product_id'],
$order['customer_id'],
$order['quantity'],
$order['price'],
$discount,
$finalAmount,
$cost,
$finalAmount - $cost,
]);
$count++;
}

return $count;
}

// 分析查询
public function salesByCategory(string $startDate, string $endDate): array
{
$stmt = $this->pdo->prepare("
SELECT
dp.category,
SUM(fs.quantity) as total_quantity,
SUM(fs.total_amount) as total_revenue,
SUM(fs.profit_amount) as total_profit,
COUNT(DISTINCT fs.customer_key) as unique_customers
FROM fact_sales fs
JOIN dim_product dp ON fs.product_key = dp.product_key
JOIN dim_date dd ON fs.date_key = dd.date_key
WHERE dd.date BETWEEN ? AND ?
GROUP BY dp.category
ORDER BY total_revenue DESC
");
$stmt->execute([$startDate, $endDate]);
return $stmt->fetchAll();
}

public function monthlyTrends(int $year): array
{
$stmt = $this->pdo->prepare("
SELECT
dd.month,
dd.month_name,
SUM(fs.total_amount) as revenue,
SUM(fs.quantity) as units_sold,
COUNT(DISTINCT fs.customer_key) as active_customers
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
WHERE dd.year = ?
GROUP BY dd.month, dd.month_name
ORDER BY dd.month
");
$stmt->execute([$year]);
return $stmt->fetchAll();
}
}

$pdo = new PDO('mysql:host=localhost;dbname=data_warehouse', 'root', '');
$designer = new DataModelDesigner($pdo);

$trends = $designer->monthlyTrends(2024);
print_r($trends);
?>

数据仓库的模型设计需要考虑查询性能和数据更新策略。星型模型是最常用的设计，事实表在中间，维度表在周围。维度表使用缓慢变化维度策略来跟踪历史变化。ETL过程定期从源系统抽取数据，经过清洗和转换后加载到数据仓库。PHP虽然不是专业的数据仓库工具，但实现基本的ETL和查询功能很方便。