-- 智能交易引擎 - 数据库表 -- 运行方式: 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;