如何在MySQL中存储面板数据（关系型Schema设计）

一、基础关系型Schema设计（第三范式，适配日常事务处理场景） #

针对你的两个数据集，先拆分冗余数据，设计符合范式的表结构：

1. 账户维度表（accounts） #

存储账户的静态/缓慢变化信息，避免重复存储相同内容：

CREATE TABLE accounts (
    account_id VARCHAR(50) PRIMARY KEY,
    gender ENUM('Male', 'Female') NOT NULL,
    birth_year INT, -- 用出生年份替代固定age，可动态计算任意日期的账户年龄
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

注意：如果你的数据中age是固定不变的快照值，也可以直接存储age INT NOT NULL，但birth_year更灵活。

2. 账户每日状态表（account_daily_status） #

消除原账户档案表的冗余，只存储每日变化的账户状态：

CREATE TABLE account_daily_status (
    account_id VARCHAR(50),
    file_date DATE NOT NULL,
    income DECIMAL(12,2) NOT NULL, -- 存储数值类型，去除原数据中的逗号
    PRIMARY KEY (account_id, file_date), -- 复合主键，保证单账户单日仅一条记录
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
);

3. 账户交易表（account_trades） #

直接基于交易数据集设计，添加外键关联账户表：

CREATE TABLE account_trades (
    trade_id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键，唯一标识每笔交易
    account_id VARCHAR(50) NOT NULL,
    stock_id VARCHAR(50) NOT NULL,
    trade_type ENUM('Buy', 'Sell') NOT NULL,
    trade_amount DECIMAL(10,2) NOT NULL,
    file_date DATE NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
);

二、数据导入与合并查询 #

1. 数据预处理导入 #

• 账户档案表：先提取唯一的account_id+gender+age组合，计算birth_year（若使用该字段）后插入accounts表；再提取唯一的account_id+file_date+income组合，插入account_daily_status表。
• 交易表：将trade_amount转换为数值类型后，直接插入account_trades表。

2. 关联合并示例 #

如果需要获取每笔交易对应的账户当时状态，可使用以下查询：

SELECT 
    t.trade_id,
    t.account_id,
    a.gender,
    YEAR(t.file_date) - a.birth_year AS age_at_trade,
    s.income AS income_at_trade,
    t.stock_id,
    t.trade_type,
    t.trade_amount,
    t.file_date
FROM account_trades t
JOIN accounts a ON t.account_id = a.account_id
LEFT JOIN account_daily_status s 
    ON t.account_id = s.account_id 
    AND s.file_date = (
        SELECT MAX(file_date) 
        FROM account_daily_status 
        WHERE account_id = t.account_id AND file_date <= t.file_date
    );

该查询会自动匹配交易日期之前或当天的最新账户状态。

三、星型/雪花模型的选择 #

星型、雪花模型是数据仓库（OLAP）场景的专用设计，适合大规模数据分析、报表生成。如果你的需求是：

• 频繁进行多维度统计分析（如按时间、账户性别、股票类型聚合交易数据）
• 数据量极大，需要优化查询性能

可以考虑星型模型：

1. 维度表：accounts（账户维度）、date_dim（时间维度，存储年/月/周等日期属性）、stocks（股票维度）
2. 事实表：account_daily_status_fact（账户状态事实表）、account_trades_fact（交易事实表）

但如果你的需求是日常事务处理（OLTP）（如账户管理、交易录入），前面的第三范式设计更合适——它数据冗余少，更新效率更高。

内容的提问来源于stack exchange，提问作者Leon