sum(if(in_out_type=1,score_num,0))-sum(if(in_out_type=2,score_num,0))as scoreTotal, sum(scoreTotal) as totalScore 统计sql中 sum的值

在这个统计 SQL 中，`sum(if(in_out_type=1,score_num,0))` 表示筛选 in_out_type 等于 1 的记录，然后对这些记录的 score_num 字段求和；`sum(if(in_out_type=2,score_num,0))` 表示筛选 in_out_type 等于 2 的记录，然后对这些记录的 score_num 字段求和。这两个求和结果相减就得到了 scoreTotal 的值。最后，`sum(scoreTotal)` 表示对所有 scoreTotal 求和，即得到总分 totalScore 的值。

相关问题

param = {'num_leaves': 31, 'min_data_in_leaf': 20, 'objective': 'binary', 'learning_rate': 0.06, "boosting": "gbdt", "metric": 'None', "verbosity": -1} trn_data = lgb.Dataset(trn, trn_label) val_data = lgb.Dataset(val, val_label) num_round = 666 # clf = lgb.train(param, trn_data, num_round, valid_sets=[trn_data, val_data], verbose_eval=100, # early_stopping_rounds=300, feval=win_score_eval) clf = lgb.train(param, trn_data, num_round) # oof_lgb = clf.predict(val, num_iteration=clf.best_iteration) test_lgb = clf.predict(test, num_iteration=clf.best_iteration)thresh_hold = 0.5 oof_test_final = test_lgb >= thresh_hold print(metrics.accuracy_score(test_label, oof_test_final)) print(metrics.confusion_matrix(test_label, oof_test_final)) tp = np.sum(((oof_test_final == 1) & (test_label == 1))) pp = np.sum(oof_test_final == 1) print('accuracy1:%.3f'% (tp/(pp)))test_postive_idx = np.argwhere(oof_test_final == True).reshape(-1) # test_postive_idx = list(range(len(oof_test_final))) test_all_idx = np.argwhere(np.array(test_data_idx)).reshape(-1) stock_info['trade_date_id'] = stock_info['trade_date'].map(date_map) stock_info['trade_date_id'] = stock_info['trade_date_id'] + 1tmp_col = ['ts_code', 'trade_date', 'trade_date_id', 'open', 'high', 'low', 'close', 'ma5', 'ma13', 'ma21', 'label_final', 'name'] stock_info.iloc[test_all_idx[test_postive_idx]] tmp_df = stock_info[tmp_col].iloc[test_all_idx[test_postive_idx]].reset_index() tmp_df['label_prob'] = test_lgb[test_postive_idx] tmp_df['is_limit_up'] = tmp_df['close'] == tmp_df['high'] buy_df = tmp_df[(tmp_df['is_limit_up']==False)].reset_index() buy_df.drop(['index', 'level_0'], axis=1, inplace=True)buy_df['buy_flag'] = 1 stock_info_copy['sell_flag'] = 0tmp_idx = (index_df['trade_date'] == test_date_min+1) close1 = index_df[tmp_idx]['close'].values[0] test_date_max = 20220829 tmp_idx = (index_df['trade_date'] == test_date_max) close2 = index_df[tmp_idx]['close'].values[0]tmp_idx = (stock_info_copy['trade_date'] >= test_date_min) & (stock_info_copy['trade_date'] <= test_date_max) tmp_df = stock_info_copy[tmp_idx].reset_index(drop=True)from imp import reload import Account reload(Account) money_init = 200000 account = Account.Account(money_init, max_hold_period=20, stop_loss_rate=-0.07, stop_profit_rate=0.12) account.BackTest(buy_df, tmp_df, index_df, buy_price='open')tmp_df2 = buy_df[['ts_code', 'trade_date', 'label_prob', 'label_final']] tmp_df2 = tmp_df2.rename(columns={'trade_date':'buy_date'}) tmp_df = account.info tmp_df['buy_date'] = tmp_df['buy_date'].apply(lambda x: int(x)) tmp_df = tmp_df.merge(tmp_df2, on=['ts_code', 'buy_date'], how='left')最终的tmp_df是什么？tmp_df[tmp_df['label_final']==1]又选取了什么股票？

根据代码逐行分析，tmp_df 是一个 DataFrame，包含了股票的信息以及回测结果。其中，选取了 label_final 为 1 的股票，也就是模型预测为涨的股票，并且过滤掉了当天涨停的股票。最终买入的股票信息保存在 buy_df 中，回测时将其与 stock_info_copy、index_df 进行合并，得到了回测的结果，并将买入的股票信息与回测结果合并，生成了最终的 tmp_df。

def Land_cover_pred_plot(array_folder,raster_file, reference_file,ML_algo, plot = False): df_train , train_array = get_data_eval(array_folder,raster_file, reference_file) df_train = df_train.dropna() print(df_train) train_array = np.array(train_array, dtype=object) tile_df = pd.DataFrame() for i, array in enumerate(train_array[0]): # print(train_array[i], train_array_name[i]) tile_df[train_array[1][i]] = np.nan_to_num(array.ravel(), copy=False) # print(train_array[0][i], train_array[1][i]) X_train, X_test, y_train, y_test = train_test_split(df_train.drop('type' , axis = 1),df_train['type'],test_size = 0.1) print(X_train) ML_algo.fit(X_train,y_train) test_pred = ML_algo.predict(X_test) confusion_mat = confusion_matrix(y_test,test_pred) classification_repo = classification_report(y_test, test_pred) test_acc = accuracy_score(y_test, test_pred) print("Confusion Matri : \n", confusion_mat) print("Classification Report : \n", classification_repo) print("Accuracy on Test : ", test_acc) pred_array = ML_algo.predict(tile_df) mask_array = np.reshape(pred_array, train_array[0][0].shape) class_sum = [] for i,j in enumerate(df_train['type'].unique()): sum = (mask_array == j).sum() class_sum.append([j,sum]) print(class_sum) print(mask_array) if plot == True: arr_f = np.array(mask_array, dtype = float) arr_f = np.rot90(arr_f, axes=(-2,-1)) arr_f = np.flip(arr_f,0) plt.imshow(arr_f) plt.colorbar() return mask_array

该函数是一个用于地表覆盖预测和绘图的函数。它需要一个包含训练数据的文件夹路径，一个栅格文件和一个参考文件作为输入。它还需要一个机器学习算法和一个布尔值作为是否要绘制图表的标志。函数调用 get_data_eval 函数来获取训练数据，并使用 train_test_split 函数将其分成训练集和测试集。然后，使用机器学习算法来拟合训练数据，预测测试数据，并计算准确度、混淆矩阵和分类报告。最后，使用训练后的模型来预测栅格文件中的地表覆盖，并将结果绘制成图表（如果 plot 参数为 True）。函数返回预测结果的数组。