mysql 数据仓库案例

-- 创建事实表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

-- 创建维度表 products
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

-- 创建维度表 customers
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    region VARCHAR(50)
);

-- 插入示例数据
INSERT INTO products (product_id, product_name, category) VALUES
(1, 'Laptop', 'Electronics'),
(2, 'Smartphone', 'Electronics'),
(3, 'Book', 'Books');

INSERT INTO customers (customer_id, customer_name, region) VALUES
(1, 'Alice', 'North'),
(2, 'Bob', 'South');

INSERT INTO sales (sale_id, product_id, customer_id, sale_date, amount) VALUES
(1, 1, 1, '2023-01-01', 1000),
(2, 2, 2, '2023-01-02', 800),
(3, 3, 1, '2023-01-03', 50);

-- 查询示例
SELECT 
    c.customer_name,
    p.product_name,
    s.sale_date,
    s.amount
FROM 
    sales s
JOIN 
    customers c ON s.customer_id = c.customer_id
JOIN 
    products p ON s.product_id = p.product_id;