freedakgmail
73 lines
3.0 KiB
SQL
73 lines
3.0 KiB
SQL
-- 模拟交易系统 - PostgreSQL 数据库表
|
|
-- 运行方式: psql -U postgres -d stock_app -f init_sim_trade.sql
|
|
|
|
-- 模拟交易记录表
|
|
CREATE TABLE IF NOT EXISTS sim_trades (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
stock_name VARCHAR(50),
|
|
trade_type VARCHAR(10) NOT NULL, -- 'buy' 或 'sell'
|
|
price DECIMAL(10,4) NOT NULL,
|
|
quantity INTEGER NOT NULL DEFAULT 1000,
|
|
trade_date DATE NOT NULL,
|
|
trade_time TIME,
|
|
recommend_rate DECIMAL(5,2), -- 推荐率
|
|
signal_reason TEXT, -- 交易信号原因
|
|
commission DECIMAL(10,4) DEFAULT 0, -- 佣金 (万2.5, 最低5元)
|
|
stamp_tax DECIMAL(10,4) DEFAULT 0, -- 印花税 (千1, 仅卖出)
|
|
total_fee DECIMAL(10,4) DEFAULT 0, -- 总手续费
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 模拟持仓表
|
|
CREATE TABLE IF NOT EXISTS sim_positions (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
stock_name VARCHAR(50),
|
|
quantity INTEGER NOT NULL DEFAULT 0,
|
|
avg_cost DECIMAL(10,4) NOT NULL DEFAULT 0,
|
|
total_cost DECIMAL(15,4) NOT NULL DEFAULT 0,
|
|
current_price DECIMAL(10,4),
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, stock_code)
|
|
);
|
|
|
|
-- 模拟交易统计表（每日汇总）
|
|
CREATE TABLE IF NOT EXISTS sim_daily_stats (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
stat_date DATE NOT NULL,
|
|
total_market_value DECIMAL(15,4) DEFAULT 0, -- 总市值
|
|
total_cost DECIMAL(15,4) DEFAULT 0, -- 总成本
|
|
unrealized_profit DECIMAL(15,4) DEFAULT 0, -- 浮动盈亏
|
|
realized_profit DECIMAL(15,4) DEFAULT 0, -- 已实现盈亏
|
|
total_profit DECIMAL(15,4) DEFAULT 0, -- 总盈亏
|
|
trade_count INTEGER DEFAULT 0, -- 当日交易次数
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, stat_date)
|
|
);
|
|
|
|
-- 模拟交易配置表
|
|
CREATE TABLE IF NOT EXISTS sim_config (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
initial_capital DECIMAL(15,4) DEFAULT 1000000, -- 初始资金（默认100万）
|
|
trade_quantity INTEGER DEFAULT 1000, -- 每次交易数量
|
|
auto_trade_enabled BOOLEAN DEFAULT true, -- 是否启用自动交易
|
|
auto_trade_time TIME DEFAULT '10:00:00', -- 自动交易时间
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_sim_trades_user ON sim_trades(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_trades_date ON sim_trades(trade_date);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_trades_stock ON sim_trades(stock_code);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_positions_user ON sim_positions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_daily_stats_user_date ON sim_daily_stats(user_id, stat_date);
|
|
|
|
-- 输出结果
|
|
SELECT 'Simulation trade tables created successfully!' as status;
|