Simon
23972e7063
汇总三轮未提交的开发（每轮均在本机 MariaDB + 真实浏览器上验证）：
1) 股息率案例（全市场股息率最高 n 只，默认 20，每 m 月择股）
- 新增日频估值表 daily_basic + 迁移；股息率因子（dv_ratio / dividend_yield / TTM）
- 名称历史表 stock_name_history：剔除 ST 按**择股日当时名称**判定，消除
「曾高股息后 ST」的股息陷阱（实测 3.70pp 偏差）
- 区间择股/调仓双周期（m 择股 / y 调仓）、指数成分与白名单、停牌近似剔除
- 复权因子口径核对（4,164,742 行、缺失 0.0%）、收盘价成交与涨跌停拦单
- 案例实测：2020-01-01~2026-09-04 总收益 +24.86%（年化 3.52%、回撤 -28.58%）
2) 策略库与前端统一
- strategy 表 + CRUD/PUT 原地更新 + `describe_strategy` 按 spec 真实推导
「一句话说明 + 计算公式 + 执行步骤 + 注意事项」（与引擎实执行规则同源）
- 任何出现股票代码处都成对显示名称且可点击进个股页
- 全站图表基座统一 TradingView Lightweight Charts（ECharts 依赖、
锁文件、组件与文档标注一并清除），买卖点标记只落在真实交易日上
3) 回测存档完整化（可往复查看）
- 同步端点（POST /api/backtests、/api/factor-tests）此前完全不落库 → 现在同样归档，
归档 id 经响应头 X-Experiment-Id 返回（不破坏 response_model）
- data_version 首次真实写入（数据快照指纹：最新交易日 + 各表规模）
- 个股收益曲线默认**全量保存**（此前硬截断 60 只）；超出体积预算才裁剪，
并写 archive_meta（机器可读）+ unimplemented（人可读）如实标注
- 列表 kind/q 过滤 + X-Total-Count（此前 limit=50 静默截断）、DELETE 归档
- 只读归档页 /experiments/{id}（Server Component，SSR 直出**选股条件**与
**交易执行依据**）；结果视图按 kind 分发（backtest/factor_test/selection），
非回测归档不套用回测口径
- 新增 CLI：prune_experiments（保留策略，默认 dry-run）、
restore_experiment_from_job（从 Job 副本按原 id 重建被删的历史归档，默认 dry-run）
门禁：pytest 388 passed、ruff All checks passed、tsc 0 错误、图表单测 7 passed、
next build 成功、契约脚本 verify_strategy_workspace 59/59（含按 kind 逐类验证归档页）。
172 lines
8.0 KiB
Python
172 lines
8.0 KiB
Python
"""Phase 1 市场数据表模型（SQLAlchemy 2.x 声明式）。
|
||
|
||
列名与 domain.entities.market 字段一一对应，便于 Repository 双向映射。
|
||
Decimal 字段用 Numeric：SQLite 以浮点近似存储，未来 MySQL 下精确。
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
from datetime import date, datetime
|
||
from decimal import Decimal
|
||
|
||
from sqlalchemy import (
|
||
BigInteger,
|
||
Boolean,
|
||
Date,
|
||
DateTime,
|
||
Integer,
|
||
Numeric,
|
||
String,
|
||
UniqueConstraint,
|
||
)
|
||
from sqlalchemy.orm import Mapped, mapped_column
|
||
|
||
from app.infrastructure.persistence.sqlalchemy.base import Base
|
||
|
||
# SQLite 只对 INTEGER PRIMARY KEY 自增；MySQL 下用 BIGINT
|
||
PK_INT = BigInteger().with_variant(Integer, "sqlite")
|
||
|
||
SYMBOL_LEN = 12
|
||
|
||
|
||
class StockModel(Base):
|
||
__tablename__ = "stock"
|
||
|
||
id: Mapped[int] = mapped_column(primary_key=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), unique=True, index=True)
|
||
name: Mapped[str] = mapped_column(String(64))
|
||
industry: Mapped[str | None] = mapped_column(String(64), nullable=True)
|
||
area: Mapped[str | None] = mapped_column(String(32), nullable=True)
|
||
market: Mapped[str | None] = mapped_column(String(16), nullable=True)
|
||
exchange: Mapped[str | None] = mapped_column(String(8), nullable=True)
|
||
list_date: Mapped[date] = mapped_column(Date)
|
||
delist_date: Mapped[date | None] = mapped_column(Date, nullable=True)
|
||
status: Mapped[str] = mapped_column(String(8), default="L")
|
||
|
||
|
||
class TradingCalendarModel(Base):
|
||
__tablename__ = "trading_calendar"
|
||
|
||
id: Mapped[int] = mapped_column(primary_key=True)
|
||
calendar_date: Mapped[date] = mapped_column(Date, unique=True, index=True)
|
||
is_open: Mapped[bool] = mapped_column(Boolean, default=True)
|
||
|
||
|
||
class StockDailyModel(Base):
|
||
"""不复权日线。"""
|
||
|
||
__tablename__ = "stock_daily"
|
||
__table_args__ = (UniqueConstraint("symbol", "trade_date", name="uq_daily_symbol_date"),)
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), index=True)
|
||
trade_date: Mapped[date] = mapped_column(Date, index=True)
|
||
source: Mapped[str] = mapped_column(String(16), default="tushare", server_default="tushare")
|
||
adjust: Mapped[str] = mapped_column(String(8), default="none", server_default="none")
|
||
open: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
high: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
low: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
close: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
volume: Mapped[Decimal | None] = mapped_column(Numeric(24, 2), nullable=True)
|
||
amount: Mapped[Decimal | None] = mapped_column(Numeric(24, 2), nullable=True)
|
||
|
||
|
||
class AdjustFactorModel(Base):
|
||
__tablename__ = "adjust_factor"
|
||
__table_args__ = (UniqueConstraint("symbol", "trade_date", name="uq_adj_symbol_date"),)
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), index=True)
|
||
trade_date: Mapped[date] = mapped_column(Date, index=True)
|
||
factor: Mapped[Decimal] = mapped_column(Numeric(20, 6))
|
||
|
||
|
||
class DailyBasicModel(Base):
|
||
"""每日指标快照（Tushare daily_basic）—— 估值 / 股息率 / 市值。
|
||
|
||
幂等键 (symbol, trade_date)：同一交易日同一股票唯一一行。
|
||
dv_ratio/dv_ttm 为时点值，研究侧按 trade_date <= as_of 取用（无未来函数）。
|
||
"""
|
||
|
||
__tablename__ = "daily_basic"
|
||
__table_args__ = (UniqueConstraint("symbol", "trade_date", name="uq_basic_symbol_date"),)
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), index=True)
|
||
trade_date: Mapped[date] = mapped_column(Date, index=True)
|
||
source: Mapped[str] = mapped_column(String(16), default="tushare", server_default="tushare")
|
||
close: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
turnover_rate: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
volume_ratio: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
pe: Mapped[Decimal | None] = mapped_column(Numeric(16, 4), nullable=True)
|
||
pe_ttm: Mapped[Decimal | None] = mapped_column(Numeric(16, 4), nullable=True)
|
||
pb: Mapped[Decimal | None] = mapped_column(Numeric(16, 4), nullable=True)
|
||
ps: Mapped[Decimal | None] = mapped_column(Numeric(16, 4), nullable=True)
|
||
ps_ttm: Mapped[Decimal | None] = mapped_column(Numeric(16, 4), nullable=True)
|
||
dv_ratio: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
dv_ttm: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
total_share: Mapped[Decimal | None] = mapped_column(Numeric(24, 4), nullable=True)
|
||
float_share: Mapped[Decimal | None] = mapped_column(Numeric(24, 4), nullable=True)
|
||
free_share: Mapped[Decimal | None] = mapped_column(Numeric(24, 4), nullable=True)
|
||
total_mv: Mapped[Decimal | None] = mapped_column(Numeric(24, 4), nullable=True)
|
||
circ_mv: Mapped[Decimal | None] = mapped_column(Numeric(24, 4), nullable=True)
|
||
|
||
|
||
class FinancialIndicatorModel(Base):
|
||
"""财务指标快照 —— report_date(报告期) 与 announce_date(公告日) 并存。"""
|
||
|
||
__tablename__ = "financial_indicator"
|
||
__table_args__ = (
|
||
UniqueConstraint("symbol", "report_date", "announce_date", name="uq_fin_sym_rep_ann"),
|
||
)
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), index=True)
|
||
report_date: Mapped[date] = mapped_column(Date, index=True)
|
||
announce_date: Mapped[date] = mapped_column(Date, index=True)
|
||
source: Mapped[str] = mapped_column(
|
||
String(16), default="tushare", server_default="tushare"
|
||
)
|
||
eps: Mapped[Decimal | None] = mapped_column(Numeric(12, 4), nullable=True)
|
||
roe: Mapped[Decimal | None] = mapped_column(Numeric(10, 4), nullable=True)
|
||
total_revenue: Mapped[Decimal | None] = mapped_column(Numeric(24, 2), nullable=True)
|
||
net_profit: Mapped[Decimal | None] = mapped_column(Numeric(24, 2), nullable=True)
|
||
gross_margin: Mapped[Decimal | None] = mapped_column(Numeric(10, 4), nullable=True)
|
||
|
||
|
||
class SyncLogModel(Base):
|
||
__tablename__ = "sync_log"
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
source: Mapped[str] = mapped_column(String(16), index=True)
|
||
api: Mapped[str] = mapped_column(String(32))
|
||
request_time: Mapped[datetime] = mapped_column(DateTime)
|
||
success: Mapped[bool] = mapped_column(Boolean)
|
||
failure_reason: Mapped[str | None] = mapped_column(String(500), nullable=True)
|
||
row_count: Mapped[int] = mapped_column(default=0)
|
||
data_start: Mapped[date | None] = mapped_column(Date, nullable=True)
|
||
data_end: Mapped[date | None] = mapped_column(Date, nullable=True)
|
||
|
||
|
||
class StockNameHistoryModel(Base):
|
||
"""股票名称变更历史（Tushare namechange）—— 时点 ST / 风险警示判定的依据。
|
||
|
||
幂等键 (symbol, start_date)：同一股票同一名称生效起点唯一一行。
|
||
查询语义：`name` 在 [start_date, end_date] 内有效；`end_date` 为空表示至今有效。
|
||
时点取值：`start_date <= as_of AND (end_date IS NULL OR end_date >= as_of)`。
|
||
"""
|
||
|
||
__tablename__ = "stock_name_history"
|
||
__table_args__ = (
|
||
UniqueConstraint("symbol", "start_date", name="uq_name_symbol_start"),
|
||
)
|
||
|
||
id: Mapped[int] = mapped_column(PK_INT, primary_key=True, autoincrement=True)
|
||
symbol: Mapped[str] = mapped_column(String(SYMBOL_LEN), index=True)
|
||
name: Mapped[str] = mapped_column(String(64))
|
||
start_date: Mapped[date] = mapped_column(Date, index=True)
|
||
end_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
|
||
ann_date: Mapped[date | None] = mapped_column(Date, nullable=True)
|
||
change_reason: Mapped[str | None] = mapped_column(String(32), nullable=True)
|
||
source: Mapped[str] = mapped_column(String(16), default="tushare", server_default="tushare")
|