package trade_analysis import ( "eta/eta_api/utils" "fmt" "github.com/beego/beego/v2/client/orm" "strings" "time" ) const ( TradeDataTypeNull = 0 // 无值 TradeDataTypeOrigin = 1 // 原始值 TradeDataTypeCalculate = 2 // 推算值 WarehouseBuyChartType = 1 // 多单图 WarehouseSoldChartType = 2 // 空单图 WarehousePureBuyChartType = 3 // 净多单图 WarehouseDefaultUnit = "手" WarehouseDefaultFrequency = "日度" GuangZhouTopCompanyAliasName = "日成交持仓排名" // 广期所TOP20对应的公司名称 GuangZhouSeatNameBuy = "持买单量" // 广期所指标名称中的多单名称 GuangZhouSeatNameSold = "持卖单量" // 广期所指标名称中的空单名称 GuangZhouTopSeatNameBuy = "持买单量总计" // 广期所指标名称中的TOP20多单名称 GuangZhouTopSeatNameSold = "持卖单量总计" // 广期所指标名称中的TOP20空单名称 GuangZhouTopSeatNameDeal = "成交量总计" // 广期所指标名称中的TOP20成交量名称 ) const ( TradeExchangeDalian = "dalian" TradeExchangeZhengzhou = "zhengzhou" TradeExchangeGuangzhou = "guangzhou" ) var WarehouseTypeSuffixNames = map[int]string{ WarehouseBuyChartType: "席位多单", WarehouseSoldChartType: "席位空单", WarehousePureBuyChartType: "席位净多单", } // GuangzhouSeatNameValType 广期所数据名称对应的席位方向 var GuangzhouSeatNameValType = map[string]int{ GuangZhouSeatNameBuy: 1, GuangZhouSeatNameSold: 2, GuangZhouTopSeatNameBuy: 1, GuangZhouTopSeatNameSold: 2, GuangZhouTopSeatNameDeal: 3, } // 合约查询方式 var ( ContractQueryTypeTop = 1 // 主力合约 ContractQueryTypeTop2 = 2 // 成交量前2 ContractQueryTypeTop3 = 3 // 成交量前3 ContractQueryTypeAll = 4 // 所有合约(多个) ContractQueryTypeTotal = 5 // 合约加总(1个) ) // 合约方向 var ( ContractPositionBuy = 1 // 多单 ContractPositionSold = 2 // 空单 ContractPositionPureBuy = 3 // 净多单 ) // TradePositionTop 持仓榜单 type TradePositionTop struct { Id uint64 `gorm:"primaryKey;column:id" json:"id"` ClassifyName string `gorm:"column:classify_name" json:"classify_name"` //分类名称 ClassifyType string `gorm:"column:classify_type" json:"classify_type"` //分类名称下的类型 DealShortName string `gorm:"column:deal_short_name" json:"deal_short_name"` //成交量公司简称 DealValue int `gorm:"column:deal_value" json:"deal_value"` //成交量 DealChange int `gorm:"column:deal_change" json:"deal_change"` //成交变化量 DataTime time.Time `gorm:"column:data_time" json:"data_time"` //数据日期 CreateTime time.Time `gorm:"column:create_time" json:"create_time"` //插入时间 ModifyTime time.Time `gorm:"column:modify_time" json:"modify_time"` //修改时间 DealType int `gorm:"column:deal_type" json:"deal_type"` //交易类型：1多单，2空单，3净多单，4净空单 SourceType int `gorm:"column:source_type" json:"source_type"` //数据来源，0是原始数据的值，1是由T+1日推算出的值，2是由T日的榜单数据推算出的值 Rank int `gorm:"column:rank" json:"rank"` //排名 } type TradeClassifyNameList struct { Exchange string `description:"交易所"` ExchangeEn string `description:"交易所英文"` Sort int `description:"排序字段" ` Num int `description:"品种数量"` DataTime string `description:"最新更新时间"` CurrDate string `description:"当前日期"` Items []TradeClassifyNameListItem `description:"子类"` } type TradeClassifyNameListSort []TradeClassifyNameList func (v TradeClassifyNameListSort) Len() int { return len(v) } func (v TradeClassifyNameListSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListSort) Less(i, j int) bool { return v[i].Sort < v[j].Sort } type TradeClassifyNameListItemSort []TradeClassifyNameListItem func (v TradeClassifyNameListItemSort) Len() int { return len(v) } func (v TradeClassifyNameListItemSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListItemSort) Less(i, j int) bool { return v[i].ClassifyName < v[j].ClassifyName } type TradeClassifyNameListItem struct { ClassifyName string `description:"交易分类"` Items []TradeClassifyNameListItemItem `description:"合约代码"` } type TradeClassifyNameListItemItemSort []TradeClassifyNameListItemItem func (v TradeClassifyNameListItemItemSort) Len() int { return len(v) } func (v TradeClassifyNameListItemItemSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListItemItemSort) Less(i, j int) bool { return v[i].ClassifyType < v[j].ClassifyType } type TradeClassifyNameListItemItem struct { ClassifyType string `description:"分类名称下的类型"` } type TradeClassifyName struct { ClassifyName string //分类名称 ClassifyType string //分类名称下的类型 LatestDate string //分类下最晚日期 } // GetExchangeClassify 获取交易所分类列表 func GetExchangeClassify(exchange string) (list []TradeClassifyName, err error) { tableName := "base_from_trade_" + exchange + "_index" orderStr := "classify_name DESC, classify_type asc" if exchange == "zhengzhou" { orderStr = "classify_name asc" } sql := "SELECT classify_name, classify_type FROM " + tableName + " WHERE `rank` <=20 and `rank` > 0 GROUP BY classify_name, classify_type " sql += ` ORDER BY ` + orderStr o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql).QueryRows(&list) return } type LastTimeItem struct { CreateTime time.Time } // GetExchangeLastTime 获取交易所数据最晚的时间 func GetExchangeLastTime(exchange string) (item LastTimeItem, err error) { tableName := "base_from_trade_" + exchange + "_index" sql := `SELECT create_time FROM ` + tableName + ` ORDER BY create_time desc` o := orm.NewOrmUsingDB("data") err = o.Raw(sql).QueryRow(&item) return } type GetPositionTopReq struct { Exchange string `json:"exchange" form:"exchange"` //交易所 ClassifyName string `json:"classify_name" form:"classify_name"` //分类名称 ClassifyType string `json:"classify_type" form:"classify_type"` //具体合约名称 DataTime string `json:"data_time" form:"data_time"` //请求日期，如果为空，则返回最新的榜单日期 } type GetPositionTopResp struct { BuyList GetPositionTopList `description:"多单列表"` SoldList GetPositionTopList `description:"空单列表"` CleanBuyList GetPositionTopList `description:"净多单列表"` CleanSoldList GetPositionTopList `description:"净空单列表"` DataTime string `description:"最新日期或者请求日期"` LastDataTime string `description:"最新日期"` } type GetPositionTopList struct { TotalDealValue int `description:"总计成交量"` TotalDealChange int `description:"校昨日变化"` List []GetPositionTopListItem `description:"榜单详情列表"` } type GetPositionTopListItem struct { DealShortName string `description:"成交量公司简称"` DealValue int `description:"成交量"` DealChange int `description:"成交变化量"` Rank int `description:"当前名次"` Rate string `description:"当前占比"` BeforeAllRate string `description:"排在前面的成交量总计占比（包含）"` BeforeAllValue int `description:"排在前面的成交量总计"` BeforeAllChange int `description:"排在前面的成交量增减总计"` } func GetTradePositionTop(exchange string, classifyName, classifyType, dataTime string) (list []TradePositionTop, err error) { tableName := "trade_position_" + exchange + "_top" sql := "SELECT * FROM " + tableName + " WHERE classify_name=? and classify_type=? and data_time=? and `rank` <=20 and `rank` > 0 ORDER BY deal_value desc" o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql, classifyName, classifyType, dataTime).QueryRows(&list) return } type OriginTradeData struct { Rank int `description:"排名"` CompanyName string `description:"期货公司名称"` Val int `description:"持仓量"` ValChange int `description:"持仓增减"` DataTime time.Time `description:"数据日期"` ClassifyName string `description:"品种名称"` ClassifyType string `description:"合约代码"` ValType int `description:"数据类型: 1-多单; 2-空单"` } // BaseFromTradeCommonIndex 郑商所/大商所/上期所/上期能源指标表通用字段 type BaseFromTradeCommonIndex struct { Rank int `description:"排名"` DealShortName string `description:"成交量公司简称"` DealName string `description:"成交量指标名称"` DealCode string `description:"成交量指标编码"` DealValue int `description:"成交量"` DealChange int `description:"成交变化量"` BuyShortName string `description:"持买单量公司简称"` BuyName string `description:"持买单量指标名称"` BuyCode string `description:"持买单量指标编码"` BuyValue int `description:"持买单量"` BuyChange int `description:"持买单量变化量"` SoldShortName string `description:"持卖单量公司简称"` SoldName string `description:"持卖单量指标名称"` SoldCode string `description:"持卖单量指标编码"` SoldValue int `description:"持卖单量"` SoldChange int `description:"持卖单变化量"` Frequency string `description:"频度"` ClassifyName string `description:"品种"` ClassifyType string `description:"合约"` CreateTime time.Time `description:"创建时间"` ModifyTime time.Time `description:"更新时间"` DataTime time.Time `description:"数据日期"` } // GetTradeDataByContracts 根据合约获取持仓数据 func GetTradeDataByContracts(exchange string, classifyNames, contracts []string, startDate, endDate time.Time) (items []*BaseFromTradeCommonIndex, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } var cond string var pars []interface{} if len(classifyNames) > 0 { cond += fmt.Sprintf(` AND classify_name IN (%s)`, utils.GetOrmInReplace(len(classifyNames))) pars = append(pars, classifyNames) } if len(contracts) > 0 { cond += fmt.Sprintf(` AND classify_type IN (%s)`, utils.GetOrmInReplace(len(contracts))) pars = append(pars, contracts) } if !startDate.IsZero() && !endDate.IsZero() { cond += ` AND (data_time BETWEEN ? AND ?)` pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate)) } fields := []string{"rank", "buy_short_name", "buy_value", "buy_change", "sold_short_name", "sold_value", "sold_change", "classify_name", "classify_type", "data_time"} tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s ORDER BY data_time DESC`, strings.Join(fields, ","), tableName, cond) _, err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRows(&items) return } // GetZhengzhouTradeDataByContracts 郑商所-根据合约获取持仓数据 func GetZhengzhouTradeDataByContracts(classifyNames []string, startDate, endDate time.Time) (items []*BaseFromTradeCommonIndex, err error) { var cond string var pars []interface{} if len(classifyNames) > 0 { cond += fmt.Sprintf(` AND classify_name IN (%s)`, utils.GetOrmInReplace(len(classifyNames))) pars = append(pars, classifyNames) } if !startDate.IsZero() && !endDate.IsZero() { cond += ` AND (data_time BETWEEN ? AND ?)` pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate)) } // ps.classify_name实为合约代码 fields := []string{"rank", "buy_short_name", "buy_value", "buy_change", "sold_short_name", "sold_value", "sold_change", "classify_name AS classify_type", "data_time"} sql := fmt.Sprintf(`SELECT %s FROM base_from_trade_zhengzhou_index WHERE 1=1 %s ORDER BY data_time DESC`, strings.Join(fields, ","), cond) _, err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRows(&items) return } // ContractCompanyTradeData [合约-期货公司]持仓数据 type ContractCompanyTradeData struct { Exchange string `description:"交易所"` ClassifyName string `description:"品种"` ClassifyType string `description:"合约代码"` CompanyName string `description:"期货公司名称"` IsTotal bool `description:"是否为合约加总"` StartDate time.Time `description:"数据开始日期"` EndDate time.Time `description:"数据结束日期"` DataList []*ContractCompanyTradeDataList `description:"数据序列"` } // ContractCompanyTradeDataList [合约-期货公司]持仓数据详情 type ContractCompanyTradeDataList struct { Date time.Time `description:"数据日期"` BuyVal int `description:"多单持仓量"` BuyValType int `description:"多单数据类型: 0-无值; 1-原始值; 2-推算值"` BuyChange int `description:"多单持仓增减"` BuyChangeType int `description:"多单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` SoldVal int `description:"空单持仓量"` SoldValType int `description:"空单数据类型: 0-无值; 1-原始值; 2-推算值"` SoldChange int `description:"空单持仓增减"` SoldChangeType int `description:"空单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` PureBuyVal int `description:"净多单持仓量"` PureBuyValType int `description:"净多单数据类型: 0-无值; 1-原始值; 2-推算值"` PureBuyChange int `description:"净多单持仓增减"` PureBuyChangeType int `description:"净多单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` } type BaseFromTradeGuangzhouIndex struct { BaseFromTradeGuangzhouIndexId int `orm:"column(base_from_trade_guangzhou_index_id);pk"` BaseFromTradeGuangzhouClassifyId int `description:"分类id"` IndexCode string `description:"指标编码"` IndexName string `description:"指标名称"` Frequency string `description:"频率"` Unit string `description:"单位"` StartDate string `description:"开始日期"` EndDate string `description:"结束日期"` CreateTime time.Time `description:"创建日期"` ModifyTime time.Time `description:"修改日期"` } func GetBaseFromTradeGuangzhouIndex(classifyIds []int, contracts []string, indexKeyword string) (list []*BaseFromTradeGuangzhouIndex, err error) { o := orm.NewOrmUsingDB("data") cond := `` pars := make([]interface{}, 0) if len(classifyIds) > 0 { cond += fmt.Sprintf(` AND b.base_from_trade_guangzhou_classify_id IN (%s)`, utils.GetOrmInReplace(len(classifyIds))) pars = append(pars, classifyIds) } if len(contracts) > 0 { cond += fmt.Sprintf(` AND b.contract IN (%s)`, utils.GetOrmInReplace(len(contracts))) pars = append(pars, contracts) } if indexKeyword != "" { cond += fmt.Sprintf(` AND a.index_name LIKE ?`) pars = append(pars, indexKeyword) } sql := `SELECT a.* FROM base_from_trade_guangzhou_index AS a JOIN base_from_trade_guangzhou_contract AS b ON a.base_from_trade_guangzhou_contract_id = b.base_from_trade_guangzhou_contract_id WHERE 1=1 %s` sql = fmt.Sprintf(sql, cond) _, err = o.Raw(sql, pars).QueryRows(&list) return } type BaseFromTradeGuangzhouData struct { BaseFromTradeGuangzhouDataId int `orm:"column(base_from_trade_guangzhou_data_id);pk"` BaseFromTradeGuangzhouIndexId int `description:"指标id"` IndexCode string `description:"指标编码"` DataTime time.Time `description:"数据日期"` Value float64 `description:"数据值"` QtySub float64 `description:"增减"` CreateTime time.Time `description:"创建日期"` ModifyTime time.Time `description:"修改日期"` } // GetBaseFromTradeGuangzhouDataByIndexIds 广期所-获取指标数据 func GetBaseFromTradeGuangzhouDataByIndexIds(indexIds []int, startDate, endDate time.Time) (list []*BaseFromTradeGuangzhouData, err error) { if len(indexIds) == 0 { return } cond := fmt.Sprintf(` AND base_from_trade_guangzhou_index_id IN (%s)`, utils.GetOrmInReplace(len(indexIds))) pars := make([]interface{}, 0) pars = append(pars, indexIds) if !startDate.IsZero() && !endDate.IsZero() { if startDate.Equal(endDate) { cond += ` AND data_time = ?` pars = append(pars, startDate.Format(utils.FormatDate)) } if !startDate.Equal(endDate) { cond += ` AND (data_time BETWEEN ? AND ?)` pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate)) } } sql := fmt.Sprintf(`SELECT * FROM base_from_trade_guangzhou_data WHERE 1=1 %s ORDER BY base_from_trade_guangzhou_index_id`, cond) _, err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRows(&list) return } // ContractTopRankData TOP20合约排名数据 type ContractTopRankData struct { Exchange string `description:"交易所"` DealValue int `description:"成交量"` BuyValue int `description:"多单持仓量"` BuyChange int `description:"多单变化"` SoldValue int `description:"空单持仓量"` SoldChange int `description:"空单变化"` PureBuyValue int `description:"净多单持仓量"` PureBuyChange int `description:"净多单变化"` ClassifyName string `description:"品种名称"` ClassifyType string `description:"合约代码"` DataTime time.Time `description:"数据日期"` } // GetContractTopRankData 获取合约TOP20根据当日成交量排名 func GetContractTopRankData(exchange string, classifyNames []string, dataDate time.Time) (items []*ContractTopRankData, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(classifyNames) == 0 { return } tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) // 大商所存在TOP20的rank=0 queryRank := ` rank = 999` if exchange == TradeExchangeDalian { queryRank = ` (rank = 999 OR rank = 0)` } sql := `SELECT * FROM %s WHERE data_time = ? AND classify_name IN (%s) AND %s GROUP BY classify_type ORDER BY deal_value DESC` sql = fmt.Sprintf(sql, tableName, utils.GetOrmInReplace(len(classifyNames)), queryRank) _, err = orm.NewOrmUsingDB("data").Raw(sql, dataDate.Format(utils.FormatDate), classifyNames).QueryRows(&items) return } // GetZhengzhouContractTopRankData 郑商所-获取合约根据当日成交量排名 func GetZhengzhouContractTopRankData(classifyNames []string, dataDate time.Time) (items []*ContractTopRankData, err error) { if len(classifyNames) == 0 { return } sql := `SELECT * FROM base_from_trade_zhengzhou_index WHERE data_time = ? AND classify_name IN (%s) AND rank = 999 GROUP BY classify_name ORDER BY deal_value DESC` sql = fmt.Sprintf(sql, utils.GetOrmInReplace(len(classifyNames))) _, err = orm.NewOrmUsingDB("data").Raw(sql, dataDate.Format(utils.FormatDate), classifyNames).QueryRows(&items) return } // ContractCompanyTradeEdb [合约-期货公司]指标 type ContractCompanyTradeEdb struct { Exchange string `description:"交易所"` ClassifyName string `description:"品种"` ClassifyType string `description:"合约代码"` CompanyName string `description:"期货公司名称"` IsTotal bool `description:"是否为合约加总"` ContractPosition int `description:"合约方向"` StartDate time.Time `description:"数据开始日期"` EndDate time.Time `description:"数据结束日期"` DataList []*ContractCompanyTradeEdbData `description:"数据序列"` } // ContractCompanyTradeEdbData [合约-期货公司]指标数据 type ContractCompanyTradeEdbData struct { DataTime time.Time `description:"数据日期"` Val int `description:"数据值"` } // GetClassifyNewestDataTime 获取品种最新数据日期 func GetClassifyNewestDataTime(exchange string, classifyNames []string) (dateTime time.Time, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(classifyNames) == 0 { return } tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := `SELECT data_time FROM %s WHERE classify_name IN (%s) ORDER BY data_time DESC LIMIT 1` sql = fmt.Sprintf(sql, tableName, utils.GetOrmInReplace(len(classifyNames))) err = orm.NewOrmUsingDB("data").Raw(sql, classifyNames).QueryRow(&dateTime) return } // GetGuangzhouClassifyNewestDataTime 广期所-获取品种最新数据日期 func GetGuangzhouClassifyNewestDataTime(indexIds []int) (dateTime time.Time, err error) { if len(indexIds) == 0 { return } cond := fmt.Sprintf(` AND base_from_trade_guangzhou_index_id IN (%s)`, utils.GetOrmInReplace(len(indexIds))) pars := make([]interface{}, 0) pars = append(pars, indexIds) sql := fmt.Sprintf(`SELECT data_time FROM base_from_trade_guangzhou_data WHERE 1=1 %s ORDER BY data_time DESC LIMIT 1`, cond) err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRow(&dateTime) return }