SHA256
401 lines
12 KiB
Go
401 lines
12 KiB
Go
// Package repository 数据访问层，直接操作 MySQL 数据库执行 SQL 查询
|
|
package repository
|
|
|
|
import (
|
|
"ashareview-server/model"
|
|
"sync"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
// StockRepo 个股与指数数据的数据访问对象
|
|
type StockRepo struct {
|
|
db *gorm.DB
|
|
dateCache struct { // 最新交易日期缓存，避免频繁查询
|
|
mu sync.RWMutex
|
|
date string
|
|
expires time.Time
|
|
}
|
|
}
|
|
|
|
func NewStockRepo(db *gorm.DB) *StockRepo {
|
|
return &StockRepo{db: db}
|
|
}
|
|
|
|
// getLatestDate 获取最新交易日期，结果缓存 5 分钟
|
|
func (r *StockRepo) getLatestDate() (string, error) {
|
|
r.dateCache.mu.RLock()
|
|
if r.dateCache.date != "" && time.Now().Before(r.dateCache.expires) {
|
|
d := r.dateCache.date
|
|
r.dateCache.mu.RUnlock()
|
|
return d, nil
|
|
}
|
|
r.dateCache.mu.RUnlock()
|
|
|
|
var result struct {
|
|
MaxDate string
|
|
}
|
|
err := r.db.Raw("SELECT MAX(date) as max_date FROM stock_daily").Scan(&result).Error
|
|
if err != nil {
|
|
return "", err
|
|
}
|
|
|
|
r.dateCache.mu.Lock()
|
|
r.dateCache.date = result.MaxDate
|
|
r.dateCache.expires = time.Now().Add(5 * time.Minute)
|
|
r.dateCache.mu.Unlock()
|
|
|
|
return result.MaxDate, nil
|
|
}
|
|
|
|
// resolveTradeDate 将用户指定日期映射到不晚于该日期的最近交易日。
|
|
// 当用户选中非交易日时，使用最近一个有数据的交易日，避免页面空白。
|
|
func (r *StockRepo) resolveTradeDate(date string) (string, error) {
|
|
if date == "" {
|
|
return r.getLatestDate()
|
|
}
|
|
|
|
var result struct {
|
|
ResolvedDate string
|
|
}
|
|
err := r.db.Raw("SELECT MAX(date) as resolved_date FROM stock_daily WHERE date <= ?", date).Scan(&result).Error
|
|
if err != nil {
|
|
return "", err
|
|
}
|
|
return result.ResolvedDate, nil
|
|
}
|
|
|
|
// GetLatestTradeDate 获取最新交易日期（公开方法）
|
|
func (r *StockRepo) GetLatestTradeDate() (string, error) {
|
|
return r.getLatestDate()
|
|
}
|
|
|
|
// GetSummaryByDate 获取指定日期的市场概览数据
|
|
// 包括: 涨跌统计、涨停跌停列表、百日新高列表
|
|
// 使用单条聚合查询替代多次 COUNT 查询，提升性能
|
|
func (r *StockRepo) GetSummaryByDate(date string) (*model.DashboardSummary, error) {
|
|
var err error
|
|
date, err = r.resolveTradeDate(date)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if date == "" {
|
|
return &model.DashboardSummary{
|
|
LimitUpList: []model.StockRank{},
|
|
LimitDownList: []model.StockRank{},
|
|
High100List: []model.StockRank{},
|
|
}, nil
|
|
}
|
|
|
|
summary := &model.DashboardSummary{
|
|
LimitUpList: []model.StockRank{},
|
|
LimitDownList: []model.StockRank{},
|
|
High100List: []model.StockRank{},
|
|
}
|
|
|
|
// 聚合统计: 总数、上涨、下跌、平盘、总成交额、平均涨跌幅
|
|
var agg struct {
|
|
Total int64 `gorm:"column:total"`
|
|
Up int64 `gorm:"column:up"`
|
|
Down int64 `gorm:"column:down"`
|
|
Flat int64 `gorm:"column:flat"`
|
|
Turnover float64 `gorm:"column:turnover"`
|
|
AvgPct float64 `gorm:"column:avg_pct"`
|
|
}
|
|
r.db.Raw(`
|
|
SELECT
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN pct_change > 0 THEN 1 ELSE 0 END) as up,
|
|
SUM(CASE WHEN pct_change < 0 THEN 1 ELSE 0 END) as down,
|
|
SUM(CASE WHEN pct_change = 0 OR pct_change IS NULL THEN 1 ELSE 0 END) as flat,
|
|
COALESCE(SUM(turnover), 0) as turnover,
|
|
COALESCE(AVG(pct_change), 0) as avg_pct
|
|
FROM stock_daily WHERE date = ?
|
|
`, date).Scan(&agg)
|
|
|
|
summary.TotalStocks = agg.Total
|
|
summary.UpCount = agg.Up
|
|
summary.DownCount = agg.Down
|
|
summary.FlatCount = agg.Flat
|
|
summary.TotalTurnover = agg.Turnover
|
|
summary.AvgPctChange = agg.AvgPct
|
|
|
|
// 涨停列表: 涨幅 >= 9.5%（覆盖主板10%和创业板/科创板20%）
|
|
r.db.Raw(`
|
|
SELECT sd.code, si.name, sd.pct_change, sd.close
|
|
FROM stock_daily sd
|
|
LEFT JOIN stock_info si ON sd.code = si.code
|
|
WHERE sd.date = ? AND sd.pct_change >= 9.5
|
|
ORDER BY sd.pct_change DESC
|
|
`, date).Scan(&summary.LimitUpList)
|
|
|
|
// 跌停列表: 跌幅 <= -9.5%
|
|
r.db.Raw(`
|
|
SELECT sd.code, si.name, sd.pct_change, sd.close
|
|
FROM stock_daily sd
|
|
LEFT JOIN stock_info si ON sd.code = si.code
|
|
WHERE sd.date = ? AND sd.pct_change <= -9.5
|
|
ORDER BY sd.pct_change ASC
|
|
`, date).Scan(&summary.LimitDownList)
|
|
|
|
// 百日新高: 当日收盘价等于近 150 个交易日最高收盘价的股票
|
|
r.db.Raw(`
|
|
SELECT t.code, si.name, t.pct_change, t.close
|
|
FROM (
|
|
SELECT sd.code, sd.pct_change, sd.close
|
|
FROM stock_daily sd
|
|
JOIN (
|
|
SELECT code, MAX(close) as max_close
|
|
FROM stock_daily
|
|
WHERE date BETWEEN DATE_SUB(?, INTERVAL 150 DAY) AND ?
|
|
GROUP BY code
|
|
) mh ON sd.code = mh.code AND sd.close = mh.max_close
|
|
WHERE sd.date = ? AND sd.close > 0
|
|
) t
|
|
LEFT JOIN stock_info si ON t.code = si.code
|
|
ORDER BY t.pct_change DESC
|
|
`, date, date, date).Scan(&summary.High100List)
|
|
|
|
return summary, nil
|
|
}
|
|
|
|
// GetHigh100 获取最新交易日创百日新高的股票列表
|
|
func (r *StockRepo) GetHigh100() ([]model.StockRank, error) {
|
|
return r.GetHigh100ByDate("")
|
|
}
|
|
|
|
// GetHigh100ByDate 获取指定日期创百日新高的股票列表，date 为空时使用最新交易日
|
|
func (r *StockRepo) GetHigh100ByDate(date string) ([]model.StockRank, error) {
|
|
var err error
|
|
date, err = r.resolveTradeDate(date)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if date == "" {
|
|
return []model.StockRank{}, nil
|
|
}
|
|
var list []model.StockRank
|
|
r.db.Raw(`
|
|
SELECT t.code, si.name, t.pct_change, t.close
|
|
FROM (
|
|
SELECT sd.code, sd.pct_change, sd.close
|
|
FROM stock_daily sd
|
|
JOIN (
|
|
SELECT code, MAX(close) as max_close
|
|
FROM stock_daily
|
|
WHERE date BETWEEN DATE_SUB(?, INTERVAL 150 DAY) AND ?
|
|
GROUP BY code
|
|
) mh ON sd.code = mh.code AND sd.close = mh.max_close
|
|
WHERE sd.date = ? AND sd.close > 0
|
|
) t
|
|
LEFT JOIN stock_info si ON t.code = si.code
|
|
ORDER BY t.pct_change DESC
|
|
`, date, date, date).Scan(&list)
|
|
if list == nil {
|
|
list = []model.StockRank{}
|
|
}
|
|
return list, nil
|
|
}
|
|
|
|
// GetDailySummary 获取近 N 天的每日市场统计
|
|
// 包含: 涨跌数量、成交额、涨停跌停数量、连板数量
|
|
func (r *StockRepo) GetDailySummary(days int, endDate string) ([]model.DailySummary, error) {
|
|
var latest string
|
|
var err error
|
|
latest, err = r.resolveTradeDate(endDate)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if latest == "" {
|
|
return []model.DailySummary{}, nil
|
|
}
|
|
|
|
var result []model.DailySummary
|
|
r.db.Raw(`
|
|
SELECT
|
|
date,
|
|
COUNT(*) as total_stocks,
|
|
SUM(CASE WHEN pct_change > 0 THEN 1 ELSE 0 END) as up_count,
|
|
SUM(CASE WHEN pct_change < 0 THEN 1 ELSE 0 END) as down_count,
|
|
SUM(CASE WHEN pct_change = 0 OR pct_change IS NULL THEN 1 ELSE 0 END) as flat_count,
|
|
COALESCE(SUM(turnover), 0) as total_turnover,
|
|
COALESCE(AVG(pct_change), 0) as avg_pct_change,
|
|
SUM(CASE WHEN pct_change >= 9.5 AND pct_change < 19.5 THEN 1 ELSE 0 END) as limit_up_10,
|
|
SUM(CASE WHEN pct_change <= -9.5 AND pct_change > -19.5 THEN 1 ELSE 0 END) as limit_down_10,
|
|
SUM(CASE WHEN pct_change >= 19.5 THEN 1 ELSE 0 END) as limit_up_20,
|
|
SUM(CASE WHEN pct_change <= -19.5 THEN 1 ELSE 0 END) as limit_down_20
|
|
FROM stock_daily
|
|
WHERE date >= DATE_SUB(?, INTERVAL ? DAY) AND date <= ?
|
|
GROUP BY date
|
|
ORDER BY date ASC
|
|
`, latest, days-1, latest).Scan(&result)
|
|
|
|
// 计算连板数量: 获取相邻交易日对，批量查询当日和前一日都涨停的股票数量
|
|
type datePair struct {
|
|
CurDate string `gorm:"column:cur_date"`
|
|
PrevDate string `gorm:"column:prev_date"`
|
|
}
|
|
var pairs []datePair
|
|
r.db.Raw(`
|
|
SELECT d1.date AS cur_date, MAX(d2.date) AS prev_date
|
|
FROM (SELECT DISTINCT date FROM stock_daily WHERE date >= DATE_SUB(?, INTERVAL ? DAY) AND date <= ?) d1
|
|
JOIN (SELECT DISTINCT date FROM stock_daily WHERE date >= DATE_SUB(?, INTERVAL ? DAY) AND date <= ?) d2 ON d2.date < d1.date
|
|
GROUP BY d1.date
|
|
`, latest, days-1, latest, latest, days, latest).Scan(&pairs)
|
|
|
|
type consecRow struct {
|
|
Date string `gorm:"column:date"`
|
|
Cnt int `gorm:"column:cnt"`
|
|
}
|
|
var consecResults []consecRow
|
|
if len(pairs) > 0 {
|
|
// 使用 UNION ALL 批量查询，利用索引优于 OR 条件
|
|
unionSQL := ""
|
|
args := []interface{}{}
|
|
for _, p := range pairs {
|
|
if unionSQL != "" {
|
|
unionSQL += " UNION ALL "
|
|
}
|
|
unionSQL += "SELECT ? as date, COUNT(DISTINCT cur.code) as cnt FROM stock_daily cur JOIN stock_daily prev ON prev.code = cur.code AND prev.pct_change >= 9.5 WHERE cur.pct_change >= 9.5 AND cur.date = ? AND prev.date = ?"
|
|
args = append(args, p.CurDate, p.CurDate, p.PrevDate)
|
|
}
|
|
r.db.Raw(unionSQL, args...).Scan(&consecResults)
|
|
}
|
|
|
|
// 将连板数量映射到对应的日期
|
|
consecMap := make(map[string]int)
|
|
for _, row := range consecResults {
|
|
consecMap[row.Date] = row.Cnt
|
|
}
|
|
for i := range result {
|
|
if v, ok := consecMap[result[i].Date]; ok {
|
|
result[i].ConsecutiveLimitCount = v
|
|
}
|
|
}
|
|
|
|
if result == nil {
|
|
result = []model.DailySummary{}
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
// GetConsecutiveLimits 获取最新交易日连板股票列表
|
|
// 查询近 30 天的涨停记录，计算当日涨停股票的连续涨停天数
|
|
func (r *StockRepo) GetConsecutiveLimits() ([]model.ConsecutiveLimit, error) {
|
|
return r.GetConsecutiveLimitsByDate("")
|
|
}
|
|
|
|
// GetConsecutiveLimitsByDate 获取指定日期的连板股票列表，date 为空时使用最新交易日
|
|
func (r *StockRepo) GetConsecutiveLimitsByDate(date string) ([]model.ConsecutiveLimit, error) {
|
|
var latest string
|
|
var err error
|
|
latest, err = r.resolveTradeDate(date)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if latest == "" {
|
|
return []model.ConsecutiveLimit{}, nil
|
|
}
|
|
|
|
// 获取近 20 个交易日（覆盖 30 天范围）
|
|
var dates []string
|
|
r.db.Raw(`SELECT date FROM stock_daily WHERE date BETWEEN DATE_SUB(?, INTERVAL 30 DAY) AND ? GROUP BY date ORDER BY date DESC LIMIT 20`, latest, latest).Scan(&dates)
|
|
if len(dates) == 0 {
|
|
return []model.ConsecutiveLimit{}, nil
|
|
}
|
|
earliest := dates[len(dates)-1]
|
|
|
|
// 加载近期的全部涨停记录到内存，用 set 快速判断
|
|
type limitRow struct {
|
|
Code string `gorm:"column:code"`
|
|
D string `gorm:"column:date"`
|
|
}
|
|
var rows []limitRow
|
|
r.db.Raw(`SELECT code, date FROM stock_daily WHERE pct_change >= 9.5 AND date >= ?`, earliest).Scan(&rows)
|
|
|
|
limitSet := make(map[string]bool)
|
|
for _, row := range rows {
|
|
limitSet[row.Code+"_"+row.D[:10]] = true
|
|
}
|
|
|
|
// 获取当日涨停股票详情
|
|
type stockInfo struct {
|
|
Code string `gorm:"column:code"`
|
|
Name string `gorm:"column:name"`
|
|
Close float64 `gorm:"column:close"`
|
|
PctChange float64 `gorm:"column:pct_change"`
|
|
}
|
|
var todayLimit []stockInfo
|
|
r.db.Raw(`
|
|
SELECT sd.code, si.name, sd.close, sd.pct_change
|
|
FROM stock_daily sd
|
|
LEFT JOIN stock_info si ON sd.code = si.code
|
|
WHERE sd.date = ? AND sd.pct_change >= 9.5
|
|
ORDER BY sd.pct_change DESC
|
|
`, latest).Scan(&todayLimit)
|
|
|
|
// 逐只股票从最近交易日往前计算连续涨停天数
|
|
var result []model.ConsecutiveLimit
|
|
for _, s := range todayLimit {
|
|
streak := 0
|
|
for _, d := range dates {
|
|
ds := d[:10]
|
|
if limitSet[s.Code+"_"+ds] {
|
|
streak++
|
|
} else {
|
|
break
|
|
}
|
|
}
|
|
result = append(result, model.ConsecutiveLimit{
|
|
Code: s.Code,
|
|
Name: s.Name,
|
|
Streak: streak,
|
|
Close: s.Close,
|
|
PctChange: s.PctChange,
|
|
})
|
|
}
|
|
|
|
// 按连板天数降序排列
|
|
for i := 0; i < len(result); i++ {
|
|
for j := i + 1; j < len(result); j++ {
|
|
if result[j].Streak > result[i].Streak || (result[j].Streak == result[i].Streak && result[j].PctChange > result[i].PctChange) {
|
|
result[i], result[j] = result[j], result[i]
|
|
}
|
|
}
|
|
}
|
|
|
|
return result, nil
|
|
}
|
|
|
|
// GetIndexKline 获取指数 K 线数据（全部历史，来自 index_daily 表）
|
|
func (r *StockRepo) GetIndexKline(code string, days int) ([]model.IndexKline, error) {
|
|
var result []model.IndexKline
|
|
r.db.Raw(`
|
|
SELECT date, open, close, high, low, volume, amount, pct_change
|
|
FROM index_daily
|
|
WHERE code = ?
|
|
ORDER BY date ASC
|
|
`, code).Scan(&result)
|
|
return result, nil
|
|
}
|
|
|
|
// GetStockKline 获取个股 K 线数据（全部历史，来自 stock_daily 表）
|
|
func (r *StockRepo) GetStockKline(code string) ([]model.IndexKline, error) {
|
|
var result []model.IndexKline
|
|
r.db.Raw(`
|
|
SELECT date, open, close, high, low, volume, turnover as amount, pct_change
|
|
FROM stock_daily
|
|
WHERE code = ?
|
|
ORDER BY date ASC
|
|
`, code).Scan(&result)
|
|
return result, nil
|
|
}
|
|
|
|
// GetStockName 根据 code 查询股票名称
|
|
func (r *StockRepo) GetStockName(code string) (string, error) {
|
|
var name string
|
|
r.db.Raw(`SELECT name FROM stock_info WHERE code = ?`, code).Scan(&name)
|
|
return name, nil
|
|
}
|