86 lines
3.5 KiB
MySQL
86 lines
3.5 KiB
MySQL
|
-- 自有库（Postgres）建表脚本。
|
|||
|
|
-- docker-compose 首次启动会自动执行；也可手工 psql -f 执行。
|
|||
|
|
-- 与 backend/app/db.py 的 ORM 模型保持一致。
|
|||
|
|
|
|||
|
|
-- ============ 指数日线（同步落地）============
|
|||
|
|
CREATE TABLE IF NOT EXISTS index_daily (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
index_code VARCHAR(16) NOT NULL,
|
|||
|
|
trade_date DATE NOT NULL,
|
|||
|
|
open NUMERIC(16,4),
|
|||
|
|
high NUMERIC(16,4),
|
|||
|
|
low NUMERIC(16,4),
|
|||
|
|
close NUMERIC(16,4),
|
|||
|
|
volume BIGINT,
|
|||
|
|
amount NUMERIC(24,4),
|
|||
|
|
pct_chg NUMERIC(10,4),
|
|||
|
|
turnover_rate NUMERIC(10,4),
|
|||
|
|
ohlc_source VARCHAR(24) DEFAULT 'close_only',
|
|||
|
|
updated_at TIMESTAMP DEFAULT now(),
|
|||
|
|
CONSTRAINT uq_index_daily UNIQUE (index_code, trade_date)
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_index_daily_code ON index_daily (index_code);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_index_daily_date ON index_daily (trade_date);
|
|||
|
|
|
|||
|
|
-- ============ 情绪日度 ============
|
|||
|
|
CREATE TABLE IF NOT EXISTS sentiment_daily (
|
|||
|
|
trade_date DATE PRIMARY KEY,
|
|||
|
|
up_down_ratio NUMERIC(10,4),
|
|||
|
|
median_pct_chg NUMERIC(10,4),
|
|||
|
|
pct_chg_gt_5_count INTEGER,
|
|||
|
|
limit_up_count INTEGER,
|
|||
|
|
limit_down_count INTEGER,
|
|||
|
|
margin_balance NUMERIC(24,4),
|
|||
|
|
new_accounts INTEGER,
|
|||
|
|
sentiment_score NUMERIC(10,4),
|
|||
|
|
updated_at TIMESTAMP DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ============ ETF 流入（人工/导入/自动）============
|
|||
|
|
-- 说明：etf_code 每个交易日应唯一。类别聚合行请用形如 'AGG_broad' 的 code，避免空串冲突。
|
|||
|
|
CREATE TABLE IF NOT EXISTS etf_flow (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
trade_date DATE NOT NULL,
|
|||
|
|
etf_code VARCHAR(24) DEFAULT '',
|
|||
|
|
etf_name VARCHAR(64),
|
|||
|
|
category VARCHAR(32),
|
|||
|
|
related_index VARCHAR(16),
|
|||
|
|
net_inflow NUMERIC(20,4), -- 亿元
|
|||
|
|
shares_change NUMERIC(20,4), -- 亿份
|
|||
|
|
source VARCHAR(16) DEFAULT 'manual',
|
|||
|
|
note TEXT,
|
|||
|
|
created_at TIMESTAMP DEFAULT now(),
|
|||
|
|
CONSTRAINT uq_etf_flow UNIQUE (trade_date, etf_code)
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_etf_flow_date ON etf_flow (trade_date);
|
|||
|
|
|
|||
|
|
-- ============ 重大事件 ============
|
|||
|
|
CREATE TABLE IF NOT EXISTS market_event (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
event_date DATE NOT NULL,
|
|||
|
|
title VARCHAR(255) NOT NULL,
|
|||
|
|
category VARCHAR(32) DEFAULT '其他', -- 监管/IPO/政策/资金/外部/其他
|
|||
|
|
impact_direction VARCHAR(16) DEFAULT 'neutral', -- bullish/bearish/neutral
|
|||
|
|
severity INTEGER DEFAULT 3, -- 1-5
|
|||
|
|
related_indices VARCHAR(128), -- CSV，空=全市场
|
|||
|
|
cycle_tag VARCHAR(16) DEFAULT 'none', -- top/bottom/none
|
|||
|
|
description TEXT,
|
|||
|
|
source_url VARCHAR(512),
|
|||
|
|
source VARCHAR(16) DEFAULT 'manual',
|
|||
|
|
created_at TIMESTAMP DEFAULT now(),
|
|||
|
|
updated_at TIMESTAMP DEFAULT now()
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_market_event_date ON market_event (event_date);
|
|||
|
|
|
|||
|
|
-- ============ 人工顶底标注 ============
|
|||
|
|
CREATE TABLE IF NOT EXISTS cycle_annotation (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
index_code VARCHAR(16) NOT NULL,
|
|||
|
|
anno_date DATE NOT NULL,
|
|||
|
|
kind VARCHAR(16) DEFAULT 'watch', -- top/bottom/watch
|
|||
|
|
note TEXT,
|
|||
|
|
created_at TIMESTAMP DEFAULT now()
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_cycle_anno_code ON cycle_annotation (index_code);
|
|||
|
|
CREATE INDEX IF NOT EXISTS ix_cycle_anno_date ON cycle_annotation (anno_date);
|