88 lines
3.8 KiB
SQL
88 lines
3.8 KiB
SQL
-- ================================
|
||
-- 农业股票数据处理系统 - 扩展表结构
|
||
-- 用于存储Spark处理后的分析结果
|
||
-- ================================
|
||
|
||
-- 技术指标数据表
|
||
CREATE TABLE IF NOT EXISTS stock_technical_indicators (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
stock_code VARCHAR(10) NOT NULL COMMENT '股票代码',
|
||
stock_name VARCHAR(100) NOT NULL COMMENT '股票名称',
|
||
trade_date DATE NOT NULL COMMENT '交易日期',
|
||
close_price DECIMAL(10,2) COMMENT '收盘价',
|
||
ma5 DECIMAL(10,2) COMMENT '5日移动平均',
|
||
ma10 DECIMAL(10,2) COMMENT '10日移动平均',
|
||
ma20 DECIMAL(10,2) COMMENT '20日移动平均',
|
||
ma30 DECIMAL(10,2) COMMENT '30日移动平均',
|
||
rsi DECIMAL(5,2) COMMENT 'RSI相对强弱指标',
|
||
macd_dif DECIMAL(10,4) COMMENT 'MACD DIF值',
|
||
macd_dea DECIMAL(10,4) COMMENT 'MACD DEA值',
|
||
bb_upper DECIMAL(10,2) COMMENT '布林带上轨',
|
||
bb_middle DECIMAL(10,2) COMMENT '布林带中轨',
|
||
bb_lower DECIMAL(10,2) COMMENT '布林带下轨',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='股票技术指标表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_technical_stock ON stock_technical_indicators(stock_code);
|
||
CREATE INDEX idx_technical_date ON stock_technical_indicators(trade_date);
|
||
CREATE INDEX idx_technical_stock_date ON stock_technical_indicators(stock_code, trade_date);
|
||
|
||
-- 行业分析表
|
||
CREATE TABLE IF NOT EXISTS industry_analysis (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
industry VARCHAR(50) NOT NULL COMMENT '行业名称',
|
||
analysis_date DATE NOT NULL COMMENT '分析日期',
|
||
stock_count INT COMMENT '股票数量',
|
||
avg_change_percent DECIMAL(5,2) COMMENT '平均涨跌幅',
|
||
total_market_cap DECIMAL(15,2) COMMENT '行业总市值',
|
||
total_volume BIGINT COMMENT '行业总成交量',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行业分析表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_industry_date ON industry_analysis(analysis_date);
|
||
CREATE INDEX idx_industry_name ON industry_analysis(industry);
|
||
CREATE INDEX idx_industry_date_name ON industry_analysis(analysis_date, industry);
|
||
|
||
-- 市场趋势表
|
||
CREATE TABLE IF NOT EXISTS market_trends (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
trade_date DATE NOT NULL COMMENT '交易日期',
|
||
avg_price DECIMAL(10,2) COMMENT '平均价格',
|
||
avg_change_percent DECIMAL(5,2) COMMENT '平均涨跌幅',
|
||
total_volume BIGINT COMMENT '总成交量',
|
||
total_turnover DECIMAL(15,2) COMMENT '总成交额',
|
||
stock_count INT COMMENT '股票数量',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='市场趋势表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_trends_date ON market_trends(trade_date);
|
||
|
||
-- 为已有的market_analysis表添加唯一索引（防止重复数据）
|
||
CREATE UNIQUE INDEX idx_market_analysis_date ON market_analysis(analysis_date);
|
||
|
||
-- ================================
|
||
-- 示例查询语句
|
||
-- ================================
|
||
|
||
-- 查询最新的市场分析数据
|
||
-- SELECT * FROM market_analysis ORDER BY analysis_date DESC LIMIT 1;
|
||
|
||
-- 查询特定股票的技术指标
|
||
-- SELECT * FROM stock_technical_indicators
|
||
-- WHERE stock_code = 'sz000876'
|
||
-- ORDER BY trade_date DESC LIMIT 30;
|
||
|
||
-- 查询行业表现排行
|
||
-- SELECT industry, avg_change_percent, stock_count
|
||
-- FROM industry_analysis
|
||
-- WHERE analysis_date = (SELECT MAX(analysis_date) FROM industry_analysis)
|
||
-- ORDER BY avg_change_percent DESC;
|
||
|
||
-- 查询市场趋势
|
||
-- SELECT trade_date, avg_change_percent, total_volume
|
||
-- FROM market_trends
|
||
-- ORDER BY trade_date DESC LIMIT 30; |