protected double cacluateGroupRSquare( DataSet dataSet, String tableName, String labelName, LinearRegressionModelDB model, String groupValue, StringBuilder sb_notNull) throws OperatorException { // String notnull=sb_notNull.toString().substring(beginIndex, endIndex); // notnull.replace("where", " and "); double RSquare = 0.0; StringBuffer RSquareSQL = new StringBuffer(); StringBuffer avgSQL = new StringBuffer(); avgSQL .append(" select avg(") .append(labelName) .append(") from ") .append(tableName) .append(sb_notNull) .append(" and ") .append(StringHandler.doubleQ(groupbyColumn)) .append("=") .append(StringHandler.singleQ(groupValue)); double avg = 0.0; try { itsLogger.debug(classLogInfo + ".cacluateRSquare():sql=" + avgSQL); ResultSet rs = st.executeQuery(avgSQL.toString()); if (rs.next()) { avg = rs.getDouble(1); } } catch (SQLException e) { itsLogger.error(e.getMessage(), e); return Double.NaN; } StringBuffer predictedValueSQL = new StringBuffer(); predictedValueSQL.append(model.generatePredictedString(dataSet)); RSquareSQL.append("select 1 - sum((") .append(predictedValueSQL) .append("-") .append(labelName) .append(")*(") .append(predictedValueSQL) .append("-") .append(labelName) .append("))*1.0/sum((") .append(labelName) .append("-(") .append(avg) .append("))*(") .append(labelName) .append("-(") .append(avg) .append("))) from ") .append(tableName) .append(sb_notNull) .append(" and ") .append(StringHandler.doubleQ(groupbyColumn)) .append("=") .append(StringHandler.singleQ(groupValue)); try { itsLogger.debug("LinearRegressionImpPGGP.cacluateRSquare():sql=" + RSquareSQL); ResultSet rs = st.executeQuery(RSquareSQL.toString()); if (rs.next()) { RSquare = rs.getDouble(1); } } catch (SQLException e) { itsLogger.error(e.getMessage(), e); return Double.NaN; } return RSquare; }
protected StringBuffer createSSQLLGroup( DataSet dataSet, String tableName, Column label, String[] columnNames, HashMap<String, Double[]> coefficients2, StringBuilder sb_notNull) { StringBuffer predictedY = new StringBuffer("( case "); StringBuffer countString = new StringBuffer(" (case "); for (String groupValue : coefficients2.keySet()) { predictedY .append(" when ") .append(StringHandler.doubleQ(groupbyColumn)) .append("=") .append(StringHandler.singleQ(groupValue)) .append(" then ") .append(coefficients.get(groupValue)[coefficients.get(groupValue).length - 1]); for (int i = 0; i < columnNames.length; i++) { predictedY .append("+") .append(coefficients2.get(groupValue)[i]) .append("*\"") .append(columnNames[i]) .append("\""); } if (groupCount.get(groupValue) > (columnNames.length + 1)) { countString .append(" when ") .append(StringHandler.doubleQ(groupbyColumn)) .append("=") .append(StringHandler.singleQ(groupValue)) .append(" then ") .append(groupCount.get(groupValue)) .append(" - ") .append(columnNames.length + 1); } else { countString .append(" when ") .append(StringHandler.doubleQ(groupbyColumn)) .append("=") .append(StringHandler.singleQ(groupValue)) .append(" then ") .append(" null "); dataErrorList.add(groupValue); } } countString.append(" end )::double precision"); predictedY.append(" end "); String labelName = StringHandler.doubleQ(label.getName()); predictedY.append(")"); StringBuffer sSQL = new StringBuffer("select sqrt("); sSQL.append("sum((") .append(labelName) .append(" - ") .append(predictedY) .append(")*1.0*(") .append(labelName) .append(" - ") .append(predictedY) .append("))/") .append("( ") .append(countString) .append(")"); sSQL.append("),") .append(StringHandler.doubleQ(groupbyColumn)) .append(" from ") .append(tableName) .append(" ") .append(sb_notNull) .append(" group by ") .append(StringHandler.doubleQ(groupbyColumn)); return sSQL; }