package models import ( "encoding/json" "eta/eta_index_lib/utils" "fmt" "github.com/beego/beego/v2/client/orm" "math" "sort" "strconv" "time" ) type edbDataResidualAnalysis struct { EdbDataId int `orm:"column(edb_data_id);pk;auto" description:"自增id"` EdbInfoId int `orm:"column(edb_info_id)" description:"指标id"` EdbCode string `orm:"column(edb_code)" description:"指标编码"` DataTime string `orm:"column(data_time)" description:"数据日期"` Value float64 `orm:"column(value)" description:"数据值"` CreateTime time.Time `orm:"column(create_time)" description:"创建时间"` ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"` DataTimeStamp int64 `orm:"column(data_timestamp)"` } func init() { orm.RegisterModel(new(edbDataResidualAnalysis)) } // AddResidualAnalysisData 新增指标数据 func AddResidualAnalysisData(dataList []edbDataResidualAnalysis) (num int64, err error) { o := orm.NewOrm() num, err = o.InsertMulti(len(dataList), dataList) if err != nil { return 0, err } return num, nil } // RefreshAllCalculateResidualAnalysis 刷新残差分析 func RefreshAllCalculateResidualAnalysis(edbInfoId, source, subSource, formulaInt, moveType int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, moveFrequency string) (err error) { to := orm.NewOrm() /*to, err := o.Begin() if err != nil { return } defer func() { if err != nil { fmt.Println("RefreshAllCalculateResidualAnalysis,Err:" + err.Error()) _ = to.Rollback() } else { _ = to.Commit() } }()*/ configMapping, err := GetConfigMappingListByConditionNotBase(edbInfoId) if err != nil { return err } var edbInfoIdList []int64 for _, v := range configMapping { edbInfoIdList = append(edbInfoIdList, v.EdbInfoId) } //清空原有数据 sql := ` DELETE FROM edb_data_residual_analysis WHERE edb_info_id in (` + utils.GetOrmInReplace(len(edbInfoIdList)) + `) ` var params []interface{} for _, i := range edbInfoIdList { params = append(params, i) } _, err = to.Raw(sql, params...).Exec() if err != nil { return } //计算数据 err = refreshAllCalculateResidualAnalysis(edbInfoId, source, subSource, formulaInt, moveType, fromEdbInfo, edbCode, startDate, endDate, moveFrequency, configMapping) return } // refreshAllCalculateResidualAnalysis 刷新所有残差分析 func refreshAllCalculateResidualAnalysis(edbInfoId, source, subSource, formulaInt, moveType int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, moveFrequency string, configMapping []CalculateResidualAnalysisConfigMapping) (err error) { fmt.Println("refreshAllCalculateResidualAnalysis startDate:", startDate) calculateMappingList, err := GetCalculateMappingListByEdbInfoId(edbInfoId) if err != nil { return err } edbInfoIdA := calculateMappingList[0].FromEdbInfoId edbInfoIdB := calculateMappingList[1].FromEdbInfoId mappingList, err := GetEdbInfoListByIds([]int{edbInfoIdA, edbInfoIdB}) if err != nil { return err } var edbInfoMappingA, edbInfoMappingB *EdbInfoList for _, v := range mappingList { if v.Unit == "无" { v.Unit = "" } if v.EdbInfoId == edbInfoIdA { edbInfoMappingA = v } if v.EdbInfoId == edbInfoIdB { edbInfoMappingB = v } } if edbInfoMappingA == nil { return fmt.Errorf("指标A不存在") } if edbInfoMappingB == nil { return fmt.Errorf("指标B不存在") } // 从配置中取出时间范围 analysisConfig, err := GetResidualAnalysisConfigById(edbInfoId) if err != nil { return err } configString := analysisConfig.Config var config ResidualAnalysisConfig err = json.Unmarshal([]byte(configString), &config) // 时间处理 switch config.DateType { case 0: startDate = config.StartDate endDate = config.EndDate case 1: startDate = config.StartDate endDate = "" default: startDate = utils.GetPreYearTime(config.DateType) endDate = "" } // 原始图表信息 originalEdbList := make([]EdbInfoList, 0) originalEdbList, fullADataList, fullBDataList, err := fillOriginalChart(config, fromEdbInfo, mappingList, startDate, endDate, edbInfoMappingA, edbInfoMappingB, originalEdbList) if err != nil { return err } indexADataMap := map[string]*EdbData{} for _, indexData := range edbInfoMappingA.DataList { indexADataMap[indexData.DataTime] = indexData } // 映射图表信息 mappingEdbList, _, _, _, err := fillMappingChartInfo(config, fromEdbInfo, edbInfoMappingA, edbInfoMappingB, originalEdbList, indexADataMap, startDate, endDate, fullADataList, fullBDataList) if err != nil { return err } // 残差图表信息 residualEdbList, _, err := fillResidualChartInfo(config, fromEdbInfo, edbInfoMappingA, edbInfoMappingB, mappingEdbList) // 映射指标 与 残差指标 同步刷新 for _, mapping := range configMapping { var edbDataResidualAnalysisList []edbDataResidualAnalysis if mapping.IndexType == 1 { edbInfo, err := GetEdbInfoById(int(mapping.EdbInfoId)) if err != nil { return err } for _, edbData := range mappingEdbList[1].DataList { value, _ := strconv.ParseFloat(edbData.Value, 64) edbDataResidualAnalysisList = append(edbDataResidualAnalysisList, edbDataResidualAnalysis{ EdbInfoId: int(mapping.EdbInfoId), EdbCode: edbInfo.EdbCode, DataTime: edbData.DataTime, Value: value, CreateTime: time.Now(), ModifyTime: time.Now(), DataTimeStamp: edbData.DataTimestamp, }) } _, err = AddResidualAnalysisData(edbDataResidualAnalysisList) if err != nil { return err } } else if mapping.IndexType == 2 { edbInfo, err := GetEdbInfoById(int(mapping.EdbInfoId)) if err != nil { return err } for _, edbData := range residualEdbList[1].DataList { value, _ := strconv.ParseFloat(edbData.Value, 64) edbDataResidualAnalysisList = append(edbDataResidualAnalysisList, edbDataResidualAnalysis{ EdbInfoId: int(mapping.EdbInfoId), EdbCode: edbInfo.EdbCode, DataTime: edbData.DataTime, Value: value, CreateTime: time.Now(), ModifyTime: time.Now(), DataTimeStamp: edbData.DataTimestamp, }) } _, err = AddResidualAnalysisData(edbDataResidualAnalysisList) if err != nil { return err } } } return } func fillResidualChartInfo(config ResidualAnalysisConfig, req *EdbInfo, edbInfoMappingA *EdbInfoList, edbInfoMappingB *EdbInfoList, mappingEdbList []EdbInfoList) ([]EdbInfoList, float64, error) { // 计算公式 映射残差 = 因变量指标 - 映射指标 var edbInfoA, edbInfoB EdbInfoList if mappingEdbList[0].EdbInfoId == edbInfoMappingA.EdbInfoId { edbInfoA = mappingEdbList[0] edbInfoB = mappingEdbList[1] } else { edbInfoA = mappingEdbList[1] edbInfoB = mappingEdbList[0] } dataAList := edbInfoA.DataList edbData := make([]*EdbDataList, len(dataAList)) for i, data := range dataAList { f, _ := strconv.ParseFloat(data.Value, 64) edbData[i] = &EdbDataList{ Value: f, DataTimestamp: data.DataTimestamp, DataTime: data.DataTime, EdbInfoId: data.EdbInfoId, EdbDataId: data.EdbDataId, } } dataBList := edbInfoB.DataList // 映射指标开始时间 var startTime string if len(dataBList) > 0 { startTime = dataBList[0].DataTime } var indexDataBMap = make(map[string]*EdbData) for _, data := range dataBList { indexDataBMap[data.DataTime] = data } // 求R2 var valueB, sumValueA, averageValueA, residualQuadraticSum, totalQuadraticSum, R2 float64 for _, indexData := range edbData { // 因变量的值总和 sumValueA += indexData.Value } // 因变量平均值 averageValueA = sumValueA / float64(len(edbData)) var indexMax, indexMin float64 var edbDataResp []*EdbDataList if len(edbData) > 0 { indexMax = edbData[0].Value indexMin = edbData[0].Value for _, indexData := range edbData { if dataB, ok := indexDataBMap[indexData.DataTime]; ok { f, _ := strconv.ParseFloat(dataB.Value, 64) valueB = f } else { continue } // 总因变量平方和 totalQuadraticSum += math.Pow(indexData.Value-averageValueA, 2) // 补全残差值 indexData.Value = math.Round((indexData.Value-valueB)*10000) / 10000 // 残差平方和 residualQuadraticSum += math.Pow(indexData.Value, 2) if indexData.Value > indexMax { indexMax = indexData.Value } if indexData.Value < indexMin { indexMin = indexData.Value } // 获取映射指标之后的数据 if startTime != "" && utils.CompareDate(startTime, indexData.DataTime) { edbDataResp = append(edbDataResp, indexData) } } } // 计算R2 公式:R2=1-SSE/SST R2越大,越符合线性 R2 = 1 - 残差平方和/总平方和 R2 = 1 - residualQuadraticSum/totalQuadraticSum mappingEdb := make([]EdbInfoList, len(mappingEdbList)) copy(mappingEdb, mappingEdbList) for i, mapping := range mappingEdb { if mapping.EdbInfoId != edbInfoMappingA.EdbInfoId { toEdbData := convertEdbDataListToEdbData(edbDataResp) mappingEdb[i].DataList = toEdbData mappingEdb[i].EdbName = edbInfoMappingA.EdbName + "映射残差/" + edbInfoMappingB.EdbName if config.IndexType == 2 { if config.LeadValue > 0 { mappingEdb[i].EdbName = edbInfoMappingA.EdbName + "映射残差/" + edbInfoMappingB.EdbName + "(领先" + strconv.Itoa(config.LeadValue) + config.LeadFrequency + ")" } } } } return mappingEdb, R2, nil } func fillMappingChartInfo(config ResidualAnalysisConfig, req *EdbInfo, edbInfoMappingA *EdbInfoList, edbInfoMappingB *EdbInfoList, originalEdbList []EdbInfoList, indexADataMap map[string]*EdbData, startDate string, endDate string, fullADataList []*EdbDataList, fullBDataList []*EdbDataList) ([]EdbInfoList, float64, float64, float64, error) { // 计算公式:Y=aX+b,Y为映射后的指标,X为自变量指标 // 正序:a=(L2-L1)/(R2-R1) b=L2-R2*a // 逆序:a=(L2-L1)/(R1-R2) b=L2-R1*a // L2:左轴下限 R2:右轴上限 L1:左轴上限 R1:右轴下限 var a, b, r float64 // 映射残差 计算a,b if config.ResidualType == 1 { if config.IsOrder { a = (config.LeftIndexMax - config.LeftIndexMin) / (config.RightIndexMin - config.RightIndexMax) b = config.LeftIndexMax - config.RightIndexMin*a } else { a = (config.LeftIndexMax - config.LeftIndexMin) / (config.RightIndexMax - config.RightIndexMin) b = config.LeftIndexMax - config.RightIndexMax*a } } dataList := edbInfoMappingB.DataList // 指标B数据补充 // 新建一个切片来保存补充的数据 var replenishDataList []*EdbData for index := 0; index < len(dataList)-1; index++ { // 获取当前数据和下一个数据 beforeIndexData := dataList[index] afterIndexData := dataList[index+1] // 从最早时间开始,补充时间为自然日 for utils.IsMoreThanOneDay(beforeIndexData.DataTime, afterIndexData.DataTime) { // 创建补充数据 nextDay := utils.GetNextDay(beforeIndexData.DataTime) toTime := utils.StringToTime(nextDay) replenishIndexData := EdbData{ DataTime: nextDay, // 计算下一个自然日 DataTimestamp: toTime.UnixMilli(), Value: beforeIndexData.Value, // 可以选择使用前一天的值,或者其他逻辑来计算值 } // 将补充数据加入补充数据列表 replenishDataList = append(replenishDataList, &replenishIndexData) // 更新 beforeIndexData 为新创建的补充数据 beforeIndexData = &replenishIndexData } } // 将补充数据插入原始数据列表 dataList = append(dataList, replenishDataList...) // 排序 sort.Sort(ByDataTime(dataList)) // 拟合残差 计算a,b var coordinateList []utils.Coordinate var replenishADataList []*EdbDataList var replenishBDataList []*EdbDataList if config.ResidualType == 2 { // // 因变量指标也转换为日度 for index := 0; index < len(fullADataList)-1; index++ { // 获取当前数据和下一个数据 beforeIndexData := fullADataList[index] afterIndexData := fullADataList[index+1] replenishADataList = append(replenishADataList, beforeIndexData) // 从最早时间开始,补充时间为自然日 if utils.IsMoreThanOneDay(beforeIndexData.DataTime, afterIndexData.DataTime) { for { // 创建补充数据 nextDay := utils.GetNextDay(beforeIndexData.DataTime) toTime := utils.StringToTime(nextDay) replenishIndexData := EdbDataList{ DataTime: nextDay, // 计算下一个自然日 DataTimestamp: toTime.UnixMilli(), Value: beforeIndexData.Value, // 可以选择使用前一天的值,或者其他逻辑来计算值 } // 将补充数据加入补充数据列表 replenishADataList = append(replenishADataList, &replenishIndexData) // 更新 beforeIndexData 为新创建的补充数据 beforeIndexData = &replenishIndexData // 检查是否还需要继续补充数据 if !utils.IsMoreThanOneDay(beforeIndexData.DataTime, afterIndexData.DataTime) { break } } } } replenishADataList = append(replenishADataList, fullADataList[len(fullADataList)-1]) // 自变量指标也转换为日度 for index := 0; index < len(fullBDataList)-1; index++ { // 获取当前数据和下一个数据 beforeIndexData := fullBDataList[index] afterIndexData := fullBDataList[index+1] replenishBDataList = append(replenishBDataList, beforeIndexData) // 从最早时间开始,补充时间为自然日 if utils.IsMoreThanOneDay(beforeIndexData.DataTime, afterIndexData.DataTime) { for { // 创建补充数据 nextDay := utils.GetNextDay(beforeIndexData.DataTime) toTime := utils.StringToTime(nextDay) replenishIndexData := EdbDataList{ DataTime: nextDay, // 计算下一个自然日 DataTimestamp: toTime.UnixMilli(), Value: beforeIndexData.Value, // 可以选择使用前一天的值,或者其他逻辑来计算值 } // 将补充数据加入补充数据列表 replenishBDataList = append(replenishBDataList, &replenishIndexData) // 更新 beforeIndexData 为新创建的补充数据 beforeIndexData = &replenishIndexData // 检查是否还需要继续补充数据 if !utils.IsMoreThanOneDay(beforeIndexData.DataTime, afterIndexData.DataTime) { break } } } } replenishBDataList = append(replenishBDataList, fullBDataList[len(fullBDataList)-1]) // replenishADataList --> map replenishADataMap := make(map[string]*EdbDataList) for _, indexData := range replenishADataList { if (utils.StringToTime(indexData.DataTime).After(utils.StringToTime(startDate)) || utils.StringToTime(indexData.DataTime).Equal(utils.StringToTime(startDate))) && (endDate == "" || utils.StringToTime(indexData.DataTime).Before(utils.StringToTime(endDate))) { replenishADataMap[indexData.DataTime] = indexData } } for _, indexData := range replenishBDataList { if _, ok := replenishADataMap[indexData.DataTime]; ok { coordinate := utils.Coordinate{ X: indexData.Value, Y: replenishADataMap[indexData.DataTime].Value, } coordinateList = append(coordinateList, coordinate) } } a, b = utils.GetLinearResult(coordinateList) r = utils.ComputeCorrelation(coordinateList) } // 填充映射指标值 使得时间长度一致 dataList = FillDataBList(dataList, edbInfoMappingA) // 根据指标A的时间key,在B的映射指标中筛选出对应的值 var dataBList []*EdbDataList var indexMax, indexMin string if len(dataList) > 0 { indexMax = dataList[0].Value indexMin = dataList[0].Value for _, indexData := range dataList { if _, ok := indexADataMap[indexData.DataTime]; ok { indexDataCopy := *indexData // 计算指标B映射值 f, _ := strconv.ParseFloat(indexData.Value, 64) indexDataCopy.Value = fmt.Sprintf("%f", math.Round((a*f+b)*10000)/10000) // 比较最大值 if indexData.Value > indexMax { indexMax = indexData.Value } // 比较最小值 if indexData.Value < indexMin { indexMin = indexData.Value } // 将副本添加到 dataBList copyValue, _ := strconv.ParseFloat(indexDataCopy.Value, 64) dataBList = append(dataBList, &EdbDataList{ DataTime: indexDataCopy.DataTime, DataTimestamp: indexDataCopy.DataTimestamp, EdbDataId: indexDataCopy.EdbDataId, EdbInfoId: indexDataCopy.EdbInfoId, Value: copyValue, }) } } } mappingEdbList := make([]EdbInfoList, len(originalEdbList)) copy(mappingEdbList, originalEdbList) for i, mapping := range mappingEdbList { if mapping.EdbInfoId != edbInfoMappingA.EdbInfoId { mappingEdbList[i].EdbInfoId = 0 mappingEdbList[i].EdbCode = "" mappingEdbList[i].EdbName = edbInfoMappingB.EdbName + "映射" + edbInfoMappingA.EdbName if config.IndexType == 2 { if config.LeadValue > 0 { mappingEdbList[i].EdbName = edbInfoMappingB.EdbName + "映射" + edbInfoMappingA.EdbName + "(领先" + strconv.Itoa(config.LeadValue) + config.LeadFrequency + ")" } } edbData := convertEdbDataListToEdbData(dataBList) mappingEdbList[i].DataList = edbData } } return mappingEdbList, a, b, r, nil } // FillDataBList 填充B的数据 使得与A的时间保持一致 func FillDataBList(dataList []*EdbData, edbInfoMappingA *EdbInfoList) []*EdbData { dataAList := edbInfoMappingA.DataList for utils.StringToTime(dataList[len(dataList)-1].DataTime).Before(utils.StringToTime(dataAList[len(dataAList)-1].DataTime)) { // 使用A的时间填充时间差 timeDiff := utils.GetNextDayN(dataList[len(dataList)-1].DataTime, 1) // 创建新的数据点并填充 前值填充 newDataPoint := &EdbData{ DataTime: timeDiff, Value: dataList[len(dataList)-1].Value, DataTimestamp: utils.StringToTime(timeDiff).UnixMilli(), } // 将新数据点添加到dataList末尾 dataList = append(dataList, newDataPoint) } return dataList } func fillOriginalChart(config ResidualAnalysisConfig, req *EdbInfo, mappingList []*EdbInfoList, startDate string, endDate string, edbInfoMappingA *EdbInfoList, edbInfoMappingB *EdbInfoList, originalEdbList []EdbInfoList) ([]EdbInfoList, []*EdbDataList, []*EdbDataList, error) { var fullADataList, fullBDataList []*EdbDataList for _, v := range mappingList { var edbInfoMapping EdbInfoList edbInfoMapping.EdbName = v.EdbName // 获取图表中的指标数据 dataList, err := GetEdbDataList(v.Source, v.SubSource, v.EdbInfoId, startDate, endDate) if err != nil { return nil, nil, nil, fmt.Errorf("获取指标数据失败,Err:%s", err.Error()) } data := convertEdbDataListToEdbData(dataList) // 重新获取指标数据 产品要求需要和计算指标-拟合残差逻辑保持一致 fullDataList, err := GetEdbDataList(v.Source, v.SubSource, v.EdbInfoId, "", "") if err != nil { return nil, nil, nil, fmt.Errorf("获取指标数据失败,Err:%s", err.Error()) } if v.EdbInfoId == edbInfoMappingB.EdbInfoId { // 领先指标 dataList进行数据处理 if config.IndexType == 1 { if config.LeadValue < 0 { return nil, nil, nil, fmt.Errorf("领先值不能小于0") } else if config.LeadValue > 0 { edbInfoMapping.EdbName = v.EdbName + "(领先" + strconv.Itoa(config.LeadValue) + config.LeadFrequency + ")" for _, indexData := range dataList { switch config.LeadFrequency { case "天": indexData.DataTime = utils.GetNextDayN(indexData.DataTime, config.LeadValue) case "周": indexData.DataTime = utils.GetNextDayN(indexData.DataTime, config.LeadValue*7) case "月": indexData.DataTime = utils.TimeToString(utils.AddDate(utils.StringToTime(indexData.DataTime), 0, config.LeadValue), utils.YearMonthDay) case "季": indexData.DataTime = utils.TimeToString(utils.AddDate(utils.StringToTime(indexData.DataTime), 0, config.LeadValue*3), utils.YearMonthDay) case "年": indexData.DataTime = utils.TimeToString(utils.AddDate(utils.StringToTime(indexData.DataTime), config.LeadValue, 0), utils.YearMonthDay) } indexData.DataTimestamp = utils.StringToTime(indexData.DataTime).UnixMilli() } } } edbInfoMappingB.DataList = data fullBDataList = fullDataList } else { edbInfoMappingA.DataList = data fullADataList = fullDataList } edbInfoMapping.EdbInfoId = v.EdbInfoId edbInfoMapping.EdbCode = v.EdbCode edbInfoMapping.Unit = v.Unit edbInfoMapping.Frequency = v.Frequency edbInfoMapping.Source = v.Source edbInfoMapping.SourceName = v.SourceName edbInfoMapping.LatestDate = v.LatestDate edbInfoMapping.LatestValue = v.LatestValue edbInfoMapping.DataList = data originalEdbList = append(originalEdbList, edbInfoMapping) } return originalEdbList, fullADataList, fullBDataList, nil } func convertEdbDataListToEdbData(edbDataLists []*EdbDataList) []*EdbData { var edbDataList []*EdbData for _, edbData := range edbDataLists { data := &EdbData{ DataTime: edbData.DataTime, DataTimestamp: edbData.DataTimestamp, EdbDataId: edbData.EdbDataId, EdbInfoId: edbData.EdbInfoId, Value: fmt.Sprintf("%f", edbData.Value), } edbDataList = append(edbDataList, data) } return edbDataList } type ByDataTime []*EdbData func (a ByDataTime) Len() int { return len(a) } func (a ByDataTime) Swap(i, j int) { a[i], a[j] = a[j], a[i] } func (a ByDataTime) Less(i, j int) bool { t1 := utils.StringToTime(a[i].DataTime) t2 := utils.StringToTime(a[j].DataTime) return t1.Before(t2) }