86 lines
3.5 KiB
SQL
86 lines
3.5 KiB
SQL
-- 自有库（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);
|