freedakgmail
170 lines
8.6 KiB
SQL
170 lines
8.6 KiB
SQL
-- 智能交易引擎 - 数据库表
|
|
-- 运行方式: psql -U postgres -d stock_app -f init_smart_trade.sql
|
|
|
|
-- ═══════════════════════════════════════════════════════
|
|
-- 1. 算法配置表: 每个用户选择的算法及其参数
|
|
-- ═══════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS sim_algo_config (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
algo_name VARCHAR(100) NOT NULL DEFAULT 'PE50G3+BE8', -- 算法名称
|
|
-- 止盈止损
|
|
take_profit_pct DECIMAL(5,2) DEFAULT 12, -- 止盈百分比
|
|
stop_loss_pct DECIMAL(5,2) DEFAULT 8, -- 止损百分比
|
|
-- 卖出信号处理
|
|
ignore_sell_signal BOOLEAN DEFAULT FALSE, -- 忽略扫描卖出信号
|
|
sell_confirm_days INTEGER DEFAULT 3, -- 卖出信号确认天数
|
|
-- 持仓管理
|
|
max_hold_days INTEGER DEFAULT 60, -- 最大持仓天数 (0=无限)
|
|
no_timeout_if_rising BOOLEAN DEFAULT TRUE, -- 连涨中不强制平仓
|
|
-- 资金管理
|
|
total_capital DECIMAL(15,2) DEFAULT 200000, -- 总本金
|
|
position_pct DECIMAL(5,2) DEFAULT 8, -- 单笔仓位占比%
|
|
signal_weight BOOLEAN DEFAULT TRUE, -- 信号加权仓位
|
|
-- v6特性: 部分止盈
|
|
partial_exit_pct INTEGER DEFAULT 50, -- 到TP时卖出比例% (0=全卖)
|
|
momentum_trail_gap DECIMAL(5,2) DEFAULT 3, -- 跟踪止盈回撤%
|
|
-- v6特性: 保本止损
|
|
breakeven_at DECIMAL(5,2) DEFAULT 8, -- 盈利N%后止损移至成本 (0=关闭)
|
|
-- v6特性: 动量跟踪
|
|
momentum_tp BOOLEAN DEFAULT FALSE, -- 连涨保护
|
|
momentum_days INTEGER DEFAULT 3, -- 连涨判定天数
|
|
-- v7特性: 交易时间
|
|
buy_time VARCHAR(5) DEFAULT '09:35', -- 买入时间点
|
|
sell_time VARCHAR(5) DEFAULT '13:40', -- 卖出时间点
|
|
-- 启用状态
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id)
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════
|
|
-- 2. 持仓元数据表: 跟踪每个持仓的算法状态
|
|
-- ═══════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS sim_position_meta (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
-- 买入信息
|
|
buy_date DATE NOT NULL,
|
|
buy_price DECIMAL(10,4) NOT NULL,
|
|
buy_reason TEXT,
|
|
buy_signal_rate INTEGER DEFAULT 0, -- 买入时信号强度
|
|
buy_triggered_count INTEGER DEFAULT 0, -- 买入时触发信号数
|
|
-- 算法状态跟踪
|
|
days_held INTEGER DEFAULT 0, -- 已持仓天数
|
|
max_price_since_buy DECIMAL(10,4) DEFAULT 0, -- 买入以来最高价
|
|
consecutive_up_days INTEGER DEFAULT 0, -- 连续上涨天数
|
|
consecutive_sell_signals INTEGER DEFAULT 0, -- 连续卖出信号天数
|
|
-- v6特性状态
|
|
partial_exit_done BOOLEAN DEFAULT FALSE, -- 是否已部分止盈
|
|
breakeven_active BOOLEAN DEFAULT FALSE, -- 保本止损是否激活
|
|
momentum_trailing_active BOOLEAN DEFAULT FALSE, -- 动量跟踪是否激活
|
|
momentum_high_price DECIMAL(10,4) DEFAULT 0, -- 动量跟踪最高价
|
|
-- 当前状态
|
|
current_shares INTEGER DEFAULT 0, -- 当前持股数
|
|
original_shares INTEGER DEFAULT 0, -- 原始买入股数
|
|
last_update_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, stock_code)
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════
|
|
-- 3. 交易信号日志表: 记录每日的交易决策过程
|
|
-- ═══════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS sim_trade_signals (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
signal_date DATE NOT NULL,
|
|
signal_time TIME,
|
|
stock_code VARCHAR(10) NOT NULL,
|
|
stock_name VARCHAR(50),
|
|
-- 信号信息
|
|
action VARCHAR(20) NOT NULL, -- buy/sell/partial_sell/hold/skip
|
|
reason TEXT, -- 详细原因
|
|
algo_rule VARCHAR(50), -- 触发的算法规则 (TP/SL/PE/BE/MT/TIMEOUT等)
|
|
-- 价格信息
|
|
signal_price DECIMAL(10,4),
|
|
buy_price DECIMAL(10,4), -- 成本价
|
|
profit_pct DECIMAL(8,4), -- 当前盈亏%
|
|
-- 执行信息
|
|
executed BOOLEAN DEFAULT FALSE, -- 是否已执行
|
|
execute_price DECIMAL(10,4),
|
|
execute_shares INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 索引
|
|
CREATE INDEX IF NOT EXISTS idx_sim_algo_config_user ON sim_algo_config(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_position_meta_user ON sim_position_meta(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_position_meta_code ON sim_position_meta(user_id, stock_code);
|
|
CREATE INDEX IF NOT EXISTS idx_sim_trade_signals_date ON sim_trade_signals(user_id, signal_date);
|
|
|
|
-- ═══════════════════════════════════════════════════════
|
|
-- 4. 预置算法配置 (3个推荐算法)
|
|
-- ═══════════════════════════════════════════════════════
|
|
-- 注意: 以下INSERT语句用于初始化默认算法模板, 实际用户配置通过API创建
|
|
|
|
-- 默认算法模板表
|
|
CREATE TABLE IF NOT EXISTS algo_templates (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
display_name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
risk_level VARCHAR(20), -- aggressive / balanced / conservative
|
|
-- 参数 (与 sim_algo_config 相同)
|
|
take_profit_pct DECIMAL(5,2),
|
|
stop_loss_pct DECIMAL(5,2),
|
|
ignore_sell_signal BOOLEAN DEFAULT FALSE,
|
|
sell_confirm_days INTEGER DEFAULT 0,
|
|
max_hold_days INTEGER DEFAULT 0,
|
|
no_timeout_if_rising BOOLEAN DEFAULT FALSE,
|
|
position_pct DECIMAL(5,2) DEFAULT 10,
|
|
signal_weight BOOLEAN DEFAULT TRUE,
|
|
partial_exit_pct INTEGER DEFAULT 0,
|
|
momentum_trail_gap DECIMAL(5,2) DEFAULT 3,
|
|
breakeven_at DECIMAL(5,2) DEFAULT 0,
|
|
momentum_tp BOOLEAN DEFAULT FALSE,
|
|
momentum_days INTEGER DEFAULT 3,
|
|
buy_time VARCHAR(5) DEFAULT '09:35',
|
|
sell_time VARCHAR(5) DEFAULT '13:40',
|
|
-- 回测业绩
|
|
backtest_annual_return DECIMAL(8,2),
|
|
backtest_max_drawdown DECIMAL(8,2),
|
|
backtest_win_rate DECIMAL(8,2),
|
|
backtest_calmar DECIMAL(8,2),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 插入3个推荐算法模板
|
|
INSERT INTO algo_templates (name, display_name, description, risk_level,
|
|
take_profit_pct, stop_loss_pct, ignore_sell_signal, sell_confirm_days,
|
|
max_hold_days, no_timeout_if_rising, position_pct, signal_weight,
|
|
partial_exit_pct, momentum_trail_gap, breakeven_at, momentum_tp,
|
|
backtest_annual_return, backtest_max_drawdown, backtest_win_rate, backtest_calmar)
|
|
VALUES
|
|
-- 🏆 最高收益
|
|
('PE50G3_TP10_SL6', '📈 最高收益策略',
|
|
'部分止盈50% + 跟踪止盈 | 止盈10%/止损6% | 忽略卖出信号 | 最长60天 | 10%仓位',
|
|
'aggressive',
|
|
10, 6, TRUE, 0, 60, TRUE, 10, TRUE, 50, 3, 0, FALSE,
|
|
18.2, 7.1, 58.2, 2.56),
|
|
|
|
-- 🛡️ 风险调整最优
|
|
('PE50G3_BE8_TP12_SL8', '🛡️ 风控优先策略',
|
|
'部分止盈50% + 保本止损8% + 延迟卖出3天 | 止盈12%/止损8% | 最长60天 | 8%仓位',
|
|
'balanced',
|
|
12, 8, FALSE, 3, 60, TRUE, 8, TRUE, 50, 3, 8, FALSE,
|
|
17.0, 5.3, 53.8, 3.22),
|
|
|
|
-- 📊 最稳健
|
|
('PE30G3_BE8_TP10_SL6', '📊 稳健均衡策略',
|
|
'部分止盈30% + 保本止损8% + 忽略卖出 | 止盈10%/止损6% | 最长60天 | 10%仓位',
|
|
'conservative',
|
|
10, 6, TRUE, 0, 60, TRUE, 10, TRUE, 30, 3, 8, FALSE,
|
|
17.4, 7.5, 55.9, 2.32)
|
|
|
|
ON CONFLICT (name) DO NOTHING;
|