CREATE TABLE IF NOT EXISTS security (
|
ticker VARCHAR(16) NOT NULL COMMENT '带市场后缀的证券代码',
|
company VARCHAR(128) NOT NULL COMMENT '公司简称',
|
market VARCHAR(16) NOT NULL COMMENT '交易市场',
|
currency CHAR(3) NOT NULL COMMENT 'CNY或HKD',
|
active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否纳入每日更新',
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
PRIMARY KEY (ticker),
|
KEY idx_security_active (active, market),
|
CONSTRAINT chk_security_active CHECK (active IN (0, 1)),
|
CONSTRAINT chk_security_currency CHECK (currency IN ('CNY', 'HKD'))
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
CREATE TABLE IF NOT EXISTS valuation_version (
|
valuation_id VARCHAR(64) NOT NULL COMMENT '可稳定复现的估值版本ID',
|
ticker VARCHAR(16) NOT NULL,
|
valuation_date DATE NOT NULL COMMENT '正式估值基准日',
|
method VARCHAR(32) NOT NULL COMMENT '主估值模型',
|
pessimistic_low DECIMAL(20,6) NULL,
|
pessimistic_high DECIMAL(20,6) NULL,
|
base_low DECIMAL(20,6) NOT NULL,
|
base_high DECIMAL(20,6) NOT NULL,
|
optimistic_low DECIMAL(20,6) NULL,
|
optimistic_high DECIMAL(20,6) NULL,
|
normalized_profit DECIMAL(24,6) NULL,
|
normalized_pe DECIMAL(20,6) NULL,
|
pb DECIMAL(20,6) NULL,
|
ps DECIMAL(20,6) NULL,
|
consensus_year SMALLINT UNSIGNED NULL,
|
consensus_profit DECIMAL(24,6) NULL,
|
consensus_count INT UNSIGNED NULL,
|
report_path VARCHAR(1024) NOT NULL,
|
snapshot_path VARCHAR(1024) NULL,
|
source_hash CHAR(64) NOT NULL,
|
active_from DATE NOT NULL,
|
active_to DATE NULL,
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
PRIMARY KEY (valuation_id),
|
UNIQUE KEY uq_valuation_source (ticker, valuation_date, source_hash),
|
KEY idx_valuation_active (ticker, active_from, active_to),
|
CONSTRAINT fk_valuation_security FOREIGN KEY (ticker) REFERENCES security(ticker),
|
CONSTRAINT chk_valuation_active_dates CHECK (active_to IS NULL OR active_to >= active_from),
|
CONSTRAINT chk_valuation_base CHECK (base_low <= base_high),
|
CONSTRAINT chk_valuation_optimistic CHECK (optimistic_low IS NULL OR optimistic_high IS NULL OR optimistic_low <= optimistic_high),
|
CONSTRAINT chk_valuation_pessimistic CHECK (pessimistic_low IS NULL OR pessimistic_high IS NULL OR pessimistic_low <= pessimistic_high)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
CREATE TABLE IF NOT EXISTS daily_price (
|
ticker VARCHAR(16) NOT NULL,
|
trade_date DATE NOT NULL,
|
close DECIMAL(20,6) NOT NULL,
|
currency CHAR(3) NOT NULL,
|
source_id VARCHAR(64) NOT NULL,
|
source_timestamp DATETIME(6) NULL,
|
ingested_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
PRIMARY KEY (ticker, trade_date),
|
KEY idx_daily_price_date (trade_date, ticker),
|
CONSTRAINT fk_daily_price_security FOREIGN KEY (ticker) REFERENCES security(ticker),
|
CONSTRAINT chk_daily_price_close CHECK (close > 0),
|
CONSTRAINT chk_daily_price_currency CHECK (currency IN ('CNY', 'HKD'))
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
CREATE TABLE IF NOT EXISTS daily_judgement (
|
ticker VARCHAR(16) NOT NULL,
|
trade_date DATE NOT NULL,
|
valuation_id VARCHAR(64) NOT NULL,
|
close DECIMAL(20,6) NOT NULL,
|
base_low DECIMAL(20,6) NOT NULL,
|
base_high DECIMAL(20,6) NOT NULL,
|
optimistic_high DECIMAL(20,6) NULL,
|
label VARCHAR(16) NOT NULL,
|
distance_to_base_low DECIMAL(20,8) NOT NULL,
|
distance_to_base_high DECIMAL(20,8) NOT NULL,
|
computed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
PRIMARY KEY (ticker, trade_date),
|
KEY idx_judgement_date_label (trade_date, label, ticker),
|
KEY idx_judgement_valuation (valuation_id),
|
CONSTRAINT fk_judgement_security FOREIGN KEY (ticker) REFERENCES security(ticker),
|
CONSTRAINT fk_judgement_valuation FOREIGN KEY (valuation_id) REFERENCES valuation_version(valuation_id),
|
CONSTRAINT chk_judgement_base CHECK (base_low <= base_high),
|
CONSTRAINT chk_judgement_label CHECK (label IN ('偏低', '基本合理', '偏贵', '明显偏贵'))
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
