833 lines
26 KiB
Markdown
833 lines
26 KiB
Markdown
# 思投录数据库设计文档
|
||
|
||
## 一、数据库选型分析
|
||
|
||
### 1.1 业务特点分析
|
||
|
||
根据业务需求，系统需要处理以下类型的数据：
|
||
|
||
1. **结构化数据**
|
||
- 用户账户、持仓、交易记录
|
||
- 交易计划、计划步骤
|
||
- 资产净值、收益率等数值数据
|
||
|
||
2. **时间序列数据**
|
||
- 每日单位净值记录
|
||
- 资产快照（每日）
|
||
- 价格历史数据
|
||
|
||
3. **文本内容数据**
|
||
- 交易思考（交易记录时填写）
|
||
- 复盘内容（定期复盘时填写）
|
||
- 计划思考（创建计划时填写）
|
||
|
||
4. **关联关系**
|
||
- 交易记录与持仓的关联
|
||
- 交易计划与交易记录的关联
|
||
- 复盘与交易记录的关联
|
||
- 计划步骤与计划的关联
|
||
|
||
5. **查询需求**
|
||
- 复杂的时间范围查询
|
||
- 多表关联查询
|
||
- 聚合统计查询
|
||
- 时间线查询（按时间排序）
|
||
|
||
### 1.2 数据库选型推荐
|
||
|
||
#### 推荐方案：PostgreSQL
|
||
|
||
**推荐理由：**
|
||
|
||
1. **关系型数据库优势**
|
||
- ✅ 数据结构清晰，关系明确
|
||
- ✅ 支持复杂查询和统计（JOIN、聚合函数）
|
||
- ✅ 支持事务，保证数据一致性
|
||
- ✅ ACID特性，数据可靠性高
|
||
|
||
2. **PostgreSQL特有优势**
|
||
- ✅ 支持JSON/JSONB类型，可灵活存储思考内容等文本
|
||
- ✅ 支持数组类型，适合存储计划步骤等
|
||
- ✅ 强大的时间序列查询能力
|
||
- ✅ 支持全文搜索（可用于搜索思考内容）
|
||
- ✅ 性能优秀，适合中小型应用
|
||
- ✅ 开源免费，社区活跃
|
||
|
||
3. **其他考虑**
|
||
- 如果团队更熟悉MySQL，MySQL 8.0+ 也是不错的选择
|
||
- 对于时间序列数据，可以考虑用TimescaleDB（基于PostgreSQL的扩展）
|
||
|
||
#### 备选方案：MySQL 8.0+
|
||
|
||
**适用场景：**
|
||
- 团队更熟悉MySQL
|
||
- 需要更好的云服务支持（如阿里云RDS）
|
||
- 数据量不是特别大
|
||
|
||
**MySQL优势：**
|
||
- ✅ 生态成熟，工具丰富
|
||
- ✅ 云服务支持好
|
||
- ✅ 性能稳定
|
||
- ⚠️ JSON支持不如PostgreSQL强大
|
||
- ⚠️ 复杂查询性能略逊于PostgreSQL
|
||
|
||
#### 不推荐方案
|
||
|
||
- **MongoDB等NoSQL**：虽然可以存储JSON，但关联查询复杂，不适合这种关系明确的数据结构
|
||
- **Redis**：只适合缓存，不适合持久化存储
|
||
- **SQLite**：适合单机应用，不支持多用户并发
|
||
|
||
### 1.3 最终推荐
|
||
|
||
**推荐使用 PostgreSQL 14+**
|
||
|
||
理由：
|
||
1. 数据结构关系明确，适合关系型数据库
|
||
2. JSONB类型可以灵活存储思考内容
|
||
3. 强大的查询能力，适合复杂统计
|
||
4. 时间序列查询性能好
|
||
5. 支持全文搜索，方便搜索思考内容
|
||
|
||
---
|
||
|
||
## 二、数据库表设计
|
||
|
||
### 2.1 表结构总览
|
||
|
||
```
|
||
用户相关
|
||
├── users (用户表)
|
||
└── user_settings (用户设置表)
|
||
|
||
账户相关
|
||
├── accounts (账户表)
|
||
├── positions (持仓表)
|
||
├── cash_accounts (现金账户表)
|
||
└── position_warnings (持仓预警表)
|
||
|
||
交易相关
|
||
├── transactions (交易记录表)
|
||
├── transaction_thoughts (交易思考表)
|
||
└── transaction_reviews (交易复盘表)
|
||
|
||
计划相关
|
||
├── trading_plans (交易计划表)
|
||
├── plan_steps (计划步骤表)
|
||
└── plan_thoughts (计划思考表)
|
||
|
||
净值相关
|
||
├── net_value_snapshots (净值快照表)
|
||
└── daily_net_values (每日净值表)
|
||
|
||
市场数据
|
||
└── market_prices (市场价格表)
|
||
```
|
||
|
||
### 2.2 详细表结构设计
|
||
|
||
#### 2.2.1 用户相关表
|
||
|
||
##### users (用户表)
|
||
|
||
```sql
|
||
CREATE TABLE users (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(100) UNIQUE,
|
||
phone VARCHAR(20) UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
nickname VARCHAR(50),
|
||
avatar_url VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
last_login_at TIMESTAMP,
|
||
status VARCHAR(20) DEFAULT 'active' -- active, inactive, deleted
|
||
);
|
||
|
||
CREATE INDEX idx_users_email ON users(email);
|
||
CREATE INDEX idx_users_phone ON users(phone);
|
||
CREATE INDEX idx_users_status ON users(status);
|
||
```
|
||
|
||
##### user_settings (用户设置表)
|
||
|
||
```sql
|
||
CREATE TABLE user_settings (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
setting_key VARCHAR(50) NOT NULL,
|
||
setting_value TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(user_id, setting_key)
|
||
);
|
||
|
||
CREATE INDEX idx_user_settings_user_id ON user_settings(user_id);
|
||
```
|
||
|
||
**常用设置项：**
|
||
- `position_limit_per_stock`: 单只股票仓位上限（百分比）
|
||
- `currency`: 默认货币
|
||
- `theme`: 主题设置
|
||
- `notification_enabled`: 是否启用通知
|
||
|
||
#### 2.2.2 账户相关表
|
||
|
||
##### accounts (账户表)
|
||
|
||
```sql
|
||
CREATE TABLE accounts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
name VARCHAR(100) NOT NULL,
|
||
type VARCHAR(20) NOT NULL, -- stock, fund, cash, mixed
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY', -- CNY, USD, HKD
|
||
description TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(20) DEFAULT 'active' -- active, archived, deleted
|
||
);
|
||
|
||
CREATE INDEX idx_accounts_user_id ON accounts(user_id);
|
||
CREATE INDEX idx_accounts_status ON accounts(status);
|
||
```
|
||
|
||
##### positions (持仓表)
|
||
|
||
```sql
|
||
CREATE TABLE positions (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
||
symbol VARCHAR(20) NOT NULL, -- 股票/基金代码
|
||
name VARCHAR(100) NOT NULL, -- 股票/基金名称
|
||
market VARCHAR(20) NOT NULL, -- A股, 港股, 美股
|
||
shares DECIMAL(18, 4) NOT NULL DEFAULT 0, -- 持仓份额
|
||
cost_price DECIMAL(18, 4) NOT NULL, -- 成本价（每股/每份）
|
||
current_price DECIMAL(18, 4), -- 当前价格
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
|
||
status VARCHAR(20) DEFAULT 'active', -- active, suspended, delisted
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(account_id, symbol, market) -- 同一账户同一股票只能有一条持仓
|
||
);
|
||
|
||
CREATE INDEX idx_positions_account_id ON positions(account_id);
|
||
CREATE INDEX idx_positions_symbol ON positions(symbol);
|
||
CREATE INDEX idx_positions_status ON positions(status);
|
||
CREATE INDEX idx_positions_updated_at ON positions(updated_at);
|
||
```
|
||
|
||
##### cash_accounts (现金账户表)
|
||
|
||
```sql
|
||
CREATE TABLE cash_accounts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
||
balance DECIMAL(18, 2) NOT NULL DEFAULT 0, -- 余额
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
|
||
interest_rate DECIMAL(5, 4) DEFAULT 0, -- 年化利率
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(account_id, currency) -- 同一账户同一货币只能有一条记录
|
||
);
|
||
|
||
CREATE INDEX idx_cash_accounts_account_id ON cash_accounts(account_id);
|
||
```
|
||
|
||
##### position_warnings (持仓预警表)
|
||
|
||
```sql
|
||
CREATE TABLE position_warnings (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
position_id BIGINT NOT NULL REFERENCES positions(id) ON DELETE CASCADE,
|
||
warning_type VARCHAR(20) NOT NULL, -- position_limit, price_alert
|
||
threshold_value DECIMAL(18, 4), -- 阈值
|
||
is_triggered BOOLEAN DEFAULT FALSE,
|
||
triggered_at TIMESTAMP,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX idx_position_warnings_user_id ON position_warnings(user_id);
|
||
CREATE INDEX idx_position_warnings_position_id ON position_warnings(position_id);
|
||
```
|
||
|
||
#### 2.2.3 交易相关表
|
||
|
||
##### transactions (交易记录表)
|
||
|
||
```sql
|
||
CREATE TABLE transactions (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
||
position_id BIGINT REFERENCES positions(id) ON DELETE SET NULL, -- 可为空，因为可能是现金交易
|
||
trading_plan_id BIGINT REFERENCES trading_plans(id) ON DELETE SET NULL, -- 关联交易计划
|
||
type VARCHAR(20) NOT NULL, -- buy, sell, dividend, split, bonus, rights, deposit, withdraw
|
||
date DATE NOT NULL, -- 交易日期
|
||
symbol VARCHAR(20), -- 股票/基金代码
|
||
name VARCHAR(100), -- 股票/基金名称
|
||
market VARCHAR(20), -- 市场
|
||
shares DECIMAL(18, 4), -- 交易份额
|
||
price DECIMAL(18, 4), -- 交易价格
|
||
amount DECIMAL(18, 2) NOT NULL, -- 交易金额（正数表示收入，负数表示支出）
|
||
fee DECIMAL(18, 2) DEFAULT 0, -- 交易费用
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
|
||
exchange_rate DECIMAL(10, 6) DEFAULT 1, -- 汇率（用于多货币）
|
||
metadata JSONB, -- 存储额外信息，如拆股比例、分红金额等
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
|
||
CREATE INDEX idx_transactions_account_id ON transactions(account_id);
|
||
CREATE INDEX idx_transactions_position_id ON transactions(position_id);
|
||
CREATE INDEX idx_transactions_trading_plan_id ON transactions(trading_plan_id);
|
||
CREATE INDEX idx_transactions_date ON transactions(date);
|
||
CREATE INDEX idx_transactions_type ON transactions(type);
|
||
CREATE INDEX idx_transactions_symbol ON transactions(symbol);
|
||
CREATE INDEX idx_transactions_date_desc ON transactions(date DESC); -- 用于时间线查询
|
||
```
|
||
|
||
**metadata字段示例：**
|
||
```json
|
||
{
|
||
"split_ratio": 2, // 拆股比例
|
||
"dividend_per_share": 0.5, // 每股分红
|
||
"bonus_ratio": 0.5, // 送股比例
|
||
"rights_price": 10.5, // 配股价格
|
||
"rights_shares": 100 // 配股数量
|
||
}
|
||
```
|
||
|
||
##### transaction_thoughts (交易思考表)
|
||
|
||
```sql
|
||
CREATE TABLE transaction_thoughts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
|
||
content TEXT NOT NULL, -- 思考内容
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(transaction_id) -- 每笔交易只能有一条思考
|
||
);
|
||
|
||
CREATE INDEX idx_transaction_thoughts_transaction_id ON transaction_thoughts(transaction_id);
|
||
CREATE INDEX idx_transaction_thoughts_content ON transaction_thoughts USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引（需要安装pg_trgm扩展）
|
||
```
|
||
|
||
##### transaction_reviews (交易复盘表)
|
||
|
||
```sql
|
||
CREATE TABLE transaction_reviews (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
|
||
review_type VARCHAR(20) DEFAULT 'manual', -- manual, scheduled
|
||
content TEXT NOT NULL, -- 复盘内容
|
||
review_date DATE NOT NULL, -- 复盘日期
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX idx_transaction_reviews_transaction_id ON transaction_reviews(transaction_id);
|
||
CREATE INDEX idx_transaction_reviews_review_date ON transaction_reviews(review_date);
|
||
CREATE INDEX idx_transaction_reviews_content ON transaction_reviews USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引
|
||
```
|
||
|
||
**说明：**
|
||
- 一笔交易可以有多次复盘（不同时间点）
|
||
- `review_type`: manual（手动复盘）、scheduled（定期提醒复盘）
|
||
|
||
#### 2.2.4 计划相关表
|
||
|
||
##### trading_plans (交易计划表)
|
||
|
||
```sql
|
||
CREATE TABLE trading_plans (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
symbol VARCHAR(20) NOT NULL, -- 股票代码
|
||
name VARCHAR(100) NOT NULL, -- 股票名称
|
||
market VARCHAR(20) NOT NULL, -- 市场
|
||
target_price DECIMAL(18, 4) NOT NULL, -- 目标价格
|
||
target_amount DECIMAL(18, 2), -- 目标金额
|
||
target_shares DECIMAL(18, 4), -- 目标份额（金额和份额二选一）
|
||
deadline DATE, -- 截止日期
|
||
status VARCHAR(20) DEFAULT 'pending', -- pending, in_progress, completed, cancelled
|
||
progress DECIMAL(5, 2) DEFAULT 0, -- 完成进度（百分比）
|
||
current_price DECIMAL(18, 4), -- 当前价格（用于提醒）
|
||
is_price_alert_enabled BOOLEAN DEFAULT TRUE, -- 是否启用价格提醒
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
completed_at TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX idx_trading_plans_user_id ON trading_plans(user_id);
|
||
CREATE INDEX idx_trading_plans_status ON trading_plans(status);
|
||
CREATE INDEX idx_trading_plans_deadline ON trading_plans(deadline);
|
||
CREATE INDEX idx_trading_plans_symbol ON trading_plans(symbol);
|
||
```
|
||
|
||
##### plan_steps (计划步骤表)
|
||
|
||
```sql
|
||
CREATE TABLE plan_steps (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
trading_plan_id BIGINT NOT NULL REFERENCES trading_plans(id) ON DELETE CASCADE,
|
||
step_order INTEGER NOT NULL, -- 步骤顺序（1, 2, 3...）
|
||
target_price DECIMAL(18, 4) NOT NULL, -- 该步骤的目标价格
|
||
target_amount DECIMAL(18, 2), -- 该步骤的目标金额
|
||
target_shares DECIMAL(18, 4), -- 该步骤的目标份额
|
||
status VARCHAR(20) DEFAULT 'pending', -- pending, completed
|
||
completed_at TIMESTAMP,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(trading_plan_id, step_order)
|
||
);
|
||
|
||
CREATE INDEX idx_plan_steps_trading_plan_id ON plan_steps(trading_plan_id);
|
||
CREATE INDEX idx_plan_steps_status ON plan_steps(status);
|
||
```
|
||
|
||
##### plan_thoughts (计划思考表)
|
||
|
||
```sql
|
||
CREATE TABLE plan_thoughts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
trading_plan_id BIGINT NOT NULL REFERENCES trading_plans(id) ON DELETE CASCADE,
|
||
content TEXT NOT NULL, -- 思考内容
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(trading_plan_id) -- 每个计划只能有一条思考
|
||
);
|
||
|
||
CREATE INDEX idx_plan_thoughts_trading_plan_id ON plan_thoughts(trading_plan_id);
|
||
CREATE INDEX idx_plan_thoughts_content ON plan_thoughts USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引
|
||
```
|
||
|
||
#### 2.2.5 净值相关表
|
||
|
||
##### net_value_snapshots (净值快照表)
|
||
|
||
```sql
|
||
CREATE TABLE net_value_snapshots (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE, -- 可为空，表示总账户
|
||
snapshot_date DATE NOT NULL, -- 快照日期
|
||
total_asset DECIMAL(18, 2) NOT NULL, -- 总资产
|
||
total_cost DECIMAL(18, 2) NOT NULL, -- 总成本（累计投入）
|
||
total_profit DECIMAL(18, 2) NOT NULL, -- 总收益
|
||
net_value DECIMAL(18, 6) NOT NULL, -- 单位净值
|
||
cumulative_return DECIMAL(10, 6) NOT NULL, -- 累计收益率
|
||
annualized_return DECIMAL(10, 6), -- 年化收益率
|
||
year_to_date_return DECIMAL(10, 6), -- 当年收益率
|
||
positions_data JSONB, -- 持仓明细（JSON格式，便于查询）
|
||
cash_data JSONB, -- 现金明细
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(user_id, account_id, snapshot_date) -- 同一用户同一账户同一日期只能有一条快照
|
||
);
|
||
|
||
CREATE INDEX idx_net_value_snapshots_user_id ON net_value_snapshots(user_id);
|
||
CREATE INDEX idx_net_value_snapshots_account_id ON net_value_snapshots(account_id);
|
||
CREATE INDEX idx_net_value_snapshots_date ON net_value_snapshots(snapshot_date);
|
||
CREATE INDEX idx_net_value_snapshots_user_date ON net_value_snapshots(user_id, snapshot_date DESC);
|
||
```
|
||
|
||
**positions_data字段示例：**
|
||
```json
|
||
[
|
||
{
|
||
"position_id": 1,
|
||
"symbol": "600519",
|
||
"name": "贵州茅台",
|
||
"shares": 100,
|
||
"cost_price": 1600,
|
||
"current_price": 1850,
|
||
"market_value": 185000,
|
||
"profit": 25000
|
||
}
|
||
]
|
||
```
|
||
|
||
##### daily_net_values (每日净值表)
|
||
|
||
```sql
|
||
CREATE TABLE daily_net_values (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE, -- 可为空，表示总账户
|
||
value_date DATE NOT NULL, -- 净值日期
|
||
net_value DECIMAL(18, 6) NOT NULL, -- 单位净值
|
||
total_asset DECIMAL(18, 2) NOT NULL, -- 总资产
|
||
total_cost DECIMAL(18, 2) NOT NULL, -- 总成本
|
||
daily_return DECIMAL(10, 6), -- 日收益率
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(user_id, account_id, value_date)
|
||
);
|
||
|
||
CREATE INDEX idx_daily_net_values_user_id ON daily_net_values(user_id);
|
||
CREATE INDEX idx_daily_net_values_account_id ON daily_net_values(account_id);
|
||
CREATE INDEX idx_daily_net_values_date ON daily_net_values(value_date);
|
||
CREATE INDEX idx_daily_net_values_user_date ON daily_net_values(user_id, account_id, value_date DESC); -- 用于净值曲线查询
|
||
```
|
||
|
||
#### 2.2.6 市场数据表
|
||
|
||
##### market_prices (市场价格表)
|
||
|
||
```sql
|
||
CREATE TABLE market_prices (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
symbol VARCHAR(20) NOT NULL,
|
||
market VARCHAR(20) NOT NULL,
|
||
price_date DATE NOT NULL,
|
||
open_price DECIMAL(18, 4), -- 开盘价
|
||
close_price DECIMAL(18, 4) NOT NULL, -- 收盘价
|
||
high_price DECIMAL(18, 4), -- 最高价
|
||
low_price DECIMAL(18, 4), -- 最低价
|
||
volume BIGINT, -- 成交量
|
||
amount DECIMAL(18, 2), -- 成交额
|
||
currency VARCHAR(10) DEFAULT 'CNY',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(symbol, market, price_date)
|
||
);
|
||
|
||
CREATE INDEX idx_market_prices_symbol ON market_prices(symbol, market);
|
||
CREATE INDEX idx_market_prices_date ON market_prices(price_date);
|
||
CREATE INDEX idx_market_prices_symbol_date ON market_prices(symbol, market, price_date DESC);
|
||
```
|
||
|
||
---
|
||
|
||
## 三、关键业务逻辑实现
|
||
|
||
### 3.1 基金净值法计算
|
||
|
||
#### 3.1.1 每日净值计算流程
|
||
|
||
```sql
|
||
-- 1. 计算当日总资产
|
||
-- 总资产 = 所有持仓市值 + 现金余额
|
||
|
||
-- 2. 计算累计投入资金
|
||
-- 累计投入 = 所有买入交易的金额总和 - 所有卖出交易的金额总和 + 初始资金
|
||
|
||
-- 3. 计算单位净值
|
||
-- 单位净值 = 总资产 / 累计投入资金
|
||
|
||
-- 4. 计算收益率
|
||
-- 累计收益率 = (当前净值 - 初始净值) / 初始净值
|
||
-- 日收益率 = (今日净值 - 昨日净值) / 昨日净值
|
||
```
|
||
|
||
#### 3.1.2 净值计算SQL示例
|
||
|
||
```sql
|
||
-- 计算用户某账户的当日净值
|
||
WITH asset_value AS (
|
||
-- 计算持仓市值
|
||
SELECT
|
||
COALESCE(SUM(p.shares * p.current_price), 0) as position_value
|
||
FROM positions p
|
||
WHERE p.account_id = :account_id
|
||
AND p.status = 'active'
|
||
),
|
||
cash_value AS (
|
||
-- 计算现金余额
|
||
SELECT
|
||
COALESCE(SUM(c.balance), 0) as cash_balance
|
||
FROM cash_accounts c
|
||
WHERE c.account_id = :account_id
|
||
),
|
||
total_cost AS (
|
||
-- 计算累计投入
|
||
SELECT
|
||
COALESCE(SUM(
|
||
CASE
|
||
WHEN t.type IN ('buy', 'deposit') THEN t.amount
|
||
WHEN t.type IN ('sell', 'withdraw') THEN -t.amount
|
||
ELSE 0
|
||
END
|
||
), 0) as total_invested
|
||
FROM transactions t
|
||
WHERE t.account_id = :account_id
|
||
)
|
||
SELECT
|
||
(av.position_value + cv.cash_balance) as total_asset,
|
||
tc.total_invested,
|
||
CASE
|
||
WHEN tc.total_invested > 0
|
||
THEN (av.position_value + cv.cash_balance) / tc.total_invested
|
||
ELSE 1.0
|
||
END as net_value
|
||
FROM asset_value av, cash_value cv, total_cost tc;
|
||
```
|
||
|
||
### 3.2 交易记录与思考关联查询
|
||
|
||
```sql
|
||
-- 查询交易记录及其思考（时间线）
|
||
SELECT
|
||
t.id,
|
||
t.date,
|
||
t.type,
|
||
t.symbol,
|
||
t.name,
|
||
t.shares,
|
||
t.price,
|
||
t.amount,
|
||
tt.content as thought_content,
|
||
tt.created_at as thought_created_at
|
||
FROM transactions t
|
||
LEFT JOIN transaction_thoughts tt ON t.id = tt.transaction_id
|
||
WHERE t.user_id = :user_id
|
||
ORDER BY t.date DESC, t.created_at DESC
|
||
LIMIT :limit OFFSET :offset;
|
||
```
|
||
|
||
### 3.3 交易复盘查询
|
||
|
||
```sql
|
||
-- 查询交易及其所有复盘记录
|
||
SELECT
|
||
t.id,
|
||
t.date,
|
||
t.symbol,
|
||
t.name,
|
||
t.type,
|
||
tr.id as review_id,
|
||
tr.review_date,
|
||
tr.content as review_content,
|
||
tr.created_at as review_created_at
|
||
FROM transactions t
|
||
LEFT JOIN transaction_reviews tr ON t.id = tr.transaction_id
|
||
WHERE t.user_id = :user_id
|
||
AND t.id = :transaction_id
|
||
ORDER BY tr.review_date DESC;
|
||
```
|
||
|
||
### 3.4 交易计划进度更新
|
||
|
||
```sql
|
||
-- 更新计划进度
|
||
UPDATE trading_plans tp
|
||
SET
|
||
progress = (
|
||
SELECT
|
||
CASE
|
||
WHEN tp.target_amount > 0
|
||
THEN LEAST(100, (COALESCE(SUM(CASE WHEN t.type = 'buy' THEN t.amount ELSE 0 END), 0) / tp.target_amount) * 100)
|
||
WHEN tp.target_shares > 0
|
||
THEN LEAST(100, (COALESCE(SUM(CASE WHEN t.type = 'buy' THEN t.shares ELSE 0 END), 0) / tp.target_shares) * 100)
|
||
ELSE 0
|
||
END
|
||
FROM transactions t
|
||
WHERE t.trading_plan_id = tp.id
|
||
),
|
||
status = CASE
|
||
WHEN progress >= 100 THEN 'completed'
|
||
WHEN progress > 0 THEN 'in_progress'
|
||
ELSE 'pending'
|
||
END,
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE tp.id = :plan_id;
|
||
```
|
||
|
||
---
|
||
|
||
## 四、索引优化建议
|
||
|
||
### 4.1 必须创建的索引
|
||
|
||
1. **外键索引**：所有外键字段都应创建索引
|
||
2. **查询字段索引**：经常用于WHERE、JOIN、ORDER BY的字段
|
||
3. **时间字段索引**：date、created_at等时间字段
|
||
|
||
### 4.2 全文搜索索引
|
||
|
||
对于思考内容、复盘内容等文本字段，使用PostgreSQL的全文搜索功能：
|
||
|
||
```sql
|
||
-- 安装扩展（需要管理员权限）
|
||
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
||
CREATE EXTENSION IF NOT EXISTS jieba; -- 中文分词（如果可用）
|
||
|
||
-- 创建全文搜索索引
|
||
CREATE INDEX idx_transaction_thoughts_content_search
|
||
ON transaction_thoughts USING gin(to_tsvector('jiebacfg', content));
|
||
```
|
||
|
||
### 4.3 复合索引
|
||
|
||
根据查询模式创建复合索引：
|
||
|
||
```sql
|
||
-- 用户时间线查询（最常用）
|
||
CREATE INDEX idx_transactions_user_date ON transactions(user_id, date DESC, created_at DESC);
|
||
|
||
-- 净值查询
|
||
CREATE INDEX idx_daily_net_values_user_account_date ON daily_net_values(user_id, account_id, value_date DESC);
|
||
|
||
-- 持仓查询
|
||
CREATE INDEX idx_positions_account_status ON positions(account_id, status);
|
||
```
|
||
|
||
---
|
||
|
||
## 五、数据维护建议
|
||
|
||
### 5.1 定期任务
|
||
|
||
1. **每日净值计算**
|
||
- 每日收盘后（如18:00）自动计算并保存净值
|
||
- 更新持仓的当前价格
|
||
|
||
2. **价格数据更新**
|
||
- 每日更新市场价格表
|
||
- 清理过期的价格数据（保留最近2年）
|
||
|
||
3. **计划提醒**
|
||
- 检查到达目标价格的计划
|
||
- 发送提醒通知
|
||
|
||
4. **复盘提醒**
|
||
- 定期（如每月）提醒用户进行复盘
|
||
|
||
### 5.2 数据清理
|
||
|
||
```sql
|
||
-- 清理过期的市场价格数据（保留最近2年）
|
||
DELETE FROM market_prices
|
||
WHERE price_date < CURRENT_DATE - INTERVAL '2 years';
|
||
|
||
-- 归档已删除的数据（软删除的数据）
|
||
-- 可以考虑定期归档到历史表
|
||
```
|
||
|
||
### 5.3 数据备份
|
||
|
||
1. **定期全量备份**：每日备份
|
||
2. **增量备份**：每小时备份事务日志
|
||
3. **重要数据备份**：交易记录、思考内容等关键数据单独备份
|
||
|
||
---
|
||
|
||
## 六、性能优化建议
|
||
|
||
### 6.1 查询优化
|
||
|
||
1. **分页查询**：所有列表查询都应使用LIMIT和OFFSET
|
||
2. **避免N+1查询**：使用JOIN一次性获取关联数据
|
||
3. **使用EXPLAIN分析**：定期分析慢查询
|
||
|
||
### 6.2 缓存策略
|
||
|
||
1. **净值数据缓存**：最近30天的净值数据可以缓存
|
||
2. **持仓数据缓存**：当前持仓数据可以缓存，更新时失效
|
||
3. **市场价格缓存**：使用Redis缓存常用股票的最新价格
|
||
|
||
### 6.3 读写分离
|
||
|
||
如果数据量大，可以考虑：
|
||
- 主库：写操作
|
||
- 从库：读操作（查询、统计）
|
||
|
||
---
|
||
|
||
## 七、数据迁移方案
|
||
|
||
### 7.1 初始化数据
|
||
|
||
```sql
|
||
-- 创建初始用户
|
||
INSERT INTO users (username, password_hash, nickname)
|
||
VALUES ('admin', 'hashed_password', '管理员');
|
||
|
||
-- 创建初始账户
|
||
INSERT INTO accounts (user_id, name, type, currency)
|
||
VALUES (1, '主账户', 'mixed', 'CNY');
|
||
|
||
-- 设置初始净值
|
||
INSERT INTO daily_net_values (user_id, account_id, value_date, net_value, total_asset, total_cost)
|
||
VALUES (1, 1, CURRENT_DATE, 1.0, 0, 0);
|
||
```
|
||
|
||
### 7.2 数据导入
|
||
|
||
如果从其他系统迁移数据，需要：
|
||
1. 清洗数据格式
|
||
2. 按依赖关系顺序导入（先用户，再账户，再交易）
|
||
3. 重新计算净值历史
|
||
|
||
---
|
||
|
||
## 八、安全考虑
|
||
|
||
### 8.1 数据安全
|
||
|
||
1. **敏感数据加密**：密码使用bcrypt等加密
|
||
2. **SQL注入防护**：使用参数化查询
|
||
3. **权限控制**：用户只能访问自己的数据
|
||
|
||
### 8.2 数据完整性
|
||
|
||
1. **外键约束**：确保数据关联正确
|
||
2. **唯一约束**：防止重复数据
|
||
3. **检查约束**：确保数据有效性
|
||
|
||
```sql
|
||
-- 示例：检查约束
|
||
ALTER TABLE transactions
|
||
ADD CONSTRAINT check_transaction_amount
|
||
CHECK (amount != 0);
|
||
|
||
ALTER TABLE positions
|
||
ADD CONSTRAINT check_shares_positive
|
||
CHECK (shares >= 0);
|
||
```
|
||
|
||
---
|
||
|
||
## 九、总结
|
||
|
||
### 9.1 数据库选型
|
||
|
||
**推荐使用 PostgreSQL 14+**
|
||
|
||
优势：
|
||
- ✅ 关系型数据库，结构清晰
|
||
- ✅ JSONB支持，灵活存储思考内容
|
||
- ✅ 强大的查询能力
|
||
- ✅ 全文搜索支持
|
||
- ✅ 时间序列查询性能好
|
||
|
||
### 9.2 核心表设计
|
||
|
||
1. **用户体系**：users, user_settings
|
||
2. **账户体系**：accounts, positions, cash_accounts
|
||
3. **交易体系**：transactions, transaction_thoughts, transaction_reviews
|
||
4. **计划体系**：trading_plans, plan_steps, plan_thoughts
|
||
5. **净值体系**：daily_net_values, net_value_snapshots
|
||
6. **市场数据**：market_prices
|
||
|
||
### 9.3 关键特性
|
||
|
||
1. **基金净值法**：通过daily_net_values表记录每日净值
|
||
2. **思考记录**：transaction_thoughts和plan_thoughts表
|
||
3. **复盘功能**：transaction_reviews表支持多次复盘
|
||
4. **时间线查询**：通过索引优化时间线查询性能
|
||
|
||
### 9.4 后续优化
|
||
|
||
1. 根据实际使用情况调整索引
|
||
2. 监控慢查询，持续优化
|
||
3. 考虑使用TimescaleDB处理时间序列数据
|
||
4. 根据数据量考虑分表策略
|
||
|
||
---
|
||
|
||
**文档版本**：v1.0
|
||
**创建日期**：2024年
|
||
**数据库版本**：PostgreSQL 14+
|
||
|