SHA256
269 lines
7.6 KiB
Go
269 lines
7.6 KiB
Go
package repository
|
|
|
|
import (
|
|
"ashareview-server/model"
|
|
"sync"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
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 returns the latest trade date, cached for 5 minutes
|
|
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
|
|
}
|
|
|
|
func (r *StockRepo) GetLatestTradeDate() (string, error) {
|
|
return r.getLatestDate()
|
|
}
|
|
|
|
func (r *StockRepo) GetSummaryByDate(date string) (*model.DashboardSummary, error) {
|
|
summary := &model.DashboardSummary{}
|
|
|
|
// Single aggregation query instead of 6 separate queries
|
|
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
|
|
|
|
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)
|
|
|
|
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)
|
|
|
|
// 100-day new high: pre-aggregate max_close with covering index
|
|
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
|
|
}
|
|
|
|
func (r *StockRepo) GetDailySummary(days int) ([]model.DailySummary, error) {
|
|
latest, err := r.getLatestDate()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
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)
|
|
GROUP BY date
|
|
ORDER BY date ASC
|
|
`, latest, days-1).Scan(&result)
|
|
|
|
// Consecutive limit-up: get trading date pairs, then batch query
|
|
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)) d1
|
|
JOIN (SELECT DISTINCT date FROM stock_daily WHERE date >= DATE_SUB(?, INTERVAL ? DAY)) d2 ON d2.date < d1.date
|
|
GROUP BY d1.date
|
|
`, latest, days-1, latest, days).Scan(&pairs)
|
|
|
|
type consecRow struct {
|
|
Date string `gorm:"column:date"`
|
|
Cnt int `gorm:"column:cnt"`
|
|
}
|
|
var consecResults []consecRow
|
|
if len(pairs) > 0 {
|
|
// Use UNION ALL for better index utilization than 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
|
|
}
|
|
}
|
|
|
|
return result, nil
|
|
}
|
|
|
|
func (r *StockRepo) GetConsecutiveLimits() ([]model.ConsecutiveLimit, error) {
|
|
latest, err := r.getLatestDate()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if latest == "" {
|
|
return nil, nil
|
|
}
|
|
|
|
// Get recent trading dates with date range to use index
|
|
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 nil, nil
|
|
}
|
|
earliest := dates[len(dates)-1]
|
|
|
|
// Get all limit-up records for recent dates
|
|
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
|
|
}
|
|
|
|
// Get today's limit-up stocks with info
|
|
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,
|
|
})
|
|
}
|
|
|
|
// Sort by streak desc
|
|
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
|
|
}
|